In this post how to clone an Oracle Database is shown with 11g R2 Version on Windows platform. This post is just for learning purpose. Don't directly try on Production databases till you get the insight of it.
Old database -
test007
New cloned database -
clonetest007
Step 1: Bring the old database test007 in Archivelog Mode.
SET ORACLE_SID=test007
SQLPLUS / as sysdba;
SQL> Archive log List
The output can be ARCHIVELOG or NOARCHIVELOG. We will go with ARCHIVELOG mode so following is the step for it.
SQL> Alter System Set LOG_ARCHIVE_DEST_1='Location=E:\oradata\test007\Archived_logs' scope=SPFILE;
Step 2: Now we will shutdown the database and start it in Archivelog Mode;
SQL> Shutdown Immediate;
SQL> startup mount;
SQL> Alter database Archivelog;
SQL> Alter database Open;
With this query let's check if our old database
test007 is now in Archivelog mode or not.
SQL> select log_mode from v$database; OR SQL> Archive log list;
Step 3: Now we will create a new database with the clone of old one.
First we will create new folders for our new database.
We will change our directory on command prompt till the oradata where the data files exists.
e:> cd e:\app\product\11.2.0\dbhome_1\oradata\
e:\app\product\11.2.0\dbhome_1\oradata> mkdir
clonetest007
e:\app\product\11.2.0\dbhome_1\oradata> cd clonetest007
e:\app\product\11.2.0\dbhome_1\oradata\cd clonetest007> mkdir Archived_Logs
Step 4: Now we will create the PFILE parameter file for the new database
clonetest007
Lets create as "initCLONETEST007.ora" in ORACLE_HOME\database with parameter
db_name=clonetest007
Save the file.
Step 5: Now we will create the window service for this oracle database with the shipped "oradim" utility.
oradim -new -sid clonetest007
Instance Created.
We can also start this window service if it's not started with the below command.
net start OracleServiceclonetest007
Step 6: Let's now take the backup of old database
test007
set ORACLE_SID=test007
SQLPLUS / AS SYSDBa;
SQL> select log_mode from v$database; ( Archivelog)
SQL> select * from v$backup;
If status is NOT ACTIVE then make it ACTIVE
SQL> Alter database begin backup;
SQL> quit;
Step 7: Now copy all the datafiles *.dbf to the oradata/clonetest007 folder which we created in above steps.
SQL> Alter database end backup;
SQL> Alter system archive log current;
SQL> Alter database backup control file to trace as 'e:\clonetest007.sql';
Open the trace file & make following changes:
REUSE - SET
NORESETLOGS - RESETLOGS
test007 - clonetest007
Remove comments and extra spaces. Keep the text till character set else delete and save it.
Step 8: Now lets start the new database
clonetest007
set ORACLE_SID=clonetest007
SQLPLUS / AS SYSDBA;
Connected to an Idle Instance.
Run the trace file now created in above step.
SQL> e:\clonetest007.sql;
Control File Created;
Step 9: Now we will take the current archive log of old database for recovery of new database.
set ORACLE_SID=test007
SQLPLUS / AS SYSDBA;
SQL> Alter system archive log current;
SQL> /
SQL> /
SQL> quit;
Step 10: Recover and start the new database.
set ORACLE_SID=clonetest007
SQLPLUS / AS SYSDBA;
SQL> Set logsource 'e:\oradata\test007\Archived_logs';
Press Enter four times then Cancel Type.
SQL> Alter database open resetlogs;
************************************** Done ************************************
