Migrating RAC with ASM to standalone database in filesystem using RMAN

Recently we got a requirement to migrate the RAC database to NON RAC Database. You may ask why from RAC-ASM to NON RAC-FILESYSTM :) Reason is we are hosting the database in AWS cloud Environment. AWS cloud does not provide the clustering infrastructure required to run RAC.

In this scenario, customer has given us the full RMAN back of RAC environment and shared the backup set to us. So here is the steps to restore for the single instance node.


1. Take the PFILE which will be available in the backup set and remove all RAC related parameters. That environment was of ASM which we are migrating it to File-system, so we need to add some additional parameter. Below are the format

<DB_NAME>.__db_cache_size=603979776
<DB_NAME>.__java_pool_size=50331648
<DB_NAME>.__large_pool_size=16777216
<DB_NAME>.__oracle_base='/home/'#ORACLE_BASE set from environment
<DB_NAME>.__pga_aggregate_target=603979776
<DB_NAME>.__sga_target=1157627904
<DB_NAME>.__shared_io_pool_size=0
<DB_NAME>.__shared_pool_size=469762048
<DB_NAME>.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/oracle/admin/<DB_NAME>/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='control1.ctl,control2.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/app/oracle/oradata/<DB_NAME>/'
*.db_file_name_convert='+DG_01/<DB_NAME>/datafile/','/home/oracle/app/o
racle/oradata/<DB_NAME>/'
*.db_create_online_log_dest_1=’/home/oracle/app/oracle/oradata/'
*.db_name='<DB_NAME>'
*.db_recovery_file_dest='/home/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4G
*.diagnostic_dest='/home/oracle/app/oracle/'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1761607680
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
<DB_NAME>.undo_tablespace='UNDOTBS1'


2. Create the password file

$ORACLE_HOME/bin/orapwd
file=$ORACLE_HOME/dbs/orapwd<SID>.orapassword=oracle entries=5

3. Start the database in mount state

$ sqlplus / as sysdba
Sql> startup nomount

3. Login in RMAN with no catalog

$ rman target / nocatalog

4. restore the control file which is available in backup set

$RMAN> restore controlfile from ‘<location of control file>’

5. Once the control file is restored, mount the database

RMAN> alter database mount;

6. Catalog backup set using rman

RMAN> catalog backuppiece ‘<Location of backupset>’;

7. Once the catalog is done run the below RMAN script to restore the data

run
{
SET NEWNAME FOR DATABASE TO
'<NEW_LOCATION_TO_WHERE_THE_DATAFILE_NEED_TO_RESTORE>/%d';   
SET NEWNAME FOR tempfile 1 TO
'<NEW_LOCATION_TO_WHERE_THE_DATAFILE_NEED_TO_RESTORE>/%d';
restore database;
switch datafile all;
switch tempfile all;
}

Newname is for restoring the database to the file system. If we miss to mention that the database will be restored to ASM location and while opening the database it will throw error

8. Check the sequence and threads using below command

RMAN> list backup of archivelog all;

List of Archived Logs in backup set 9
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 56 214541 01-FEB-07 226238 01-FEB-07
1 57 226238 01-FEB-07 226240 01-FEB-07
1 58 226240 01-FEB-07 233107 12-FEB-07
2 1 186185 28-JAN-07 225714 01-FEB-07
2 2 225714 01-FEB-07 226037 01-FEB-07
2 3 226037 01-FEB-07 233110 12-FEB-07

Check the last archive sequence for all redo threads and select the archive sequence
having LEAST "Next SCN" among them. In our case sequence 58 of thread 1 has Next SCN of
233107 while sequence 3 of thread 2 has Next SCN of 233110. Since squence 58 of thread 1 has
least Next SCN we will recover upto this point. (If you are keen to have recovery run until some
specific time you can always give SET UNTIL TIME)

9. Restore and recover the database using the below command in RMAN prompt

run {
set until sequence 59 thread 1;
recover database;
}

*59 is the maximum sequence number and 1 is the minimum thread. The value will change on each database

10. Disable the block change tracking in database.

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

11. Open the database in reset log file using below script:

SQL> alter database open resetlog;

Check the status of Database

SQL> select status from v$instance;

STATUS
------------

OPEN



Please revert back if i miss any steps and any clarification required