Convert 11gR2 RAC database to archivelog mode

This guide is intended for Oracle medium/low level administrators to have a reference for converting a non archive log Oracle 11gR2 RAC database into archive log mode.

To do this, first of all we connect to the machine as the “oracle” user and login the database as sysdba, then check the current mode of the database:
 

egfh@andromeda-ascendant:~$ ssh oracle@192.168.69.141
oracle@192.168.69.141's password:
Last login: Mon Jul  4 21:33:29 2011 from 192.168.69.187
[oracle@rac1 ~]$ sqlplus '/as sysdba' 

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 5 11:11:49 2011 

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.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	       /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     26
Current log sequence	       27
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Then, we create the desired ASM archive log destination:
 

We don’t really need to do this if we define on the next step just the diskgroup name (by default “+DATA”) on the SPFILE parameter “log_archive_dest_1”, but it’s very handy to know and control the location of the archive logs destination, specially if we want to play with it a bit afterwards as for creating a “Shell Scripted standby Oracle database”.

 

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@rac1 ~]$ export ORACLE_SID='+ASM1';
[oracle@rac1 ~]$ asmcmd
ASMCMD> cd DATA/
ASMCMD> ls
ORATEST/
RACORA/
rac-scan/
ASMCMD> cd RACORA
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileRACORA.ora
ASMCMD> mkdir ARCHIVELOG
ASMCMD> cd ARCHIVELOG
ASMCMD> pwd
+DATA/RACORA/ARCHIVELOG
ASMCMD> exit

Remember to change again the ORACLE_HOME and ORACLE_SID environment variables:
 

[oracle@rac1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@rac1 ~]$ export ORACLE_SID=RACORA1

Now, we stop the cluster database:
 

[oracle@rac1 ~]$ srvctl stop database -d RACORA

We check that the other node/s are not running the database’s instance:
 

[oracle@rac1 ~]$ ps -ef|grep pmon
oracle    3666     1  0 10:47 ?        00:00:00 asm_pmon_+ASM1
oracle   13336 20632  0 12:02 pts/0    00:00:00 grep pmon
[oracle@rac1 ~]$ ssh rac2 ps -ef|grep pmon
oracle    3665     1  0 10:47 ?        00:00:00 asm_pmon_+ASM2

We start in nomount mode one single instance of the RAC:
 

[oracle@rac1 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 5 12:03:14 2011 

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

Connected to an idle instance. 

SQL> startup nomount;
ORACLE instance started. 

Total System Global Area  521936896 bytes
Fixed Size		    2214936 bytes
Variable Size		  318768104 bytes
Database Buffers	  197132288 bytes
Redo Buffers		    3821568 bytes

After that, we change the spfile parameters log_archive_start and log_archive_dest_1:
 

SQL> alter system set log_archive_start=TRUE SCOPE=SPFILE; 

System altered. 

SQL> alter system set log_archive_dest_1='location=+DATA/RACORA/ARCHIVELOG' SCOPE=SPFILE; 

System altered.

Now we mount the database instance:
 

SQL> alter database mount; 

Database altered.

And finally, we set the database to archive log mode:
 

SQL> alter database archivelog;
Database altered.

We can perform some checks after that…
 

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       +DATA/racora/archivelog
Oldest online log sequence     26
Next log sequence to archive   27
Current log sequence	       27
SQL> alter system switch logfile; 

System altered. 

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       +DATA/racora/archivelog
Oldest online log sequence     27
Next log sequence to archive   28
Current log sequence	       28 

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 ~]$ export ORACLE_SID=RACORA1
[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@rac1 ~]$ export ORACLE_SID='+ASM1';
[oracle@rac1 ~]$ asmcmd
ASMCMD> cd DATA/RACORA/ARCHIVELOG
ASMCMD> ls
1_27_729858920.dbf
2011_07_05/

 

Comments are closed.