Oracle Database Archive Logging -- Where are my archive logs?

March 4, 2010

Let’s begin with the basics. In the last article, I ventured down the path of a default installation to show where the destinations for archive logs where. In this article, as default installations aren’t quite adequate, I'd like to progress through the various different options available to uniquely identify these archive log destinations.

To get started, from the last article, remember that Oracle placed archive logs in a default directory of $ORACLE_HOME/dbs and, if flash recovery was enabled during installation, in the USE_DB_RECOVERY_FILE_DEST, which equated to the init.ora parameter db_recovery_file_dest. Using some very simple commands and SQL, our system looked something like this.

From the use of the ARCHIVE LOG LIST command:

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

From the use of 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

From a query against the V$ARCHIVE_DEST view:

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.

To get things started, let’s first comment out or remove the init.ora parameter for db_recovery_file_dest. This will, if you were to look in the init.ora file, give us a system that has no parameters defined for archive log destinations but has archive logs still going to the default location of $ORACLE_HOME/dbs.

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   INACTIVE
10 rows selected.

The problem with this, as stated earlier, is that we are still relying upon the Oracle default mechanism to tell us where to place our archive log files. Having a default location, when a DBA might not know much about archiving is a good thing but putting those archive logs under the $ORACLE_HOME directory is clearly not the best place for them to go. Going one step further and taking ourselves just a little above a true bottom-of-the-barrel base configuration we can set up just one destination for archive log files and not rely on some default dictated by Oracle—giving us more control of what is actually happening within our database. The process we should be following here is simply:

1. shutdown the database
2. add an entry in the init.ora file such as

*.log_archive_dest=/disk1/arch

3. startup the database

Please note, for our three commands that:

1. The archive destination from the ARCHIVE LOG LIST command is /disk1/arch
2. From the V$ARCHIVE_DEST view there is now defined a LOG_ARCHIVE_DEST_1. Where is my LOG_ARCHIVE_DEST definition?
3. From the SHOW PARAMETER command, the value of LOG_ARCHIVE_DEST is /disk1/arch.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /disk1/arch
Oldest online log sequence     29
Next log sequence to archive   31
Current log sequence           31
 
SQL> select dest_name,status,destination from V$ARCHIVE_DEST;
DEST_NAME                      STATUS    DESTINATION
------------------------------ --------- ------------------------
LOG_ARCHIVE_DEST_1             VALID     /disk1/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            INACTIVE
10 rows selected.
 
SQL> show parameter archive_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
log_archive_dest                     string      /disk1/arch
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

I hope that you noticed the inconsistency here as Oracle, using the LOG_ARCHIVE_DEST init.ora parameter has equated it to the LOG_ARCHIVE_DEST_1 destination in the V$ARCHIVE_DEST view. I think this is a bit odd to say the least and a cause of some minor confusion for those of us who like any form of consistency of parameter usage. Now, if we were coming into this database cold turkey, we have to not only use the commands above to interrogate archive log placement BUT we also have to look at the physical init.ora parameter file to see that the actual parameter name was used.

The above configuration is what I’d like to call a true baseline or rock-bottom configuration for archive log destinations. It provides archiving to a single destination and can have major implications associated with recovery of an Oracle database if there happened to be some form of disk failure on /disk1.

In order to “beef up” the configuration for archive log destinations, and not have a single point of failure, it is suggested to multiplex archive logs to multiple destinations. As a step in that direction, Oracle provides another init.ora parameter called LOG_ARCHIVE_DUPLEX_DEST that can be used to specify another archive log destination (different than LOG_ARCHIVE_DEST) where Oracle will simultaneously write a copy of every archive log generated. The process we should be following here is simply:

1. shutdown the database
2. add another entry in the init.ora file so there are two archive destinations such as:

log_archive_dest=/disk1/arch

log_archive_duplex_dest=/disk2/arch

3. startup the database

Again, looking at our three commands below we now find that:

