Email: service@parnassusdata.com 7 x 24 online support!

Home

DOWNLOAD DBRECOVER

    You are here

    • You are here:
      • Home > Blogs > PDSERVICE's blog > How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS

How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS

How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS

Posted by PDSERVICE on Feb 28, 2016 In

 

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline:  +86 13764045638 E-mail: service@parnassusdata.com

 

 

 

 

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.2 [Release 9.0.1 to 11.2]

Information in this document applies to any platform.

***Checked for relevance on 05-Nov-2014***

GOAL

What is the LEAST amount of recovery that has to be done before a database restored from a backup can be opened?

After performing a RESTORE / RECOVER, we need to perform a quick validation to ensure database is consistent and ready for OPEN RESETLOGS.

This proactive check helps to prevent several issues which may appear during or after OPEN RESETLOGS.

Through out this note we assume that you are restoring from a valid backup.

There can be more scenarios than discussed here. Please consult Oracle Support when in doubt.

 

SOLUTION

For cold/offline backups, no archivelogs/recovery is necessary. You can simply open the database with resetlogs.

However for HOT/ONLINE backups, ALL archivelogs from backup start to backup end must be applied before the database can be opened - this is the MINIMUM amount of recovery needed. 

To determine which log was current at the time the backup completed, note the COMPLETION time of the database backup - take this from the backup log. 

If this is an RMAN backup you can also query the RMAN metadata. Ensure that the environment variable NLS_DATE_FORMAT is set before invoking rman so that timestamps as well as date are returned:

For unix:    

% export NLS_DATE_FORMAT='dd-mon-rr hh24:mi:ss'
% rman target /

For windows: 

> set nls_date_format=dd-mon-rr:hh24:mi:ss                
> rman target /

To find your backup:


RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER '<date>';
 or 
RMAN> LIST BACKUP OF DATABASE COMPLETED AFTER 'sysdate -n';

Set <date> to limit the output to the backups that you are interested and note the completion time - for a multi-piece backup, note the completion time of the LAST backuppiece created.

 
Through out this note, when running SQL queries you should set NLS_DATE_FORMAT at the session level as follows:
 

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;

Check 1: Checkpoint Time and Fuzziness

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 (Point In Time up to 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 ;

FUZ STATUS  ERROR           REC CHECKPOINT_CHANGE# CHECKPOINT_TIME        COUNT(*)
--- ------- --------------- --- ------------------ -------------------- ----------
NO  ONLINE                                 5311260 31-AUG-2011 23:10:14          6
YES ONLINE                                 5311260 31-AUG-2011 23:10:14          1
 

a) 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.

b) If FUZZY=YES for some datafiles, it means more recovery is required. If these archived logs are lost, identify such datafiles and determine if we can take them offline.Warning: we will lose data in those datafiles if they are taken offline!

If the datafiles belong to SYSTEM or UNDO tablespace, we 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' ;

     FILE# SUBSTR(NAME,1,50)                                  SUBSTR(TABLESPA UNDO_OPT_CURRENT_CHANGE#
---------- -------------------------------------------------- --------------- ------------------------
         3 /u01/app/oracle/oradata/prod111/undotbs01.dbf      UNDOTBS1                         5117431

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 <file#> offline ;

Check 1 can be considered PASSED when :

a) Verified that all the datafiles are at the some checkpoint_time, and this is your intended Point in time.

b) 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 are available.
 

Check 2: Datafile Status

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          4
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 <file#> ONLINE ;
 

Check 2 can be considered Passed when:

All the intended datafiles are not OFFLINE 

 

Check 3: Absolute Fuzzy

Objective: Additional Fuzzy check (Absolute Fuzzy check) 

Occasionally, it is possible to see Fuzzy=NO and same checkpoint_change# for all the intended datafiles but OPEN RESETLOGS still fails

eg:

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                                 5311260 31-AUG-2011 23:10:14          7

SQL> ALTER DATABASE OPEN RESETLOGS ;

ORA-01194: file 4 needs more recovery to be consistent
ORA-01110: data file 3: '/u01/app/oracle/oradata/prod111/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 ;

FILE#      NAME                                               CHECKPOINT_CHANG ABSOLUTE_FUZZY_S     MIN_PIT_SCN
---------- -------------------------------------------------- ---------------- ---------------- ----------------
         4 /u01/app/oracle/oradata/prod111/undotbs01.dbf               5311260          5311524          5311524
         6 /u01/app/oracle/oradata/prod111/system01.dbf                5311260          5311379          5311524

 

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> -- V$ARCHIVED_LOG
SQL> --
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> -- V$LOG_HISTORY  view does not have a column NEXT_TIME
SQL> --
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: Post OPEN RESETLOGS Activities

