Restore full Database by using RMAN
Posted by Mir Sayeed Hassan on October 7th, 2017
Restore full Database by using RMAN
The below step by step procedure is to Restore DB from the Production DB to Test DB Env for the verification purpose by using RMAN
Transfer the Production Full DB Backup to Test DB for restore by using WINSCP, SCP etc
[oracle@PRODDB friday_level_0]$ cd /oradata/backup/rman/friday_level_0
[oracle@PRODDB friday_level_0]$ pwd /oradata/backup/rman/friday_level_0
[oracle@PRODDB friday_level_0]$ ls df_PRODDB_20171006_832.bck arch_PRODDB_20171006_833.bck ctlfileL0_PRODDB_20171006.bck rman2017-10-06.log
[oracle@PRODDB friday_level_0]$ cat rman2017-10-06.log ------ Verify the availability of backup
[oracle@PRODDB friday_level_0]$ cat rman2017-10-06.log Recovery Manager: Release 11.2.0.4.0 - Production on Fri Oct 6 00:30:01 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates All rights reserved. connected to target database: PRODDB (DBID=1438307396) using target database control file instead of recovery catalog
RMAN> run {
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
CROSSCHECK COPY;
backup as compressed backupset incremental level 0 database tag friday_level_0 format '/oradata/backup/rman/friday_level_0/df_%d_%T_%s.bck';
backup as compressed backupset archivelog all format '/oradata/backup/rman/friday_level_0/arch_%d_%T_%s.bck' delete input;
backup spfile format '/oradata/backup/rman/friday_level_0/spf_%d_%T_%s.bck';
backup current controlfile format '/oradata/backup/rman/friday_level_0/ctlfileL0_%d_%T.bck';
delete noprompt obsolete;
}
exit;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=470 device type=DISK
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2367_942092742.arc RECID=4702 STAMP=956543822
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2368_942092742.arc RECID=4704 STAMP=956548064
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2369_942092742.arc RECID=4706 STAMP=956553011
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2370_942092742.arc RECID=4708 STAMP=956553515
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2371_942092742.arc RECID=4710 STAMP=956553731
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2372_942092742.arc RECID=4712 STAMP=956554244
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2373_942092742.arc RECID=4714 STAMP=956569919
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2374_942092742.arc RECID=4716 STAMP=956583293
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2375_942092742.arc RECID=4718 STAMP=956596916
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2376_942092742.arc RECID=4720 STAMP=956609594
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2377_942092742.arc RECID=4722 STAMP=956613617
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2378_942092742.arc RECID=4724 STAMP=956613770
Crosschecked 12 objects
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/friday_level_0/df_PRODDB_20170915_727.bck RECID=725 STAMP=954721803
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/friday_level_0/arch_PRODDB_20170915_728.bck RECID=726 STAMP=954722019
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/saturday_level_1/arch_PRODDB_20170916_733.bck RECID=731 STAMP=954808271
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/sunday_level_1/arch_PRODDB_20170917_738.bck RECID=736 STAMP=954894669
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/monday_level_1/arch_PRODDB_20170918_743.bck RECID=741 STAMP=954981070
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/tuesday_level_1/arch_PRODDB_20170919_748.bck RECID=746 STAMP=955067469
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/wednesday_level_1/arch_PRODDB_20170920_753.bck RECID=751 STAMP=955153869
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/thursday_level_1/df_PRODDB_20170921_757.bck RECID=755 STAMP=955240203
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/thursday_level_1/arch_PRODDB_20170921_758.bck RECID=756 STAMP=955240279
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_09_21/o1_mf_s_955240308_dw5l9d3y_.bkp RECID=759 STAMP=955240308
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/friday_level_0/df_PRODDB_20170922_762.bck RECID=760 STAMP=955326605
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/friday_level_0/arch_PRODDB_20170922_763.bck RECID=761 STAMP=955326831
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/friday_level_0/ctlfileL0_PRODDB_20170922.bck RECID=763 STAMP=955326858
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_09_22/o1_mf_s_955326859_dw8bbn11_.bkp RECID=764 STAMP=955326860
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/saturday_level_1/df_PRODDB_20170923_767.bck RECID=765 STAMP=955413004
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/saturday_level_1/arch_PRODDB_20170923_768.bck RECID=766 STAMP=955413069
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/saturday_level_1/ctlfileL1_PRODDB_20170923_770.bck RECID=768 STAMP=955413097
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_09_23/o1_mf_s_955413098_dwbyklhc_.bkp RECID=769 STAMP=955413098
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/sunday_level_1/df_PRODDB_20170924_772.bck RECID=770 STAMP=955499404
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/sunday_level_1/arch_PRODDB_20170924_773.bck RECID=771 STAMP=955499470
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/sunday_level_1/ctlfileL1_PRODDB_20170924_775.bck RECID=773 STAMP=955499498
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_09_24/o1_mf_s_955499499_dwflxmbz_.bkp RECID=774 STAMP=955499499
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/monday_level_1/df_PRODDB_20170925_777.bck RECID=775 STAMP=955585804
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/monday_level_1/arch_PRODDB_20170925_778.bck RECID=776 STAMP=955585870
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/monday_level_1/ctlfileL1_PRODDB_20170925_780.bck RECID=778 STAMP=955585898
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_09_25/o1_mf_s_955585899_dwj79m8s_.bkp RECID=779 STAMP=955585899
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/tuesday_level_1/df_PRODDB_20170926_782.bck RECID=780 STAMP=955672204
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/tuesday_level_1/arch_PRODDB_20170926_783.bck RECID=781 STAMP=955672270
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/tuesday_level_1/ctlfileL1_PRODDB_20170926_785.bck RECID=783 STAMP=955672297
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_09_26/o1_mf_s_955672298_dwlvolx8_.bkp RECID=784 STAMP=955672298
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/wednesday_level_1/df_PRODDB_20170927_787.bck RECID=785 STAMP=955758603
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/wednesday_level_1/arch_PRODDB_20170927_788.bck RECID=786 STAMP=955758669
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/wednesday_level_1/ctlfileL1_PRODDB_20170927_790.bck RECID=788 STAMP=955758697
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_09_27/o1_mf_s_955758698_dwoj1lhb_.bkp RECID=789 STAMP=955758698
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/thursday_level_1/df_PRODDB_20170928_792.bck RECID=790 STAMP=955845004
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/thursday_level_1/arch_PRODDB_20170928_793.bck RECID=791 STAMP=955845070
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/thursday_level_1/ctlfileL1_PRODDB_20170928_795.bck RECID=793 STAMP=955845098
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_09_28/o1_mf_s_955845099_dwr4fm62_.bkp RECID=794 STAMP=955845099
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/friday_level_0/df_PRODDB_20170929_797.bck RECID=795 STAMP=955931404
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/friday_level_0/arch_PRODDB_20170929_798.bck RECID=796 STAMP=955931630
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/friday_level_0/ctlfileL0_PRODDB_20170929.bck RECID=798 STAMP=955931657
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_09_29/o1_mf_s_955931658_dwtrym10_.bkp RECID=799 STAMP=955931659
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/saturday_level_1/df_PRODDB_20170930_802.bck RECID=800 STAMP=956017804
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/saturday_level_1/arch_PRODDB_20170930_803.bck RECID=801 STAMP=956017869
rosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/saturday_level_1/ctlfileL1_PRODDB_20170930_805.bck RECID=803 STAMP=956017897
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_09_30/o1_mf_s_956017898_dwxf5lgg_.bkp RECID=804 STAMP=956017898
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/sunday_level_1/df_PRODDB_20171001_807.bck RECID=805 STAMP=956190604
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/sunday_level_1/arch_PRODDB_20171001_808.bck RECID=806 STAMP=956190670
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/sunday_level_1/ctlfileL1_PRODDB_20171001_810.bck RECID=808 STAMP=956190698
rosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_10_01/o1_mf_s_956190699_dx01km96_.bkp RECID=809 STAMP=956190699
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/monday_level_1/df_PRODDB_20171002_812.bck RECID=810 STAMP=956277004
rosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/monday_level_1/arch_PRODDB_20171002_813.bck RECID=811 STAMP=956277069
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/monday_level_1/ctlfileL1_PRODDB_20171002_815.bck RECID=813 STAMP=956277097
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_10_02/o1_mf_s_956277098_dx2oxlh3_.bkp RECID=814 STAMP=956277098
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/tuesday_level_1/df_PRODDB_20171003_817.bck RECID=815 STAMP=956363404
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/tuesday_level_1/arch_PRODDB_20171003_818.bck RECID=816 STAMP=956363470
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/tuesday_level_1/ctlfileL1_PRODDB_20171003_820.bck RECID=818 STAMP=956363498
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_10_03/o1_mf_s_956363499_dx5b9m9r_.bkp RECID=819 STAMP=956363499
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/wednesday_level_1/df_PRODDB_20171004_822.bck RECID=820 STAMP=956449804
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/wednesday_level_1/arch_PRODDB_20171004_823.bck RECID=821 STAMP=956449869
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/wednesday_level_1/ctlfileL1_PRODDB_20171004_825.bck RECID=823 STAMP=956449897
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_10_04/o1_mf_s_956449898_dx7yolqb_.bkp RECID=824 STAMP=956449898
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/thursday_level_1/df_PRODDB_20171005_827.bck RECID=825 STAMP=956536203
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/thursday_level_1/arch_PRODDB_20171005_828.bck RECID=826 STAMP=956536279
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/oradata/backup/rman/thursday_level_1/ctlfileL1_PRODDB_20171005_830.bck RECID=828 STAMP=956536307
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_10_05/o1_mf_s_956536308_dxbm1wf5_.bkp RECID=829 STAMP=956536308
Crosschecked 66 objects
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=470 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2367_942092742.arc RECID=4702 STAMP=956543822
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2368_942092742.arc RECID=4704 STAMP=956548064
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2369_942092742.arc RECID=4706 STAMP=956553011
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2370_942092742.arc RECID=4708 STAMP=956553515
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2371_942092742.arc RECID=4710 STAMP=956553731
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2372_942092742.arc RECID=4712 STAMP=956554244
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2373_942092742.arc RECID=4714 STAMP=956569919
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2374_942092742.arc RECID=4716 STAMP=956583293
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2375_942092742.arc RECID=4718 STAMP=956596916
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2376_942092742.arc RECID=4720 STAMP=956609594
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2377_942092742.arc RECID=4722 STAMP=956613617
validation succeeded for archived log
archived log file name=/oradata/archivelog/1_2378_942092742.arc RECID=4724 STAMP=956613770
Crosschecked 12 objects
Starting backup at 06-OCT-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/oradata/datafiles/proj1 _tbs.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PRODDB/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/PRODDB/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PRODDB/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PRODDB/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PRODDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-OCT-17
channel ORA_DISK_1: finished piece 1 at 06-OCT-17
piece handle=/oradata/backup/rman/friday_level_0/df_PRODDB_20171006_832.bck tag=FRIDAY_LEVEL_0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:55
Finished backup at 06-OCT-17
Starting backup at 06-OCT-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2367 RECID=4702 STAMP=956543822
input archived log thread=1 sequence=2368 RECID=4704 STAMP=956548064
input archived log thread=1 sequence=2369 RECID=4706 STAMP=956553011
input archived log thread=1 sequence=2370 RECID=4708 STAMP=956553515
input archived log thread=1 sequence=2371 RECID=4710 STAMP=956553731
input archived log thread=1 sequence=2372 RECID=4712 STAMP=956554244
input archived log thread=1 sequence=2373 RECID=4714 STAMP=956569919
input archived log thread=1 sequence=2374 RECID=4716 STAMP=956583293
input archived log thread=1 sequence=2375 RECID=4718 STAMP=956596916
input archived log thread=1 sequence=2376 RECID=4720 STAMP=956609594
input archived log thread=1 sequence=2377 RECID=4722 STAMP=956613617
input archived log thread=1 sequence=2378 RECID=4724 STAMP=956613770
input archived log thread=1 sequence=2379 RECID=4726 STAMP=956622840
channel ORA_DISK_1: starting piece 1 at 06-OCT-17
channel ORA_DISK_1: finished piece 1 at 06-OCT-17
piece handle=/oradata/backup/rman/friday_level_0/arch_PRODDB_20171006_833.bck tag=TAG20171006T003400 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/oradata/archivelog/1_2367_942092742.arc RECID=4702 STAMP=956543822
archived log file name=/oradata/archivelog/1_2368_942092742.arc RECID=4704 STAMP=956548064
archived log file name=/oradata/archivelog/1_2369_942092742.arc RECID=4706 STAMP=956553011
archived log file name=/oradata/archivelog/1_2370_942092742.arc RECID=4708 STAMP=956553515
archived log file name=/oradata/archivelog/1_2371_942092742.arc RECID=4710 STAMP=956553731
archived log file name=/oradata/archivelog/1_2372_942092742.arc RECID=4712 STAMP=956554244
archived log file name=/oradata/archivelog/1_2373_942092742.arc RECID=4714 STAMP=956569919
archived log file name=/oradata/archivelog/1_2374_942092742.arc RECID=4716 STAMP=956583293
archived log file name=/oradata/archivelog/1_2375_942092742.arc RECID=4718 STAMP=956596916
archived log file name=/oradata/archivelog/1_2376_942092742.arc RECID=4720 STAMP=956609594
archived log file name=/oradata/archivelog/1_2377_942092742.arc RECID=4722 STAMP=956613617
archived log file name=/oradata/archivelog/1_2378_942092742.arc RECID=4724 STAMP=956613770
archived log file name=/oradata/archivelog/1_2379_942092742.arc RECID=4726 STAMP=956622840
Finished backup at 06-OCT-17
Starting backup at 06-OCT-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 06-OCT-17
channel ORA_DISK_1: finished piece 1 at 06-OCT-17
piece handle=/oradata/backup/rman/friday_level_0/spf_PRODDB_20171006_834.bck tag=TAG20171006T003425 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-17
Starting backup at 06-OCT-17
using channel ORA_DISK_1
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 06-OCT-17
channel ORA_DISK_1: finished piece 1 at 06-OCT-17
piece handle=/oradata/backup/rman/friday_level_0/ctlfileL0_PRODDB_20171006.bck tag=TAG20171006T003426 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-17
Starting Control File and SPFILE Autobackup at 06-OCT-17
piece handle=/u01/app/oracle/fast_recovery_area/PRODDB/autobackup/2017_10_06/o1_mf_s_956622868_dxf7lwx7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-OCT-17
On Test DB — Copy the backup from production database as shown below
[oracle@DB backup]$ ls arch_PRODDB_20171006_833.bck df_PRODDB_20171006_832.bck ctlfileL0_PRODDB_20171006.bck rman2017-10-06.log (The contain FULL DB) 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=PRODDB; 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 PRODDB
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 3691200512 bytes Fixed Size 2258680 bytes Variable Size 1073744136 bytes Database Buffers 2600468480 bytes Redo Buffers 14729216 bytes
[oracle@DB ~]$ cd /backup/
[oracle@DB backup]$ ls df_PRODDB_20171006_832.bck arch_PRODDB_20171006_833.bck ctlfileL0_PRODDB_20171006.bck rman2017-10-06.log
[oracle@DB backup]$ rman target / RMAN> restore controlfile from '/backup/ctlfileL0_PRODDB_20171006.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:03 output file name=/u01/app/oracle/oradata/PRODDB/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/PRODDB/control02.ctl Finished restore at 07-OCT-17
RMAN> alter database mount; database mounted released channel: ORA_DISK_1 Catalog the backup
[oracle@DB backup]$ rman target /
RMAN> catalog start with '/backup/'; using target database control file instead of recovery catalog searching for all files that match the pattern /backup/ List of Files Unknown to the Database ===================================== File Name: /backup/arch_PRODDB_20171006_833.bck File Name: /backup/ctlfileL0_PRODDB_20171006.bck File Name: /backup/df_PRODDB_20171006_832.bck File Name: /backup/rman2017-10-06.log Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /backup/arch_PRODDB_20171006_833.bck File Name: /backup/ctlfileL0_PRODDB_20171006.bck File Name: /backup/df_PRODDB_20171006_832.bck List of Files Which Where Not Cataloged ======================================= File Name: /backup/rman2017-10-06.log RMAN-07517: Reason: The file header is corrupted - Don’t worry its an logfile
RMAN> crosscheck backup; using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/df_PRODDB_20171006_832.bck RECID=835 STAMP=956763610 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/arch_PRODDB_20171006_833.bck RECID=833 STAMP=956763609 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/backup/ctlfileL0_PRODDB_20171006.bck RECID=834 STAMP=956763609 Crosschecked 3 objects
RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 830 Incr 0 1.39G DISK 00:03:51 06-OCT-17 BP Key: 835 Status: AVAILABLE Compressed: YES Tag: FRIDAY_LEVEL_0 Piece Name: /backup/df_PRODDB_20171006_832.bck List of Datafiles in backup set 830 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 0 Incr 58032926 06-OCT-17 /u01/app/oracle/oradata/PRODDB/system01.dbf 2 0 Incr 58032926 06-OCT-17 /u01/app/oracle/oradata/PRODDB/sysaux01.dbf 3 0 Incr 58032926 06-OCT-17 /u01/app/oracle/oradata/PRODDB/undotbs01.dbf 4 0 Incr 58032926 06-OCT-17 /u01/app/oracle/oradata/PRODDB/users01.dbf 5 0 Incr 58032926 06-OCT-17 /u01/app/oracle/oradata/PRODDB/example01.dbf 6 0 Incr 58032926 06-OCT-17 /oradata/datafiles/proj1_tbs.dbf BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 831 117.20M DISK 00:00:15 06-OCT-17 BP Key: 833 Status: AVAILABLE Compressed: YES Tag: TAG20171006T003400 Piece Name: /backup/arch_PRODDB_20171006_833.bck List of Archived Logs in backup set 831 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 2367 57705462 05-OCT-17 57727217 05-OCT-17 1 2368 57727217 05-OCT-17 57735391 05-OCT-17 1 2369 57735391 05-OCT-17 57754553 05-OCT-17 1 2370 57754553 05-OCT-17 57790109 05-OCT-17 1 2371 57790109 05-OCT-17 57828334 05-OCT-17 1 2372 57828334 05-OCT-17 57863503 05-OCT-17 1 2373 57863503 05-OCT-17 57893265 05-OCT-17 1 2374 57893265 05-OCT-17 57920915 05-OCT-17 1 2375 57920915 05-OCT-17 57949676 05-OCT-17 1 2376 57949676 05-OCT-17 57977334 05-OCT-17 1 2377 57977334 05-OCT-17 58009592 05-OCT-17 1 2378 58009592 05-OCT-17 58012925 05-OCT-17 1 2379 58012925 05-OCT-17 58033800 06-OCT-17 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 833 Full 11.17M DISK 00:00:00 06-OCT-17 BP Key: 834 Status: AVAILABLE Compressed: NO Tag: TAG20171006T003426 Piece Name: /backup/ctlfileL0_PRODDB_20171006.bck Control File Included: Ckp SCN: 58033908 Ckp time: 06-OCT-17
Start restore the database
RMAN> run {
set newname for database to '/oradata/datafiles/%b'; ------ Define a new location for the restored datafile
restore database;
switch datafile all; ----- Its rename all datafiles
}
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/proj1_tbs.dbf
channel ORA_DISK_1: reading from backup piece /backup/df_PRODDB_20171006_832.bck
channel ORA_DISK_1: piece handle=/backup/df_PRODDB_20171006_832.bck tag=FRIDAY_LEVEL_0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:12:18
Finished restore at 07-OCT-17
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=956764505 file name=/oradata/datafiles/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=956764505 file name=/oradata/datafiles/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=956764505 file name=/oradata/datafiles/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=956764505 file name=/oradata/datafiles/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=956764505 file name=/oradata/datafiles/example01.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 ------- ---------- ----------- ------------ --------------- 831 117.20M DISK 00:00:15 06-OCT-17 BP Key: 833 Status: AVAILABLE Compressed: YES Tag: TAG20171006T003400 Piece Name: /backup/arch_PRODDB_20171006_833.bck List of Archived Logs in backup set 831 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 2367 57705462 05-OCT-17 57727217 05-OCT-17 1 2368 57727217 05-OCT-17 57735391 05-OCT-17 1 2369 57735391 05-OCT-17 57754553 05-OCT-17 1 2370 57754553 05-OCT-17 57790109 05-OCT-17 1 2371 57790109 05-OCT-17 57828334 05-OCT-17 1 2372 57828334 05-OCT-17 57863503 05-OCT-17 1 2373 57863503 05-OCT-17 57893265 05-OCT-17 1 2374 57893265 05-OCT-17 57920915 05-OCT-17 1 2375 57920915 05-OCT-17 57949676 05-OCT-17 1 2376 57949676 05-OCT-17 57977334 05-OCT-17 1 2377 57977334 05-OCT-17 58009592 05-OCT-17 1 2378 58009592 05-OCT-17 58012925 05-OCT-17 1 2379 58012925 05-OCT-17 58033800 06-OCT-17 --- This is last updated SCN Got the Last updated SCN -- 58033800
RMAN> run {
set until scn 58033800;
recover database;
}
executing command: SET until clause
Starting recover 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
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=2379
channel ORA_DISK_1: reading from backup piece /backup/arch_PRODDB_20171006_833.bck
channel ORA_DISK_1: piece handle=/backup/arch_PRODDB_20171006_833.bck tag=TAG20171006T003400
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_2379_942092742.arc thread=1 sequence=2379
media recovery complete, elapsed time: 00:00:03
Finished recover at 07-OCT-17
RMAN> alter database open resetlogs; database opened
Hence restore the production DB in Test ENV, Tested & Verified in Real time