Oracle Archivelog Mode Tips Oracle Tips by Burleson Consulting

Administer the Archived Redo Logs

An Oracle database can run in one of two modes. By default, the database is created in NOARCHIVELOG mode. This command will check to see if you have altered your database to run in ARCHIVELOG mode.  See my notes on how to check if running in ARCHIVELOG mode.

$ sqlplus /nolog
SQL> connect / as sysdba;

connected.

SQL> archive log list;

SQL> select log_mode from v$database;

When in NOARCHIVELOG mode the database runs normally, but there is no capacity to perform any type of point in time recovery operations or online backups. Thus, you have to shutdown the database to back it up, and when you recover the database you can only recover it to the point of the last backup. While this might be fine for a development environment, the big corporate types tend to frown when a weeks worth of current production accounting data is lost forever.

Using the ARCHIVELOG Mode

So, if you wish to avoid the wrath of the CEO and angry end-users, you will want to run Oracle in ARCHIVELOG mode. In ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via the ARCH process. The ARCH process copies the archived redo log files to one or more archive log destination directories.

The use of ARCHIVELOG mode requires some configuration of the database. First you must put the database in ARCHIVELOG mode and you must also configure the ARCH process, and prepare the archived redo log destination directories.

There are some down sides to running the database in ARCHIVELOG mode. For example, once an online redo log has been filled, it cannot be reused until it has been archived. If Oracle cannot archive the online redo log (for example, the destination directory for the archived redo logs is filled up), it will switch to the next online redo log and keep working. At the same time, Oracle will continue to try to archive the log file.

Unfortunately, once the database runs out of available online redo logs, we have a problem.  If the log files can not be written out, then Oracle would have to overwrite them.  This is not good because it means we would lose the data that was in those files since Oracle could not archive the file.  As a result of this, in an effort to protect the database, Oracle will not overwrite data in an online redo log file until that log file has been archived.  Until the file has been archived, the database will simply stop processing user requests.  Once the log file has been archived, the database will be freed, and processing can proceed as normal.

You can see how an incorrect configuration of the database when it is in ARCHIVELOG mode can eventually lead to the database suspending operations because it can not archive the current online redo logs.

In the next sections we will look at how to configure the database for ARCHIVELOG mode and how to put the database in ARCHIVELOG mode.

Configuring the database for ARCHIVELOG Mode

One of the main features of a database that is in ARCHIVELOG mode is that it generates copies of the online redo logs called archived redo logs. By default in Oracle Database 10g and beyond, Oracle will send archived redo logs to the flash recovery area and we recommend this configuration.

To properly setup the flash recovery area, you will want to set two parameters as seen in the following list:

• db_recovery_file_dest - ORACLE_BASE/flash_recovery_area - This is the location of the flash recovery area.

• db_recovery_file_dest_size - 2g - This is the maximum size that can be used by the flash recovery area.  If this size limit is exceeded, you must clear out space or database operations will eventually stall.

Use the alter system command to set these parameters if you do not want to use the default values.  You will find examples of the use of the alter system command to change parameters earlier in this chapter.  We recommend that the db_recovery_file_dest parameter be set to a directory location that is separate from the location of the Oracle software, your redo logs, and your data files.  You do not want to accidentally fill up ORACLE_HOME or cause performance issues due to contention.

When the flash recovery area is configured, a directory for the database will be created in the location defined by the db_recovery_file_dest parameter.  For example, our database has a directory called:

C:\Oracle\product\flash_recovery_area\BOOKTST

Under this directory are individual directories for various file types such as ARCHIVELOG where the archived redo logs will reside.

In earlier versions of Oracle you had to enable a special Oracle process called ARCH by setting another parameter. Oracle Database 10g does not require this. When the database is in ARCHIVELOG mode, it will start the ARCH process automatically.

Putting the database in ARCHIVELOG Mode

Once you have configured the flash recovery area, you can put the database in ARCHIVELOG mode. Unfortunately, this requires that the database be shutdown first with the shutdown command (however, from earlier in the chapter, we note that shutdown immediate is the best option). Once you have shutdown the database, you will start the database in mount mode with the startup mount command. Then put the database in ARCHIVELOG mode, and finally open the database. Here is an example of how this all works from the command line:

Once the database is in ARCHIVELOG mode, it will start generating archived redo logs. It's always a good idea to make sure that the archived redo logs are getting generated. To do this, first force a log switch with the alter system switch logfile command. Then check the flash recovery area to make sure an archived redo log is created.

Note!  Oracle flash recovery area re-named to fast recovery area

The archived redo logs will be in the flash recovery area in the ARCHIVELOG directory. Under that directory you will find individual directories, each represents a different date such as 2005_03_09 for March 3, 1005. The directory structure on my computer looks like this:

C:\Oracle\product\flash_recovery_area\BOOKTST\ARCHIVELOG\2005_03_09

It might look a little different on your computer (sometimes Oracle does different things on different Operating Systems) but it should be pretty easy to figure it out.

Now, go to the directory that is named for today?s date. In my case, I?ll go to the 2005_03_16 directory. Next, we do a directory listing in that directory and you should see archived redo logs in the directory. Here is an example of what you will see on your computer:

If you are seeing files get generated here, you know archiving is working all right.

Archived Redo Log Data Dictionary Views

Oracle provides data dictionary views for the archived redo logs as seen in this list:

• v$archived_log - Information about archived redo logs.

• v$parameter - Shows the location of the flash recovery area where archived redo logs are created.

• v$log_history - Contains information on previous redo logs

NOTE:  In RAC, a separate set of archive log files is created by each instance. Since each RAC instance has its own redo log files, the corresponding archive log files are produced when the log switch takes place. The archive log files may be written to a local file system or to a cluster file system. Oracle does not insist upon a particular type of file system. Writing to a clustered file system has the added advantage of being available to archive all the nodes.  More information is available on RAC archive log files is available  HERE. 

From the Oracle? Database Installation Guide 10g Release 1 (10.1) for UNIX Systems: AIX-Based Systems, hp HP-UX PA-RISC (64-bit), hp Tru64 UNIX, Linux x86, and Solaris Operating System (SPARC), the stated benefits include the ability to install different products with the same release number in the same Oracle base directory as well as the ability to install the same product more than once in the same Oracle base directory.

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It?s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.