Pay Attention to your Oracle Database Archive Logging Function

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.


[[email protected] 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.

»


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