Convert Single Instance DB to RAC DB

Converting single instance DB to RAC DB. a) Login to the single instance server, shutdown the db and take the cold backup using rman. 1) Create a rman script as below $ vi fullbkpofdb.rcv connect target / shutdown immediate; startup mount; run { allocate channel a1 device type disk; allocate channel a2 device type disk; backup database format 'datafiles_%t_%d_%U.bkp' tag='Full_Database'; backup current controlfile format 'ctrlfile_%t_%d_%U.bkp' tag='ctrl_file'; backup spfile format 'spfile_%t_%d_%U.bkp' tag='spfilebkp'; release channel a1; release channel a2; } exit; 2) Run the script. $ rman cmdfile 'fullbkpofdb.rcv' log 'backup.log' 3) Once the backup is completed, copy the backup to the primary node 4) Create the password for the database in both the instance Execute the below in node1 $ orapwd file=$ORACLE_HOME/dbs/orapwmanzy1 password=****** entries=20 Execute the below in node2 $ orapwd file=$ORACLE_HOME/dbs/orapwmanzy2 password=****** entries=20 5) Startup the instance using rman to restore db, rman will create a dummy pfile and starts the instance inorder to restore the spfile. Now restore the spfile. rhelrac1-> export ORACLE_SID=manzy1 rhelrac1-> rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 6 19:19:12 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database (not started) RMAN> startup startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initmanzy.ora' starting Oracle instance without parameter file for retrival of spfile Oracle instance started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 01/06/2012 19:19:33 ORA-00205: error in identifying control file, check alert log for more info #### Restore the spfile to an pfile. RMAN> restore spfile to pfile '/u01/initmanzy1.ora' from '/u01/backupofmanzy/spfile_771874551_MANZY_0kn03nnn_1_1.bkp'; Starting restore at 09-JAN-12 using channel ORA_DISK_1 channel ORA_DISK_1: autobackup found: /u01/backupofmanzy/spfile_771874551_MANZY_0kn03nnn_1_1.bkp channel ORA_DISK_1: SPFILE restore from autobackup complete Finished restore at 09-JAN-12 RMAN> exit; ##### Now create the directories in asm diskgroup to store your spfile,controlfile, datafiles and onlinelogfiles. $ export ORACLE_SID=+ASM1 $ asmcmd ASMCMD> lsdg State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name MOUNTED EXTERN N N 512 4096 1048576 3858 675 0 675 0 DATA1/ MOUNTED NORMAL N N 512 4096 1048576 3765 2361 1255 553 0 DATA2/ MOUNTED NORMAL N N 512 4096 1048576 3858 3075 1286 894 0 DATA3/ MOUNTED EXTERN N N 512 4096 1048576 3858 3304 0 3304 0 FLASH/ #### Once the pfile is restored edit the pfile with appropirate RAC parameter as below. *.cluster_database_instances=2 *.cluster_database=TRUE *.control_files='+DATA1/manzy/controlfiles/control01.ctl','+DATA1/manzy/controlfiles/control02.ctl' manzy1.instance_name='manzy1' manzy2.instance_name='manzy2' manzy1.instance_number=1 manzy2.instance_number=2 manzy1.thread=1 manzy2.thread=2 manzy1.undo_tablespace='undotbs1' manzy2.undo_tablespace='undotbs2' Note : Also change the adump, bdump, cdump, udump directories accordingly. ###### After editing the pfile, create a spfile using the modified pfile. rhelrac1-> export ORACLE_SID=manzy1 rhelrac1-> sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 9 19:38:25 2012 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> create spfile='+DATA1/manzy/spfiles/spfilemanzy.ora' from pfile = '/u01/initmanzy1.ora'; File created. SQL> shut immediate; ORA-01507: database not mounted ORACLE instance shut down. ###### Now create a pfile in $ORACLE_HOME/dbs location on both the instance pointing to the same spfile. Node 1 $ cd $ORACLE_HOME/dbs $ vi initmanzy1.ora ### add the below line alone in the pfile. spfile=+DATA1/manzy/spfiles/spfilemanzy.ora Node 2 $ cd $ORACLE_HOME/dbs $ vi initmanzy2.ora ### add the below line alone in the pfile. spfile=+DATA1/manzy/spfiles/spfilemanzy.ora ### Now restore the controlfile in node1. rhelrac1-> rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jan 9 19:43:54 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database (not started) RMAN> startup nomount; Oracle instance started Total System Global Area 444596224 bytes Fixed Size 1267980 bytes Variable Size 130025204 bytes Database Buffers 310378496 bytes Redo Buffers 2924544 bytes RMAN> restore controlfile from '/u01/backupofmanzy/contfiles_771874550_MANZY_0jn03nnm_1_1.bkp'; Starting restore at 09-JAN-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=150 instance=manzy1 devtype=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 output filename=+DATA1/manzy/controlfiles/control01.ctl output filename=+DATA1/manzy/controlfiles/control02.ctl Finished restore at 09-JAN-12 ##### Mount the controlfile RMAN> alter database mount; database mounted released channel: ORA_DISK_1 ##### Now remove all the details of backup repository which is stored in this controfile RMAN> crosscheck backup; using channel ORA_DISK_1 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/datafiles_771873888_MANZY recid=1 stamp=771873889 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/datafiles_771873913_MANZY recid=2 stamp=771873913 RMAN> delete noprompt expired backup; using channel ORA_DISK_1 List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 1 1 1 1 EXPIRED DISK /u01/datafiles_771873888_MANZY recid=1 stamp=771873889 2 2 1 1 EXPIRED DISK /u01/datafiles_771873913_MANZY recid=2 stamp=771873913 deleted backup piece backup piece handle=/u01/datafiles_771873888_MANZY recid=1 stamp=771873889 recid=15 stamp=771874538 deleted backup piece backup piece handle=/u01/datafiles_771873913_MANZY recid=2 stamp=771873913 recid=16 stamp=771874527 Deleted 2 EXPIRED objects RMAN> list backup; RMAN> #### Now catalog the backup files which we have taken from the single instance. RMAN> catalog start with '/u01/backupofmanzy/'; searching for all files that match the pattern /u01/backupofmanzy/ List of Files Unknown to the Database ===================================== File Name: /u01/backupofmanzy/contfiles_771874550_MANZY_0jn03nnm_1_1.bkp File Name: /u01/backupofmanzy/datafiles_771874526_MANZY_0fn03nmu_1_1.bkp File Name: /u01/backupofmanzy/datafiles_771874533_MANZY_0hn03nn5_1_1.bkp File Name: /u01/backupofmanzy/datafiles_771874526_MANZY_0gn03nmu_1_1.bkp File Name: /u01/backupofmanzy/spfile_771874551_MANZY_0kn03nnn_1_1.bkp File Name: /u01/backupofmanzy/datafiles_771874538_MANZY_0in03nna_1_1.bkp Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/backupofmanzy/contfiles_771874550_MANZY_0jn03nnm_1_1.bkp File Name: /u01/backupofmanzy/datafiles_771874526_MANZY_0fn03nmu_1_1.bkp File Name: /u01/backupofmanzy/datafiles_771874533_MANZY_0hn03nn5_1_1.bkp File Name: /u01/backupofmanzy/datafiles_771874526_MANZY_0gn03nmu_1_1.bkp File Name: /u01/backupofmanzy/spfile_771874551_MANZY_0kn03nnn_1_1.bkp File Name: /u01/backupofmanzy/datafiles_771874538_MANZY_0in03nna_1_1.bkp ##### Now restore the datafiles to the asm diskgroup use the set newname command to rename the datafiles. RMAN> run 2> { 3> set newname for datafile '/optware/oracle/oradata/manzy/system01.dbf' to '+DATA2/manzy/datafiles/system01.dbf'; 4> set newname for datafile '/optware/oracle/oradata/manzy/undotbs01.dbf' to '+DATA2/manzy/datafiles/undotbs01.dbf'; 5> set newname for datafile '/optware/oracle/oradata/manzy/sysaux01.dbf' to '+DATA3/manzy/datafiles/sysaux01.dbf'; 6> set newname for datafile '/optware/oracle/oradata/manzy/users01.dbf' to '+DATA3/manzy/datafiles/users01.dbf'; 7> restore database; 8> switch datafile all; 9> } executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 09-JAN-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00002 to +DATA2/manzy/datafiles/undotbs01.dbf restoring datafile 00003 to +DATA3/manzy/datafiles/sysaux01.dbf channel ORA_DISK_1: reading from backup piece /u01/backupofmanzy/datafiles_771874526_MANZY_0gn03nmu_1_1.bkp channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/backupofmanzy/datafiles_771874526_MANZY_0gn03nmu_1_1.bkp tag=FULL_DB channel ORA_DISK_1: restore complete, elapsed time: 00:00:46 channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to +DATA2/manzy/datafiles/system01.dbf restoring datafile 00004 to +DATA3/manzy/datafiles/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/backupofmanzy/datafiles_771874526_MANZY_0fn03nmu_1_1.bkp channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/backupofmanzy/datafiles_771874526_MANZY_0fn03nmu_1_1.bkp tag=FULL_DB channel ORA_DISK_1: restore complete, elapsed time: 00:00:47 Finished restore at 09-JAN-12 datafile 1 switched to datafile copy input datafile copy recid=5 stamp=772142636 filename=+DATA2/manzy/datafiles/system01.dbf datafile 2 switched to datafile copy input datafile copy recid=6 stamp=772142636 filename=+DATA2/manzy/datafiles/undotbs01.dbf datafile 3 switched to datafile copy input datafile copy recid=7 stamp=772142637 filename=+DATA3/manzy/datafiles/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy recid=8 stamp=772142637 filename=+DATA3/manzy/datafiles/users01.dbf RMAN> exit; ### Now we have restored the database to the new location i.e. asm diskgroup, now we need to rename the online logfiles for that connect to the sqlplus and rename the files. rhelrac1-> sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 9 20:04:55 2012 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> select status from V$instance; STATUS ------------ MOUNTED SQL> select member from V$logfile; MEMBER -------------------------------------------------------------------------------- /optware/oracle/oradata/manzy/redo03.log /optware/oracle/oradata/manzy/redo02.log /optware/oracle/oradata/manzy/redo01.log #### Rename the logfiles to point to the asm diskgroups. SQL> alter database rename file '/optware/oracle/oradata/manzy/redo01.log' to '+FLASH/manzy/onlinelogfiles/redo01.log'; Database altered. SQL> alter database rename file '/optware/oracle/oradata/manzy/redo02.log' to '+FLASH/manzy/onlinelogfiles/redo02.log'; Database altered. SQL> alter database rename file '/optware/oracle/oradata/manzy/redo03.log' to '+FLASH/manzy/onlinelogfiles/redo03.log'; Database altered. #### Now add the logfiles which will be used by the instance 2. SQL> select * from V$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 1 52428800 1 NO CURRENT 464631 06-JAN-12 3 1 0 52428800 1 YES UNUSED 0 2 1 0 52428800 1 YES UNUSED 0 SQL> alter database add logfile thread 2 group 4 '+FLASH/manzy/onlinelogfiles/redo04.log' size 52428800; Database altered. SQL> alter database add logfile thread 2 group 5 '+FLASH/manzy/onlinelogfiles/redo05.log' size 52428800; Database altered. SQL> alter database add logfile thread 2 group 6 '+FLASH/manzy/onlinelogfiles/redo06.log' size 52428800; Database altered. #### Open the database with resetlogs option. SQL> alter database open resetlogs; Database altered. #### Recreate the temporary tablespace SQL> create temporary tablespace temp2 tempfile '+DATA3/manzy/datafiles/temp02.dbf' size 50m; Tablespace created. SQL> alter database default temporary tablespace temp2; Database altered. SQL> drop tablespace TEMP including contents and datafiles; Tablespace dropped. ### Enable the thread for the instance 2 SQL> alter database enable thread 2; Database altered. ### Create the undo tablespace for the instance 2 SQL> create undo tablespace undotbs2 datafile '+DATA3/manzy/datafiles/undotbs02.dbf' size 50m; Tablespace created. SQL> exit; ##### Now add the database to the cluster registry (OCR) using the srvctl command. $ srvctl add database -d manzy -o /u01/app/oracle/product/10.2.0/db_1 -p +DATA1/manzy/spfiles/spfilemanzy.ora $ srvctl add instance -d manzy -i manzy1 -n rhelrac1 $ srvctl add instance -d manzy -i manzy2 -n rhelrac2 ### Shutdown the database and start it using srvctl $ srvctl stop database -d manzy $ srvctl start database -d manzy ### Check the status of the instance. $ srvctl status database -d manzy Instance manzy1 is running on node rhelrac1 Instance manzy2 is running on node rhelrac2 #### Configure the tnsnames and listerners. ##### End of converting single instance to RAC Instance.