How much recovery is enough recovery

Long ago in one of the non-technical group discussions we had to decide ‘How much money is enough money?’ Obviously no answer is enough answer. Fortunately in Oracle, we know how much recovery is just sufficient to OPEN the DB.

During the recovery of the DB, when the archives are being applied one after other and another, is it possible to definitively confirm that the most recently applied archive was the last one required to be applied and it is safe thereafter to open the DB in RESETLOGS?

There should be several ways of identifying where to stop, and here are three that I can think of:

1. Alert log – If the backup was hot, finding out the last occurrence of END BACKUP and its corresponding SEQ# should tell us where to stop. Usually we stop after applying the above identified SEQ# and the DB should then be good to OPEN RESETLOGS.
   
2. RMAN log – If the backup was taken using RMAN, its log also shows the SEQ# where the backup ends. We can stop applying archives once we reach the max SEQ#
   
3. Query the Data Dictionary – Query the FHSTA column in X$KCVFH table during recovery to know if the recovery was sufficient or does it need more archives.
   

As I said, there must be other ways too.
I prefer option (3) because the information is right there in the DB to be queried for, anytime.

Consider a typical scenario where a user, say Lucy, is recovering the DB from the hot backup of the source database. She has already applied 30 archives and swears that she hasn’t seen another hungrier database. She has no access to the alert log of the source database to check how many more archives are to be applied. After each archive log has been applied, she CANCELs the recovery and gets the below error:

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
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: ‘/u11/data/PRD/PRD_SYSTEM_01.DBF’

The better way to confirm if the recovery was sufficient or not is to query the data dictionary. The SQL is:

SELECT hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE FROM x$kcvfh;

SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh

The output will look like above.

The STATUS (or fhsta) column has the value of 1 which means the datafile with FILENUMBER needs more recovery. This is the time to apply more archives.

The STATUS is 0 when the recovery is sufficient and it is safe to OPEN the database.

The fhsta can have one of the below values:

0 – DB is consistent. No more recovery required.
1 – DB needs more recovery. It’s time to apply more archives.
4 – DB is in a FUZZY state. Was the backup good?
8192 -
8196 -

I have come across 8192 and 8196 statuses too, but I don’t know their significance yet.

When the archives have been sufficiently applied, the fhsta column will be zero and looks like below:

At this stage, it is possible to cancel the recovery and bring up the DB in RESETLOGS.

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>
SQL> alter database open resetlogs;
Database altered.

The table X$KCVFH is a fixed table in Oracle. It stores the file headers along with their statuses. The name is derived from:

K – Kernel layer
C – Cache layer
V – RecoVery component
FH - File Header

Hope this post was useful.