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