ORA-01194: When Opening Database After Restoring Backup

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

Problem Description:
====================
 
You have restored backup 

a) which you believe is a valid offline backup
OR
b) a valid online backup and then applied some archivelogs
yet you receive the following error when opening the database:

 ORA-01194: file <name> needs more recovery to be consistent 
     Cause: An incomplete recovery session was started, but an insufficient 
            number of redo logs were applied to make the file consistent.
            The named file was not closed cleanly when it was last opened by 
            the database.
            The most likely cause of this message is forgetting to restore the 
            file from a backup before doing incomplete recovery.
    Action: The file must be recovered to a time when it was not being updated.
            Either apply more redo logs until the file is consistent or restore 
            the file from an older backup and repeat recovery.
            For more information about recovery, see the index entry "recovery"
            in the <Oracle7 Server Administrator's Guide>.


Problem Explanation:
====================

A proper offline backup is with the database shutdown cleanly prior to the 
backup using either immediate or normal shutdown option. This flushes out all 
dirty blocks to the datafiles and make sure that they do not require any form 
of recovery when restored.

If you fail to do this for an offline backup, then the current online redolog will
be required to get the datafiles consistent. If you really did shutdown the database,
and backed up the online redologs, then you can restore them and recover using the
current redolog. If the database was not shutdown, then you cannot recover using this
backup.

The state of a datafile when the database is open is called Online Fuzzy, and is 
cleared when the database is shutdown cleanly.


A proper online backup requires the database (10g and above) or tablespaces to be in 
backup mode when the files are backed up. What happens is that Oracle marks the 
datafile header that the file is in hot backup mode, and then allows updates to the
file during the backup. The datafile will require media recovery from that time, and
at to either a) a time past the end backup in the archivelogs or b) to the current
redolog. 

The state of a datafile in hot backup mode is called Hot Backup In Progress fuzzy.
It is set when the datafile enters hot backup mode, and is cleared when it is taken
out of backup mode, or we reach the end-of-redo marker in the current redolog, ie by
doing a complete recovery.


There are two other fuzzy flags, but they are not discussed here.


Solution Description:
=====================

In order for any database to open resetlogs, three conditions have to be met:

1. All online, read-write datafiles must be checkpointed at the same time.
2. No datafiles can be fuzzy. That means that none of the fuzzy flags can be set.
3. No datafiles can be in hot backup mode

The following queries can be run with the database in mount to determine this:

