Pay Attention to your Oracle Database Archive Logging Function

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:

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.

»


See All Articles by Columnist James Koopmann