Oracle Database Archive Logging — Where are my archive logs?

Archive
log destinations can be tricky when defining or viewing in Oracle Database. Stick to a few common
rules and never get lost.

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
  1. 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
  1. 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'
  1. 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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles