Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Feb 16, 2010

Pay Attention to your Oracle Database Archive Logging Function

By James Koopmann

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.

[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:

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date