Cloning a Database

Cloning a database allows the creation of replicas of a production system for the purposes of training, development or the practising of disaster recovery procedures. Here we are considering two databases Sid LIVE and Sid DEVP . Here we are assuming both the database running in different machines or in the same machine with different location of database files.

Here we are doing the cloning manually not through any tool (Like RMAN ..).

STEPS TO BE PERFORM IN LIVE DATABASE.

1. We start by taking a tracefile backup of the control file of database (LIVE) that is running in the fully open state:

That command produces a text file in the directory to be specified as your USER_DUMP_DEST:

SQL> show parameter user_dump_dest

This will give the result like
User_dump_dest string /oracle/admin/ORACLE_SID(LIVE)/udump

SQL> alter database backup controlfile to trace resetlogs;
Database altered.

SQL> exit
$ cd /oracle/admin/ORACLE_SID(LIVE)/udump
$ls –ltr *.trc

This will give u the file required to create new control file for DEVP database.Like ora_22213.trc.Change it to sql file .

$ mv ora_22213.trc ora_22213.sql

STEPS TO BE PERFORM IN DEVP DATABASE

1. Shut down the database DEVP cleanly. Apparently this is quit important.

Login as sysdba

SQL > shutdown immediate

………………………………

Database dismounted

Database shutdown

Oracle Instance Shut Down

2. Delete all the datafiles ,controlfiles and redolog files from the location specified for

the database DEVP.

3. Copy the file ora_22213.sql from LIVE environment to DEVP env.

mv /oracle/admin/ORACLE_SID(LIVE)/udump

/oracle/admin/ORACLE_SID(DEVP)/udump

4. Now edit the ora_22213.sql file. Here snapshot of the file are given,how it looks

like.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “LIVE” RESETLOGS ARCHIVELOG

MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/oracle/oradata/LIVE/redo01.log’ SIZE 10M,
GROUP 2 ‘/oracle/oradata/LIVE/redo02.log’ SIZE 10M,
GROUP 3 ‘/oracle/oradata/LIVE/redo03.log’ SIZE 10M—STANDBY LOGFILE
DATAFILE
’/oracle/oradata/LIVE/system01.dbf’,
’/oracle/oradata/LIVE/undotbs01.dbf’,
’/oracle/oradata/LIVE/sysaux01.dbf’,
’/oracle/oradata/LIVE/users01.dbf’
CHARACTER SET WE8ISO8859P1
;
-- Commands to re-create incarnation table—Below log names MUST be changed to existing filenames on—disk. Any one log file from each branch can be used to—re-create incarnation records.—ALTER DATABASE REGISTER LOGFILE

Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE—Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.—Online tempfiles have complete space information.—Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/oradata/LIVE/temp01.dbf’
SIZE 15728640 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.

Change CREATE CONTROLFILE REUSE DATABASE “LIVE” RESETLOGS ARCHIVELOG to CREATE CONTROLFILE SET DATABASE “DEVP” RESETLOGS NO ARCHIVELOG

Change wherever the /oradata/LIVE to /oradata/DEVP.

You also need to remove the reference to the script to recovering the database USING BACKUP CONTROLFILE. You’re actually going to be re-creating a brand new controlfile, not using a backup one, so that line should just read RECOVER DATABASE.

You should therefore end up with a script which reads like this:

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE “LIVE” RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/oracle/oradata/DEVP/redo01.log’ SIZE 10M,
GROUP 2 ‘/oracle/oradata/DEVP/redo02.log’ SIZE 10M,
GROUP 3 ‘/oracle/oradata/DEVP/redo03.log’ SIZE 10M—STANDBY LOGFILE
DATAFILE
’/oracle/oradata/ DEVP /system01.dbf’,
’/oracle/oradata/ DEVP /undotbs01.dbf’,
’/oracle/oradata/ DEVP /sysaux01.dbf’,
’/oracle/oradata/ DEVP /users01.dbf’

CHARACTER SET WE8ISO8859P1
;
-- Commands to re-create incarnation table—Below log names MUST be changed to existing filenames on—disk. Any one log file from each branch can be used to—re-create incarnation records.—ALTER DATABASE REGISTER LOGFILE

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 zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.—Online tempfiles have complete space information.—Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/oracle/oradata/DEVP/temp01.dbf’
SIZE 15728640 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.—

Now for copying the datafile from LIVE to DEVP env.

1. Shutdown the database LIVE cleanly

Login as sysdba SQL>shutdown immediate.

2. copy all the datafile from the location like ‘/oracle/oradata/ LIVE /system01.dbf’ to

‘/oracle/oradata/ DEVP /system01.dbf’. like

Mv /oracle/oradata/ LIVE/* /oracle/oradata/ DEVP

Go to DEVP area and run

Cd /oracle/admin/ORACLE_SID(DEVP)/udump

SQL>ora_22213.sql

This will create the new control file and start the database.