Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Archivelog Mode On RAC

This article highlights the differences between resetting the archive log mode on a single node instance and a Real Application Clusters (RAC).

Single Instance

Oracle 9i

On a single node 9i instance the archive log mode is reset as follows.

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;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ARCHIVE LOG START;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Oracle 10g Upward

In Oracle 10g the LOG_ARCHIVE_START parameter and ARCHIVE LOG START command have been deprecated, so you will use the following code.

ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYSID/archive/' SCOPE=spfile;
ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Real Application Clusters (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.

Oracle 9i

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 the following.

$ 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.

Oracle 10g Upward

The process is the same for 10g+, with the exception of the removing the deprecated parameters. First we set the relevant archive parameters.

ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYSID/archive/' SCOPE=spfile;
ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.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 the following.

$ srvctl stop database -d MYSID

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

STARTUP MOUNT;
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.

Hope this helps. Regards Tim...

Back to the Top.