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)

Categories: Computing, Database, ToolsTags: , , , ,

2 comments

  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

dbaonTap

What's #OnTapToday

globetrottingteen

If travel is an addiction, I'm afflicted.

The Black Veil One

My Diary Travel, Photography, My Sight About Somethings

Off to Ireland

Trotz Knacks den Schritt ins Ausland wagen

Randy Johnson's Oracle Blog

Oracle DBA Topics Including Unix, Scripting, & Automation

Model For {Change

Creating a positive change.

Globe-T.

Le Bonnet voyageur • The travelling Winter Hat

Another Photography Enthusiast

CLICKS THROUGH MY EYES

corkandspoon

Adventures in food and drink

Break Your Cage

Embracing The Unknown - Travel is not a pause from life, merely an extension of it.

My Girls On Tour

Fashion & Travel Blog by Abigail Royston

NYCQuirk

Two college girls trying to make the most out of living in NYC without breaking the bank!

Don Charisma

because anything is possible with Charisma

Bunny Kitchen

Exploring the possibilities of cruelty free food

Angelart Star

The beautiful picture of angels makes you happy.

%d bloggers like this: