RMAN database cloning using backup

1>.       Move RMAN back of PROD to Any server.

2>.       Now Change ‘DB NAME’ in init file (PROD)

3>.       Start Database on nomount.

           

            SQL> startup nomount pfile=’PATH’

4>.       Connect to RMAN

            Rman target / nocatalog

5>.       Restore controlfile from backup

            RMAN> restore controlfile from ‘Path of Backup’

6>.       Now start database on mount mode.

            RMAN> alter database mount;

7>.       Need to change catalog path in controlfile.

            RMAN> catalog start with ‘Path of backup folder’

8>.       Restore database

            RMAN>run

{

set newname for DATAFILE 121 to ‘Path of Datafile location\WAAPP.DBF';

set newname for DATAFILE 120 to ‘Path of Datafile location\TLWORK.DBF';

set newname for DATAFILE 119 to ‘Path of Datafile location\TLLARGE.DBF';

set newname for DATAFILE 118 to ‘Path of Datafile location\TLAPP.DBF';

set newname for DATAFILE 117 to ‘Path of Datafile location\STWORK.DBF';

set newname for DATAFILE 116 to ‘Path of Datafile location\STLARGE.DBF';

set newname for DATAFILE 115 to ‘Path of Datafile location\STAPP.DBF';

set newname for DATAFILE 114 to ‘Path of Datafile location\SRAPP.DBF';

set newname for DATAFILE 113 to ‘Path of Datafile location\SALARGE.DBF';

set newname for DATAFILE 112 to ‘Path of Datafile location\SACAPP.DBF';

set newname for DATAFILE 111 to ‘Path of Datafile location\SAAPP.DBF';

set newname for DATAFILE 110 to ‘Path of Datafile location\PYWORK.DBF';

set newname for DATAFILE 109 to ‘Path of Datafile location\PYLARGE.DBF';

set newname for DATAFILE 108 to ‘Path of Datafile location\PYAPP.DBF';

set newname for DATAFILE 107 to ‘Path of Datafile location\PY0LRG.DBF';

set newname for DATAFILE 106 to ‘Path of Datafile location\PVAPP.DBF';

set newname for DATAFILE 105 to ‘Path of Datafile location\PTWORK.DBF';

set newname for DATAFILE 104 to ‘Path of Datafile location\PTTREE.DBF';

set newname for DATAFILE 103 to ‘Path of Datafile location\PTTLRG.DBF';

set newname for DATAFILE 102 to ‘Path of Datafile location\PTTBL.DBF';

set newname for DATAFILE 101 to ‘Path of Datafile location\PTRPTS.DBF';

set newname for DATAFILE 100 to ‘Path of Datafile location\PTPRJWK.DBF';

set newname for DATAFILE 99 to ‘Path of Datafile location\PTPRC.DBF';

set newname for DATAFILE 98 to ‘Path of Datafile location\PTLOCK.DBF';

set newname for DATAFILE 97 to ‘Path of Datafile location\PTCMSTAR.DBF';

set newname for DATAFILE 96 to ‘Path of Datafile location\PTAUDIT.DBF';

set newname for DATAFILE 95 to ‘Path of Datafile location\PTAPPE.DBF';

set newname for DATAFILE 94 to ‘Path of Datafile location\PTAPP.DBF';

set newname for DATAFILE 93 to ‘Path of Datafile location\PTAMSG.DBF';

set newname for DATAFILE 92 to ‘Path of Datafile location\PSINDEX.DBF';

set newname for DATAFILE 91 to ‘Path of Datafile location\PSIMGR.DBF';

set newname for DATAFILE 90 to ‘Path of Datafile location\PSIMAGE.DBF';

set newname for DATAFILE 89 to ‘Path of Datafile location\POAPP.DBF';

set newname for DATAFILE 88 to ‘Path of Datafile location\PIWORK.DBF';

set newname for DATAFILE 87 to ‘Path of Datafile location\PILARGE.DBF';

set newname for DATAFILE 86 to ‘Path of Datafile location\PIAPP.DBF';

set newname for DATAFILE 85 to ‘Path of Datafile location\PCLARGE.DBF';

set newname for DATAFILE 84 to ‘Path of Datafile location\PCAPP.DBF';

set newname for DATAFILE 83 to ‘Path of Datafile location\PALARGE.DBF';

set newname for DATAFILE 82 to ‘Path of Datafile location\PAAPP.DBF';

set newname for DATAFILE 81 to ‘Path of Datafile location\INAPP.DBF';

set newname for DATAFILE 80 to ‘Path of Datafile location\HTAPP.DBF';

set newname for DATAFILE 79 to ‘Path of Datafile location\HRWORK.DBF';

set newname for DATAFILE 78 to ‘Path of Datafile location\HRSWORK.DBF';

set newname for DATAFILE 77 to ‘Path of Datafile location\HRSLARGE.DBF';

set newname for DATAFILE 76 to ‘Path of Datafile location\HRSARCH.DBF';

set newname for DATAFILE 75 to ‘Path of Datafile location\HRSAPP.DBF';

set newname for DATAFILE 74 to ‘Path of Datafile location\HRLARGE.DBF';

set newname for DATAFILE 73 to ‘Path of Datafile location\HRLARG1.DBF';

set newname for DATAFILE 72 to ‘Path of Datafile location\HRIMAGE.DBF';

set newname for DATAFILE 71 to ‘Path of Datafile location\HRAPP7.DBF';

set newname for DATAFILE 70 to ‘Path of Datafile location\HRAPP6.DBF';

set newname for DATAFILE 69 to ‘Path of Datafile location\HRAPP5.DBF';

set newname for DATAFILE 68 to ‘Path of Datafile location\HRAPP4.DBF';

set newname for DATAFILE 67 to ‘Path of Datafile location\HRAPP3.DBF';

set newname for DATAFILE 66 to ‘Path of Datafile location\HRAPP2.DBF';

set newname for DATAFILE 65 to ‘Path of Datafile location\HRAPP1.DBF';

set newname for DATAFILE 64 to ‘Path of Datafile location\HRAPP.DBF';

set newname for DATAFILE 63 to ‘Path of Datafile location\HPAPP.DBF';

set newname for DATAFILE 62 to ‘Path of Datafile location\GPDEAPP.DBF';

set newname for DATAFILE 61 to ‘Path of Datafile location\GPAPP.DBF';

set newname for DATAFILE 60 to ‘Path of Datafile location\GIAPP.DBF';

set newname for DATAFILE 59 to ‘Path of Datafile location\FSAPP.DBF';

set newname for DATAFILE 58 to ‘Path of Datafile location\FGLARGE.DBF';

set newname for DATAFILE 57 to ‘Path of Datafile location\FGAPP.DBF';

set newname for DATAFILE 56 to ‘Path of Datafile location\FALARGE.DBF';

set newname for DATAFILE 55 to ‘Path of Datafile location\FAAPP.DBF';

set newname for DATAFILE 54 to ‘Path of Datafile location\ERWORK.DBF';

set newname for DATAFILE 53 to ‘Path of Datafile location\ERLARGE.DBF';

set newname for DATAFILE 52 to ‘Path of Datafile location\ERAPP.DBF';

set newname for DATAFILE 51 to ‘Path of Datafile location\EPLARGE.DBF';

set newname for DATAFILE 50 to ‘Path of Datafile location\EPAPP.DBF';

set newname for DATAFILE 49 to ‘Path of Datafile location\EOTPLRG.DBF';

set newname for DATAFILE 48 to ‘Path of Datafile location\EOTPAPP.DBF';

set newname for DATAFILE 47 to ‘Path of Datafile location\EOPPLRG.DBF';

set newname for DATAFILE 46 to ‘Path of Datafile location\EOPPAPP.DBF';

set newname for DATAFILE 45 to ‘Path of Datafile location\EOLTAPP.DBF';

set newname for DATAFILE 44 to ‘Path of Datafile location\EOLARGE.DBF';

set newname for DATAFILE 43 to ‘Path of Datafile location\EOIUWRK.DBF';

set newname for DATAFILE 42 to ‘Path of Datafile location\EOIULRG.DBF';

set newname for DATAFILE 41 to ‘Path of Datafile location\EOIUAPP.DBF';

set newname for DATAFILE 40 to ‘Path of Datafile location\EOEWWRK.DBF';

set newname for DATAFILE 39 to ‘Path of Datafile location\EOEWLRG.DBF';

set newname for DATAFILE 38 to ‘Path of Datafile location\EOEWAPP.DBF';

set newname for DATAFILE 37 to ‘Path of Datafile location\EOEILRG.DBF';

set newname for DATAFILE 36 to ‘Path of Datafile location\EOEIAPP.DBF';

set newname for DATAFILE 35 to ‘Path of Datafile location\EOECWRK.DBF';

set newname for DATAFILE 34 to ‘Path of Datafile location\EOECLRG.DBF';

set newname for DATAFILE 33 to ‘Path of Datafile location\EOECAPP.DBF';

set newname for DATAFILE 32 to ‘Path of Datafile location\EODSLRG.DBF';

set newname for DATAFILE 31 to ‘Path of Datafile location\EODSAPP.DBF';

set newname for DATAFILE 30 to ‘Path of Datafile location\EOCULRG.DBF';

set newname for DATAFILE 29 to ‘Path of Datafile location\EOCUAPP.DBF';

set newname for DATAFILE 28 to ‘Path of Datafile location\EOCMWRK.DBF';

set newname for DATAFILE 27 to ‘Path of Datafile location\EOCMLRG.DBF';

set newname for DATAFILE 26 to ‘Path of Datafile location\EOCMAPP.DBF';

set newname for DATAFILE 25 to ‘Path of Datafile location\EOCFAPP.DBF';

set newname for DATAFILE 24 to ‘Path of Datafile location\EOBFAPP.DBF';

set newname for DATAFILE 23 to ‘Path of Datafile location\EOAPP.DBF';

set newname for DATAFILE 22 to ‘Path of Datafile location\DTAPP.DBF';

set newname for DATAFILE 21 to ‘Path of Datafile location\DIAPP.DBF';

set newname for DATAFILE 20 to ‘Path of Datafile location\CULARGE.DBF';

set newname for DATAFILE 19 to ‘Path of Datafile location\CULARG3.DBF';

set newname for DATAFILE 18 to ‘Path of Datafile location\CULARG2.DBF';

set newname for DATAFILE 17 to ‘Path of Datafile location\CULARG1.DBF';

set newname for DATAFILE 16 to ‘Path of Datafile location\CUAUDIT.DBF';

set newname for DATAFILE 15 to ‘Path of Datafile location\COAPP.DBF';

set newname for DATAFILE 14 to ‘Path of Datafile location\CCAPP.DBF';

set newname for DATAFILE 13 to ‘Path of Datafile location\BNLARGE.DBF';

set newname for DATAFILE 12 to ‘Path of Datafile location\BNAPP.DBF';

set newname for DATAFILE 11 to ‘Path of Datafile location\BDAPP.DBF';

set newname for DATAFILE 10 to ‘Path of Datafile location\AVAPP.DBF';

set newname for DATAFILE 9 to ‘Path of Datafile location\AMAPP.DBF';

set newname for DATAFILE 8 to ‘Path of Datafile location\ADAPP.DBF';

set newname for DATAFILE 7 to ‘Path of Datafile location\AALARGE.DBF';

set newname for DATAFILE 6 to ‘Path of Datafile location\AAAPP.DBF';

set newname for DATAFILE 5 to ‘Path of Datafile location\PSDEFAULT.DBF';

set newname for DATAFILE 4 to ‘Path of Datafile location\USERS01.DBF';

set newname for DATAFILE 3 to ‘Path of Datafile location\SYSAUX01.DBF';

set newname for DATAFILE 2 to ‘Path of Datafile location\UNDOTBS01.DBF';

set newname for DATAFILE 1 to ‘Path of Datafile location\SYSTEM01.DBF';

SQL "ALTER DATABASE RENAME FILE ''D:\oradata\PROD\REDO01.LOG'' TO '‘Path of Datafile location\REDO01.LOG'' ";

SQL "ALTER DATABASE RENAME FILE ''D:\oradata\PROD\REDO02.LOG'' TO '‘Path of Datafile location\REDO02.LOG'' ";

SQL "ALTER DATABASE RENAME FILE ''D:\oradata\PROD\REDO03.LOG'' TO '‘Path of Datafile location\REDO03.LOG'' ";

RESTORE DATABASE;

SWITCH DATAFILE ALL;

# recover the database

RECOVER DATABASE;

}

