HowTo Restore RAC Database to Single Instance On Another Node

Take RMAN backup of the production RAC database..

RMAN> run{
allocate channel c1 type disk format '/tmp/%U';
backup database;
backup archivelog all;
}

- Create a PFILE for the single instance database using the production RAC parameter file

And modify the parameters %dest, control_files, log_archive_dest_1, %convert, cluster_database_instances, cluster_database etc.. for undo_tablespace, mention any one undo tablespace name

- Copy the backup pieces and the modified INIT.ORA file to the new host to same mount point.

- Use the pfile created above to STARTUP NOMOUNT the database on the new host

$ sqlplus "/ as sysdba"

SQL> startup nomount;

$ rman target / nocatalog

RMAN> restore controlfile from '/tmp/< backup piece name of controlfile auto backup>';

RMAN> alter database mount;

- Determine the recovery point.

RMAN> list backup of archivelog all;

Check the last archive sequence for all redo threads and select the archive sequence having LEAST "Next SCN" among them.

- Having determined the point upto which media recovery should run, start the restore/recovery using:

RMAN> run {
set until sequence < sequence# from above> thread < thread# >;
restore database;
recover database;
}

SQL> alter database open resetlogs;

If open database fail with error ORA-38856
then, Set the following parameter in the init.ora file:

_no_recovery_through_resetlogs=TRUE

Then open with resetlogs.
Once the database has opened, removed this hidden parameter.

- Once the database is opened successfully, you may remove the redolog groups for redo threads of other instances.

SQL> select THREAD#, STATUS, ENABLED
2 from v$thread;

THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PRIVATE

SQL> select group# from v$log where THREAD#=2;

GROUP#
----------
4
5
6

SQL> alter database disable thread 2;

Database altered.

SQL> alter database clear unarchived logfile group 4; ( repeat for 4 to 6)

Database altered.

SQL> alter database drop logfile group 4; ( repeat for 4 to 6)

Database altered.

- Now you can remove the undo tablespaces of other instances.

SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

SQL> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.