Cold backup Database Cloning is the simplest method to clone the database. The disadvantage of this approach is that database will be down during cold backup.
The high-level steps for How to clone the database using Cold backup Database Cloning
1) Take the cold backup of the database to be cloned
2) Copy all the backup files to the destination server
3) Create the init.ora, then startup nomount then create the control file on the new location
4) alter database open resetlogs to bring the new clone online
Let us take an example
Source Database Name: TEST
Source Database physical files path=/u001/oracle/TEST/oradata
Cloned Database Name: TEST_NEW
Target Database physical files path=/u001/oracle/TEST_NEW/oradata
Take the cold backup of the database to be cloned
(a) Find the location of the data files and shutdown the DB
sqlplus / as sysdba
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;
shutdown immediate
exit
(b) copy all the data files to the backup location. We dont need to copy the Temp tablespace if it is temporary files only
(c) Start the database and backup control to trace
startup
alter database backup controlfile to trace;
This will copy the database information to the trace location
(d) Parameter file backup.
If the ‘TEST’ database is using spfile,
SQL> create pfile=’/tmp/initTEST.ora’ from spfile;
Use the OS command to copy the pfile to a backup location.
Copy all the backup files to the destination server
Copy all data files from the backup location to the Target location. It could be a different server or the same server
$ mkdir /u001/oracle/TEST_NEW/oradata cp or rcp depending the target nodes
Create the init.ora, then startup nomount then create the control file on the new location
On the target node
(a) Copy the backup init. ora to the $ORACLE_HOME/dbs and then change the db_name, control file paths, dump parameter and other things as necessary
$ cd $ORACLE_HOME/dbs $ vi initTEST_NEW.ora db_name=TEST_NEW control_files=/u001/oracle/TEST_NEW/oradata
(b) Create appropriate directory structure in clone database for dumps as specified in init.ora file
(c) Copy the control file trace file from the source database to the target node and then edit it for redo log files location, datafiles location, database name
CREATE CONTROLFILE SET DATABASE “TEST_NEW” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u001/oracle/TEST_NEW/oradataredo01.log’ SIZE 200M,
GROUP 2 ‘/u001/oracle/TEST_NEW/oradataredo02.log’ SIZE 200M,
GROUP 2 ‘/u001/oracle/TEST_NEW/oradataredo02.log’ SIZE 200M,
DATAFILE
‘/u001/oracle/TEST_NEW/oradata/system01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/undotbs01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/sysaux01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/users01.dbf’,
‘/u001/oracle/TEST_NEW/oradata/example01.dbf’
CHARACTER SET UTF8;
(d) Source the target database env like ORACLE_SID, ORACLE_HOME
$ export ORACLE_SID=TEST_NEW SQL> startup nomount SQL> @$ORACLE_HOME/dbs/cntrl.sql
alter database open resetlogs to bring the new clone online
Once the control file’s successfully created, open the database with the resetlogs option.
SQL> alter database open resetlogs;
It will automatically create the temp files on opening. Now you cloned database is Up and running and users can use it
Related Articles
backup controlfile in RMAN : Check out this post on how to backup controlfile in RMAN, How to backup controlfile to trace, and How to enable auto backup of controlfile
check rman backup status: Check out How to check RMAN backup status in sql, what is the status of the backup and input_type of the backup
RMAN List backup : RMAN List backup commands are used to list the backup taken using RMAN,Date and Time and many other details are included
RMAN Backup commands :Check out the RMAN Backup commands in this post. This is going to be very helpful for the person who is involved in backup and recovery