The Geek Diary

How to Create or Remove Restore Point on Oracle Standby database

by

This post is to provide steps to create or remove restore point on standby database.

Create restore point on standby database

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

ORA-38784: Cannot create restore point '[name]'.
ORA-01153: an incompatible media recovery is active
sqlplus / as sysdba

Stop the managed recovery and create restore point:

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

Remove a restore point on standby database

For release prior to 12.1, if standby database is running in Active Data Guard mode, close the database is required, otherwise the following error will happen: ORA-16000: database open for read-only access.

sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> drop restore point [name];
SQL> alter database open read only;   -- (if using Active Data Guard)
SQL> alter database recover managed standby database using current logfile disconnect;  -- (restart the managed recovery)

For 12.1 onward, only stop the managed recovery is required in order to drop the restore point. Above commands are applicable for all standby Protection mode (MaxPerformance/MaxAvailability/MaxProtection).

You May Also Like