Enabling/Disable Archive Log mode in RAC (11gR2)

Whether a single instance or clustered database, Oracle tracks (logs) all changes to database blocks in online redolog files. In an Oracle RAC environment, each instance will have its own set of online redolog files known as a thread. Each Oracle instance will use its set (group) of online redologs in a circular manner. Once an online redolog fills, Oracle moves to the next one.
If the database is in "Archive Log Mode", Oracle will make a copy of the online redo log before it gets reused. A thread must contain at least two online redologs (or online redolog groups). The same holds true for a single instance configuration. The single instance must contain at least two online redologs (or online redolog groups). 

The size of an online redolog file is completely independent of another intances' redolog size. Although in most configurations the size is the same, it may be different depending on the workload and backup / recovery considerations for each node. It is also worth mentioning that each instance has exclusive write access to its own online redolog files. In a correctly configured RAC environment, however, each instance can read another instance's current online redolog file to perform instance recovery if that instance was terminated abnormally. It is therefore a requirement that online redo logs be located on a shared storage device (just like the database files).

The Database Creation Assistant (DBCA) allows users to configure a new database to be in archive log mode, however most DBA's opt to bypass this option. In cases like this where the database is in no archive log mode, it is a simple task to put the database into archive log mode. Note however that this will require a short database outage. From one of the nodes in the Oracle RAC 11g configuration, use the following tasks to put a RAC enabled database into archive log mode.

Enable Archivelog

1)  Login to one of the nodes , verify the archive log mode

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 197
Current log sequence 198

SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
PUB NOARCHIVELOG

2) Disable the cluster instance parameter by setting cluster_database to FALSE from the current instance:

sqlplus "/ as sysdba"
SQL> alter system set cluster_database=false scope=spfile sid='pub1';

3) Shutdown all instances accessing the clustered database:

  srvctl stop database -d pub

4)Using the local instance, MOUNT the database:

sqlplus "/ as sysdba"
SQL> startup mount 

5) Enable archiving:

SQL> alter database archivelog;

6) Re-enable support for clustering by modifying the instance parameter cluster_database to TRUE from the current instance:

SQL> alter system set cluster_database=true scope=spfile sid='pub1';

7) Shutdown the local instance:

SQL> shutdown immediate 

8) Bring all instance back up using srvctl:

srvctl start database -d pub

9)Login to the local instance and verify Archive Log Mode is enabled:

sqlplus "/ as sysdba"
SQL> archive log list
Database log mode            Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     198
Next log sequence to archive  199
Current log sequence               199

After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs.

Disable Archivelog
1)
set oracle_sid=pub1

SQL> alter system set cluster_database=false scope=spfile sid='pub1';

System altered.

 

2) 

C:\app\11.2.0\grid\BIN>srvctl stop database -d pub -o immediate

3)

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.3696E+10 bytes

Fixed Size 2268224 bytes

Variable Size 7046431680 bytes

Database Buffers 6610223104 bytes

Redo Buffers 36966400 bytes

Database mounted.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 18

Next log sequence to archive 19

Current log sequence 19

 

SQL> alter database noarchivelog;

Database altered.

SQL> alter system set cluster_database=true scope=spfile sid='pub1';

System altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

 

4)

C:\app\11.2.0\grid\BIN>srvctl start database -d pub

 

5)

set oracle_sid=pub1

C:\app\Administrator\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 2 08:44:21 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management,

OLAP,

Data Mining and Real Application Testing options

 

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 18

Current log sequence 19

SQL>