EDUCBA

Home Data Science Data Science Tutorials Oracle Tutorial Oracle Clone Database

Oracle Clone Database

By Priya PedamkarPriya Pedamkar

Oracle Clone Database

Introduction to Oracle CLONE Database

Oracle Database Cloning is nothing but a procedure that is used to create an identical database of the existing Oracle database. The cloning technique is used to make a copy of the existing Database to perform various test activities by DBA like backup and recovery. In this topic, we are going to learn about Oracle Clone Database.

  • DBA performs Oracle database cloning to test backup and recovery strategies.
  • This technique uses to export Oracle object that gets dropped somehow from the production database.
  • Cloning can be performed separately or on the same host.

Why Cloning Needed?

  • To perform a backup and recovery strategy.
  • To keep a backup of the production database.
  • That copy of the database can be used for development.
  • That copy of the database can be used for production testing, beta testing, etc.
  • This technique is used to take a copy of the database before renaming that.
  • It is used for relocating an Oracle database to another machine.
  • This technique is very useful for moving the Oracle database to new storage media.
  • Oracle cloning is often the fastest way to make a copy of an existing Oracle database.
  • Oracle cloning procedure mostly used by DBA to provide a full-sized DEV instance to the developers.

Types of Oracle Cloning

There are three methods to perform Oracle Database Cloning. Those are listed below.

  • Cold Cloning
  • Host Cloning
  • RMAN Cloning

We’ll go ahead with a brief explanation of how cloning operations can be performed in all these three methods.

1. COLD Cloning

It is a reliable method to perform Oracle Database Cloning. To perform Database Cloning, require database information as given below: Here file name and path is assumed, it can vary user to use depends on the installation.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • Source Database Name: PROD
  • Source Database Physical File Path:/u01/PROD/oradata
  • Clone Database Name: PRODCLON
  • Clone Database Physical File Path:/u01/PRODCLON/oradata

Steps:

1. Start the source database

export ORACLE_SID=PROD
sqlplus / as sysdba
startup

2. Find the path and names of datafiles, control files, and redo log files.

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

3. Take the control file backup.

alter database backup controlfile to trace;

4. Parameter file backup.

If ‘PROD’ database is using spfile,

create pfile=’/u02/PRODCLON/initPRODCLON.ora’ from spfile;

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

5. Shutdown the ‘PROD’ database

shutdown

6. Copy all data files, control files, and redo log files of the ‘PROD’ database to a target database location.

mkdir /u02/PRODCLON/oradata
cp /u01/PROD/oradata/* /u02/PRODCLON/oradata/

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

mkdir -p /u02/PRODCLON/{bdump,udump}

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

cd /u02/PRODCLON/
vi initPRODCLON.ora
db_name=PRODCLON
control_files=/u02/PRODCLON/oradata/cntrl01.ctl
background_dump_dest=/u02/PRODCLON/bdump
user_dump_dest=/u02/PRODCLON/udump
. . .
. . .
:wq!

9. Start the clone database in NOMOUNT stage.

export ORACLE_SID=PRODCLON
startup nomount pfile=’/u02/PRODCLON/initPRODCLON.ora’

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

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

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

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

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

alter database open resetlogs;

2. HOT Cloning

Hot Cloning method is more suitable for a 24*7*365 running database. To apply this method for database cloning, the Database has to be in archive log mode, and this method, no need to shut down the database.

  • Source Database Name: PROD
  • Source Database Physical File Path:/u01/PROD /oradata
  • Clone Database Name: PRODCLON
  • Clone Database Physical File Path:/u01/PRODCLON/oradata

Steps:

1. Find the path and names of data files.

select name from v$datafile;

2. Backup the parameter file

If ‘PROD’ database is using spfile create pfile,

create pfile=’/u02/PRODCLON/initPRODCLON.ora’ from spfile;

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

3. Note down the oldest log sequence number.

alter system switch logfile;
archive log list;

4. Place the database to backup mode

alter database begin backup;

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

mkdir /u02/PRODCLON/oradata
cp /u01/PROD/source/oradata/*.dbf /u02/PRODCLON/oradata/

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

alter database end backup;

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

alter system switch logfile;
archive log list;

8. Copy all archive log files generated during the first old log sequence no.to the LAST old log sequence no.

9. Take the control file trace backup to the trace path

alter database backup controlfile to trace;

10. Create a directory structure for the clone database and specify the same

cd /u02/PRODCLON
mkdirbdumpudump

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

cd /u02/PRODCLON
vi initPRODCLON.ora
db_name=PRODCLON
control_files=/u02/PRODCLON/oradata/cntrl01.ctl
background_dump_dest=/u02/PRODCLON/bdump
user_dump_dest=/u02/PRODCLON/udump
. . .
. . .
:wq!

12. Start the clone database in NOMOUNT phase.

export ORACLE_SID=PRODCLON
startup nomount pfile=’/u02/PRODCLON/initPRODCLON.ora’

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

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

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

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

15. Recover the database using a backup controlfile option.

recover database using backup controlfile until cancel;

16. 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), type CANCEL to end the media recovery.

17. Open the database with resetlogs option.

alter database open resetlogs;

3. RMAN Cloning

There is a DUPLICATE command that is provided by RMAN and that is used for backup of the database to create the clone database. This command restores files to the target database after which an incomplete recovery is performed and the clone database is opened using the RESETLOGS option.

  • Source Database Name: PROD
  • Source Database Physical File Path:/u01/PROD /oradata
  • Clone Database Name: PRODCLON
  • Clone Database Physical File Path:/u01/PRODCLON/oradata

Steps:

1. Parameter file backup.

If ‘PROD’ database is using spfile,

create pfile=’/u02/PRODCLON/initPRODCLON.ora’ from spfile;

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

2. Create an appropriate directory structure for the clone database

cd /u02/PRODCLON
mkdirbdumpudump

3. Edit the clone database parameter file

cd /u02/PRODCLON
viinitPRODCLON.ora
db_name=PRODCLON
control_files=/u02/PRODCLON/oradata/cntrl01.ctl
db_file_name_convert=(‘/u01/PROD/oradata’,’/u02/PRODCLON/oradata’)

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

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

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

background_dump_dest=/u02/PRODCLON/bdump
user_dump_dest=/u02/PRODCLON/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 = PRODCLON)
(ORACLE_HOME = /u02/oracle/product/10.2.0/db_1/)
(SID_NAME =PRODCLON)
)
)

5. Add the following information to the ‘tnsnames.ora’ file.

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

6. Startup the database in NOMOUNT stage and exit.

export ORACLE_SID=PRODCLON
startup nomount pfile=’/u02/PRODCLON/initPRODCLON.ora’
exit

7. Start RMAN, make ‘PROD’ as a target, and ‘PRODCLON’ as auxiliary.

export ORACLE_SID=PROD
rman target / auxiliary sys/sys@con_PRODCLON

8. Issue the RMAN DUPLICATE command to start the cloning process.

RMAN> duplicate target database to ‘PRODCLON’;

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.

Conclusion – Oracle Clone Database

Oracle Cloning is a very useful procedure to take the existing database back up. Administrators do not need to connect to the source database while performing database cloning and that reduces the chances of impact on the production system.

Recommended Articles

This is a guide to Oracle Clone Database. Here we discuss why cloning is needed and 3 types of Oracle Cloning along with the respective steps. You may also have a look at the following articles to learn more –

  1. Oracle COALESCE
  2. Oracle CTE
  3. Oracle REGEXP_REPLACE
  4. PIVOT in Oracle
0 Shares
Share
Tweet
Share