Clone an Oracle Database using controlfile
Clone of Oracle Database with new database name using control file
Source Database Name: DG731124
Source Database Version: 11.2.0.4 Standard Edition
Source Datafile location: /u03/app/oracle/oradata/DG731124/
Source Logfile location: /u03/app/oracle/oradata/DG731124/
Target location of datafile and logfile: /u04/testdir/
Target Database name: TCLONE
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/DG731124/system01.dbf
/u03/app/oracle/oradata/DG731124/sysaux01.dbf
/u03/app/oracle/oradata/DG731124/undotbs01.dbf
/u03/app/oracle/oradata/DG731124/users01.dbf
/u03/app/oracle/oradata/DG731124/test011.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/DG731124/redo03.log
/u03/app/oracle/oradata/DG731124/redo02.log
/u03/app/oracle/oradata/DG731124/redo01.log
SQL> create pfile='/tmp/initTCLONE.ora' from spfile;
Add tablespace, user and table to test the cloning of the database.
Connect to source database
Create tablespace test01
SQL> create tablespace test01 datafile '/u03/app/oracle/oradata/DG731124/test011.dbf' size 15M;
Add user 'u11' to the database
SQL> create user u11 identified by ******;
SQL> grant connect ,resource , create session to u11;
SQL> alter user u11 quota unlimited on test01;
SQL> conn u11;
Now user can create database object.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
SQL> alter system switch logfile;
Create control file
SQL> alter database backup controlfile to trace;
SQL> shut immediate;
Go to the trace file location and rename recently create trace file as control.sql
cd /u03/app/oracle/diag/rdbms/dg731124/DG731124/trace
ls -lrt
cp DG731124_ora_12252.trc control.sql
Remove unwanted lines from the control file keep information like this:
--------------------------------------------------------------------------------------------------------
CREATE CONTROLFILE SET DATABASE "TCLONE" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u04/testdir/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u04/testdir/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u04/testdir/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u04/testdir/system01.dbf',
'/u04/testdir/sysaux01.dbf',
'/u04/testdir/undotbs01.dbf',
'/u04/testdir/users01.dbf',
'/u04/testdir/test011.dbf'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE;
-- Database can now be opened normally.
--ALTER DATABASE OPEN;
--ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/testdir/temp01.dbf'
-- SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
----------------------------------------------------------------------------------------------------------
1- Use set option in create control file to set new name of the database.
2- Replace the old path of datafile and logfile with new location.
3- Edit the initTCLONE.ora replace the path and create following directory as described in pfile.
4- Shut down the source database.
5- copy the datafile, logfile, control file and pfile from source to target location '/u04/testdir/
-------------------------------------------------------------------------------------------------------------
export the oracle_sid for new database instance
# export ORACLE_SID=TCLONE
Start database in nomount state using pfile:
SQL> startup nomount pfile='/u04/testdir/initTCLONE.ora';
Execute the control file
SQL> @/u04/testdir/control.sql
Control file created.
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1258136 generated at 03/29/2019 10:47:01 needed for thread 1
ORA-00289: suggestion :
/u04/testdir/fast_recovery_area/TCLONE/archivelog/2019_03_29/o1_mf_1_9_%u_.arc
ORA-00280: change 1258136 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u04/testdir/redo03.log
Log applied.
Media recovery complete.
Note: Apply the logfile until the Media recovery get completed.
SQL> alter database open resetlogs;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/testdir/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Clone database TCLONE get created in different location.
SQL> select name from v$database;
NAME
---------
TCLONE
SQL> SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u04/testdir/system01.dbf
/u04/testdir/sysaux01.dbf
/u04/testdir/undotbs01.dbf
/u04/testdir/users01.dbf
/u04/testdir/test011.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u04/testdir/redo03.log
/u04/testdir/redo02.log
/u04/testdir/redo01.log
SQL> conn u11;
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment