After reading through some Oracle books, I still have trouble on these bits. What are the primary uses of a data dictionary? What is the function and purpose of the redo log files, and why do the redo log files get archived even though all databases are set to noarchivelog mode by default? Isn't the background process LGWR? Where can I find information on an Oracle instance?
As you already know, many different objects are stored in the database. First, we store tables, lots of them. And we have users who own and/or access the data in those tables. There are indexes, views, stored procedures, functions and other objects as well. In order for the database to do its job, it needs to know about all of these objects. For instance, when you select data from a table, the database needs to determine if that table even exists! If it exists, do the columns you specified exist in that table? Do you have the requisite privileges to access data in that table? If you query a view, what is the underlying SQL text of that view? If you access a stored procedure, what code makes up that procedure?
So many questions go on behind the scenes. Where does the database turn to answer these questions? It goes to the Data Dictionary. The Data Dictionary holds information about the objects in the database. The database system manages the Data Dictionary for you. For example, when you create a new table, the Data Dictionary will be updated to reflect this change to the database.
The main purpose of the redo logs are to capture all transactions in the database. If a disaster strikes, one can restore the database from a backup and then "roll forward" through all of the transactions since that backup was taken. A transaction is not committed until that transaction is recorded in the redo logs. Therefore all committed transactions can be recovered. In short, the redo logs aid in recovery of the database.
By default, the database is in NOARCHIVELOG mode. In ARCHIVELOG mode, when an "online" redo log fills up, it is copied to the archive log destination. In NOARCHIVELOG mode, when an online redo log fills up, it will just get written over the next time that online redo log is used. You still need online redo logs, even if you are not archiving transactions. Archiving will only take place when the database configuration is changed. If archiving is taking place in your system, then someone changed the configuration.
LGWR is a background process called Log Writer. It's main purpose is to write transactions to the online redo logs. Recall that I stated above that a transaction is not complete until that transaction is recorded in the online redo logs. It is LGWR's job to record this information.
There are some good books on the market. One that I suggest is the Oralce 9i DBA Handbook by Loney available on Oracle Press. Another great source of information is the Oracle 9i Concepts Guide in the Oracle documentation!
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.