Posted Feb 16, 2010

Pay Attention to your Oracle Database Archive Logging Function

By James Koopmann

Ignore your archive logging function and your Oracle database will soon become crippled and unusable. Learn how to pay attention, starting with ensuring that Automatic archival is Enabled, how to switch into archive log mode if it's not enabled and how to locate where redo logs are archived.

It isn't too much of a stretch to say that archive logging within Oracle is one of the very first areas a DBA learns to master. Without paying proper attention to the archive logging function, an Oracle database could soon become crippled and unusable.

For the record, if this happens to be the first content you read on archive logging, archive logging is nothing more than a the process of saving filled groups of redo log files to disk, either one or multiple locations. We wont get into what redo logs are as that is a whole different discussion and tuning exercise but lets just say that there is some very important information being saved in the redo logs and moved into archive logs if you ever want to recover your database from a variety of failures. For this reason, and the fact it has been quite some years since I actually looked at the manuals in regards to archive logging, Ive decided to give it a read and see if there is anything new and exciting.

One of the first concerns when looking at archive logging is actually having your database recognize the fact that it is in archive log mode. You see, you can actually run an Oracle database in two modes. The first being non-archive log mode (no archiving of redo log files) and archive log mode (archiving of redo log files), with which we are concerned. To ensure your Oracle database is running in archive log mode (ARCHIVELOG mode) you need just issue one command when logged into SQL*Plus. It looks something like this:

SQL> ARCHIVE LOG LIST Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 11 Next log sequence to archive 13 Current log sequence 13

Nothing too exciting but we can easily see that the database log mode is Archive Mode and that Automatic archival is Enabled; both of which are very important so that a DBA doesnt need to get involved to manually archive filled redo.

The tricky part in reading this output comes when looking at the Archive destination. In this case, our archive destination is USE_DB_RECOVERY_FILE_DEST. Normally when we see these types of things in Oracle, they relate to a parameter that we can easily see using the SHOW PARAMETER command. Unfortunately, under this scenario those options do not yield any results.

SQL> SHOW PARAMETER USE_DB_RECOVERY_DEST SQL>

The reason for this is that the use of the parameter USE_DB_RECOVERY_FILE_DEST is actually a redirect for the DB_RECOVERY_FILE_DEST, which is the default location for Oracles flash recovery area. This location on disk can be seen through the SHOW PARAMETER command.

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST NAME TYPE VALUE ------------------------ ----------- ------ db_recovery_file_dest string /opt/app/oracle/flash_recovery_area db_recovery_file_dest_size big integer 2G

If you looked on disk for the db_recovery_file_dest location youd quickly find a tree structure such as the following where there is a clear indication of where archive logs are on a daily basis.

[oracle@ludwig flash_recovery_area]$ tree . |-- DB11 | |-- archivelog | | |-- 2010_01_29 | | | |-- o1_mf_1_10_5p5cfv1p_.arc | | | |-- o1_mf_1_2_5p575dok_.arc | | | |-- o1_mf_1_3_5p57qnbl_.arc | | | |-- o1_mf_1_4_5p58gtfy_.arc | | | |-- o1_mf_1_5_5p598sjc_.arc | | | |-- o1_mf_1_6_5p59cnwc_.arc | | | |-- o1_mf_1_7_5p59xnom_.arc | | | |-- o1_mf_1_8_5p5bmhg1_.arc | | | `-- o1_mf_1_9_5p5cbm6z_.arc | | `-- 2010_02_02 | | |-- o1_mf_1_11_5pkwyv1l_.arc | | `-- o1_mf_1_12_5pl1cdns_.arc | `-- onlinelog

Now before we venture any further, it is advantageous to know that the use of DB_RECOVERY_FILE_DEST is the default for Oracle. This is most assuredly a good thing when you start to take into consideration that Oracle will help manage all files that are located in the flash recovery area automaticallyeliminating many of the tasks DBAs would normally perform to keep a database up and running.

One of the more interesting aspects of a default installation (clicking through dbca without much thought), around archive logging, is the parameters Oracle initializes and uses. One would expect to see, when issuing the SHOW PARAMETER command for ARCHIVE parameters, some values that would pinpoint where redo logs were being archived. But as you can see, the following SHOW PARAMATER ARCHIVE command actually produces nothing of value; at least where log archive destinations are concerned.