1. SELECT DISTINCT (TO_CHAR (CHECKPOINT_CHANGE#)) FROM V$DATAFILE_HEADER;
This should return just one row for all online, read-write datafiles. 
As media recover is applied on datafiles, Oracle will advance the checkpoint of
the files, so that we don't have to start recovery over, but it will resume from
where it left off. We can use this to monitor the recovery process and see that 
the older checkpointed files are approaching the most recent file.

2. SELECT DISTINCT (FUZZY) FROM V$DATAFILE_HEADER;
This should return just one row: NO
Note that this doesn't tell you what fuzzy flag is set, it just tells you if any
fuzzy flag is set. 

3. SELECT DISTINCT (STATUS) FROM V$BACKUP;
This should return just one row: NOT ACTIVE
         

Only when the above conditions are met, are you allowed to open the database with
resetlogs. The column OPEN_RESETLOGS in V$DATABASE can also be used returning either
REQUIRED, NOT ALLOWED or ALLOWED.

Solution Explanation:
=====================

For an offline backup, the usual problem if you get ORA-1194 is that the database
was open, or not properly shutdown during the backup. This renders the backup 
unusable, as it will require access to the current redolog at the time, and all 
subsequent redologs in order to clear the online fuzzy flag. You can check the 
alert.log of the database around the time of the backup to verify how the database
was shutdown, and that it was shutdown during the entire time of the backup.

For online backup, you normally get this because you haven't applied enough 
archivelogs to bring the database past the time of the end backup marker. You can
check this also in the alert.log as begin/end backup commands are logged there. 
Make sure you apply at least enough archives to bring it past the log sequence that
was active at the time the backup ended. 

You can also get this error if your backup was not in backup mode. The scenario 
then will be as for offline backup, except that you will have the option of using
the archivelogs and the current redolog in order to do a complete recovery to clear
the fuzzy flag. 


Search Words:
=============

recover restore fuzzy backup resetlogs

Bug 19791146 - startup fails with ORA-1194 because 'end backup' on physical standby

Bug 19791146  startup fails with ORA-1194 because 'end backup' on physical standby

 This note gives a brief overview of bug 19791146. 

 The content was last updated on: 29-NOV-2015

 Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 12.2
Versions confirmed as being affected	12.1.0.2 (Server Patch Set) 11.2.0.4 11.2.0.3
Platforms affected	Generic (all / most platforms affected)

Fixed:

The fix for 19791146 is first included in

12.2 (Future Release)

Interim patches may be available for earlier versions - click here to check.

Description

On Physical STANDBY database, STARTUP of the database might fail with ORA-1194.
It can affect FAILOVER or SWITCHOVER operation.
 
The standby database alert log shows an "end backup" was succesfully executed on the standby, 
eg look for these alert log messages:
   Physical Standby Database mounted.
   alter ... end backup
   Completed: alter ... end backup
 
After the above "end backup", the standby database can no longer be opened in read only mode. For evidence of this, look for the following kind of sequence of messages in the alert log:
   alter database open read only
   Beginning Standby Crash Recovery.
   Serial Media Recovery started
   Media Recovery Log ...
   Media Recovery of Online Log ...
   Recovery of Online Redo Log: ...
   Standby Crash Recovery aborted due to error 1013.
   ORA-01013: user requested cancel of current operation
   Recovery interrupted!
   Completed Standby Crash Recovery.
   ORA-10458: standby database requires recovery
   ORA-01194: file <n> needs more recovery to be consistent
   ORA-01110: data file <n>: '<datafile_pathname>'
   ORA-10458 signalled during: alter database open read only
 
and also:
   "select fhfno, to_char(fhsta,'XXXXXXXX') from x$kcvfh;" on the standby shows bit 0x4 is set for the problematic datafiles, which means these datafiles are in an online fuzzy state
 
Workaround
 
To prevent the problem from happening in the first place, do NOT execute either of these commands on the physical standby:
 
  ALTER DATABASE ... END BACKUP;
  ALTER TABLESPACE ... END BACKUP;
 
If the problem has already happened then the workaround is to do the following:
 
1. on the standby, identify which datafiles are affected, this can
   be done via the following query on the mounted standby:
     select fhfno, to_char(fhsta,'XXXXXXXX') from x$kcvfh;
   bit 0x4 will be set for the problemtic datafiles, which means these
   datafiles are in an online fuzzy state
2. shutdown the standby
3. on the primary:
     a) put the identified set of datafile(s) into hot backup mode, eg
	  alter tablespace <ts> begin backup; or
	  alter database begin backup;
     b) for the identified set of datafile(s), copy them to the standby
	ie overwriting the original datafiles on the standby
     c) take the datafile(s) out of hot backup mode, eg:
	  alter tablespace <ts> end backup; or
	  alter database end backup;
4. on the standby, do the following:
     a) startup mount
     b) start media recovery and so allow the standby to get into sync with the primary
 
This fix raises an ORA-01649 for an 'end backup' on a physical standby which has datafiles in hotbackup mode. 
Prior to this fix, the 'end backup' would succeed and then all attempts to open the standby read only would fail with ORA-01194.
 
ORA-01649: operation not allowed with a backup control file

OERR: ORA-1194 "file %s needs more recovery to be consistent" Reference Note

PURPOSE

This is a brief reference note to show the meaning of database error "ORA-1194". It includes the error text, "Cause" and "Action" from message files for each database version from 9.2 onwards, along with search links and details of any database bug issues related to the error.

SCOPE

This note is intended for general audience as a starting point to check the meaning of "ORA-1194". It does not give detailed information about the error and does not give error message text for versions below 9.2 .

DETAILS

Error Text, Cause and Action from Message File/s for ORA-01194

Versions 9.2, 10.1, 10.2, 11.1, 11.2, 12.1

