Cloning from Prodtest database to devtest database

On production server (prodtest server)

1.	RMAN Full database backup on prodtest database :

C:\ rman nocatalog target sys/sys_password

Rman> run
		{allocate channel c1 type disk format ‘f:\backup\%U.bak’;
  		  allocate channel c2 type disk format ‘f:\backup\%U.bak’;
		  allocate channel c3 type disk format ‘f:\backup\%U.bak’;
		 backup database;
		}

2.	Some Archive files backup (from 1hour before full backup to the latest seq#)

Rman> run
		{allocate channel c1 type disk format ‘f:\backup\%U.bak’;
		  Backup archivelog from sequence 13645 until 13668;
		}

3.	Control file backup
SQL> alter database backup controlfile to ‘d:\backup\controlfile_prodtest.bak’

4.	Copy backup folder to target server (devtest) 

Copy whole backup folder to the target server including database, archive and control files backup. (Location of backup folder should be some on both prodtest and devtest servers). Also create folders for datafiles, controlfiles, redolog files, bdump, udump, cdump, archivefiles and parameter file.	

5.	Copy init parameter file.

Copy initialization parameter file (initprodtest.ora) from prodtest to devtest.

On target server (devtest server)

6.	create service name with old name on devtest server
c:\> oradim –new –sid prodtest –intpwd  sys_password

7.	change parameter in initprodtest.ora parameter file on devtest server 
Change parameters like controlfiles, bdump, udump, cdump, archive_log_Dest if path is different from prodtest server.

8.	restore backup control file to original location and rename it
     Copy and rename controlfile to the location same as in parameter file from backup controlfile copy
controlfile_prodtest.bak    -->   control01.ctl and control02.ctl


9.	Set oracle sid and connect with sys
c:\> set oracle_sid=prodtest
c:\> sqlplus “sys/sys_password as sysdba”

10.	start database mount

SQL> Startup pfile= d:\<path_of_pfile\initprodtest.ora mount;

11.	connect to rman
c:\> rman nocatalog target sys/sys_password

12.	restore database with set newname 
RMAN>run
{
allocate channel c1 type disk format ‘f:\backup\%U.bak’;
allocate channel c2 type disk format ‘f:\backup\%U.bak’;
allocate channel c3 type disk format ‘f:\backup\%U.bak’;
		set newname for datafile 1 to ‘<new_path_for_datafile>’;
		set newname for datafile 2 to ‘<new_path_for_datafile>’;
		set newname for datafile 3 to ‘<new_path_for_datafile>’;
                -------  ----  ---- ------   -------
                      -------  ----  ---- ------   -------
 		set newname for datafile 389 to ‘<new_path_for_datafile>’;
                      restore database;
		}
NOTE:-  No need to give “set newname for datafile “  if path of datafiles on both prodtest and devtest is same. If so many files are there in database then better take output in excel from below sql statement
To check existing path and name for all datafiles.
Sql> select file#, name from v$datafile; (dba_data_files doesn’t work on mount stage)
 And then copy it in the rman script.
                          
13.	change datafiles path in control file

If path of datafiles on prodtest and devtest is not same and we have restored with set newname command then we need to change path in controlfile also.

SQL> alter database rename file ‘<old_path\datafile_name.dbf>’ to ‘<new_path\datafile_name.dbf’;

NOTE:- We need to change in control file with above command For all datafiles in database. We can use v$datafile and substr function to create script for that.

14.	recover database until latest sequence # in backup

RMAN> run
		{
		Set until sequence to 13668 thread 1;
               Recover database;
                }
13668 is the latest sequence # in backup set of archivelogs



15.	 Open database with reset logs

                 SQL> alter database open reset logs;

16.	 Shutdown database.

SQL> Shutdown immediate;

17.	 Startup mount

SQL> startup pfile= path_of_pfile\initprodtest.ora mount;

18.	Change database name with NID utility

C:\> set oracle_sid=prodtest
C:\> NID target=sys/sys_password dbname=devtest

19.	shutdown database

SQL> Shutdown immediate;

20.	create new password file

c:\> orapwd file=<ORACLE_BASE\dbs\pwddevtest.ora

21.	change dbname parameter in init file
rename initprodtest.ora file to initdevtest.ora
change parameter DBNAME in initdevtest.ora file with new dbname (devtest)

22.	startup mount

23.	open database with resetlogs.

                 SQL> alter database open reset logs;

24.	shutdown database

SQL> Shutdown immediate;

25.	create service with new name
c:\> oradim –new –sid devtest –intpwd sys_password

26.	connect to new service
c:\> set oracle_sid=devtest
c:\> sqlplus “sys/sys_password as sysdba”

27.	startup  database
SQL> startup pfile=<path_of_pfile>\initdevtest.ora
(sql> create spfile from pfile=’ <path_of_pfile>\initdevtest.ora’)
After successful completion of all above steps you can delete old service from devtest server with command   C:\> ORADIM –delete –sid prodtest