DATABASE CLONING USING BACKUP
1.
Take consistent backup using RMAN
bash-3.2$
rman target /
Recovery
Manager: Release 11.2.0.4.0 - Production on Tue Jul 26 13:05:01 2016
Copyright
(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected
to target database: MYDB (DBID=2832605173)
RMAN>
connect catalog rman/rman@orcl
connected
to recovery catalog database
RMAN>
run{
backup
current controlfile format '/u01/app/oracle/rmanbkp/control.bak';
backup
database format '/u01/app/oracle/rmanbkp/%U.bak';
backup
archivelog all format '/u01/app/oracle/rmanbkp/arch_%r%_s_%t.bak';
}
Starting
backup at 26-JUL-16
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=40 device type=DISK
channel
ORA_DISK_1: starting full datafile backup set
channel
ORA_DISK_1: specifying datafile(s) in backup set
including
current control file in backup set
channel
ORA_DISK_1: starting piece 1 at 26-JUL-16
channel
ORA_DISK_1: finished piece 1 at 26-JUL-16
piece
handle=/u01/app/oracle/rmanbkp/control.bak tag=TAG20160726T130600
comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished
backup at 26-JUL-16
Starting
backup at 26-JUL-16
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting full datafile backup set
channel
ORA_DISK_1: specifying datafile(s) in backup set
input
datafile file number=00001
name=/u01/app/oracle/oradata/mydb/system01.dbf
input
datafile file number=00002
name=/u01/app/oracle/oradata/mydb/sysaux01.dbf
input
datafile file number=00005
name=/u01/app/oracle/oradata/mydb/example01.dbf
input
datafile file number=00003
name=/u01/app/oracle/oradata/mydb/undotbs01.dbf
input
datafile file number=00004
name=/u01/app/oracle/oradata/mydb/users01.dbf
channel
ORA_DISK_1: starting piece 1 at 26-JUL-16
channel
ORA_DISK_1: finished piece 1 at 26-JUL-16
piece
handle=/u01/app/oracle/rmanbkp/02rblr5v_1_1.bak
tag=TAG20160726T130607 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:01:55
channel
ORA_DISK_1: starting full datafile backup set
channel
ORA_DISK_1: specifying datafile(s) in backup set
including
current control file in backup set
including
current SPFILE in backup set
channel
ORA_DISK_1: starting piece 1 at 26-JUL-16
channel
ORA_DISK_1: finished piece 1 at 26-JUL-16
piece
handle=/u01/app/oracle/rmanbkp/03rblr9i_1_1.bak
tag=TAG20160726T130607 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished
backup at 26-JUL-16
Starting
backup at 26-JUL-16
current
log archived
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting archived log backup set
channel
ORA_DISK_1: specifying archived log(s) in backup set
input
archived log thread=1 sequence=5 RECID=1 STAMP=918160719
input
archived log thread=1 sequence=6 RECID=2 STAMP=918160720
input
archived log thread=1 sequence=7 RECID=3 STAMP=918160721
input
archived log thread=1 sequence=8 RECID=4 STAMP=918219585
input
archived log thread=1 sequence=9 RECID=5 STAMP=918220088
channel
ORA_DISK_1: starting piece 1 at 26-JUL-16
channel
ORA_DISK_1: finished piece 1 at 26-JUL-16
piece
handle=/u01/app/oracle/rmanbkp/arch_%r%_s_918220091.bak
tag=TAG20160726T130810 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished
backup at 26-JUL-16
*********************************************************************************
1.Create
Pfile on Auxiliary side
test.__db_cache_size=184549376
test.__java_pool_size=4194304
test.__large_pool_size=8388608
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE
set from environment
test.__pga_aggregate_target=226492416
test.__sga_target=310378496
test.__shared_io_pool_size=0
test.__shared_pool_size=104857600
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/test/control01.ctl','/u01/app/oracle/oradata/test/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=testXDB)'
*.log_archive_dest=''
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=534773760
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.log_file_name_convert='/u01/app/oracle/oradata/mydb/','/u01/app/oracle/oradata/test/'
*.db_file_name_convert='/u01/app/oracle/oradata/mydb/','/u01/app/oracle/oradata/test/'
2.Create
Password file on Auxiliary database
orapwd
file=/u01/app/oracle/product/11.2.0/db_3/dbs/orapwtest password=test
3.
Startup nomount the database using pfile
SQL>
startup nomount
pfile=/u01/app/oracle/product/11.2.0/db_3/dbs/inittest.ora;
ORACLE
instance started.
Total
System Global Area 534462464 bytes
Fixed
Size 2254952 bytes
Variable
Size 394266520 bytes
Database
Buffers 134217728 bytes
Redo
Buffers 3723264 bytes
4.Create
spfile from pfile
SQL>
create spfile from pfile;
File
created.
SQL>
shut immediate
ORA-01507:
database not mounted
ORACLE
instance shut down.
5.startup
nomount using spfile
SQL>
startup nomount
ORACLE
instance started.
Total
System Global Area 534462464 bytes
Fixed
Size 2254952 bytes
Variable
Size 394266520 bytes
Database
Buffers 134217728 bytes
Redo
Buffers 3723264 bytes
6.Connect
Rman Auxiliary database only
bash-3.2$
rman auxiliary /
Recovery
Manager: Release 11.2.0.4.0 - Production on Tue Jul 26 13:46:54 2016
Copyright
(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected
to auxiliary database: TEST (not mounted)
7.
Run Duplicate command using backup location
RMAN>
duplicate database to 'test' backup location
'/u01/app/oracle/rmanbkp/';
Starting
Duplicate Db at 26-JUL-16
contents
of Memory Script:
{
sql
clone "alter system set db_name =
''MYDB''
comment=
''Modified
by RMAN duplicate'' scope=spfile";
sql
clone "alter system set db_unique_name =
''TEST''
comment=
''Modified
by RMAN duplicate'' scope=spfile";
shutdown
clone immediate;
startup
clone force nomount
restore
clone primary controlfile from
'/u01/app/oracle/rmanbkp/03rblr9i_1_1.bak';
alter
clone database mount;
}
executing
Memory Script
sql
statement: alter system set db_name = ''MYDB'' comment= ''Modified
by RMAN duplicate'' scope=spfile
sql
statement: alter system set db_unique_name = ''TEST'' comment=
''Modified by RMAN duplicate'' scope=spfile
Oracle
instance shut down
Oracle
instance started
Total
System Global Area 534462464 bytes
Fixed
Size 2254952 bytes
Variable
Size 394266520 bytes
Database
Buffers 134217728 bytes
Redo
Buffers 3723264 bytes
Starting
restore at 26-JUL-16
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=19 device type=DISK
channel
ORA_AUX_DISK_1: restoring control file
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output
file name=/u01/app/oracle/oradata/test/control01.ctl
output
file name=/u01/app/oracle/oradata/test/control02.ctl
Finished
restore at 26-JUL-16
database
mounted
released
channel: ORA_AUX_DISK_1
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=19 device type=DISK
contents
of Memory Script:
{
set
until scn 1032054;
set
newname for datafile 1 to
"/u01/app/oracle/oradata/test/system01.dbf";
set
newname for datafile 2 to
"/u01/app/oracle/oradata/test/sysaux01.dbf";
set
newname for datafile 3 to
"/u01/app/oracle/oradata/test/undotbs01.dbf";
set
newname for datafile 4 to
"/u01/app/oracle/oradata/test/users01.dbf";
set
newname for datafile 5 to
"/u01/app/oracle/oradata/test/example01.dbf";
restore
clone
database
;
}
executing
Memory Script
executing
command: SET until clause
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
Starting
restore at 26-JUL-16
using
channel ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel
ORA_AUX_DISK_1: restoring datafile 00001 to
/u01/app/oracle/oradata/test/system01.dbf
channel
ORA_AUX_DISK_1: restoring datafile 00002 to
/u01/app/oracle/oradata/test/sysaux01.dbf
channel
ORA_AUX_DISK_1: restoring datafile 00003 to
/u01/app/oracle/oradata/test/undotbs01.dbf
channel
ORA_AUX_DISK_1: restoring datafile 00004 to
/u01/app/oracle/oradata/test/users01.dbf
channel
ORA_AUX_DISK_1: restoring datafile 00005 to
/u01/app/oracle/oradata/test/example01.dbf
channel
ORA_AUX_DISK_1: reading from backup piece
/u01/app/oracle/rmanbkp/02rblr5v_1_1.bak
channel
ORA_AUX_DISK_1: piece handle=/u01/app/oracle/rmanbkp/02rblr5v_1_1.bak
tag=TAG20160726T130607
channel
ORA_AUX_DISK_1: restored backup piece 1
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:45
Finished
restore at 26-JUL-16
contents
of Memory Script:
{
switch
clone datafile all;
}
executing
Memory Script
datafile
1 switched to datafile copy
input
datafile copy RECID=6 STAMP=918222546 file
name=/u01/app/oracle/oradata/test/system01.dbf
datafile
2 switched to datafile copy
input
datafile copy RECID=7 STAMP=918222547 file
name=/u01/app/oracle/oradata/test/sysaux01.dbf
datafile
3 switched to datafile copy
input
datafile copy RECID=8 STAMP=918222547 file
name=/u01/app/oracle/oradata/test/undotbs01.dbf
datafile
4 switched to datafile copy
input
datafile copy RECID=9 STAMP=918222547 file
name=/u01/app/oracle/oradata/test/users01.dbf
datafile
5 switched to datafile copy
input
datafile copy RECID=10 STAMP=918222547 file
name=/u01/app/oracle/oradata/test/example01.dbf
contents
of Memory Script:
{
set
until scn 1032054;
recover
clone
database
delete
archivelog
;
}
executing
Memory Script
executing
command: SET until clause
Starting
recover at 26-JUL-16
using
channel ORA_AUX_DISK_1
starting
media recovery
channel
ORA_AUX_DISK_1: starting archived log restore to default destination
channel
ORA_AUX_DISK_1: restoring archived log
archived
log thread=1 sequence=9
channel
ORA_AUX_DISK_1: reading from backup piece
/u01/app/oracle/rmanbkp/arch_%r%_s_918220091.bak
channel
ORA_AUX_DISK_1: piece
handle=/u01/app/oracle/rmanbkp/arch_%r%_s_918220091.bak
tag=TAG20160726T130810
channel
ORA_AUX_DISK_1: restored backup piece 1
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived
log file
name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2016_07_26/o1_mf_1_9_csg77xt3_.arc
thread=1 sequence=9
channel
clone_default: deleting archived log(s)
archived
log file
name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2016_07_26/o1_mf_1_9_csg77xt3_.arc
RECID=1 STAMP=918222549
media
recovery complete, elapsed time: 00:00:01
Finished
recover at 26-JUL-16
Oracle
instance started
Total
System Global Area 534462464 bytes
Fixed
Size 2254952 bytes
Variable
Size 394266520 bytes
Database
Buffers 134217728 bytes
Redo
Buffers 3723264 bytes
contents
of Memory Script:
{
sql
clone "alter system set db_name =
''TEST''
comment=
''Reset
to original value by RMAN'' scope=spfile";
sql
clone "alter system reset db_unique_name scope=spfile";
shutdown
clone immediate;
startup
clone nomount;
}
executing
Memory Script
sql
statement: alter system set db_name = ''TEST'' comment= ''Reset to
original value by RMAN'' scope=spfile
sql
statement: alter system reset db_unique_name scope=spfile
Oracle
instance shut down
connected
to auxiliary database (not started)
Oracle
instance started
Total
System Global Area 534462464 bytes
Fixed
Size 2254952 bytes
Variable
Size 394266520 bytes
Database
Buffers 134217728 bytes
Redo
Buffers 3723264 bytes
sql
statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST"
RESETLOGS ARCHIVELOG
MAXLOGFILES
16
MAXLOGMEMBERS
3
MAXDATAFILES
100
MAXINSTANCES
8
MAXLOGHISTORY
292
LOGFILE
GROUP
1 ( '/u01/app/oracle/oradata/test/redo01.log' ) SIZE 50 M REUSE,
GROUP
2 ( '/u01/app/oracle/oradata/test/redo02.log' ) SIZE 50 M REUSE,
GROUP
3 ( '/u01/app/oracle/oradata/test/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/test/system01.dbf'
CHARACTER
SET WE8MSWIN1252
contents
of Memory Script:
{
set
newname for tempfile 1 to
"/u01/app/oracle/oradata/test/temp01.dbf";
switch
clone tempfile all;
catalog
clone datafilecopy "/u01/app/oracle/oradata/test/sysaux01.dbf",
"/u01/app/oracle/oradata/test/undotbs01.dbf",
"/u01/app/oracle/oradata/test/users01.dbf",
"/u01/app/oracle/oradata/test/example01.dbf";
switch
clone datafile all;
}
executing
Memory Script
executing
command: SET NEWNAME
renamed
tempfile 1 to /u01/app/oracle/oradata/test/temp01.dbf in control file
cataloged
datafile copy
datafile
copy file name=/u01/app/oracle/oradata/test/sysaux01.dbf RECID=1
STAMP=918222579
cataloged
datafile copy
datafile
copy file name=/u01/app/oracle/oradata/test/undotbs01.dbf RECID=2
STAMP=918222580
cataloged
datafile copy
datafile
copy file name=/u01/app/oracle/oradata/test/users01.dbf RECID=3
STAMP=918222580
cataloged
datafile copy
datafile
copy file name=/u01/app/oracle/oradata/test/example01.dbf RECID=4
STAMP=918222580
datafile
2 switched to datafile copy
input
datafile copy RECID=1 STAMP=918222579 file
name=/u01/app/oracle/oradata/test/sysaux01.dbf
datafile
3 switched to datafile copy
input
datafile copy RECID=2 STAMP=918222580 file
name=/u01/app/oracle/oradata/test/undotbs01.dbf
datafile
4 switched to datafile copy
input
datafile copy RECID=3 STAMP=918222580 file
name=/u01/app/oracle/oradata/test/users01.dbf
datafile
5 switched to datafile copy
input
datafile copy RECID=4 STAMP=918222580 file
name=/u01/app/oracle/oradata/test/example01.dbf
contents
of Memory Script:
{
Alter
clone database open resetlogs;
}
executing
Memory Script
database
opened
Finished
Duplicate Db at 26-JUL-16
RMAN>
exit
Recovery
Manager complete.
8.Check
the database status
SQL>
select name,open_mode,log_mode from v$database;
NAME
OPEN_MODE LOG_MODE
---------
-------------------- ------------
TEST
READ WRITE ARCHIVELOG