Create Controfile with Backup Controfile to Trace

Today, I have planned to recreate controlfile after I have done user-managed restoration for a database from old host to new host.

Duplicate of existing control file to a binary file
ALTER DATABASE BACKUP CONTROLFILE TO '/dmp1/restore/MPROD';

Produce SQL statements that can later be used to re-create your control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This command writes a SQL script to a trace file where it can be captured and edited to reproduce the control file.

For this time, I'll use
SQL> alter database backup controlfile to trace;

Once the trace file is created in user dump folder,  the trace file contains SQL statement to recreate the control file.
There are 2 set of SQL statement:
i.  RESETLOGS
ii. NORESETLOGS
I have studied the oracle online documentation (http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5003.htm#SQLRF01203) and get to know that there are prerequisites which are must have SYSDBA system privileges and the database must not mounted by any instance.

After I have copied the necessary SQL script from trace file into new SQL file called NewCTL.sql.

One of the error message due to change database name must be in RESETLOGS mode.




I changed the script from NORESETLOGS to RESETLOGS.




There was error during recovery database command.




Lastly, change the global name.



SQL>  alter  database rename global_name to MPDEV ;
Database altered.



Start listener and completed the task. :)

Reference
http://www.dba-oracle.com/oracle_tips_db_copy.htm
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5003.htm#SQLRF01203
http://orafapp.blogspot.sg/2012/07/ora-01610-recovery-using-backup.html