Oracle: archivelog mode

Title:

Oracle: archivelog mode

Author:

Douglas O’Leary <dkoleary@olearycomputers.com>

Description:

Oracle: archivelog mode

Date created:

08/04/2010

Date updated:

08/04/2010

Disclaimer:

Standard: Use the information that follows at your own risk. If you screw up a system, don’t blame it on me…

When oracle copies the redo logs before overwriting them, it is said to be in archivelog mode. There used to be a whole lot more debate over whether or not to put a database into archivelog mode as there was some significant overhead. With faster systems and cheaper disks, those debates seem to have gone away. There was a post on OTN recently that suggested it wasn’t even possible to run an 11g database in noarchivelog mode.

Chapter 7 of the Oracle Admin guide covers Managing archived redo logs.

There are a number of implications of running in noarchivelog mode - most of which have to do with recovery. In a nutshell, if something goes awry, you’re recoverying from the last cold backup - any changes since then are gone. By going awry I mean any type of disk loss, datafile corruption, user or program error, etc. You are pretty much at the game over stage.

The biggest implications for running in archivelog mode is space. The archived logs will happily generate until the filesystem is full at which time the datbase comes screaching to a halt. Watching the archive log filesystem fill up while the backups continue to fail is one of the few things guaranteed to strike panic into a dba.

On the plus side, running in archivelog mode presents a very healthy number of recovery options. With the faster systems and cheaper disks, the costs for these options have been significantly reduced. I would imagine the arguments for running in noarchivelog mode are getting quite a bit more difficult to win.

For some reason, every procedure I’ve seen to set archivelog mode, always goes right for the switch and then says “Oh yea; there are some init parameters that need to be changed first.” What’s with that?

If you want:

Specify:

comment

One archive log destination.

log_archive_dest = ${dir}

One copy of the archive logs will be dumped in that directory.

Multiplexed copies of logs on local system only:

log_archive_dest = ${dir1} log_archive_duplex_dest = ${dir2}

To repeat: for local copies of the archive logs only.

multiplexed copies both locally and remote:

log_archive_dest_# = ‘location=${dir} log_archive_dest_## = ‘ service=${oracle net service name}

Can have up to 10 of these.

You can specify/update/change log_archive_format to a variety of formats. The important one to have in there, though, is ‘%r’; that one tracks the incarnation of your database.

Say your database is chugging along nicely for months, then Zippy the Pinhead truncates a table. You decide to do a point in time recovery to get the table back. The last step, prior to opening the database for general use, is alter database resetlogs. You’ve just reincarnated your database. From here, if you did not specify the %r, you would have duplicate archive log file names. If you needed to recover log 102, are you using log 102 from the first incarnation or the second? The %r format option will differentiate those.

I had some confusion over the use of log_archive_dest when the db_recovery_file_dest parm is already set. If you try to do that, you’ll get the following error:

SQL> alter system set log_archive_dest = '/oracle/oradata/oci1/archive' scope=both;
alter system set log_archive_dest = '/oracle/oradata/oci1/archive' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

If you want your archived logs going somewhere other than db_recovery_file_dest, you can set the log_archive_dest_# parm. I did see one reference that the log_archive_dest and log_archive_duplex_dest parameters are obsolete and that you should use the newer ones instead

So, here’s the results of playing around w/archivelog mode, destinations, and db_recovery_file_dest.

  1. If db_recovery_file_dest is set, you cannot specify log_archive_dest.

  2. If you specify both db_recovery_file_dest and log_archive_dest_n parameters without using the ‘location=use_db_recovery_file_dest’ trick, your archive logs will only go to the log_archive_dest_n location.

  3. In order to get them to go to both locations:

    1. set db_recovery_file_dest

    2. set one or more log_archive_dest_n parameters

    3. set one log_archive_dest_n parameter as follows:

      alter system set log_archive_dest_10 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';

  4. You can see a quick summary of archive log mode items by issuing:

    SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     51
Current log sequence           53

  5. You can unset db_recovery_file_dest, then use log_archive_des*. It’s very definitely a one or the other thing.

And, last, but not least, the steps to switch to archive log mode:

  1. shutdown immediate;

  2. startup mount

  3. back up your database:

    RMAN> backup as copy database tag="db_cold_090721";
[[snip]]
RMAN> list copy;

specification does not match any archive log in the recovery catalog

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
2       1    A 21-JUL-09       559569     21-JUL-09       /oracle/flash_recovery_area/OCI1/datafile/o1_mf_system_56dkgfll_.dbf
5       2    A 21-JUL-09       559569     21-JUL-09       /oracle/flash_recovery_area/OCI1/datafile/o1_mf_undotbs1_56dkh70d_.dbf
3       3    A 21-JUL-09       559569     21-JUL-09       /oracle/flash_recovery_area/OCI1/datafile/o1_mf_sysaux_56dkgwpy_.dbf
6       4    A 21-JUL-09       559569     21-JUL-09       /oracle/flash_recovery_area/OCI1/datafile/o1_mf_users_56dkh84h_.dbf
4       5    A 21-JUL-09       559569     21-JUL-09       /oracle/flash_recovery_area/OCI1/datafile/o1_mf_example_56dkh3vt_.dbf

  4. Update init params as needed:

    1. log_archive_dest || db_recovery_file_dest

    2. log_archive_dest_n || db_recovery_file_dest

    3. log_archive_format

  5. alter database archivelog NOTE: apparently, you can only do this from sqlplus, not rman

  6. alter database open

Applicable views:

  1. v$databaes:

    SQL> select name, log_mode from v$database;

NAME      LOG_MODE
--------- ------------
OCI1      ARCHIVELOG
v$archived_log: explore
v$archive_dest; explore
v$archive_processes; explore
v$backup_redolog; explore
v$log:
SQL> select group#, members, status, archived
  2  from v$log;

    GROUP#    MEMBERS STATUS           ARC
---------- ---------- ---------------- ---
         1          1 CURRENT          NO
         2          1 INACTIVE         YES
         3          1 INACTIVE         YES