Oracle database Cloning

      No Comments on Oracle database Cloning

Oracle database cloning step by step

What is Cloning?

  • Database Cloning is a procedure that can be used to create an identical copy of the existing Oracle database. DBA’s occasionally need to clone databases to test backup and recovery strategies or export a table that was dropped from the production database and import it back into the production databases. Cloning can be done on a different host or on the same host even it is different from standby database.

Reason for Cloning

  • In all oracle development, QA and production environment there will become the requirement to transport the full database from one physical machine or server to a different machine. This copy may be used for development, production testing PA etc.. but make sure that this need will request and management will ask you to perform this task quickly. Listed below are the most typical uses:
  • Relocating an Oracle database one server to another server
  • Moving Oracle database to new Storage.
  • Renaming Oracle databases.

Database Cloning can be done using the following methods,

  • Cold Cloning
  • Hot Cloning
  • RMAN Cloning

Here is a brief explanation how to perform cloning in all these three methods

 METHOD 1: COLD CLONING

  • Cold Cloning is one the reliable methods that is done using the Cold Backup. The drawback of this method is that the database has to be shutdown while taking the cold backup.

Considerations:

  • Source Database Name: PROD
  • Clone Database Name: CLONEDB
  • Source Database physical files path=/u01/PROD/oradata
  • Cloned Database physical files path=/u02/CLONEDB/oradata

Steps to be followed:

Startup the source database (if not open)
$ export ORACLE_SID=PROD
$ sqlplus / as sysdba

SQL> startup
Find out the path and names of datafiles, control files, and redo log files.

SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;

Take the control file backup.
SQL> alter database backup controlfile to trace;

Parameter file backup.  If ‘PROD’ database is using spfile,

SQL> create pfile=’/u02/CLONEDB/initCLONEDB.ora’ from spfile;
If database is using pfile, use OS command to copy the pfile to a backup location.
Shutdown the ‘PROD’ database

SQL> shutdown
Copy all data files, control files, and redo log files of ‘PROD’ database to a target database location.

$ mkdir /u02/CLONEDB/oradata

