Oracle11gR2 comes with new features where we can duplicate the database from the Target Database to the Auxiliary Database using RMAN backup without connecting to the Target database and rman catalog. Only thing what is required is, full backup of the Target database.
High Level steps :
1. Have recent RMAN L0 backup of target database.
2. FTP the RMAN backup files from target server to auxiliary server
3. Create all the directories in auxiliary server
4. Start the instance(with nomount) in auxiliary instance
5. Duplicate the database in auxiliary instance.
6. create spfile in ASM disk
7. Database validation and verification
Target DB info :
I am restoring the above database to the new host called ractest1. The RBDMS is already installed in ractest1 host and it is same as target database version.
Step1 Take RMAN L0 backup & Archive log backup in target server. I am using the below script to backup the database in target server. We can also use the recent full backup from tape if it is already available in tape. I am using disk backup for my convenience.
Here is the log file for full backup.
Here is the log file for archive log backup.
The RMAN backup files are coped to auxiliary server as below. Also copy the password file from target server to auxiliary database server. Password file is placed under $ORACLE_HOME/dbs directory.
The below backup list has full backup, Archive log bakcup & Archive logs.
Tailing the log file while restoring the database.
High Level steps :
1. Have recent RMAN L0 backup of target database.
2. FTP the RMAN backup files from target server to auxiliary server
3. Create all the directories in auxiliary server
4. Start the instance(with nomount) in auxiliary instance
5. Duplicate the database in auxiliary instance.
6. create spfile in ASM disk
7. Database validation and verification
Target DB info :
Step1 Take RMAN L0 backup & Archive log backup in target server. I am using the below script to backup the database in target server. We can also use the recent full backup from tape if it is already available in tape. I am using disk backup for my convenience.
usben_L0_backup.sh
Here is the log file for full backup.
RMAN>
connected to target database: USBEN (DBID=2379284599)
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
using target database control file instead of recovery catalog
allocated channel: T01
channel T01: SID=61 device type=DISK
executing command: SET COMMAND ID
Starting backup at 16-FEB-16
channel T01: starting full datafile backup set
channel T01: specifying datafile(s) in backup set
including current SPFILE in backup set
channel T01: starting piece 1 at 16-FEB-16
channel T01: finished piece 1 at 16-FEB-16
piece handle=/backup/usben/USBEN_SPFILE_20160216_0927_133_45qu2cfv_1_1_903950847 tag=USBEN_L0_20160216_0927 comment=NONE
channel T01: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-16
Starting backup at 16-FEB-16
channel T01: starting full datafile backup set
channel T01: specifying datafile(s) in backup set
including current control file in backup set
channel T01: starting piece 1 at 16-FEB-16
channel T01: finished piece 1 at 16-FEB-16
piece handle=/backup/usben/USBEN_CTRL_20160216_0927_134_46qu2cg0_1_1_903950848 tag=USBEN_L0_20160216_0927 comment=NONE
channel T01: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-16
Starting backup at 16-FEB-16
channel T01: starting compressed incremental level 0 datafile backup set
channel T01: specifying datafile(s) in backup set
input datafile file number=00001 name=/data01/oradata/usben/system01.dbf
-- trimming the info here for brevity
input datafile file number=00003 name=/data01/oradata/usben/sysaux01.dbf
channel T01: starting piece 1 at 16-FEB-16
channel T01: finished piece 1 at 16-FEB-16
piece handle=/backup/usben/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851 tag=USBEN_L0_20160216_0927 comment=NONE
channel T01: backup set complete, elapsed time: 00:02:15
Finished backup at 16-FEB-16
Starting Control File and SPFILE Autobackup at 16-FEB-16
piece handle=/ora/app/oracle/fast_recovery_area/USBEN/
autobackup/2016_02_16/o1_mf_s_903950986_cd6dltf7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-FEB-16
released channel: T01
RMAN>
Recovery Manager complete.
|
usben_Arch_backup.sh
Here is the log file for archive log backup.
RMAN>
connected to target
database: USBEN (DBID=2379284599)
RMAN> 2> 3>
4> 5> 6> 7> 8> 9> 10>
using target
database control file instead of recovery catalog
allocated channel:
T01
channel T01: SID=59
device type=DISK
executing command:
SET COMMAND ID
sql statement: alter
system archive log current
Starting backup at 16-FEB-16
current log archived
channel T01:
starting archived log backup set
channel T01:
specifying archived log(s) in backup set
input archived log
thread=1 sequence=267 RECID=261 STAMP=903951084
--
trimming the info here for brevity
input archived log
thread=1 sequence=301 RECID=295 STAMP=903951315
channel T01:
starting piece 1 at 16-FEB-16
channel T01:
finished piece 1 at 16-FEB-16
piece
handle=/backup/usben/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315
tag=TAG20160216T093515 comment=NONE
channel T01: backup
set complete, elapsed time: 00:01:05
channel T01:
deleting archived log(s)
archived log file
name=/data02/oradata/arch/1_267_902782329.dbf RECID=261 STAMP=903951084
--
trimming the info here for brevity
archived log file
name=/data02/oradata/arch/1_301_902782329.dbf RECID=295 STAMP=903951315
Finished backup at
16-FEB-16
Starting Control
File and SPFILE Autobackup at 16-FEB-16
piece
handle=/ora/app/oracle/fast_recovery_area/USBEN/
autobackup/2016_02_16/o1_mf_s_903951381_cd6dz606_.bkp
comment=NONE
Finished Control
File and SPFILE Autobackup at 16-FEB-16
released channel:
T01
RMAN>
Recovery Manager
complete.
|
Step2 FTP the RMAN backup files from target server to auxiliary server.
The RMAN backup files are coped to auxiliary server as below. Also copy the password file from target server to auxiliary database server. Password file is placed under $ORACLE_HOME/dbs directory.
The below backup list has full backup, Archive log bakcup & Archive logs.
Step3 Create the necessary folders in auxiliary database. Here i am migrating the database from file system to ASM disk. This case, we don't need to create directory in the file system. I created the directory in ASM disk.
[oracle@RACTEST1 ~]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> mkdir USBEN
Add entry in /etc/oratab for usben database.
Edit the parameter file in auxiliary database according to the environment. Here is the modified pfile in auxiliary database.
initusben.ora
Step4 Start the instance using the above parameter with nomount mode:
Step5 Duplicate the database
restore.sh
Start running the above script in background mode.
[oracle@RACTEST1 ~]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> mkdir USBEN
Add entry in /etc/oratab for usben database.
Edit the parameter file in auxiliary database according to the environment. Here is the modified pfile in auxiliary database.
initusben.ora
usben.__data_transfer_cache_size=0
usben.__db_cache_size=536870912
usben.__java_pool_size=16777216
usben.__large_pool_size=150994944
usben.__oracle_base='/ora/app/oracle'
usben.__pga_aggregate_target=671088640
usben.__sga_target=989855744
usben.__shared_io_pool_size=50331648
usben.__shared_pool_size=218103808
usben.__streams_pool_size=0
*.compatible='12.1.0.2.0'
*.control_files='+DATA','+DATA'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='usben'
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/ora/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=usbenXDB)'
*.log_archive_dest_1='location=/backup/usben/arch'
*.log_archive_dest_state_1='enable'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
|
Step4 Start the instance using the above parameter with nomount mode:
Step5 Duplicate the database
restore.sh
Start running the above script in background mode.
[oracle@RACTEST1 usben]$ nohup ./restore.sh &
[1] 14120
[oracle@RACTEST1 usben]$
tail -f restore.log
Here is the complete restore log file.
Step 6 Create the spfile in ASM disk. We could do this step at the beginning too. However, i do this step at the end for my convenience.
Step7 Verify all the files and make sure all files are moved to ASM disk. Also watch out the alert log for a while. Create the listener and make sure able to connect outside the database server.
Now the database is successfully restored in different host. The database is also migrated to ASM disk as part of restore.
Enjoy reading my blog!!
Recovery Manager: Release 12.1.0.2.0 - Production on Wed Feb 17
13:23:26 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
RMAN>
connected to auxiliary database: USBEN (not mounted)
RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: a1
channel a1: SID=34 device type=DISK
Starting Duplicate Db at 17-FEB-16
contents of Memory Script:
{
sql clone "create
spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area
281018368 bytes
Fixed Size
2923440 bytes
Variable Size
222299216 bytes
Database Buffers
50331648 bytes
Redo Buffers
5464064 bytes
allocated channel: a1
channel a1: SID=33 device type=DISK
contents of Memory Script:
{
sql clone "alter system
set control_files =
''+DATA/USBEN/CONTROLFILE/current.316.904051433'',
''+DATA/USBEN/CONTROLFILE/current.295.904051435'' comment=
''Set by RMAN''
scope=spfile";
sql clone "alter system
set db_name =
''USBEN'' comment=
''Modified by RMAN duplicate''
scope=spfile";
sql clone "alter system
set db_unique_name =
''USBEN'' comment=
''Modified by RMAN duplicate''
scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary
controlfile from
'/backup/usben/rman/USBEN_CTRL_20160216_0927_134_46qu2cg0_1_1_903950848';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set
control_files = ''+DATA/USBEN/CONTROLFILE/current.316.904051433'',
''+DATA/USBEN/CONTROLFILE/current.295.904051435'' comment= ''Set by RMAN''
scope=spfile
sql statement: alter system set
db_name = ''USBEN'' comment=
''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set
db_unique_name = ''USBEN''
comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area
281018368 bytes
Fixed Size
2923440 bytes
Variable Size
222299216 bytes
Database Buffers
50331648 bytes
Redo Buffers
5464064 bytes
allocated channel: a1
channel a1: SID=33 device type=DISK
Starting restore at 17-FEB-16
channel a1: restoring control file
channel a1: restore complete, elapsed time: 00:00:02
output file name=+DATA/USBEN/CONTROLFILE/current.316.904051433
output file name=+DATA/USBEN/CONTROLFILE/current.295.904051435
Finished restore at 17-FEB-16
database mounted
contents of Memory Script:
{
set until scn 2534513;
set newname for clone
datafile 1 to new;
set newname for clone
datafile 2 to new;
set newname for clone
datafile 3 to new;
set newname for clone
datafile 4 to new;
set newname for clone
datafile 5 to new;
set newname for clone
datafile 6 to new;
set newname for clone
datafile 7 to new;
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
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 17-FEB-16
channel a1: starting datafile backup set restore
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to +DATA
channel a1: restoring datafile 00002 to +DATA
-- trimming the info here for brevity
channel a1: restoring datafile 00006 to +DATA
channel a1: restoring datafile 00007 to +DATA
channel a1: reading from backup piece
/backup/usben/rman/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851
channel a1: piece
handle=/backup/usben/rman/USBEN_L0_20160216_0927_135_47qu2cg3_1_1_903950851
tag=USBEN_L0_20160216_0927
channel a1: restored backup piece 1
channel a1: restore complete, elapsed time: 00:07:45
Finished restore at 17-FEB-16
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=904051938 file
name=+DATA/USBEN/DATAFILE/system.309.904051473
datafile 2 switched to datafile copy
-- trimming the info here for brevity
input datafile copy RECID=14 STAMP=904051938 file
name=+DATA/USBEN/DATAFILE/usben_indx.310.904051473
contents of Memory Script:
{
set until scn 2534513;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 17-FEB-16
starting media recovery
archived log for thread 1 with sequence 302 is already on disk as
file /backup/usben/rman/1_302_902782329.dbf
archived log for thread 1 with sequence 303 is already on disk as
file /backup/usben/rman/1_303_902782329.dbf
-- trimming the info here for brevity
archived log for thread 1 with sequence 331 is already on disk as
file /backup/usben/rman/1_331_902782329.dbf
archived log for thread 1 with sequence 332 is already on disk as
file /backup/usben/rman/1_332_902782329.dbf
channel a1: starting archived log restore to default destination
channel a1: restoring archived log
archived log thread=1 sequence=267
channel a1: restoring archived log
archived log thread=1 sequence=268
channel a1: restoring archived log
archived log thread=1 sequence=269
-- trimming the info here for brevity
archived log thread=1 sequence=300
channel a1: restoring archived log
archived log thread=1 sequence=301
channel a1: reading from backup piece
/backup/usben/rman/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315
channel a1: piece
handle=/backup/usben/rman/USBEN_Arch_20160216_0935_137_49qu2cuj_1_1_903951315
tag=TAG20160216T093515
channel a1: restored backup piece 1
channel a1: restore complete, elapsed time: 00:00:55
archived log file name=/backup/usben/arch/1_267_902782329.dbf
thread=1 sequence=267
channel clone_default: deleting archived log(s)
archived log file name=/backup/usben/arch/1_267_902782329.dbf
RECID=66 STAMP=904051990
archived log file name=/backup/usben/arch/1_268_902782329.dbf
thread=1 sequence=268
channel clone_default: deleting archived log(s)
archived log file name=/backup/usben/arch/1_268_902782329.dbf
RECID=44 STAMP=904051963
-- trimming the info here for brevity
archived log file name=/backup/usben/arch/1_300_902782329.dbf
thread=1 sequence=300
channel clone_default: deleting archived log(s)
archived log file name=/backup/usben/arch/1_300_902782329.dbf
RECID=65 STAMP=904051989
archived log file name=/backup/usben/arch/1_301_902782329.dbf
thread=1 sequence=301
channel clone_default: deleting archived log(s)
archived log file name=/backup/usben/arch/1_301_902782329.dbf
RECID=64 STAMP=904051989
archived log file name=/backup/usben/rman/1_302_902782329.dbf
thread=1 sequence=302
archived log file name=/backup/usben/rman/1_303_902782329.dbf
thread=1 sequence=303
-- trimming the info here for brevity
archived log file name=/backup/usben/rman/1_331_902782329.dbf
thread=1 sequence=331
archived log file name=/backup/usben/rman/1_332_902782329.dbf
thread=1 sequence=332
media recovery complete, elapsed time: 00:05:50
Finished recover at 17-FEB-16
Oracle instance started
Total System Global Area
281018368 bytes
Fixed Size
2923440 bytes
Variable Size
222299216 bytes
Database Buffers
50331648 bytes
Redo Buffers
5464064 bytes
contents of Memory Script:
{
sql clone "alter system
set db_name =
''USBEN'' comment=
''Reset to original value by
RMAN'' scope=spfile";
sql clone "alter system
reset db_unique_name
scope=spfile";
}
executing Memory Script
sql statement: alter system set
db_name = ''USBEN'' comment=
''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset
db_unique_name scope=spfile
Executing: create
pfile='/ora/app/oracle/product/12.1.0.1/db_1/dbs/initusben.ora' from spfile
Oracle instance started
Total System Global Area
281018368 bytes
Fixed Size
2923440 bytes
Variable Size
222299216 bytes
Database Buffers
50331648 bytes
Redo Buffers
5464064 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE
"USBEN" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA', '+DATA' ) SIZE 200 M ,
GROUP 2 ( '+DATA', '+DATA' ) SIZE 200 M
DATAFILE
'+DATA/USBEN/DATAFILE/system.309.904051473'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for clone
tempfile 2 to new;
switch clone tempfile all;
catalog clone
datafilecopy
"+DATA/USBEN/DATAFILE/usben_data.299.904051473",
"+DATA/USBEN/DATAFILE/sysaux.314.904051473",
"+DATA/USBEN/DATAFILE/undotbs1.302.904051473",
"+DATA/USBEN/DATAFILE/example.306.904051473",
"+DATA/USBEN/DATAFILE/users.296.904051473",
"+DATA/USBEN/DATAFILE/usben_indx.310.904051473";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 2 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/usben_data.299.904051473
RECID=1 STAMP=904052369
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/sysaux.314.904051473
RECID=2 STAMP=904052369
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/undotbs1.302.904051473
RECID=3 STAMP=904052369
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/example.306.904051473
RECID=4 STAMP=904052369
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/users.296.904051473
RECID=5 STAMP=904052369
cataloged datafile copy
datafile copy file name=+DATA/USBEN/DATAFILE/usben_indx.310.904051473
RECID=6 STAMP=904052369
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=904052369 file
name=+DATA/USBEN/DATAFILE/usben_data.299.904051473
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=904052369 file
name=+DATA/USBEN/DATAFILE/sysaux.314.904051473
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=904052369 file
name=+DATA/USBEN/DATAFILE/undotbs1.302.904051473
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=904052369 file
name=+DATA/USBEN/DATAFILE/example.306.904051473
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=904052369 file
name=+DATA/USBEN/DATAFILE/users.296.904051473
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=904052369 file
name=+DATA/USBEN/DATAFILE/usben_indx.310.904051473
contents of Memory Script:
{
Alter clone database open
resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 17-FEB-16
RMAN>
Recovery Manager complete.
|
sys@usben> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
-- Creating spfile in ASM disk
sys@usben> create spfile='+DATA/USBEN/PARAMETERFILE/spfileusben.ora' from PFILE;
File created.
sys@usben> !
[oracle@RACTEST1 dbs]$ cat initusben.ora
SPFILE='+DATA/USBEN/PARAMETERFILE/spfileusben.ora'
[oracle@RACTEST1 dbs]$
-- Restart the database with spfile.
sys@usben> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@usben> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2923440 bytes
Variable Size 222299216 bytes
Database Buffers 50331648 bytes
Redo Buffers 5464064 bytes
Database mounted.
Database opened.
sys@usben> set echo on
sys@usben> set feedback on
sys@usben> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/USBEN/PARAMETERFILE/spfileusben.ora
sys@usben>
|
Step7 Verify all the files and make sure all files are moved to ASM disk. Also watch out the alert log for a while. Create the listener and make sure able to connect outside the database server.
Now the database is successfully restored in different host. The database is also migrated to ASM disk as part of restore.
Enjoy reading my blog!!
No comments:
Post a Comment