TIP # 49 : Recover database before resetlogs.

I had a client which 5 instances on the same box were crashed because of disk failure.They did recover databases more than once and each time they found out that DBs were not recovered to right time.
They called me and asked me to recover database to June 7th which was the time that all DBs were opened with RESETLOGS.
As I checked, I found that DBs were opened with resetlogs 4 times.

June 3rd
June 7th <==== Requested recovery time.
June 9th
June 10th

Recovery seems to be straight forward with RMAN.Just need to find right incarnation and reset database to this incarnation and recover database.
This was my first thought but it did not work.
Why ? Reason is very simple. Since RMAN had no backup in June 7th right after resrlogs, database could not be recovered to that SCN.Database should be recovered to SCN in June 7th which RMAN has backup for all datafiles.
Therefore, the scenario was changed to this way :


- Connect to recovery catalog.
- List incarnation; <== Find incarnation for June 7th, sassume 1234.
- RESET database to incarnation 1234; <=== Incarnation from previous step.
- List backup of database; <==== Find the max SCN in June 7th which all datafiles have been backed up. (For example 28234442198 )
- run {
allocate channel ch1 device type disk;
set UNTIL SCN 28234442198 ;
restore controlfile ;
sql 'alter database mount';
restore database;
recover database;
}


=========

Example :

RMAN> list incarnation;


List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
136421 136465 ALTP 2638891487 NO 190578 30-SEP-06
136421 136422 ALTP 2638891487 YES 28209020897 07-JUN-07
136421 141144 ALTP 2638891487 NO 28235483091 10-JUN-07

RESET DATABASE TO INCARNATION 136422;

