--------------------------------------------------------------------------------
Modified 18-NOV-2011 Type HOWTO Status MODERATED
In this Document
Goal
Solution
References
--------------------------------------------------------------------------------
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 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:
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
sqlplus / as sysdba
SQL> shutdown immediate
NOTE: Make sure the database is closed cleanly
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/
OS> cp /u01/
<< Repeat the same for all datafiles, controlfiles and redologfiles >>
Note : for simplicity sake we'll assume all files are in the same directory
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
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 the clonedb pfile 'initclonedb.ora'.
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, the database can be started immediately after copying the files.
1. Mount the database and rename the datafiles and redolog files.
# export ORACLE_SID=clonedb
# sqlplus / as sysdba
SQL> startup mount
SQL> alter database rename file '/u01/
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:
Normally, temp files can be added later.
However, if the database is to be renamed with the NID utility, you should also copy and rename the temp files to prevent an issue as documented in
Note 1339657.1 - Unable to open database after NID fails due to missing temp files
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.
Make all the 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.
Make all the 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:
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
Please see the following note for more details
Note 178992.1 - Title: How to Recover from Missing Tempfiles or an Empty Temporary Tablespace
References
NOTE:1339657.1 - Unable to open database after NID fails due to missing temp files
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
Keywords
--------------------------------------------------------------------------------
BACKUP; CLONING DATABASE; DBID; NID; RESTORE
Errors
--------------------------------------------------------------------------------
ORA-1276
Back to top
Copyright (c) 2007, 2010, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement Rate this document
Article Rating Rate this document
Excellent
Good
Poor
Did this document help you?
Yes
No
Just browsing
How easy was it to find this document?
Very easy
Somewhat easy
Not easy
CommentsProvide feedback for this article. Please use 'Contact Us' for other feedback. Important Note: this feedback may be anonymously visible to other customers until processed by Oracle Support.
Cancel
No comments:
Post a Comment