Steps to clone the database
The following steps can be used to clone a database on the same server or on another server,
We will clone a database with name proddb the cloned database will have the name clonedb,and clonedb will reside on the same server as proddb,
The cloning process mainly has 2 steps,
1. Take a cold backup of the production database
2. Restore the backup on to the clone destination and startup the clone database
Optionally you can rename the clone database, before starting it up.the steps below describe the process of cloning a database and also renaming the database.
Perform Cold Backup of the Production database
1. Note down the datafile, controlfile and redologfile locations on proddb
OS> sqlplus / as sysdba
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> show parameter control_files
2. Shutdown the production database proddb
OS> sqlplus / as sysdba
SQL> shutdown immediate
NOTE : Ensure clean shutdown.
3. Copy the database files (datafiles, logfiles, controlfiles and parameter file) into the clone destination.
Using the information gathered in step 1, change directory to the production database directories
OS> cd /u01/<path_to_proddb_files>
Note : for simplicity sake we'll assume all files are in the same directory
OS> cp /u01/<path_to_proddb_files>/<filename> /u01/<path_to_clonedb_files>/
repeat the same for all datafiles, controlfiles and redologfiles
OS> cp $ORACLE_HOME/dbs/initproddb.ora $ORACLE_HOME/dbs/initclonedb.ora
4. Modify the parameter CONTROL_FILES in the pfile for clonedb to reflect the new path of the control files.
Using your favorite text editor edit the pfile to reflect the new path of the control files
vi initclonedb.ora
CONTROL_FILES = '/u01/clonedb/control/control01.ctl','/u01/clonedb/control/control02.ctl'
Rename the database as per requirements and start the clone database. We rename the database only if needed, if you plan to retain the same name, the whole process simplifies to taking a cold backup
and restoring it in the target location and starting up the database,however renaming becomes mandatory if the cloning happens on the same server and in the same Oracle Home.
If the database version is less than version 9.2,please go to section "Renaming database with version lower than 9.2"
Note :
Ensure DB_NAME is still proddb in initclonedb.ora, clonedb pfile,Please note that the production database should be shutdown throughout the clone process, this is because we are cloning the database on the same server
and using the same Oracle Home, if either one is different, database can be started immediately after copying the files.
1. Mount the database and rename the datafiles and redolog files.
export ORACLE_SID=clonedb
OS> sqlplus / as sysdba
OS> startup mount
OS> alter database rename file '/u01/<path_to_proddb_files>/<filename>' to '/u01/<path_to_clonedb_files>/<filename>'
2. Rename the database with the dbnewid (nid) utility
For information on how to use the DBNEWID utility, please refer this note Note 224266.1 Title: How to Change the DBID and the DBNAME by using NID
NOTE : If you are using a database of version lesser than 9.2 then the DBNEWID (NID) utility cannot be used as it was introduced only from 9.2
3. Edit the pfile and update the new database name
vi initclonedb.ora
---> Modify parameter DB_NAME to reflect the new database name.
also ensure you make necessary changes to the environment for ORACLE_SID, ORACLE_HOME
as needed.
4. Open the clone database.
5. Startup and open the production database.
Note : These steps may not be applicable to databases using OMF files as there are restrictions on renaming the datafiles, controlfiles and logfiles when they are Oracle Managed, it is Likely that you will receive a ORA-1276 when you rename a OMF file.
Renaming database with version lower than 9.2
Note : In this case it is not required to copy controlfiles or redologfiles
as they will be created newly, also note that when using this method to rename the database, the production database can be started immediately following the copying of the datafiles to the clone location.
1. Generate a controlfile backup trace file with the command
SQL> alter database backup controlfile to trace as '/path/filename.trc';
2. Edit the pfile and update the new database name
vi initclonedb.ora
---> Modify parameter DB_NAME to reflect the new database name.
also ensure you make necessary changes to the environment for ORACLE_SID, ORACLE_HOME
as needed.
3. Make a script to create a new controlfile
Editing the trace generated in
step 1 of this section, ensure to use the SET option to set the new database name.
Example
CREATE CONTROLFILE SET DATABASE "CLONEDB" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 '/home/oracle/databases/clonedb/redo1.ora' SIZE 100M,
GROUP 2 '/home/oracle/databases/clonedb/redo2.ora' SIZE 100M,
GROUP 3 '/home/oracle/databases/clonedb/redo3.ora' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/databases/clonedb/system.dbf',
'/home/oracle/databases/clonedb/undo.dbf',
'/home/oracle/databases/clonedb/data.dbf'
CHARACTER SET WE8ISO8859P1
;
File saved as createcontrol.sql
4. Connect to the clone instance and start in the NOMOUNT stage.
export ORACLE_SID=clonedb
sqlplus /nolog
SQL> conn / as sysdba
SQL> startup nomount
SQL> @createcontrol.sql
Control File Created.
SQL> alter database open resetlogs ;
Please note you may have to add tempfiles to temporary tablespaces
as needed.
These steps complete the cloning process.
A note about temporary tempfiles
In this process of cloning especially when using the method where control files have to be recreated generally the temporary tablespace tempfiles get disassociated from the database, in this case the
tempfile just needs to be added back to the respective tablespace with this command alter tablespace <temp_tablespace_name> add tempfile '/path/filename';
Please see this metalink note for more details
Note 178992.1 Title: How to Recover from Missing Tempfiles or an Empty Temporary Tablespace
The following steps can be used to clone a database on the same server or on another server,
We will clone a database with name proddb the cloned database will have the name clonedb,and clonedb will reside on the same server as proddb,
The cloning process mainly has 2 steps,
1. Take a cold backup of the production database
2. Restore the backup on to the clone destination and startup the clone database
Optionally you can rename the clone database, before starting it up.the steps below describe the process of cloning a database and also renaming the database.
Perform Cold Backup of the Production database
1. Note down the datafile, controlfile and redologfile locations on proddb
OS> sqlplus / as sysdba
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> show parameter control_files
2. Shutdown the production database proddb
OS> sqlplus / as sysdba
SQL> shutdown immediate
NOTE : Ensure clean shutdown.
3. Copy the database files (datafiles, logfiles, controlfiles and parameter file) into the clone destination.
Using the information gathered in step 1, change directory to the production database directories
OS> cd /u01/<path_to_proddb_files>
Note : for simplicity sake we'll assume all files are in the same directory
OS> cp /u01/<path_to_proddb_files>/<filename> /u01/<path_to_clonedb_files>/
repeat the same for all datafiles, controlfiles and redologfiles
OS> cp $ORACLE_HOME/dbs/initproddb.ora $ORACLE_HOME/dbs/initclonedb.ora
4. Modify the parameter CONTROL_FILES in the pfile for clonedb to reflect the new path of the control files.
Using your favorite text editor edit the pfile to reflect the new path of the control files
vi initclonedb.ora
CONTROL_FILES = '/u01/clonedb/control/control01.ctl','/u01/clonedb/control/control02.ctl'
Rename the database as per requirements and start the clone database. We rename the database only if needed, if you plan to retain the same name, the whole process simplifies to taking a cold backup
and restoring it in the target location and starting up the database,however renaming becomes mandatory if the cloning happens on the same server and in the same Oracle Home.
If the database version is less than version 9.2,please go to section "Renaming database with version lower than 9.2"
Note :
Ensure DB_NAME is still proddb in initclonedb.ora, clonedb pfile,Please note that the production database should be shutdown throughout the clone process, this is because we are cloning the database on the same server
and using the same Oracle Home, if either one is different, database can be started immediately after copying the files.
1. Mount the database and rename the datafiles and redolog files.
export ORACLE_SID=clonedb
OS> sqlplus / as sysdba
OS> startup mount
OS> alter database rename file '/u01/<path_to_proddb_files>/<filename>' to '/u01/<path_to_clonedb_files>/<filename>'
2. Rename the database with the dbnewid (nid) utility
For information on how to use the DBNEWID utility, please refer this note Note 224266.1 Title: How to Change the DBID and the DBNAME by using NID
NOTE : If you are using a database of version lesser than 9.2 then the DBNEWID (NID) utility cannot be used as it was introduced only from 9.2
3. Edit the pfile and update the new database name
vi initclonedb.ora
---> Modify parameter DB_NAME to reflect the new database name.
also ensure you make necessary changes to the environment for ORACLE_SID, ORACLE_HOME
as needed.
4. Open the clone database.
5. Startup and open the production database.
Note : These steps may not be applicable to databases using OMF files as there are restrictions on renaming the datafiles, controlfiles and logfiles when they are Oracle Managed, it is Likely that you will receive a ORA-1276 when you rename a OMF file.
Renaming database with version lower than 9.2
Note : In this case it is not required to copy controlfiles or redologfiles
as they will be created newly, also note that when using this method to rename the database, the production database can be started immediately following the copying of the datafiles to the clone location.
1. Generate a controlfile backup trace file with the command
SQL> alter database backup controlfile to trace as '/path/filename.trc';
2. Edit the pfile and update the new database name
vi initclonedb.ora
---> Modify parameter DB_NAME to reflect the new database name.
also ensure you make necessary changes to the environment for ORACLE_SID, ORACLE_HOME
as needed.
3. Make a script to create a new controlfile
Editing the trace generated in
step 1 of this section, ensure to use the SET option to set the new database name.
Example
CREATE CONTROLFILE SET DATABASE "CLONEDB" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 '/home/oracle/databases/clonedb/redo1.ora' SIZE 100M,
GROUP 2 '/home/oracle/databases/clonedb/redo2.ora' SIZE 100M,
GROUP 3 '/home/oracle/databases/clonedb/redo3.ora' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/databases/clonedb/system.dbf',
'/home/oracle/databases/clonedb/undo.dbf',
'/home/oracle/databases/clonedb/data.dbf'
CHARACTER SET WE8ISO8859P1
;
File saved as createcontrol.sql
4. Connect to the clone instance and start in the NOMOUNT stage.
export ORACLE_SID=clonedb
sqlplus /nolog
SQL> conn / as sysdba
SQL> startup nomount
SQL> @createcontrol.sql
Control File Created.
SQL> alter database open resetlogs ;
Please note you may have to add tempfiles to temporary tablespaces
as needed.
These steps complete the cloning process.
A note about temporary tempfiles
In this process of cloning especially when using the method where control files have to be recreated generally the temporary tablespace tempfiles get disassociated from the database, in this case the
tempfile just needs to be added back to the respective tablespace with this command alter tablespace <temp_tablespace_name> add tempfile '/path/filename';
Please see this metalink note for more details
Note 178992.1 Title: How to Recover from Missing Tempfiles or an Empty Temporary Tablespace