List backup of database ;

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136888 Full 161M DISK 00:00:08 07-JUN-07
BP Key: 136903 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662601_S1666_P1
List of Datafiles in backup set 136888
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\CWMLITE01.DBF
6 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\INDX01.DBF
13 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\CONVDATA.DBF
18 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\ININDX.DBF
28 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\SQINDX.DBF
29 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\SYDATA.DBF
30 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\SYINDX.DBF
38 Full 28234451112 07-JUN-07 D:\ORADATA\YKTP\WOINDX.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136889 Full 348M DISK 00:00:10 07-JUN-07
BP Key: 136904 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662616_S1667_P1
List of Datafiles in backup set 136889
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\DRSYS01.DBF
5 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\EXAMPLE01.DBF
11 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\APPDDATA.DBF
14 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\CUSTDATA.DBF
19 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\INVCDATA.DBF
24 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\RFQDATA.DBF
35 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\T_DATA.DBF
37 Full 28234451193 07-JUN-07 D:\ORADATA\YKTP\WODATA.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136890 Full 200M DISK 00:00:09 07-JUN-07
BP Key: 136905 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662631_S1668_P1
List of Datafiles in backup set 136890
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\ODM01.DBF
9 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\USERS01.DBF
10 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\XDB01.DBF
16 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\EQDATA.DBF
17 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\EQINDX.DBF
23 Full 28234451214 07-JUN-07 D:\ORADATA\YKTP\PRCHDATA.DBF
41 Full 28234451214 07-JUN-07 C:\ORACLE\ORADATA\YKTP\ARCHDATA.DBF
42 Full 28234451214 07-JUN-07 C:\ORACLE\ORADATA\YKTP\ARCHINDX.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136891 Full 365M DISK 00:00:11 07-JUN-07
BP Key: 136906 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662646_S1669_P1
List of Datafiles in backup set 136891
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\SYSTEM01.DBF
8 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\TOOLS01.DBF
12 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\APPDINDX.DBF
15 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\CUSTINDX.DBF
20 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\INVCINDX.DBF
22 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\MSPROJ.DBF
26 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\BIGROLL1.DBF
32 Full 28234451320 07-JUN-07 D:\ORADATA\YKTP\YKTPTOOL.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136892 Full 330M DISK 00:00:11 07-JUN-07
BP Key: 136907 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662662_S1670_P1
List of Datafiles in backup set 136892
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
21 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\IN_ITEM_XREF.DBF
25 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\RFQINDX.DBF
27 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\SQDATA.DBF
31 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\INDATA.ORA
33 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\TPDATA.DBF
34 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\TPINDX.DBF
36 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\YKTPUSER.DBF
40 Full 28234451389 07-JUN-07 D:\ORADATA\YKTP\PRCHINDX.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136893 Full 1000M DISK 00:00:40 07-JUN-07
BP Key: 136908 Status: AVAILABLE Tag: TAG20070607T212319
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624662677_S1671_P1
List of Datafiles in backup set 136893
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 28234451491 07-JUN-07 E:\ORADATA\YKTP\UNDOTBS01.DBF
39 Full 28234451491 07-JUN-07 C:\ORACLE\ORADATA\YKTP\COPYROL1.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141796 Full 161M DISK 00:00:08 11-JUN-07
BP Key: 141802 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998729_S1681_P1
List of Datafiles in backup set 141796
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\CWMLITE01.DBF
6 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\INDX01.DBF
13 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\CONVDATA.DBF
18 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\ININDX.DBF
28 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\SQINDX.DBF
29 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\SYDATA.DBF
30 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\SYINDX.DBF
38 Full 28239402207 11-JUN-07 D:\ORADATA\YKTP\WOINDX.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141797 Full 349M DISK 00:00:09 11-JUN-07
BP Key: 141803 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998745_S1682_P1
List of Datafiles in backup set 141797
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\DRSYS01.DBF
5 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\EXAMPLE01.DBF
11 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\APPDDATA.DBF
14 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\CUSTDATA.DBF
19 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\INVCDATA.DBF
24 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\RFQDATA.DBF
35 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\T_DATA.DBF
37 Full 28239402216 11-JUN-07 D:\ORADATA\YKTP\WODATA.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141798 Full 201M DISK 00:00:08 11-JUN-07
BP Key: 141804 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998760_S1683_P1
List of Datafiles in backup set 141798
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\ODM01.DBF
9 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\USERS01.DBF
10 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\XDB01.DBF
16 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\EQDATA.DBF
17 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\EQINDX.DBF
23 Full 28239402297 11-JUN-07 D:\ORADATA\YKTP\PRCHDATA.DBF
41 Full 28239402297 11-JUN-07 C:\ORACLE\ORADATA\YKTP\ARCHDATA.DBF
42 Full 28239402297 11-JUN-07 C:\ORACLE\ORADATA\YKTP\ARCHINDX.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141799 Full 365M DISK 00:00:10 11-JUN-07
BP Key: 141805 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998775_S1684_P1
List of Datafiles in backup set 141799
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\SYSTEM01.DBF
8 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\TOOLS01.DBF
12 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\APPDINDX.DBF
15 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\CUSTINDX.DBF
20 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\INVCINDX.DBF
22 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\MSPROJ.DBF
26 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\BIGROLL1.DBF
32 Full 28239402306 11-JUN-07 D:\ORADATA\YKTP\YKTPTOOL.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141800 Full 330M DISK 00:00:10 11-JUN-07
BP Key: 141806 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998790_S1685_P1
List of Datafiles in backup set 141800
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
21 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\IN_ITEM_XREF.DBF
25 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\RFQINDX.DBF
27 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\SQDATA.DBF
31 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\INDATA.ORA
33 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\TPDATA.DBF
34 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\TPINDX.DBF
36 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\YKTPUSER.DBF
40 Full 28239402384 11-JUN-07 D:\ORADATA\YKTP\PRCHINDX.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141801 Full 1000M DISK 00:00:37 11-JUN-07
BP Key: 141807 Status: AVAILABLE Tag: TAG20070611T184529
Piece Name: E:\BACKUP\YKTP\RMAN\DB_BACKUP_YKTP_T624998806_S1686_P1
List of Datafiles in backup set 141801
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 28239402393 11-JUN-07 E:\ORADATA\YKTP\UNDOTBS01.DBF
39 Full 28239402393 11-JUN-07 C:\ORACLE\ORADATA\YKTP\COPYROL1.DBF

After finding proper SCN, run the following to recover database before resetlogs.

run {
allocate channel ch1 device type disk;
set UNTIL SCN 28234451320 ;
restore controlfile ;
sql 'alter database mount';
restore database;
recover database;
}

2 comments:

Anonymous said...

Thanks for sharing .

Alexis said...

For work with databases I advise-dbf recovery,because in my view tool is quite reliable in this sphere and it is free as far as I know.Moreover program can also analyzing data in damaged dbf files and associated files containing MEMO fields (fpt or dbt depending on the version).