Tuesday, July 5, 2011

ARCHIVE REDO LOGS

ARCHIVE LOG MODE

Archive log file (or Offline Redo log File)
Archiver bg process (ARCH0)


If the database is in Archive Log mode, for every Log Switch along with Checkpoint process one more optional bg process ARCHIVER is invoked which copies the filled up Redo Log File contents to the separate location specified by parameter LOG_ARCHIVE_DEST in the form of Archived or Offline Redo Log File.

Multiplexing of Archived Redo Log Files is possible upto 2 or 10 locations by specifying the parameter LOG_ARCHIVE_DEST_DUPLEX
LOG_ARCHIVE_DEST_n(1 – 9)

The naming convention for Archive Log Files is specified by parameter
LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
%t – Thread Number (Used in RAC)
%s – Log Sequence Number
%r – Reset Log ID

ENABLING or Converting Database in ARCHIVE LOG MODE:  

1) Shutdown the Database
SQL>archive log list
SQL>shut immediate (must be gracefully )
SQL>exit

2) Edit the Parameter File (SPFILE)
$ cd $ORACLE_HOME/dbs
$ vi init$ORACLE_SID.ora
  Log_archive_dest=/u99/smdb/archive #entry to be made in init$ORACLE_SID.ora file
     :wq!      #save the file

3)After saving the file create the physical directory
$ mkdir –p /u99/smdb/archive

4)Now Login to the Database with as sysdba in mount stage only (and not in open stage)
$sqlplus / as sysdba
SQL>startup mount
SQL>archive log list
or
SQL>select log_mode from v$database;

5) At this point the Archival will be started but the Database still will be in  NOARCHIVE LOG MODE  why are you sleeping PUT THE DATABASE IN ARCHIVE LOG MODE
SQL>alter database archivelog;

6) NOW OPEN THE DATABASE
SQL>alter database open;

Now lets check the bg process that takes care of archive logs is active or not (either we can see at $ prompt or with ! we can write the same command at SQL> prompt also)

SQL>!ps –x

Yes the bg process arc0, arc1 etc., are the ones that takes care of Archive logs.

Its time now to check if we got any redo log files archived or not.
SQL>alter system switch logfile;

Check if the redo file is archived or not
$ cd /u99/smdb/archive
$ls

Hurray! I see the archive log files. Now I can say that I can crash the database of my company and my boss will not mind it AT ALL! You Doubt? Try Yourself and let me know the outcome please!!!!


Related Dictionary views:
$ARCHIVED_LOG
$ARCHIVE_DEST
$ARCHIVE_PROCESSES


No comments:

Post a Comment