Monday, February 13, 2017

ORA-38784 + ORA-01153 + ORA-16000 : During restore point creation/Deletion on Standby Database

Create or Remove Restore Point on Active Dataguard Standby databases



It is required to stop the managed recovery on the standby, otherwise the following error will happen:

SQL> create restore point restore_point_2017 guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'restore_point_2017'.
ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database cancel;
SQL> create restore point ;



To drop the restore point on standby7 database which is running in Active Dataguard, database shutdown is required

We get below error when we try to drop the restore point in read only mode


SQL> DROP RESTORE POINT restore_point_2017;
DROP RESTORE POINT restore_point_2017
*
ERROR at line 1:
ORA-16000: database open for read-only access


Check the restore Point Name

SELECT NAME,SCN, TIME,DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM GV$RESTORE_POINT;

NAME                        SCN TIME                                     DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- ---------------------------------------- --------------------- --- ------------
restore_point_2017          1.4933E+13 17-Jan-17 11.33.52.000000000 PM                              2 YES   2.4934E+11


sqlplus / as sysdba
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup mount
ORACLE instance started.

Database mounted.
SQL> DROP RESTORE POINT restore_point_2017;

Restore point dropped.

SET LINESIZE 200
SET PAGESIZE 200
COL NAME FORMAT A20
COL TIME FORMAT A40
SET TRIMS ON
SELECT NAME,SCN, TIME,DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM GV$RESTORE_POINT;

no rows selected

SQL> alter database open read only;

Database altered.

Restart the Managed Recovery
SQL> alter database recover managed standby database using current logfile disconnect; 

No comments:

Post a Comment