During OPEN RESETLOGS, monitor the alert.log for additonal errors/messages. You might see some messages like below during dictionary check: 

Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary, <(============================== (1)
but not in the controlfile. Adding to controlfile.
Tablespace 'USERS' #4 found in data dictionary, 
but not in the controlfile. Adding to controlfile.
File #4 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00004' in the controlfile. <(==================== (2)
File #5 is online, but is part of an offline tablespace. <(==================== (3)
data file 5: '/u01/app/oracle/oradata/prod111/example01.dbf'
File #7 found in data dictionary but not in controlfile. <(==================== (2)
Creating OFFLINE file 'MISSING00007' in the controlfile.
File #8 is offline, but is part of an online tablespace. <(==================== (4)
data file 8: '/u01/app/oracle/oradata/prod111/mydata02.dbf'
File #9 is online, but is part of an offline tablespace. <(==================== (3)
data file 9: '/u01/app/oracle/oradata/prod111/example02.dbf'
Dictionary check complete
 

We discuss below the points highlighted :

(1) Check if the temp files exist. If not, add them as per your preference: 

SQL> select file#, name from v$tempfile ;

no rows selected

SQL> select file#, name from dba_temp_files ;

no rows selected

SQL> select tablespace_name, status, contents from dba_tablespaces where contents='TEMPORARY' ;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
TEMP                           ONLINE    TEMPORARY

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/temp01.dbf' size 10m ;

Tablespace altered.

SQL> select file#, substr(name, 1, 50), status, enabled from v$tempfile

FILE#    SUBSTR(NAME,1,50)                                  STATUS  ENABLED
-------- -------------------------------------------------- ------- ----------
       1 /u01/app/oracle/oradata/temp01.dbf                 ONLINE  READ WRITE

(2) It appears that the tablespace was brought offline using "ALTER TABLESPACE USERS OFFLINE" command. So, verify if the missing files really exist with original name. You may need to consult your pear DBAs, or refer alert.log / RMAN backup log or any such information which may provide clue about the actual file name. 

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   /u01/app/oracle/product/11.1.0/db_1/dbs/MISSING000
       7 OFFLINE DISABLED   /u01/app/oracle/product/11.1.0/db_1/dbs/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: '/u01/app/oracle/product/11.1.0/db_1/dbs/MISSING00004'

SQL> alter database rename file 'MISSING00004' to '/u01/app/oracle/oradata/prod111/users01.dbf' ;

Database altered.

SQL> alter database rename file 'MISSING00007' to '/u01/app/oracle/oradata/prod111/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.
 

Before proceeding, let's query the status for these files in alert.log:

SQL> select a.file#, substr(a.name, 1, 50) file_name, a.status file_status, a.error, substr(a.tablespace_name, 1, 10) tablespace_name, b.status tablespace_status from v$datafile_header a, dba_tablespaces b
where a.tablespace_name=b.tablespace_name /* and a.file# in (4, 5, 7, 8, 9) */ ;

FILE# FILE_NAME                                     FILE_STATUS ERROR           TABLESPA TABLESPACE_STATUS
----- --------------------------------------------- ----------- --------------- -------- ------------------
    1 /u01/app/oracle/oradata/prod111/system01.dbf  ONLINE                      SYSTEM   ONLINE
    2 /u01/app/oracle/oradata/prod111/sysaux01.dbf  ONLINE                      SYSAUX   ONLINE
    3 /u01/app/oracle/oradata/prod111/undotbs01.dbf ONLINE                      UNDOTBS1 ONLINE
    4 /u01/app/oracle/oradata/prod111/users01.dbf   OFFLINE     OFFLINE NORMAL  USERS    OFFLINE <(== related to (2) in alert.log excerpt above
    5 /u01/app/oracle/oradata/prod111/example01.dbf ONLINE                      EXAMPLE  OFFLINE <(== related to (3) in alert.log excerpt above 
    6 /u01/app/oracle/oradata/prod111/mydata01.dbf  ONLINE                      MYDATA   ONLINE 
    7 /u01/app/oracle/oradata/prod111/users02.dbf   OFFLINE     OFFLINE NORMAL  USERS    OFFLINE <(== related to (2) in alert.log excerpt above 
    8 /u01/app/oracle/oradata/prod111/mydata02.dbf  OFFLINE     WRONG RESETLOGS MYDATA   ONLINE <(=== related to (4) in alert.log excerpt above 
    9 /u01/app/oracle/oradata/prod111/example02.dbf ONLINE                      EXAMPLE  OFFLINE <(== related to (3) in alert.log excerpt above

9 rows selected.

So, we can attempt to correct the "ERROR" as displayed in above query depending on the availability of file / archived logs and other possible factors. 

Let's continue,

(3) It seems that tablespace was brought offline inconsistently ( ALTER TABLESPACE EXAMPLE OFFLINE IMMEDIATE ). If the archived log generated at that time has got applied, the file may be back online : 
 

SQL> alter tablespace example ONLINE ;

Tablespace altered.

 

(4) This tablespace MYDATA has 2 datafiles File# 6 & 8. It appears that File# 8 was brought offline ( using ALTER DATABASE DATAFILE 8 OFFLINE ) and it was OFFLINE before OPEN RESETLOGS. If the archived log generated at that time has got applied during recovery or all the archived logs are available for recovery since that time, the file may be back online :

SQL> alter database datafile 8 online ;
alter database datafile 8 online
*
ERROR at line 1:
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/u01/app/oracle/oradata/prod111/mydata02.dbf'

SQL> alter tablespace mydata online ;
alter tablespace mydata online
*
ERROR at line 1:
ORA-01190: control file or data file 8 is from before the last RESETLOGS
ORA-01110: data file 8: '/u01/app/oracle/oradata/prod111/mydata02.dbf'

SQL> recover datafile 8 ;
Media recovery complete.
SQL> alter database datafile 8 online ;

Database altered.

SQL> alter tablespace mydata online ;

Tablespace altered.
 

Please note that it is not always possible to recover and bring the file online which is failing with error " ORA-01190: control file or data file x is from before the last RESETLOGS".

(5) There can be a scenario where the tablespace was in READ ONLY mode before OPEN RESETLOGS. Please check below Article on that: