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:
- shutdown the database
- add an entry in the init.ora file such as
*.log_archive_dest=/disk1/arch
- startup the database
Please note, for our three commands
that:
- The archive destination from the
ARCHIVE LOG LIST command is /disk1/arch - From the V$ARCHIVE_DEST view
there is now defined a LOG_ARCHIVE_DEST_1. Where is my LOG_ARCHIVE_DEST
definition? - 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:
- shutdown the database
- 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
- startup the database
Again, looking at our three
commands below we now find that:
- The archive destination from the
ARCHIVE LOG LIST command is /disk2/arch. Ok where is /disk1/arch? - 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? - 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:
- shutdown the database
- replace the init.ora parameters with two destinations such
as:
*.log_archive_dest_1 = 'LOCATION=/disk1/arch' *.log_archive_dest_2 = 'LOCATION=/disk2/arch'
- 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
- 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. - From the V$ARCHIVE_DEST LOG_ARCHIVE_DEST_1
and LOG_ARCHIVE_DEST_2 are /disk1/arch and /disk2/arch respectively. - 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.
