Cloning a database is a very common task for a DBA in-order to provide a copy of production database for testing or for development environment.
Let’s get into some actions:
Step1: First of all connect to your PROD1 database and type the below command:
SQL>Alter database controlfile to trace
Check the output which is a trace file in udump:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD1" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\REDO01.LOG' SIZE 50M,
GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\SYSTEM01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\UNDOTBS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\SYSAUX01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Database can now be opened normally.
ALTER DATABASE OPEN;
Step2: Shutdown your production database i.e. PROD1
Step3: Copy all datafiles now from PROD1 to test database i.e. REFER
From: D:\oracle\product\10.2.0\oradata\PROD1\
To: D:\oracle\product\10.2.0\oradata\REFER\
Step4: Modify now the contents of controlfile and save it as clone.sql
Old: CREATE CONTROLFILE REUSE DATABASE "PROD1" NORESETLOGS ARCHIVELOG
New: CREATE CONTROLFILE SET DATABASE "REFER" RESETLOGS
Step5: Remove the text marked as red from the contents of controlfile.
Step6: Renames the location of datafiles in the controlfile:
Old:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\SYSTEM01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\UNDOTBS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\SYSAUX01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD1\USERS01.DBF'
New:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\REFER\SYSTEM01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\REFER\UNDOTBS01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\REFER\SYSAUX01.DBF',
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\REFER\USERS01.DBF'
Step7: Now create the bdump, udump and cdump directories
Step8: Edit the pfile for test database i.e.REFER
Step9: Lets create the test instance:
C:\>set ORACLE_SID=refer
C:\>oradim -new -sid refer -intpwd refer -startmode m
Instance created.
Step10: sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 11 12:04:09 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
Step11: SQL> startup pfile='D:\oracle\product\10.2.0\admin\refer\pfile\init.ora' nomount
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248624 bytes
Variable Size 92275344 bytes
Database Buffers 197132288 bytes
Redo Buffers 2945024 bytes
Step12:
SQL> @"D:\east\clone.sql"
Control file created.
Step 13:
SQL> alter database open resetlogs;
Database altered.
It’s over and out as you have successfully created the clone database.
JEnjoy
No comments:
Post a Comment