What is Oracle Database cloning:
A database clone is a complete and separate copy of a database system that includes the business data, the DBMS software, and any other application tiers that make up the environment. The cloning can be done on the same host or a separate host.
What is the need for Database cloning?
Cloning may be required for several reasons
(1) Create a test env which is a replica of Production. IT and Business can test the things before moving the changes to Production
(2) Create a temporary environment to recover the dropped table on Production. Suppose a user drops a table by mistake in Production. We can restore the Production backup on a separate host and recover before the table drops to recover the table data. Then this table can be imported into the Production
(3) Many times system that hosts the database gets retired then at that we need to relocate the database to the new host.
(4) Creating another Production env from the existing production
There could be many more things.
How the Database cloning is performed
There are two parts of cloning
(1) Oracle software cloning
(2) Database cloning basically the datafiles and instance
How to clone Oracle RDBMS software
Prerequisite: Please note that Perl 5.6 or higher is required when cloning Oracle11g Release 2.
Step 1: Create a tarball of the source home
cd $ORACLE_HOME
tar -cvf /tmp/clone_oracle.tar .
and copy it to the target host
Some points to note
(1) Do not use the command “tar cvf /tmp/clone_oracle.tar $ORACLE_HOME” because the full path for the $ORACLE_HOME will be included in the clone_oracle.tar file and this will cause issues while extracting the tar files on the target note
(2) There should be no requirement to shut down any databases, listeners, agents, etc. that are running from the source home before copying the source installation because any processes that load the static binaries or libraries into memory should not hold a write lock.
(3) If you can run the tar with the root owner, then it would be great as the permission and time would be preserved
We can also use cp or rcp in case tar is easily available, use the -p option to preserve the timestamp
For example:
cp -Rp /u064/app/oracle/11.2.0 /u01/app/oracle/11.2.0_clone
NOTE: This command can be executed by either the ‘root’ user or the owner of the $ORACLE_HOME (for example, ‘oracle’). The intention is to make sure that the ownership of the files is preserved correctly. Some files in $ORACLE_HOME/bin are owned by root and have the SUID / SGID set:
-rws--x--- 1 root oinstall 20872 Apr 24 12:00 nmb
-rws--x--- 1 root oinstall 28720 Apr 24 11:59 nmo
-rwsr-x--- 1 root oinstall 1340408 Sep 25 2011 oradism
Step 2: Un-tar the tarball into the target location
cd <location>
tar -xvf /tmp/clone_oracle.tar
Again it would be good if it could be done with the root user
Few things to check after the unpacking
(1) If the target host username/group is different, the change the file owner and group
find . -user <UserA> -exec chown <userB> {} \;
find . -group <groupA> -exec chgrp <groupB> {} \;
(2) check that the validity of any symbolic links has been preserved. Ensure that the links point to files/directories in the new target home, not files/directories in the source home. If necessary, re-create the links.
(3) If you are cloning on AIX, make sure that the rootpre.sh script has been executed on the target server. This can be found on the Oracle11g Release 2 media.
Step 3 Run the following command to clone the installation with the Oracle Universal Installer (OUI):
cd $ORACLE_HOME/clone/bin
perl clone.pl ORACLE_HOME="<target_home>" ORACLE_HOME_NAME="<unique_home_name>" ORACLE_BASE="<path_for_ORACLE_BASE>" OSDBA_GROUP=<OSDBA_privileged_group> OSOPER_GROUP=<OSOPER_privileged_group>
Important points to check
(1) OSDBA_GROUP and OSOPER_GROUP can be ignored if the source and target OS username and group are the same
If it is different, then it is very important to specify it otherwise you may experience an ORA-1031 error when using SQL*Plus
(2) If no /etc/oraInst.loc (AIX, Linux) or /var/opt/oracle/oraInst.loc (Solaris, HP-UX) file exists on the server because Oracle has never been installed on the server before, create one. The file should contain a line like this:
inventory_loc=<path_to_oraInventory>
For example:
inventory_loc=/u541/app/oracle/oraInventory
If an oraInst.loc file exists on the server but is in a different location, edit the $ORACLE_HOME/clone/config/cs.properties file to add “-invPtrLoc <path>/oraInst.loc” to the clone_command_line.
This file can also be edited to add “-ignoreSysPrereqs” if required.
The alternative method of cloning is to use the following commands:
./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="<target_home>" ORACLE_HOME_NAME="<unique_home_name>" ORACLE_BASE="<path_for_ORACLE_BASE>" oracle_install_OSDBA=OSDBA_privileged_group oracle_install_OSOPER=OSOPER_privileged_group
If necessary, add “-invPtrLoc <path>/oraInst.loc” or “-ignoreSysPrereqs” to the command line.
(3) If the server has more than one Perl version installed then it may be necessary to specify the PERL5LIB environment variable so that the versions of the Perl modules match with the Perl version used.
Note that the full path for the target ORACLE_HOME should be provided
(4) ORACLE_HOME_NAME name must be unique (that is, it must not already exist in the central inventory file <path>/oraInventory/ContentsXML/inventory.xml).
NOTE: if you are cloning on a server that already has a central inventory and the target home already exists in <path>/oraInventory/ContentsXML/inventory.xml then you should run the following command to ‘detach’ this home from the central inventory before performing the clone operation:
./runInstaller -detachHome ORACLE_HOME=<target_home>
To clone an Oracle11g Release 2 client installation, run OUI directly:
./runInstaller -clone -silent -noconfig ORACLE_HOME="<target_home>" ORACLE_HOME_NAME="<unique_home_name>" ORACLE_BASE="<path_for_ORACLE_BASE>" OSDBA_GROUP=OSDBA_privileged_group OSOPER_GROUP=OSOPER_privileged_group
Step 4
On Unix/Linux installations, you will now need to run root.sh (as root) from the target home.
NOTE: When running the root.sh in the clone it overwrites the files in /usr/local/bin (oraenv, coraenv, dbhome). It does not prompt if those files are to be overwritten or give a choice (as in a clean install). These files should be backed up if used.
Database cloning basically the datafiles and instance
(1) Connect to the Source database using rman and backup the database
$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Tue Feb 13 00:29:33 2019 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: (DBID=6764766136) RMAN> backup database plus archivelog; Starting backup at 20-FEB-17 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=143 recid=109 stamp=614392105 channel ORA_DISK_1: starting piece 1 at 20-FEB-17 channel ORA_DISK_1: finished piece 1 at 20-FEB-17 piece handle=/u02/backup/o1_mf_annnn_TAG20170220T002825_2x21kbds _.bkp tag=TAG20070213T002825 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup on 20-FEB-17 Starting backup at 20-FEB-17 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00003 name=/u01/data/sysaux01.dbf input datafile fno=00001 name=/u01/data/system01.dbf input datafile fno=00002 name=/u01//data/undotbs01.dbf input datafile fno=00004 name=/u01/data/users01.dbf channel ORA_DISK_1: starting piece 1 at channel ORA_DISK_1: finished piece 1 at 20-FEB-17 piece handle=/u02/backup/o1_mf_nnndf_TAG20170220T002827_2x21kd12 _.bkp tag=TAG20070213T002827 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 Finished backup at 20-FEB-17 Starting backup at 20-FEB-17 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=144 recid=110 stamp=614392165 channel ORA_DISK_1: starting piece 1 at 20-FEB-17 channel ORA_DISK_1: finished piece 1 at 20-FEB-17 piece handle=/u02/backup/o1_mf_annnn_TAG20170220T002925_2x21m6ty _.bkp tag=TAG20070213T002925 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 20-FEB-17 Starting Control File and SPFILE Autobackup at 20-FEB-17 piece handle=/u02/backup/c-6764766136-20170220-02 comment=NONE Finished Control File and SPFILE Autobackup at 20-FEB-17
(2) Move the following files to the Target Node:
- The database backup pieces to location ‘/<target node path>/backup’
- Controlfile backup piece to the location ‘/<target node path>/backup’
- The parameter file i.e. init.ora file to the default location i.e. $ORACLE_HOME/dbs
(3) Source the Target Database using oraenv and Prepare the target initialization database parameter file
(4) Start the Database in the nomount state
sqlplus / as sysdba
startup nomount
(5)Restore the controlfile from the backup piece on Target Node
Control backup piece can be located from the source DB backup log and we have already copied all the backup pieces to the target DB node
rman target / restore controlfile from '//backup/c-6764766136-20170220-02';
(6)Mount the database
RMAN > alter database mount
(7) Catalog the backup if the backup piece location is different from the Source database
RMAN> catalog backuppiece '//backup/o1_mf_annnn_TAG20170220T002925_2x21m6ty_.bkp';
RMAN> catalog backuppiece '//backup/o1_mf_annnn_TAG20170220T002825_2x21kbds_.bkp';
RMAN> catalog backuppiece '//backup/o1_mf_nnndf_TAG20170220T002827_2x21kd12_.bkp';
(8) Rename the Redolog files if the target node is not having the same location as the source database
SQL> alter database rename file '/source path>/data/redo01.log' to '/target path>/data/redo01.log';
(9) Restore and Recover the database
RMAN> run
{
set newname for datafile 1 to '/<target node path>/data/sys01.dbf';
set newname for datafile 2 to '/<target node path>/data/undotbs01.dbf';
set newname for datafile 3 to '/<target node path>/data/sysaux01.dbf';
set newname for datafile 4 to '/<target node path>/data/users01.dbf';
restore database;
switch datafile all;
recover database;
}
If you want to recover till any particular sequence or time, you can give
RMAN> run
{
set until sequence <sequence no>;
set newname for datafile 1 to '/<target node path>/data/sys01.dbf';
set newname for datafile 2 to '/<target node path>/data/undotbs01.dbf';
set newname for datafile 3 to '/<target node path>/data/sysaux01.dbf';
set newname for datafile 4 to '/<target node path>/data/users01.dbf';
restore database;
switch datafile all;
recover database;
}
(10) Open the database in resetlogs mode
sqlplus / as sysdba
alter database open resetlogs;
The above was just a method of database cloning. You can look at the below post for other methods
clone database from cold backup
cloning using hot backup
RMAN DUPLICATE from ACTIVE DATABASE
RMAN Duplicate Database
Hot PDB cloning in Oracle database 12.2
Hope you like this detailed post on Database cloning.
Also Reads
how to check Oracle database version
How to clone Oracle Home in 10g/11g/12c/19c/23c