Monday, April 11, 2011

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

3 comments:

Pradeep said...

Hi Chandu,

Can I perform this cloning between Oracle 10.2 Database and 11.2 Database?

And what is the effect of Oracle version for this cloning?

Thank You in advance,
Pradeep.

chandu reddy said...

Hi pradeep,

yes, certainly its just like upgrading 10g to 11g using datapump

http://chandu208.blogspot.com/2011/04/upgradation-10gr2-to-11gr1.html

Ramesh Revella said...

Hi Chandu,

Your blog is very help full to me. If you have steps for oracle 11gR2 RAC installation using Virtual Box. Please upload.

Post a Comment

Auto Scroll Stop Scroll