You can restored a database from the backup and applied lots of archive to make it consistent. Now you would like to make sure open resets logs goes fine.
Here is how to check Database is consistent after incomplete recovery
The below statement set the date format to extended form as we would be requiring this many times in our analysis
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;
Check 1:
Objective: Verify that the datafiles are recovered to the intended point in time (PIT) and they are consistent (FUZZY=NO)
Query the current status and PIT (P-oint I-n T-ime upto which the datafiles have been recovered) of datafiles by reading datafile headers directly from the physical datafiles:
SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;
- Verify that the checkpoint_time / checkpoint_change# is in line with your intended UNTIL TIME/SCN. If not, recover the database further if you have more archived logs available.
- If FUZZY=YES for some datafiles, it means more recovery is required. If no more archived logs are available, identify such datafiles and determine if we can take them offline because we will loose the data in those datafiles. If the datafiles belong to SYSTEM or UNDO tablespace, we can / MUST not bring such datafile offline without proper analysis. Please consult Oracle Support for further actions.
SQL> select file#, substr(name, 1, 50), substr(tablespace_name, 1, 15), undo_opt_current_change# from v$datafile_header where fuzzy='YES' ;
Occasionally, if the tablespace name doesn’t indicate it to be UNDO tablespace, if we see non-zero value in column UNDO_OPT_CURRENT_CHANGE#, it indicates that the datafile contains undo segments.
To bring a datafile offline :
SQL> alter database datafile offline ;
Check 1 can be considered Passed when :
+ Verified that all the datafiles have been recovered upto the intended Point in time.
+ Fuzzy=NO for SYSTEM, UNDO and all intended datafiles. For datafiles with Fuzzy=YES, either recover them further or bring them OFFLINE if no further archived logs available.
Check 2:
Objective: Verify that the files with status=RECOVER are not OFFLINE unintentionally
SQL> select status, enabled, count(*) from v$datafile group by status, enabled ; STATUS ENABLED COUNT(*) ------- ---------- ---------- SYSTEM DISABLED 1 ONLINE READ WRITE 1114 RECOVER DISABLED 2
If the files are in RECOVER status, verify if they are OFFLINE :
SQL> select file#, substr(name, 1, 50), status, error, recover from v$datafile_header ;
If you want the data for these files to be accessible, then bring them ONLINE :
SQL> alter database datafile ONLINE ;
If a file remains offline at the time of OPEN RESETLOGS, the datafile may not be brought back online again in the same OPENED database.
Check 2 can be considered Passed when:
a) All the intended datafiles are not OFFLINE
Check 3:
Objective: Additional Fuzzy check (Absolute Fuzzy check)
Occasionally, it is possible to see Fuzzy=NO and same checkpoint_change# for all the intended datafiles ; still some of the datafiles might be fuzzy and OPEN RESETLOGS will return error, e.g.
SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ; FUZ STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*) --- ------- --------------- --- ------------------ -------------------- ---------- NO ONLINE 5375858580 31-OCT-2011 23:10:14 7 SQL> ALTER DATABASE OPEN RESETLOGS ; ORA-01194: file 14 needs more recovery to be consistent ORA-01110: data file 3: '/u01/app/oracle/oradata/TEST/undotbs02.dbf' Hence, we should perform additional fuzzy check known as Absolute Fuzzy Check:
SQL> select hxfil file#, substr(hxfnm, 1, 50) name, fhscn checkpoint_change#, fhafs Absolute_Fuzzy_SCN, max(fhafs) over () Min_PIT_SCN from x$kcvfh where fhafs!=0 ;
Note: Column Min_PIT_SCN will return same value even for multiple rows as we have applied ANALYTICAL “MAX() OVER ()” function on it.
Above query indicates that the recovery must be performed at least UNTIL SCN 5311524 to make datafiles consistent and ready to OPEN. Since the checkpoint_change# is smaller than Min_PIT_SCN, the datafiles will ask for more recovery.
Check 3 can be considered Passed when,
a) No rows selected from above query (i.e. Min_PIT_SCN is 0 (Zero) for all the datafiles)
b) Min_PIT_SCN is returned less than Checkpoint_Change#
Check 4: Archive Logs Required
Query the controlfile to find the latest archivelog required fore recovery. Lets say the backup completed at 31-AUG-2011 23:20:14:
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG
WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;
If the above query does not return any rows, it may be that the information has aged out of the controlfile – run the following query against v$log_history.
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> select a.THREAD#, a.SEQUENCE#, a.FIRST_TIME
from V$LOG_HISTORY a
where FIRST_TIME =
( SELECT MAX(b.FIRST_TIME)
FROM V$LOG_HISTORY b
WHERE b.FIRST_TIME < to_date('31-AUG-11 23:20:14', 'DD-MON-RR HH24:MI:SS') ) ; SQL>
The sequence# returned by the above query is the log sequence current at the time the backup ended - let say 530 thread 1.
For minimum recovery use: (Sequence# as returned +1 )
RMAN> RUN
{
SET UNTIL SEQUENCE 531 THREAD 1;
RECOVER DATABASE;
}
If this is a RAC implementation the use this SQL instead to query the controlfile:
SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG WHERE '31-AUG-11 23:20:14' BETWEEN FIRST_TIME AND NEXT_TIME;
For minimum recovery use the log sequence and thread that has the lowest NEXT_CHANGE# returned by the above query.
Check 4 can be considered PASSED when:
All archivelogs from the time of the backup to the end of the backup is available for use during recovery
Check 5 (After successful OPEN RESETLOGS) :
Monitor the alert.log for the time of OPEN RESETLOGS activities. You might see some messages like below during dictionary check:
Creating OFFLINE file ‘MISSING00004’ in the controlfile
if you find the file, try to rename them. If not, we can offline the datafile or drop associated tablespace:
SQL> select file#, status, enabled, substr(name, 1, 50) from v$datafile where name like '%MISSING%' ; FILE# STATUS ENABLED SUBSTR(NAME,1,50) -------- ------- ---------- -------------------------------------------------- 4 OFFLINE DISABLED /<path>/MISSING000 7 OFFLINE DISABLED /<path>/MISSING000 SQL> alter database datafile 4 online ; alter database datafile 4 online * ERROR at line 1: ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01111: name for data file 4 is unknown - rename to correct file ORA-01110: data file 4: '/<oracle_home path>/dbs/MISSING00004' SQL> alter database rename file 'MISSING00004' to '/<path>/users01.dbf' ; Database altered. SQL> alter database rename file 'MISSING00007' to '/<path>/users02.dbf' ; Database altered. SQL> select tablespace_name, status from dba_tablespaces where tablespace_name in (select tablespace_name from dba_data_files where file_id in (4, 7)) ; TABLESPACE_NAME STATUS ------------------------------ --------- USERS OFFLINE SQL> ALTER TABLESPACE USERS ONLINE ; Tablespace altered.
Hope this helps on how to check Database is consistent after incomplete recovery. Please do provide the feedback
Also Reads
how to find archive log sequence number in oracle
RMAN backup commands
RMAN list backup commands
How to recover database using RMAN