In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Database - 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
***Checked for relevance on 5-Aug-2014***

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