Restore/Recovery using RMAN with different scenarios
POINT IN TIME PRODUCTION RESTORE/RECOVERY USING RMAN BACKUPS
ASSUMPTION:
*This
restore recover situation is pertaining to Restoring database from 15th
Sep 09 backup and recovering point in time upto 16th Sep 09 12:00 Noon.
(This situation raised as a user made logical error in the database).
*Took
a backup of current database using RMAN and also kept a cold backup
after shutting down the database. This is usefull in case of reverting
back the database to original.
*Remove all Controlfiles/Logfile/Database files including undo,temp from the original location.
*Make sure RMAN catalog database tnsentry is available in Restoring production server.
Connect to target database and recovery catalog database using following command.
rman catalog username/password@catalogdb target sys/password@targetdb
Get the backup piece information with List backup command in RMAN.
RMAN> List backup;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4173 Full 3M DISK 00:00:01 15-SEP-09
BP Key: 4173 Status: AVAILABLE Tag: TAG20090915T182528
Piece Name: F:\BACKUPS\RMAN\SID\SID_20090915_697659929_4273_1.CTL
Controlfile Included: Ckp SCN: 136760496 Ckp time: 15-SEP-09
Get the Controlfile name and Tag name of 15 Sep 09 controlfile backup.
Backup piece name : SID_20090915_697659929_4273_1.CTL
Tag name : TAG20090915T182528
Restore controlfile from Tag name.
run{
allocate channel ch1 type DISK;
restore controlfile from tag 'TAG20090915T182528';
release channel ch1;
}
Mount the database.
RMAN> alter database mount;
database mounted
IMP NOTE:
At
this point, Restore database command alone will restore the latest
backup pieces from auto backup since this is from catalog. taken on 16th
Sep 09. Hence Find out the appropriate tag for database files on 15th
Sep 09 and use it for restoration.
RMAN> list backup summary completed after 'sysdate -2';
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
235823 B 0 A SBT_TAPE 15-SEP-09 1 1 TAG20090915T010133
235824 B 0 A SBT_TAPE 15-SEP-09 1 1 TAG20090915T010133
235825 B 0 A SBT_TAPE 15-SEP-09 1 1 TAG20090915T010133
235826 B 0 A SBT_TAPE 15-SEP-09 1 1 TAG20090915T010133
235827 B F A SBT_TAPE 15-SEP-09 1 1
235828 B A A SBT_TAPE 15-SEP-09 1 1 TAG20090915T012854
235829 B A A SBT_TAPE 15-SEP-09 1 1 TAG20090915T012854
235830 B A A SBT_TAPE 15-SEP-09 1 1 TAG20090915T012854
235831 B A A SBT_TAPE 15-SEP-09 1 1 TAG20090915T012854
235832 B A A SBT_TAPE 15-SEP-09 1 1 TAG20090915T012854
235833 B F A SBT_TAPE 15-SEP-09 1 1 TAG20090915T012929
235834 B F A SBT_TAPE 15-SEP-09 1 1
235835 B A A SBT_TAPE 15-SEP-09 1 1 TAG20090915T110015
235836 B A A SBT_TAPE 15-SEP-09 1 1 TAG20090915T110015
235837 B A A SBT_TAPE 15-SEP-09 1 1 TAG20090915T110015
235838 B A A SBT_TAPE 15-SEP-09 1 1 TAG20090915T110015
235839 B F A SBT_TAPE 15-SEP-09 1 1
235894 B F A DISK 15-SEP-09 1 1 TAG20090915T182007
235895 B F A DISK 15-SEP-09 1 1 TAG20090915T182007
235915 B F A DISK 15-SEP-09 1 1 TAG20090915T182528
235922 B A A DISK 15-SEP-09 1 1 TAG20090915T182532
235923 B A A DISK 15-SEP-09 1 1 TAG20090915T182532
235924 B A A DISK 15-SEP-09 1 1 TAG20090915T182532
235936 B F A DISK 15-SEP-09 1 1
236271 B F A DISK 16-SEP-09 1 1 TAG20090916T172304
236272 B F A DISK 16-SEP-09 1 1 TAG20090916T172304
236292 B F A DISK 16-SEP-09 1 1 TAG20090916T172837
236299 B A A DISK 16-SEP-09 1 1 TAG20090916T172851
236303 B F A DISK 16-SEP-09 1 1
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
235894 Full 6G DISK 00:03:56 15-SEP-09
BP Key: 235896 Status: AVAILABLE Tag: TAG20090915T182007
Piece Name: F:\BACKUPS\RMAN\SID\SID_20090915_697659608_4271_1.DBF
List of Datafiles in backup set 235894
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 136760211 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_SYSTEM01.DBF
2 Full 136760211 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWPDS02.DBF
5 Full 136760211 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWDATAMART01.DB
6 Full 136760211 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWINDEX01.DBF
8 Full 136760211 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWLOB01.DBF
14 Full 136760211 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_BUSOBJS01.DBF
15 Full 136760211 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_IVIEWS01.DBF
17 Full 136760211 15-SEP-09 E:\ORACLE\ORADATA\SID\SID_UNDOTBS1.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
235895 Full 8G DISK 00:05:12 15-SEP-09
BP Key: 235897 Status: AVAILABLE Tag: TAG20090915T182007
Piece Name: F:\BACKUPS\RMAN\SID\SID_20090915_697659609_4272_1.DBF
List of Datafiles in backup set 235895
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 136760212 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_CWMLITE01.DBF
4 Full 136760212 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_DRSYS01.DBF
7 Full 136760212 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWIVINDEX01.DBF
9 Full 136760212 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_FWPDS01.DBF
10 Full 136760212 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_ODM01.DBF
11 Full 136760212 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_TOOLS01.DBF
12 Full 136760212 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_USERS01.DBF
13 Full 136760212 15-SEP-09 D:\ORACLE\ORADATA\SID\SID_XDB01.DBF
Run the below command to restore the database from tag
run{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
allocate channel ch3 type DISK;
allocate channel ch4 type DISK;
RESTORE DATABASE from tag 'TAG20090915T182007';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
Recover database until the required time of 16th Sep 09 12:00PM noon.
*Check
the availability of Archivelog files its asking for. If Archivelog
files already available in the default location, no need to restore the
archive log files.
SQL> RECOVER DATABASE UNTIL TIME '2009-09-16:12:00:00' USING BACKUP CONTROLFILE;
ORA-00279: change 136760211 generated at 09/15/2009 18:20:09 needed for thread
1
ORA-00289: suggestion : E:\ORACLE\ORADATA\SID\ARCHIVE\ARC01202001
ORA-00280: change 136760211 for thread 1 is in sequence #1202
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 136760501 generated at 09/15/2009 18:25:31 needed for thread
1
ORA-00289: suggestion : E:\ORACLE\ORADATA\SID\ARCHIVE\ARC01203001
ORA-00280: change 136760501 for thread 1 is in sequence #1203
ORA-00278: log file 'E:\ORACLE\ORADATA\SID\ARCHIVE\ARC01202001' no longer
needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
Verify the checkpoint time for all the datafiles. It should show the same restore time.
SQL> Select distinct to_char(checkpoint_time,’DD-MON-YYYY HH24:MI:SS’) from v$datafile;
Once
all archive log files are applied and after the status is Media recover
complete, Use alter database open resetlogs to open the database.
SQL> alter database open resetlogs;
Database altered.
Add temp files as required.
SQL> alter tablespace temp add tempfile 'E:\ORACLE\ORADATA\SID\SID_TEMP01.DBF' size 200m;
Tablespace altered.
Perform a basic health check as required.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
SID READ WRITE
SQL> alter system checkpoint;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\Oracle\oradata\SID\archive
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
Verify database connectivity from local laptop or any other hosts.
IMPNOTE:
As
this is a new database incarnation all further Rman backups will fail
as this new incarnation is not registered in the catalog database. Issue
the following command to register this database incarnation to the
catalog.
RMAN> reset database;
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 2 SID 3580177852 NO 1 04-JUN-07
1 144370 SID 3580177852 NO 97271318 28-NOV-08
1 242850 SID 3580177852 YES 136846506 16-SEP-09
Finally take a backup of production database immediately after restore recovery.
