Enabling Archive Log mode for Single Node Instance & RAC

On a single node instance the archive log mode is reset as follows:

SQL> archive log list

Database log mode                         No Archive Mode

Automatic archival                           Disabled

Archive destination                         USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence         165

Current log sequence                    167

SQL> select name, log_mode from v$database;

NAME                   LOG_MODE

---------                                 ------------

TEST                       NOARCHIVELOG

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /home/oracle/product/11g/db/db

s/spfiletest.ora

Setup Archive mode and archive destination:

ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;

ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/orcl/archive/' SCOPE=spfile;

(Optionally we can also give other parameters in this statement like optional, reopen etc.)

ALTER SYSTEM SET log_archive_format='arch_%r_%t_%s.arc' SCOPE=spfile;

Now let shutdown the database and do a startup mount:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ARCHIVE LOG START;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

Test if archivelog is set properly:

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination =/u01/oradata/orcl/archive/

Oldest online log sequence 165

Next log sequence to archive 167

Current log sequence 167

SQL>  select name, log_mode from v$database;

NAME    LOG_MODE

--------- ------------

TEST       ARCHIVELOG

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

Check physical archive log files:

SQL> !ls -lrt =/u01/oradata/orcl/archive/

-rw-r----- 1 oracle oracle 45509632 2009-03-20 14:43 1_167_677948664.dbf

-rw-r----- 1 oracle oracle 1024 2009-03-20 14:43 1_168_677948664.dbf

Archive log mode is now enabled and archive files started generating.

FOR RAC:

The ALTER DATABASE ARCHIVELOG command can only be performed if the database in mounted in exclusive mode. This means the whole clustered database must be stopped before the operation can be performed. First we set the relevant archive parameters:

ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;

ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYSID/archive/' SCOPE=spfile;

ALTER SYSTEM SET log_archive_format='arch_%t_%s.arc' SCOPE=spfile;

Since we need to mount the database in exclusive mode we must also alter the following parameter:

ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;

From the command line we can stop the entire cluster using:

srvctl stop database -d MYSID

With the cluster down we can connect to a single node and issue the following commands:

STARTUP MOUNT;

ARCHIVE LOG START;

ALTER DATABASE ARCHIVELOG;

ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;

SHUTDOWN IMMEDIATE;

Notice that the CLUSTER_DATABASE parameter has been reset to it's original value. Since the datafiles and spfile are shared between all instances this operation only has to be done from a single node.

From the command line we can now start the cluster again using:

srvctl start database -d MYSID

The current settings place all archive logs in the same directory. This is acceptible since the thread (%t) is part of the archive format preventing any name conflicts between instances. If node-specific locations are required the LOG_ARCHIVE_DEST_1 parameter can be repeated for each instance with the relevant SID prefix.