ORA-38788: More standby database recovery is needed

Creating Restore Point Or Open Read Only on Physical Standby Database Fails With ORA-38784 ORA-38788 OR ORA-16004 ORA-01196 ORA-01110 [ID 845013.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.
Checked for relevance on 18-Apr-2011

Symptoms

Creating Restore point in Physical Standby fails with below errors

SQL> CREATE RESTORE POINT before_damage GUARANTEE FLASHBACK DATABASE;

ORA-38784: Cannot create restore point 'BEFORE_DAMAGE'.
ORA-38788: More standby database recovery is needed
Open physical standby in Read-only fails with below errors
SQL> alter database open; 

ORA-16004: backup database requires recovery
ORA-01196: file 4 is inconsistent due to a failed media recovery session
ORA-01110: data file 4: '/DB/SEN2G/system/temp01.dbf'
.

Cause

Bug 6150680

OR
Some of datafiles are in backup mode.

Solution

A) Check whether the patch for below bug is applied first.
Bug 6150680
Abstract: ORA-38788 / ORA-38784 from CREATE RESTORE POINT <rpname> GUARANTEE FLASHBACK DATABASE

If any datafile header's checkpointSCN are not equal to the database incomplete recovery SCN its not clean it will fail indicating one datafile is not clean (it needs recovery).Read-only datafiles will fail this check .

Flashback database is disabled
This is an physical standby database
There are read-only datafiles


B) If the issue still exits check for any datafiles that is in BACKUP mode. Check in primary using the below query.

SQL> select * from v$backup where status = 'ACTIVE';

You can end backup those tablespace in primary and switch logfiles so that it get applied in standby. Then try to create restore point.

In standby v$backup might not show exactly whether the datafile is in backup mode. Execute the below command in standby and upload the trace file generated in /udump folder  to support.

SQL> Alter Session Set Events 'immediate trace name file_hdrs level 10';

SQL> ALTER SESSION SET EVENTS 'immediate trace name controlf level 9';

@ File Number=6, Blksiz=8192, File Type=3 DATA

References

BUG:6150680 - CANNOT CREATE GUARANTEED RESTORE POINT IN PHYSICAL STANDBY DATABASE

Bug 6150680 - ORA-38788 / ORA-38784 from CREATE RESTORE POINT GUARANTEE FLASHBACK DATABASE [ID 6150680.8]
		Modified 31-AUG-2010     Type PATCH     Status PUBLISHED

Bug 6150680  ORA-38788 / ORA-38784 from CREATE RESTORE POINT <rpname> GUARANTEE FLASHBACK DATABASE

 This note gives a brief overview bug 6150680.
 The content was last updated on: 31-AUG-2010
 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 11.2
Versions confirmed as being affected	10.2.0.4 10.2.0.3
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

11.1.0.7 (Server Patch Set) 10.2.0.5 (Server Patch Set)

Symptoms:	Related To:
Error May Occur ORA-38788 / ORA-38784	Flashback Physical Standby Database / Dataguard

Description

CREATE RESTORE POINT <rpname> GUARANTEE FLASHBACK DATABASE
can fail consistently with ORA-38788 / ORA-38784 on a
physical standby database.
 
This can occur if:
 - flashback database is disabled:
    select FLASHBACK_ON from v$database;
   returns NO
 - this is on a standby database
    select controlfile_type from v$database;
   returns STANDBY
 - there are read-only datafiles, eg on the standby do this:
    alter database open read only;
    select file_name from dba_data_files
     where tablespace_name in
     (select tablespace_name from dba_tablespaces
      where status='READ ONLY');
 - for any given read-only datafile above, it is "clean"
   ie: it does not need any more recovery, the checkpointSCN
   in the datafile header matches the one in the datafile's
   controlfile entry (kccfe). To see these SCNs do this:
    alter session set events 'immediate trace name file_hdrs level 10';
 - CREATE RESTORE POINT <rpname> GUARANTEE FLASHBACK DATABASE
   fails with this pair of errors:
    ORA-38784: Cannot create restore point 'RPTEST1'.
    ORA-38788: More standby database recovery is needed
 
Workaround
  Do "alter database flashback on;" and then try doing the
    CREATE RESTORE POINT <rpname> GUARANTEE FLASHBACK DATABASE
  The command should succeed this time.