ORA-38784 ORA-38788,ORA-16004,ORA-01196,ORA-01110

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]
Information in this document applies to any platform.
Checked for relevance on 28-Sep-2012

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';