Guaranteed Restore point in Oracle with Oracle Data Guard

Please find the pointers related to this question :

Note: This is assuming the Flashback is turned OFF These parameters should be setup on Primary/standby both:

SQL> show parameter db_recovery

NAME TYPE

---

VALUE

db_recovery_file_dest string

db_recovery_file_dest_size big integer

SQL>select log_mode,flashback_on from v$database;

LOG_MODE FLASHBACK_ON

---

ARCHIVELOG NO

Note:Alter system switch logfile on Primary Database

Standby:

alter database recover managed standby database cancel;

create restore point <Restore point name> guarantee flashback database;

SQL> select NAME from v$restore_point;

NAME

<Restore Point Name>

Primary Database: create restore point <Restore Point Name> guarantee flashback database

SQL> select NAME from v$restore_point;

NAME

<Restore Point Name>

SQL> select FILE_TYPE,PERCENT_SPACE_USED from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED

---

CONTROL FILE 0 REDO LOG 0 ARCHIVED LOG 0 BACKUP PIECE 0 IMAGE COPY 0 FLASHBACK LOG 1.59 FOREIGN ARCHIVED LOG 0

7 rows selected.

start the replication on standby Database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

AFTER major maintenance work is done and all validation done

SQL> select FILE_TYPE,PERCENT_SPACE_USED from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED

---

CONTROL FILE 0 REDO LOG 0 ARCHIVED LOG 0 BACKUP PIECE 0 IMAGE COPY 0 FLASHBACK LOG 6.57 FOREIGN ARCHIVED LOG 0

SQL> drop restore point <Restore Point Name>;

Restore point dropped.

SQL> select NAME from v$restore_point;

no rows selected

SQL> select FILE_TYPE,PERCENT_SPACE_USED from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED

---

CONTROL FILE 0 REDO LOG 0 ARCHIVED LOG 0 BACKUP PIECE 0 IMAGE COPY 0 FLASHBACK LOG 0 FOREIGN ARCHIVED LOG 0

7 rows selected.

DGMGRL> connect sys/<system user password> Connected. DGMGRL> show configuration;

Configuration - DG_Config

Protection Mode: MaxPerformance Databases: <Primary DB_UNIQUE_NAME> - Primary database <STANDBY_DB_UNIQUE_NAME> - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status: SUCCESS

Hope this helps. Let me know if there any further question on this.