1. The archive destination from the ARCHIVE LOG LIST command is /disk2/arch. Ok where is /disk1/arch?
2. From the V$ARCHIVE_DEST view there is now defined a LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2. Where is my LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST definitions?
3. From the SHOW PARAMETER command, the values of LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST are /disk1/arch and /disk2/arch respectively.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /disk2/arch
Oldest online log sequence     31
Next log sequence to archive   33
Current log sequence           33
 
SQL> select dest_name,status,destination from V$ARCHIVE_DEST;
DEST_NAME                      STATUS    DESTINATION
------------------------------ --------- -----------------------
LOG_ARCHIVE_DEST_1             VALID     /disk1/arch
LOG_ARCHIVE_DEST_2             VALID     /disk2/arch
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            INACTIVE
10 rows selected.
 
SQL> show parameter log_archive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
log_archive_dest                     string      /disk1/arch
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_duplex_dest              string      /disk2/arch

Again, I hope that you picked up on the inconsistency here as Oracle, through the use of the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST init.ora parameter has equated them to the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 destinations respectively in the V$ARCHIVE_DEST view. In addition, I think it just a bit strange that /disk2/arch was chosen as the archive destination for the ARCHIVE LOG LIST command. Clearly, the ARCHIVE LOG LIST command should not be used in any form to determine the archive log destinations.

So my basic question here is why can’t we define the archive log destinations and get something back that actually shows what we’ve defined? Clearly, the methods above only offer indirection and send a mass of miss-matched signals.

Instead, what we really need to do here is start using the LOG_ARCHIVE_DEST_n parameters, as it would seem that this is where Oracle is moving all attempts to use other init.ora parameters. Note that there are ten different archive log destinations that you could use. However, for our purposes here, just having a second copy, we can begin to use these parameters by:

1. shutdown the database
2. replace the init.ora parameters with two destinations such as:

*.log_archive_dest_1 = 'LOCATION=/disk1/arch'

*.log_archive_dest_2 = 'LOCATION=/disk2/arch'

3. startup the database

Taking note the keyword ‘LOCATION=’, quotes, and lack of spaces in the LOCATION clause gives us a slightly more standard way of naming and numbering the archive log destinations. Now when we look at this configuration with our three commands we get the following. Again noticing

1. The archive destination from the ARCHIVE LOG LIST command is /disk2/arch. It really looks like this command just takes the last archive log destination defined.
2. From the V$ARCHIVE_DEST LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2 are /disk1/arch and /disk2/arch respectively.
3. From the SHOW PARAMETER command the values of LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_1 are LOCATION=/disk1/arch and LOCATION+/disk2/arch respectively.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /disk2/arch
Oldest online log sequence     30
Next log sequence to archive   32
Current log sequence           32
 
SQL> select dest_name,status,destination from V$ARCHIVE_DEST;
DEST_NAME                      STATUS    DESTINATION
------------------------------ --------- --------------
LOG_ARCHIVE_DEST_1             VALID     /disk1/arch
LOG_ARCHIVE_DEST_2             VALID     /disk2/arch
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            INACTIVE
10 rows selected.
 
SQL> show parameter archive_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/disk1/arch
log_archive_dest_10                  string
log_archive_dest_2                   string      LOCATION=/disk2/arch
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 

Again, there seems to be some smoke and mirrors here but this method of configuration, using the LOG_ARCHIVE_DEST_n parameters, gives us the best methods of defining and viewing archive log destinations. While the ARCHIVE LOG LIST command gives us a quick overview of archive status, it clearly should not be used to report on destinations. The V$ARCHIVE_DEST view gives us a clear view into where on disk these destinations are and , when using the LOG_ARCHIVE_DEST_n parameters, closely relates to what was entered in the init.ora parameter file (minus the LOCATION clause). The SHOW PARAMETER command shows us exactly what has been entered in the init.ora parameter file and equates to the actual parameter names if we stick to using the LOG_ARCHIVE_DEST_n parameters. Very simply, if you want to simplify the task of viewing archive log destinations that equate most closely to what was entered in the init.ora parameter file you should stick to using the LOG_ARCHIVE_DEST_n parameters. Anything else sends you down a rabbit trail.

» See All Articles by Columnist James Koopmann