Steps to Manually Clone a Database [ID 458450.1]

Steps to Manually Clone a Database [ID 458450.1]
		Modified 04-OCT-2010     Type HOWTO     Status MODERATED

In this Document
  Goal
  Solution
  References

---

Platforms: 1-914CU;

 

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

 

 

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.2.0.1 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.
***Checked for relevance on 04-Oct-2010***

Goal

How to clone a database manually, without using RMAN.

Solution

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

References

NOTE:172491.1 - OERR: ORA-1276 Cannot add file %s. File has an Oracle Managed Files file name
NOTE:178992.1 - How to Recover from Missing Tempfiles or an Empty Temporary Tablespace
NOTE:224266.1 - How to Change the DBID and the DBNAME by using NID
NOTE:388431.1 - Creating a Duplicate Database on a New Host.

Related Products Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition Errors ORA-1276