Conversion of Single instance to RAC database

STEPS TO CONVERT A SINGLE INSTANCE TO RAC  

(In a cluster environment)

Change the following parameters related to OMF & Flash_recovery_area to a shared location

   SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DATA01';
   SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA01';

Drop the log groups 1,2,3 and recreate log groups 1,2,3,4 with THREAD 1 & 2    ( give the switch logfile and checkpoint command when required )

   SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
   SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1;

   SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
   SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2;

   SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
   SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3;
   SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4;

Add new Undo Tablespace for the new instance

    SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA01' SIZE 500M;

Enable the thread 2

    SQL> ALTER DATABASE ENABLE THREAD 2;

Take backup of controlfile to trace

    SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Change the control_files parameter to shared location

    SQL> ALTER SYSTEM SET CONTROL_FILES='+DATA01/CONTROL_01.CTL','+DATA01/CONTROL_02.CTL' SCOPE=SPFILE;

Shutdown the database and startup again in nomount to recreate the control files 

    SQL> SHUTDOWN IMMEDIATE;
    SQL> @<CONTROL_FILE TRACE NAME...>

Take an image copy backup to the shared location using RMAN

    RMAN> SHUTDOWN IMMEDIATE
    RMAN> STARTUP MOUNT;
    RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA01';
    RMAN> SWITCH DATABASE TO COPY;

Create a new parameter file for the RAC instances on both the nodes. Add the following parameters

     CLUSTER_DATABASE=TRUE
     CLUSTER_DATABASE_INSTANCES=2
     TEST1.THREAD=1
     TEST2.THREAD=2
     TEST1.UNDOTABLESPACE=UNDOTBS1
     TEST2.UNDOTABLESPACE=UNDOTBS2
     TEST1.INSTANCE_NUMBER=1
     TEST2.INSTANCE_NUMBER=2

Shutdown down the single instance and startup the RAC instances one by one

   NODE-1
                export ORACLE_SID=TEST1
                SQL> STARTUP NOMOUNT;
                SQL> ALTER DATABASE MOUNT;
                SQL> ALTER DATABASE OPEN;

   NODE-2
                export ORACLE_SID=TEST2
                SQL> STARTUP NOMOUNT;
                SQL> ALTER DATABASE MOUNT;
                SQL> ALTER DATABASE OPEN;

Execute the catclust.sql script from $ORACLE_HOME/rdbms/admin directory

   SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catclust.sql

Optionally register it with CRS for protection

     $ srvctl add database -d TEST -o /u01/app/oracle/product/10.2.0/db1 -y AUTOMATIC 
     $ srvctl add instance -d TEST -i TEST1 -n lmststdb1
     $ srvctl add instance -d TEST -i TEST2 -n lmststdb2