$ cp /u01/PROD/oradata/* /u02/CLONEDB/oradata/

Create appropriate directory structure in clone database for dumps and specify them in the parameter file.

$ mkdir -p /u02/CLONEDB/{bdump,udump}

Edit the clone database parameter file and make necessary changes to the clone database

$ cd /u02/CLONEDB/

$ vi initCLONEDB.ora

db_name=CLONEDB
control_files=/u02/CLONEDB/oradata/cntrl01.ctl
background_dump_dest=/u02/CLONEDB/bdump
user_dump_dest=/u02/CLONEDB/udump
. . .
. . .
:wq!

Startup the clone database in NOMOUNT stage.

$ export ORACLE_SID=CLONEDB

SQL> startup nomount pfile=’/u02/CLONEDB/initCLONEDB.ora’

Create the control file trace for the clone database using the trace control file and specify the appropriate paths for redolog and datafiles.

CREATE CONTROLFILE SET DATABASE “CLONEDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u02/CLONEDB/oradata/redo01.log’ SIZE 5M,
GROUP 2 ‘/u02/CLONEDB/oradata/redo02.log’ SIZE 5M,
DATAFILE
‘/u02/CLONEDB/oradata/system01.dbf’,
‘/u02/CLONEDB/oradata/undotbs01.dbf’,
‘/u02/CLONEDB/oradata/sysaux01.dbf’,
‘/u02/CLONEDB/oradata/users01.dbf’,
‘/u02/CLONEDB/oradata/example01.dbf’
CHARACTER SET AL32UTF8

Create the control file by running from the trace path

SQL> @u01/PROD/source/udump/cntrl.sql

Once the control file’s successfully created, open the database with resetlogs option.

SQL> alter database open resetlogs;

METHOD 2: HOT CLONING

Hot database cloning is more suitable for databases which are running 24X7X365 type of databases and is done using the hot backup. For hot database cloning, database has to be in archivelog mode and there no need to shutdown the database.

Considerations:

Source Database Name: PROD
Clone Database Name: CLONEDB
Source Database physical files path=/u01/PROD/oradata
Cloned Database physical files path=/u02/CLONEDB/oradata

Steps to be followed:

  1. Find out the path and names of datafiles.

SQL> select name from v$datafile;

  1. Backup the parameter file

If ‘PROD’ database is using spfile create pfile,

SQL> create pfile=’/u02/CLONEDB/initCLONEDB.ora’ from spfile;

If database is using pfile, use OS command to copy the pfile to a backup location.
3. Note down the oldest log sequence number.

SQL> alter system switch logfile;
SQL> archive log list;

Place the database tobackup mode

SQL> alter database begin backup;

  1. Copy all data files of ‘PROD’ database to a clone location.

  $ mkdir /u02/CLONEDB/oradata
$ cp /u01/PROD/source/oradata/*.dbf /u02/CLONEDB/oradata/

  1. After copying all datafiles, release the database from backup mode.

  SQL> alter database end backup;

  1. Switch the current log file and note down the oldest log sequence number

  SQL> alter system switch logfile;

  SQL> archive log list;

  1. Copy all archive log files generated during FIRST old log sequence no. to the LAST old log sequence no. during which the database was in backup mode.
  2. Take the control file trace backup to the trace path

  SQL> alter database backup controlfile to trace;

  1. Create appropriate directory structure for the clone database and specify the same

  $ cd /u02/CLONEDB

   $ mkdir bdump udump

  1. Edit the clone database parameter file and make necessary changes to the clone database

  $ cd /u02/CLONEDB

  $ vi initCLONEDB.ora
db_name=CLONEDB
control_files=/u02/CLONEDB/oradata/cntrl01.ctl
background_dump_dest=/u02/CLONEDB/bdump
user_dump_dest=/u02/CLONEDB/udump

. .. . .

:wq!

  1. Startup the cloned database in NOMOUNT phase.

$ export ORACLE_SID=CLONEDB

SQL> startup nomount pfile=’/u02/CLONEDB/initCLONEDB.ora’

  1. Create the control file for the clone database using the trace control file.

CREATE CONTROLFILE SET DATABASE “CLONEDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u02/CLONEDB/oradata/redo01.log’ SIZE 5M,
GROUP 2 ‘/u02/CLONEDB/oradata/redo02.log’ SIZE 5M,
DATAFILE
‘/u02/CLONEDB/oradata/system01.dbf’,
‘/u02/CLONEDB/oradata/undotbs01.dbf’,
‘/u02/CLONEDB/oradata/sysaux01.dbf’,
‘/u02/CLONEDB/oradata/users01.dbf’,
‘/u02/CLONEDB/oradata/example01.dbf’
CHARACTER SET AL32UTF8;

  1. Create the control file by running trace file from the trace path

SQL> @u01/PROD/source/udump/cntrl.sql

  1. Recover the database using backup controlfile option.

SQL> recover database using backup controlfile until cancel;

  1. You will be prompted to feed the archive log files henceforth. Specify the absolute path and file name for the archive log files and keep feeding them until you cross the LAST old sequence no. (Refer: Step 8), typeCANCEL to end the media recovery.
  2. Open the database with resetlogsoption.

SQL> alter database open resetlogs;

METHOD 3 : RMAN CLONING:

RMAN provides the DUPLICATE command, which uses the backups of the database to create the clone database. Files are restored to the target database, after which an incomplete recovery is performed and the clone database is opened using RESETLOGS option. All the preceding steps are performed automatically by RMAN without any intervention from the DBA.

Considerations:

Source Database Name: PROD
Clone Database Name: CLONEDB
Source Database physical files path=/u01/PROD/oradata
Cloned Database physical files path=/u02/CLONEDB/oradata

Steps to be followed:

  1. Parameter file backup.
    If ‘PROD’ database is using spfile,

  SQL> create pfile=’/u02/CLONEDB/initCLONEDB.ora’ from spfile;

  If the database is using pfile, use OS command to copy the pfile to a backup location.

  1. Create appropriate directory structure for the clone database

  $ cd /u02/CLONEDB
$ mkdir bdump udump

  1. Edit the clone database parameter file

  $ cd /u02/CLONEDB

  $ vi initCLONEDB.ora

db_name=CLONEDB
control_files=/u02/CLONEDB/oradata/cntrl01.ctl
db_file_name_convert=(‘/u01/PROD/oradata’,’/u02/CLONEDB/oradata’)

This parameter specifies from where to where the datafiles should be cloned

log_file_name_convert=(‘/u01/PROD/oradata’,’/u02/CLONEDB/oradata’)

This parameter specifies from where to where the redologfiles should be cloned

background_dump_dest=/u02/CLONEDB/bdump
user_dump_dest=/u02/CLONEDB/udump
. . .
. . .
:wq!
NOTE: db_file_name_convert and log_file_name_convert parameters are required only if the      source database directory structure and clone database directory structure differs.
4. Configure the listener using ‘listener.ora’ file and start the listener

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/oracle/product/10.2.0/db_1/)
(SID_NAME =PROD)
)
(SID_DESC =
(GLOBAL_DBNAME = CLONEDB)
(ORACLE_HOME = /u02/oracle/product/10.2.0/db_1/)
(SID_NAME =CLONEDB)
)
)
5. Add the following information to the ‘tnsnames.ora’ file.

con_CLONEDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 200.168.1.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLONEDB)
)
)

  1. Startup the database in NOMOUNT stage and exit.
    $ export ORACLE_SID=CLONEDB
    SQL> startup nomount pfile=’/u02/CLONEDB/initCLONEDB.ora’
    SQL> exit
  2. Start RMAN, make ‘PROD’ as target and ‘CLONEDB’ as auxiliary.

  $ export ORACLE_SID=PROD
$ rman target / auxiliary sys/sys@con_CLONEDB

  1. Issue the RMAN DUPLICATE command to start the cloning process.
    RMAN> duplicate target database to ‘CLONEDB’;
    NOTE: The preceding command restores all files from the backup of the target database to the clone database destination using all available archive log files .

      RMAN opens the clone database with resetlogs option.

Leave a Reply

Your email address will not be published. Required fields are marked *