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 :
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.
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