SQL> show parameter archive NAME TYPE VALUE ------------------------ ----------- ------------------------------ archive_lag_target integer 0 log_archive_config string log_archive_dest string log_archive_dest_1 string log_archive_dest_10 string log_archive_dest_2 string log_archive_dest_3 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string log_archive_dest_9 string log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_2 string enable log_archive_dest_state_3 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable log_archive_dest_state_9 string enable log_archive_duplex_dest string log_archive_format string %t_%s_%r.dbf log_archive_local_first boolean TRUE log_archive_max_processes integer 4 log_archive_min_succeed_dest integer 1 log_archive_start boolean FALSE log_archive_trace integer 0 standby_archive_dest string ?/dbs/arch

It actually takes a query against the V$ARCHIVE_DEST view to see something. As you can see, there are two active (VALID) log archive destinations--one being the flash recovery area (USE_DB_RECOVERY_FILE_DEST) and one for destination 1. Please note here that the log archive destination 1 is actually a path that consists of $ORACLE_HOME plus a prefix of arch. I am always shocked to see this after an Oracle database creation, as this places archive logs dead smack down in with the Oracle software; clearly a no-no. I guess the only rational behind this is that Oracle suggests multiplexing archive logs (making copies on two distinct and separate disk areas) and they figure flash recovery and software will be located on separate disks. The obvious warning here is that we often allocate storage very tightly for software and you could run out of space in the LOG_ARCHIVE_DEST_1 area quicker than youd like.

SQL> select dest_name,status,destination from V$ARCHIVE_DEST; DEST_NAME STATUS DESTINATION -------------------- --------- ---------------------------------------- LOG_ARCHIVE_DEST_1 VALID /opt/app/oracle/product/11.1.0/db_1/dbs/arch LOG_ARCHIVE_DEST_2 INACTIVE LOG_ARCHIVE_DEST_3 INACTIVE LOG_ARCHIVE_DEST_4 INACTIVE LOG_ARCHIVE_DEST_5 INACTIVE LOG_ARCHIVE_DEST_6 INACTIVE LOG_ARCHIVE_DEST_7 INACTIVE LOG_ARCHIVE_DEST_8 INACTIVE LOG_ARCHIVE_DEST_9 INACTIVE LOG_ARCHIVE_DEST_10 VALID USE_DB_RECOVERY_FILE_DEST 10 rows selected.

If by chance your database is not in archive log mode, fear not, there are two easy ways you can switch into archive log mode; through Enterprise Manager if you happen to be a GUI DBA or through command line if youre still hard-core and not fond of GUIs. Please note that in both cases you may want to take a backup of your database (before and after switching archive mode) just in case something goes wrong.

For Enterprise Manager, configure Archive log mode and a flash recovery area by:

1. Click on the Availability tab 2. Click on Recovery Settings under Setup under the Backup/Recovery section. This will bring up the Recovery Settings page. 3. Check the ARCHIVELOG mode checkbox to within the Media Recovery section to place this database in ARCHIVELOG mode. 4. Within the Media Recovery section you can see that the last archiving location is set to USE_DB_RECOVERY_FILE_DEST. 5. Enter a Flash Recovery Area Location (DB_RECOVERY_FILE_DEST) within the Flash Recovery section. 6. You should also specify a Flash Recovery Area Size. 7. Check the Enable Flashback Database checkbox to enable flashback logging. 8. Choose a Flashback Retention Time that makes sense for your database. 9. Click the Apply button to save. 10. Click the Yes button to restart the database when prompted to restart the database. 11. Enter your Host and Database Credentials and then click the OK button on the Restart Database: Specify Host and Target Database Credentials page. 12. Click Yes on the Restart Database:Confirmation page to restart the database.

At this point, you can now have archive log go to the flash recovery areamaking storage, recovery, and persistence of those files much easier. Stick around for subsequent articles as I venture past a default installation and into ways to take us beyond the basic default settings, detect proper archive log setup, detection of failures, and general ways of configuring archive logging.