[RAC] RAC환경에서 archive log mode로 변경하기

Posted by

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/MYDB/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 clustered database using the following. 

$ srvctl stop database -d MYDB 
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 clustered database again using the following. 

$ srvctl start database -d MYDB 
The current settings place all archive logs in the same directory. This is acceptable 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 10gR1 

If the LOG_ARCHIVE_DEST_n parameters are not set, the archived redo logs will be placed in the FRA. If you want to specify their location and the file name format manually, you can do it as follows. 

ALTER SYSTEM SET log_archive_dest_1=’location=/u01/oradata/MYDB/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 clustered database using the following. 

$ srvctl stop database -d MYDB 
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 clustered database again using the following. 

$ srvctl start database -d MYDB 

———————————————————————Oracle 10gR2 

From 10gR2, you no longer need to reset the CLUSTER_DATABASE parameter during the process. 

If the LOG_ARCHIVE_DEST_n parameters are not set, the archived redo logs will be placed in the FRA. If you want to specify their location and the file name format manually, you can do it as follows. 

ALTER SYSTEM SET log_archive_dest_1=’location=/u01/oradata/MYDB/archive/’ SCOPE=spfile; 
ALTER SYSTEM SET log_archive_format=’arch_%t_%s_%r.arc’ SCOPE=spfile; 
From the command line we can stop the entire clustered database and start it in mount mode using the following. 

$ srvctl stop database -d MYDB 
$ srvctl start database -d MYDB -o mount 
With the database mounted issue the following commands. 

sqlplus / as sysdba 

ALTER DATABASE ARCHIVELOG; 
EXIT; 
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 stop and start the clustered database again using the following commands. 

$ srvctl stop database -d MYDB 
$ srvctl start database -d MYDB 

————Oracle 11g 

The process is exactly the same in 11g as in the previous Oracle 10gR2 section. 

————–Oracle 12c 

The process is exactly the same in 12c as in the previous Oracle 10gR2 section.

Leave a Reply

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다