9>.       Start database with restetlogs,

            SQL> alter database open resetlogs;

10>.     Drop PSTEMP

            SQL>drop tablespace pstmp;

11>.     Create PSTEMP

SQL>CREATE TEMPORARY TABLESPACE PSTEMP TEMPFILE                ‘Path of Datafile location\pstemp01.dbf'     SIZE 3000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K

            SQL>alter database default temporary tablespace pstemp;

12>.     Need to drop TEMP also.

            SQL> drop tablespace temp;

13>.     Create TEMP

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE                ‘Path of Datafile location\temp01.dbf'     SIZE 1024M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K

SQL> alter database default temporary tablespace temp;

After cloning Need to change Database name and ID with ‘NID’  utility. Currently Database Name is ‘PROD’ and need to change with ‘New Name’.

1>.       Shut down Database and start with mount

            SQL> shut immediate

            SQL> startup mount pfile=Path of pfile

2>.       NID utility.

            Nid target=sys/<sys password> DBNAME=’New Name’

3>.       After DBNEWID successfully changes the DBID, shut down the database:

             SQL>SHUTDOWN IMMEDIATE

4>.       Mount the database. For example:

             SQL>STARTUP MOUNT

5>.       Open the database in RESETLOGS mode and resume normal use. For example:

             SQL> ALTER DATABASE OPEN RESETLOGS;

 ############################# ENJOY NOW#############################