Restore Full with Incremental Backup of Database using RMAN
Posted by Mir Sayeed Hassan on October 7th, 2017
Restore Full with Incremental Backup of Database by using RMAN
The below step by step procedure is to Restore Full Database with Incremental backup from the Production DB to Test DB Env for the verification purpose by using RMAN
Transfer the Production Full DB to Test DB for restore by using WINSCP, SCP etc
[oracle@localhost rman]$ cd friday_level_0/ ---- Level 0 (Full backup)
[oracle@localhost friday_level_0]$ ls df_PRIM_20171006_4415.bck arch_PRIM_20171006_4416.bck arch_PRIM_20171006_4417.bck ctlfileL0_PRIM_20171006_PRIM.bck rman2017-10-06.log
[oracle@localhost rman]$ cd saturday_level_1/ --- Level 1 (Incremental backup)
[oracle@localhost saturday_level_1]$ ls df_PRIM_20171007_4420.bck arch_PRIM_20171007_4421.bck rch_PRIM_20171007_4422.bck ctlfileL1_PRIM_20171007_4423.bck rman2017-10-07.log
[oracle@PRIM friday_level_0]$ cat rman2017-10-06
Check & verify – All the backup should be available as per requirement
[oracle@localhost saturday_level_1]$ cat rman2017-10-07
Check & verify – All the backup should be available as per requirement
On Test DB — Copy the backup from production database as shown below
[oracle@DB backup]$ ls arch_PRIM_20171006_4416.bck ctlfileL0_PRIM_20171006_PRIM.bck rman2017-10-06.log arch_PRIM_20171006_4417.bck ctlfileL1_PRIM_20171007_4423.bck rman2017-10-07.log arch_PRIM_20171007_4421.bck df_PRIM_20171006_4415.bck arch_PRIM_20171007_4422.bck df_PRIM_20171007_4420.bck (The contain Full + Incremental backup)
Set the SID Name in bash profile
[oracle@DB ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH # Oracle Settings TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SID=prim; export ORACLE_SID ORACLE_HOME_LISTNER=$ORACLE_HOME; export ORACLE_HOME_LISTNER ORACLE_TERM=xterm; export ORACLE_TERM TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11 PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib; export CLASSPATH if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi umask 022
[oracle@DB ~]$ . .bash_profile
[oracle@DB ~]$ echo $ORACLE_SID PRIM
Now startup the database in nomount to restore the controlfile from the backup location
[oracle@DB ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 7 15:22:01 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance
SQL> startup nomount ORACLE instance started. Total System Global Area 1.5734E+10 bytes Fixed Size 2268712 bytes Variable Size 2415919576 bytes Database Buffers 1.3288E+10 bytes Redo Buffers 27828224 bytes SQL> exit
[oracle@DB backup]$ ls arch_PRIM_20171006_4416.bck ctlfileL0_PRIM_20171006_PRIM.bck rman2017-10-06.log arch_PRIM_20171006_4417.bck ctlfileL1_PRIM_20171007_4423.bck rman2017-10-07.log arch_PRIM_20171007_4421.bck df_PRIM_20171006_4415.bck arch_PRIM_20171007_4422.bck df_PRIM_20171007_4420.bck
[oracle@DB backup]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 7 17:26:16 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIM (not mounted)
RMAN> restore controlfile from '/backup/ctlfileL1_PRIM_20171007_4423.bck'; Starting restore at 07-OCT-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=385 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oradata/admin/prim/controlfile/control01.ctl Finished restore at 07-OCT-17
RMAN> alter database mount; database mounted
RMAN> crosscheck backup; using channel ORA_DISK_1 specification does not match any backup in the repository RMAN> catalog start with '/backup/'; searching for all files that match the pattern /backup/ List of Files Unknown to the Database ===================================== File Name: /backup/arch_PRIM_20171006_4416.bck File Name: /backup/arch_PRIM_20171006_4417.bck File Name: /backup/arch_PRIM_20171007_4421.bck File Name: /backup/arch_PRIM_20171007_4422.bck File Name: /backup/ctlfileL0_PRIM_20171006_PRIM.bck File Name: /backup/ctlfileL1_PRIM_20171007_4423.bck File Name: /backup/df_PRIM_20171006_4415.bck File Name: /backup/df_PRIM_20171007_4420.bck File Name: /backup/rman2017-10-06.log File Name: /backup/rman2017-10-07.log Do you really want to catalog the above files (enter YES or NO)? cataloging files... cataloging done List of Cataloged Files ======================= File Name: /backup/arch_PRIM_20171006_4416.bck File Name: /backup/arch_PRIM_20171006_4417.bck File Name: /backup/arch_PRIM_20171007_4421.bck File Name: /backup/arch_PRIM_20171007_4422.bck File Name: /backup/ctlfileL0_PRIM_20171006_PRIM.bck File Name: /backup/ctlfileL1_PRIM_20171007_4423.bck File Name: /backup/df_PRIM_20171006_4415.bck File Name: /backup/df_PRIM_20171007_4420.bck List of Files Which Where Not Cataloged ======================================= File Name: /backup/rman2017-10-06.log RMAN-07517: Reason: The file header is corrupted File Name: /backup/rman2017-10-07.log RMAN-07517: Reason: The file header is corrupted
RMAN> crosscheck backup; using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/arch_PRIM_20171006_4416.bck RECID=4353 STAMP=956770102 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/arch_PRIM_20171006_4417.bck RECID=4354 STAMP=956770102 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/arch_PRIM_20171007_4421.bck RECID=4355 STAMP=956770102 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/arch_PRIM_20171007_4422.bck RECID=4356 STAMP=956770102 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/ctlfileL0_PRIM_20171006_PRIM.bck RECID=4357 STAMP=956770103 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/ctlfileL1_PRIM_20171007_4423.bck RECID=4358 STAMP=956770103 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/df_PRIM_20171006_4415.bck RECID=4359 STAMP=956770103 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/df_PRIM_20171007_4420.bck RECID=4360 STAMP=956770103 Crosschecked 8 objects
Restore database on test db
RMAN> run { 2> set newname for database to '/oradata/datafiles/%b'; 4> restore database; 5>switch datafile all; 6>} executing command: SET NEWNAME Starting restore at 07-OCT-17 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /oradata/datafiles/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /oradata/datafiles/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /oradata/datafiles/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /oradata/datafiles/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /oradata/datafiles/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /oradata/datafiles/esb_test.dbf channel ORA_DISK_1: restoring datafile 00008 to /oradata/datafiles/sample01.dbf channel ORA_DISK_1: reading from backup piece /backup/df_PRIM_20171006_4415.bck channel ORA_DISK_1: piece handle=/backup/df_PRIM_20171006_4415.bck tag=FRIDAY_LEVEL_0 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:14:47 Finished restore at 07-OCT-17 datafile 1 switched to datafile copy input datafile copy RECID=24 STAMP=956771174 file name=/oradata/datafiles/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=25 STAMP=956771174 file name=/oradata/datafiles/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=26 STAMP=956771174 file name=/oradata/datafiles/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=27 STAMP=956771174 file name=/oradata/datafiles/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=28 STAMP=956771174 file name=/oradata/datafiles/example01.dbf datafile 6 switched to datafile copy input datafile copy RECID=29 STAMP=956771174 file name=/oradata/datafiles/esb_test.dbf datafile 8 switched to datafile copy input datafile copy RECID=30 STAMP=956771174 file name=/oradata/datafiles/sample01.dbf
Restore complete
Now recover the database with the last updated SCN
RMAN> list backup of archivelog all; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 4352 502.63M DISK 00:00:00 06-OCT-17 BP Key: 4353 Status: AVAILABLE Compressed: YES Tag: TAG20171006T003715 Piece Name: /backup/arch_PRIM_20171006_4416.bck List of Archived Logs in backup set 4352 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 15515 596050443 05-OCT-17 596091563 05-OCT-17 1 15516 596091563 05-OCT-17 596130689 05-OCT-17 1 15517 596130689 05-OCT-17 596169682 05-OCT-17 1 15518 596169682 05-OCT-17 596219627 05-OCT-17 1 15761 606999903 06-OCT-17 607037229 06-OCT-17 1 15762 607037229 06-OCT-17 607072578 06-OCT-17 1 15763 607072578 06-OCT-17 607107087 06-OCT-17 1 15764 607107087 06-OCT-17 607142634 07-OCT-17 1 15765 607142634 07-OCT-17 607179013 07-OCT-17 1 15766 607179013 07-OCT-17 607188078 07-OCT-17
RMAN> run { 2> set until scn 607188078; 3> recover database; 4> } executing command: SET until clause Starting recover at 07-OCT-17 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /oradata/datafiles/system01.dbf destination for restore of datafile 00002: /oradata/datafiles/sysaux01.dbf destination for restore of datafile 00003: /oradata/datafiles/undotbs01.dbf destination for restore of datafile 00004: /oradata/datafiles/users01.dbf destination for restore of datafile 00005: /oradata/datafiles/example01.dbf destination for restore of datafile 00006: /oradata/datafiles/esb_test.dbf destination for restore of datafile 00008: /oradata/datafiles/sample01.dbf channel ORA_DISK_1: reading from backup piece /backup/df_PRIM_20171007_4420.bck channel ORA_DISK_1: piece handle=/backup/df_PRIM_20171007_4420.bck tag=SATURDAY_LEVEL_1 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:15 starting media recovery media recovery failed RMAN-00571: =========================================================== RMAN-00569: ======ERROR MESSAGE STACK FOLLOWS ====== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 10/07/2017 17:49:00 ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed start until change 607188078 using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database
(Here you can see the error – Its indicate that the database is not recover completely as the flashback is enable in the production database, Need to disable the flashback & perform the restore)
RMAN> exit Recovery Manager complete.
[oracle@DB backup]$ !sq
sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 7 17:50:06 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter database flashback off; Database altered.
RMAN> run { 2> set until scn 607188078; 3> recover database; 4> } executing command: SET until clause Starting recover at 07-OCT-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=385 device type=DISK starting media recovery channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=15765 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=15766 channel ORA_DISK_1: reading from backup piece /backup/arch_PRIM_20171007_4422.bck channel ORA_DISK_1: piece handle=/backup/arch_PRIM_20171007_4422.bck tag=TAG20171007T003205 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:05 archived log file name=/oradata/archive_log/1_15765_905677721.dbf thread=1 sequence=15765 archived log file name=/oradata/archive_log/1_15766_905677721.dbf thread=1 sequence=15766 media recovery complete, elapsed time: 00:00:26 Finished recover at 07-OCT-17
RMAN> alter database open resetlogs; database opened