Cloning 11g RAC ASM to NON ASM

Steps to clone 11g RAC ASM database to single node NON ASM Database:
--------------------------------------------------------------
Pre-Requistive:

• Archive Log Mode Enabled. (Source Node)
• Control File AutoBackup should be ON (Source Node)

Steps on Source Node:

1. On source node (Any node of RAC database) perform the following commands for taking database backup

2. cd $ORACLE_HOME/bin
    ./rman target /
     crosscheck backup;
     crosscheck archivelog all;
     delete force obsolete;
     delete expired backup;
     crosscheck backup;
     crosscheck archivelog all;

3. Specify same location of control file and spfile backup of your RMAN backup location. (keep location of local mount points)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '<BACKUP_LOCATION>/%F_%T';

4. Connect to RMAN for taking database backup along with archivelogs.
cd $ORACLE_HOME/bin
./rman target /
RMAN> run{
allocate channel d1 type disk;
backup
format '<BACKUP_LOCATION>/PROD__%T_t%t_s%s_p%p'
database;
sql 'alter system archive log current';
format '<BACKUP_LOCATION>/arch__%T_t%t_s%s_p%p'
archivelog all;
release channel d1;
}

(Note : Make sure to take note of the thread no, and sequence number at the last of the command execution.
e.g input archived log thread=** sequence=*** RECID=1209 STAMP=835439068)

5. Copy the backup files created by RMAN through scp to the same location on the target node.

6. Copy the TNS entry of Source in tnsnames.ora file of target ORACLE HOME.

7. Generate orapw file in Source and copy it to $ORACLE_HOME/dbs location of target.


Steps on Target Node:


1. Add the following parameter in the pfile of the target node.
    *.db_create_file_dest='<Datafile_Location>'

2. Start the database in NOMOUNT State.

3. Mention UNDO Tablespace name in init<SID>.ora file of target database same as that of source database.

4. Run the following command to create duplicate database through RMAN.
cd $ORACLE_HOME/bin
./rman target sys/<password>@SOURCE_SID auxiliary /
RMAN> duplication target database '<TARGET_SID>' until sequence <no.> thread <no.>;

5. After the command complete successfully, issue the following command as checks to validate target node.
SQL> select name from v$database;
SQL> select open_mode from v$database;
SQL> select dbid from v$database;
SQL> select instance_name from v$instance;

6. Shutdown the database and bring it up using pfile.