Error:  ORA-01194 file %s needs more recovery to be consistent 
---------------------------------------------------------------------------
Cause:  An incomplete recovery session was started, but an insufficient number 
	of logs were applied to make the file consistent. The reported file was 
	not closed cleanly when it was last opened by the database. It must be 
	recovered to a time when it was not being updated. The most likely 
	cause of this error is forgetting to restore the file from a backup 
	before doing incomplete recovery. 
Action: Either apply more logs until the file is consistent or restore the 
	file from an older backup and repeat recovery. 

Search Links for ORA-01194

The links below can be used to locate ORA-1194 in the documentation, and to search for documents that give more information about the error.

• Search MOS for "ORA-01194 Troubleshooting" 
   
• Search 12.1 documentation for ORA-01194
• Search 11.2 documentation for ORA-01194
• Search 11.1 documentation for ORA-01194
• Search 10.2 documentation for ORA-01194

Database Bugs Related to ORA-01194

Errors are usually due to usage, application or configuration issues but in some cases they may be caused by a bug issue. This section lists any database bugs that have been linked to error "ORA-1194" .

 

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button: 
       

 

 

NB	Prob	Bug	Fixed	Description
	I	19791146	12.2.0.0	startup fails with ORA-1194 because 'end backup' on physical standby
	I	22360720		RMAN duplicate on Standby fails with RMAN-06136 ORA-1194
+	-	1683613	8.1.7.2, 9.0.1.0	RMAN BACKUP or COPY datafile might not be usable for recovery - ORA-1194 / ORA-1547

Open Database failed - DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT ORA-1194 ORA-1547 ORA-1110

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later

Information in this document applies to any platform.

***Checked for relevance on 27-Feb-2015***

PURPOSE

The goal of this article is to assist DBAs who encounter the following errors at database open:

SCOPE

This article is meant for database administrators and backup and recovery specialists tasked with the restore and recovery of a database

DETAILS

Assuming that all datafiles were either successfully restored, and/or recovery was done with existing database datafiles, but failed to open with the below:

The ORA-01547 before database open indicates that Oracle sees the datafiles as inconsistent. This is resolved by applying more recovery, to make the datafiles consistent.

Checks to Perform

1.  Check the current status of the datafiles:

The goal is to have the above query return 1 row and fuzzy column value as NO.

 2.  Check the archivelog sequence numbers needed to recover the files.  Execute:

This query will show the smallest and largest sequence needed by the datafiles.    To make the datafiles consistent, you must apply all archivelog files within the above range of sequence numbers.  This can be one or many archivelog files.  

NOTE:  This query will not be valid in a RAC environment.  In RAC, find the necessary sequences from all threads using the smallest (and largest) checkpoint_change# returned by the query in #1 and execute:

select thread#, first_change#, next_change# from v$archived_log where <smallest/largest checkpoint_change#> between first_change# and next_change#;

once the above is executed with the smallest and largest checkpoint_change#, you have the sequence range needed to be applied from all threads of the RAC database.  See Note 243760.1 'RMAN: RAC Backup and Recovery using RMAN' for more details.

3.  Once further recovery is applied, confirm the datafiles are consistent with query in #1, above, until the goal is accomplished.  

4.  Once all datafiles are consistent and fuzzy=NO, the database can be opened and the ORA-01547 should no longer be returned.
 

Scenario 1: Current Controlfile is Used for Recovery

If the CURRENT CONTROLFILE is in use, and all online redo logs are available, then you can simply run RECOVER DATABASE. This will apply all archivelogs, and the current online redolog.

1) Ensure instance is MOUNTed and ALL datafiles  are ONLINE (SYSTEM datafiles will have the status of SYSTEM)

2) Recover and open the database:

Scenario 2: Backup Controlfile is Used for recovery

Please see steps below showing you an example getting the errors like ORA-1194, ORA-1547, ORA-1110,
and how to verify status of Controlfile, Datafiles, Logfiles, and finally recover and open database.

If you use a "BACKUP CONTROLFILE", or previously used a CANCEL based recover command, then we need to recover, and finally apply the online current redolog. 
   
Example:
 

The following query will show you the SCN to which we must at least recover to, to get all datafiles consistent.

ABSSCN = Absolute SCN