Fuzzy Recovery

The other day, I was working on a fuzzy recovery thanks to some ugly Netapp snapshot “capabilities” and I came across some information that would just be helpful in general when understanding the Oracle media recovery process.

There have been times I have wanted to perform a “recover until cancel” recovery and on many of these occasions when I cancel the recovery, it pukes with this error:

Fri Jun 05 14:15:15 2015
ALTER DATABASE RECOVER CANCEL
Fri Jun 05 14:15:16 2015
Errors in file /ora_data/diag/rdbms/SNORT/SNORT/trace/SNORT_pr00_18570.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/ora_data/oradata/SNORT/system01.dbf’
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL …
ALTER DATABASE RECOVER CANCEL
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL …

Not desirable.  Rolling forward another log gives no guarantee that the process will complete successfully, so what’s the magic number?  You can find out by running a “restore database preview” (this preview SCN is from a later backup, so it’s not the same as the one used in my example process below):

RMAN> restore database preview;

Starting restore at JUN-05-2015 12:47:18
using channel ORA_DISK_1
List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————–
83 Incr 0 212.80M DISK 00:00:28 JUN-05-2015 12:27:39
BP Key: 104 Status: AVAILABLE Compressed: YES Tag: TAG20150605T122630
Piece Name: /oracle/fast_recovery_area/CDB1/backupset/2015_06_05/o1_mf_nnnd0_TAG20150605T122630_bq3y0hjp_.bkp
List of Datafiles in backup set 83
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——————– —-
1 0 Incr 7215122 JUN-05-2015 12:27:11 /home/oracle/app/oracle/oradata/cdb1/system01.dbf

…..

RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 7215100
Recovery must be done beyond SCN 7215204 to clear datafile fuzziness
Finished restore at JUN-05-2015 12:47:19

So let’s go back to your current state of the database and I’ll walk you through the scenario:

  • You have a database in need of recovery
  • You’ve tried “recover until cancel” but when you cancel, get the error above
  • You need to find out the newest non-fuzzy SCN.

With your database mounted, run this:

set serveroutput on
declare
scn number(12) := 0;
scnmax number(12) := 0;
begin
for f in (select * from v$datafile) loop
scn := dbms_backup_restore.scandatafile(f.file#);
dbms_output.put_line(‘File ‘ || f.file# ||’ absolute fuzzy scn = ‘ || scn);
if scn > scnmax then scnmax := scn; end if;
end loop;

dbms_output.put_line(‘Minimum PITR SCN = ‘ || scnmax);
end;
/

The output will look like this:

File 1 absolute fuzzy scn = 7203920
File 3 absolute fuzzy scn = 7203941
File 4 absolute fuzzy scn = 7203942
File 5 absolute fuzzy scn = 0
File 6 absolute fuzzy scn = 0
File 7 absolute fuzzy scn = 0
File 8 absolute fuzzy scn = 7203797
File 9 absolute fuzzy scn = 7203797
File 10 absolute fuzzy scn = 0
File 11 absolute fuzzy scn = 0
File 23 absolute fuzzy scn = 0
File 24 absolute fuzzy scn = 0
File 25 absolute fuzzy scn = 0
File 26 absolute fuzzy scn = 0
File 27 absolute fuzzy scn = 0
File 28 absolute fuzzy scn = 0
File 29 absolute fuzzy scn = 7203814
File 30 absolute fuzzy scn = 7203814
File 31 absolute fuzzy scn = 0
File 32 absolute fuzzy scn = 0
File 33 absolute fuzzy scn = 0
File 34 absolute fuzzy scn = 0
File 35 absolute fuzzy scn = 0
File 36 absolute fuzzy scn = 0
File 37 absolute fuzzy scn = 0
File 38 absolute fuzzy scn = 0
File 39 absolute fuzzy scn = 7203824
File 40 absolute fuzzy scn = 7203824
File 41 absolute fuzzy scn = 7203934
File 42 absolute fuzzy scn = 0
File 43 absolute fuzzy scn = 0
File 44 absolute fuzzy scn = 0
File 45 absolute fuzzy scn = 0
File 46 absolute fuzzy scn = 0
File 47 absolute fuzzy scn = 0
File 48 absolute fuzzy scn = 0
File 49 absolute fuzzy scn = 0
File 50 absolute fuzzy scn = 0
Minimum PITR SCN = 7203942

PL/SQL procedure successfully completed.

File 4 has the maximum SCN (bold added for readability).  You should now be able to perform a complete recovery:

SQL> alter database recover database until change 7203942;

Database altered.

SQL> alter database open resetlogs;

Database altered.

There.  Simple.  By knowing the SCN you must recover to, you have more control over your restore.  Of course, in my example, I created the need to perform this task with a “shutdown abort” then “startup mount” and prevented Oracle doing the media recovery to demonstrate this point.

MOS Note: Supported Backup, Restore and Recovery Operations using Third Party Snapshot Technologies (Doc ID 604683.1)

2 thoughts on “Fuzzy Recovery

  1. when I run the PL/SQL to find the maxmum SCN, I got the error of
    ERROR at line 1:
    ORA-19623: file 1 is open
    ORA-19600: input file is datafile 1 ()
    ORA-06512: at “SYS.DBMS_BACKUP_RESTORE”, line 3741
    ORA-06512: at “SYS.DBMS_BACKUP_RESTORE”, line 3721
    ORA-06512: at line 6

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s