Data base Cloning

 Quick Database Cloning

One of the common tasks of the DBAs is to clone a database. Very often there is a need to create a test or dev database which is a replica of Production database. In such cases Database cloning can be of great help. This process is quick and has simple steps. This is one of the fastest ways to copy a database to another server. Before you clone the database it is important to know that this procedure is Operating System dependent. You cannot clone a database from HP-UX to Windows server or Red Hat Linux server. The simple reason being the difference in Endianness. So before starting the cloning process, please make sure that the target and source OS are same or atleast follow same Endianness. 
     
These are the steps required to complete the clone:

• Configure The Network 
• Create A Password File For The New Database
• Create An Init.Ora For The New Database
• Create The Admin Directory For The New Database
• Shutdown And Startup Mount The Source Database
• Startup Nomount The New Database
• Connect To The Target (Source) And Auxiliary (New Clone) Databases Using Rman
• Execute The Duplicate Command
• Remove The Old Pfile 
• Check The New Database 

1. Create the script from Source database

Connect to the source database using SQL*PLUS (as SYSDBA) and execute the following command:

SQL> alter database backup controlfile to trace; 

This command will generate a script containing a create database syntax in the trace file directory identified by USER_DUMP_DEST parameter in init.ora file. The file looks like this:

STARTUP NOMOUNT 

CREATE CONTROLFILE REUSE DATABASE “SRC_DB” NORESETLOGS

NOARCHIVELOG 

MAXLOGFILES 20

MAXLOGMEMBERS 3 

MAXDATAFILES 300 

MAXINSTANCES 1

MAXLOGHISTORY 115

LOGFILE 

GROUP 1 (‘/app1/oradata/src_db/log1a.dbf’, ‘/app1/oradata/src_db/log1b.dbf’) SIZE 50M, 

GROUP 2 (‘/app2/oradata/src_db/log2a.dbf’, ‘/app2/oradata/src_db/log2b.dbf’) SIZE 50M 

DATAFILE

‘/appdata/oradata/src_db/system01.dbf’, ‘/appdata/oradata/src_db/srcdatabase.dbf’ ; 

# Recovery is required if any of the datafiles are restored 

# backups, or if the last shutdown was not normal or immediate. RECOVER DATABASE

# Database can now be opened normally. 

ALTER DATABASE OPEN; 

2. Shutdown the source database

SQL> Shutdown immediate 

3. Copy datafiles to destination server

Copy all the datafiles on the destination server. You may change the filenames if you wish, in this case you will also have to edit the controlfile to reflect the new datafile names of the destination server. 

rcp /app1/oradata/src_db/* newhost:/app1/oradata/dest_db

rcp /app2/oradata/src_db/* newhost:/app2/oradata/dest_db

rcp /appdata/oradata/src_db/* newhost:/appdata/oradata/dest_db 

4. Edit the Create database command

Change the Create controlfile clause in the Create database command generated in STEP1:

Old:

CREATE CONTROLFILE REUSE DATABASE “SRC_DB” NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE “DEST_DB” RESETLOGS 

5. Remove extra clauses

Remove the “recover database” and “alter database open” clause from the script generated in STEP1:

# Recovery is required if any of the datafiles are restored # backups, or if the last shutdown was not normal or immediate. #RECOVER DATABASE # Database can now be opened normally. #ALTER DATABASE OPEN; 

6. Change the path of datafiles

Change the path of the datafiles in the script generate in STPE1 to reflect the path on the destination server

Old:

DATAFILE ‘/appdata/oradata/src_db/system01.dbf’, ‘/appdata/oradata/src_db/mydatabase.dbf’

New:

DATAFILE ‘/appdata/oradata/dest_db/system01.dbf’, ‘/appdata/oradata/dest_db/mydatabase.dbf’ 

7. Create the dump directories 

 Create the dump directories: bdump, cdump, udump

>cd /app/admin 

>mkdir bdump cdump udump pfile 

8. Copy the Initialization parameter file

Copy the old init.ora file to the destination server

rcp /app/admin/src_db/pfile/*.ora newhost:/app/oracle/admin/dest_db/pfile 

9. Create the new database

SQL> @db_create_controlfile.sql 

Open the database using 'ALTER DATABASE OPEN RESETLOGS' command

Finally, your cloned database is ready to use.


Read more: 
RMAN 11g cloning using Duplicate command
RMAN Backup Views