Restore RAC database from a Physical Standby RAC database

Here we will see how to take backup from a RAC Physical Standby database and restore it to create a  RAC Test Database.

1. Cancel redo apply on physical standby Database and open database in Readonly mode 


 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 ALTER DATABASE OPEN;

2. Take backup of Database.

run{
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/dbbackups/backup/indigo/%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/dbbackups/backup/indigo/backup_%U';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
BACKUP DATABASE;
BACKUP ARCHIVELOG ALL;
}

On Test Server

New Database name - inditst
DB_UNIQUE_NAME - inditst
backup location - /u01/inditst
update /etc/oratab with the ORACLE_HOME details of new database
RAC Node names - mercury1 and mercury2

Network Location RDBMS - /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/tnsnames.ora
Network Location GRID - /u01/app/11.2.0.4/grid/network/admin/listener.ora

1. update tnsnames.ora file in /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/tnsnames.ora
Make sure to provide (UR=A). Or else you will get ORA-12528  - TNS Listener Blocked Error.

INDITST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mercury-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE = inditst)
        (UR=A)
    )
  )


2. update listener.ora file in /u01/app/11.2.0.4/grid/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = inditst)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1)
      (SID_NAME = inditst1)
    )
)


3. Update initinditst1.ora in /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initinditst1.ora

set CLUSTER_DATABASE="FALSE"

otherwise you will get below error during recovery
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode
Rman duplicate fail with RMAN-06136, ORA-01503, ORA-12720, ORA-00494 enqueue [CF] (Doc ID 1335479.1)

make other necessary changes like database name change in pfile.

4. startup nomount pfile='/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initinditst1.ora';

rman auxiliary /
run {
allocate auxiliary channel d1 type disk;
allocate auxiliary channel d2 type disk;
allocate auxiliary channel d3 type disk;
allocate auxiliary channel d4 type disk;
allocate auxiliary channel d5 type disk;
allocate auxiliary channel d6 type disk;
allocate auxiliary channel d7 type disk;
allocate auxiliary channel d8 type disk;
duplicate database to flttst pfile='/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initinditst1.ora'
backup location '/u01/inditst'
nofilenamecheck;
}

5. Once the database is opened, shutdown the database and change the parameter in pfile
CLUSTER_DATABASE=TRUE

6. Update /etc/oratab of second node with instance details
   Copy initinditst1.ora to mercury2 as initinditst2.ora

7. Add database to Oracle clusterware,use below commands as "oracle" user

srvctl add database -d inditst -o /u01/app/oracle/product/11.2.0.3/dbhome_1
srvctl config database -d inditst
srvctl status database -d inditst
srvctl add instance -d inditst -i inditst1 -n mercury1
srvctl start database -d inditst



8. Here is the tnsnames.ora entry for connections to the new inditst Database


INDI_TEST=
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = on)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = tcp)(HOST = mercury1-vip.perfitcomputer.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = tcp)(HOST = mercury2-vip.perfitcomputer.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = inditst)
)
)


INDI_TEST_N1 =
        (DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = mercury1-vip.perfitcomputer.com)(PORT = 1521))
(CONNECT_DATA =
                (SERVICE_NAME = inditst)
                  (INSTANCE_NAME = inditst1)
)
)

INDI_TEST_N2 =
(DESCRIPTION =
        (ADDRESS = (PROTOCOL = tcp)(HOST = mercury2-vip.perfitcomputer.com)(PORT = 1521))
(CONNECT_DATA =
                (SERVICE_NAME = inditst)
                  (INSTANCE_NAME = inditst2)
)
        )