Oracle DBA

Oracle 19c introduces a powerful new feature for Data Guard setups: the Automatic Flashback of Standby Database. This feature simplifies the process of flashing back a physical standby to a restore point from the primary by replicating restore points automatically.

Prerequisites

• Oracle 19c Data Guard configuration with Primary and Physical Standby databases.
• Flashback feature enabled on both Primary and Standby databases.

Steps

1. Check the Primary Database Configuration

Run the following command on the Primary Database to check the status, role, and flashback status.

SQL> select status, instance_name, database_role, protection_mode, flashback_on 
     from v$database, v$instance;

2. Create a Testing Table

To simulate changes, create a test table.

SQL> create table odbatab as select * from all_objects;
SQL> select count(*) from odbatab;

3. Set Restore Point on Primary Database

Create a guaranteed restore point on the primary.

SQL> create restore point odbatab_test guarantee flashback database;

Verify the restore point with the following command:

SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED 
     from v$restore_point;

4. Check Restore Point Replication on Standby

On the Standby Database, check if the restore point from the primary has replicated.

SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED 
     from v$restore_point;

Testing Flashback in MOUNT State

Step 1: Truncate Table on Primary

To simulate a flashback, truncate the test table on the primary.

SQL> truncate table odbatab;

Step 2: Shutdown and Startup Primary in MOUNT Mode

SQL> shut immediate;
SQL> startup mount;

Step 3: Flashback Primary Database

Flashback the primary database to the previously created restore point.

SQL> flashback database to restore point odbatab_test;

Then open the database with resetlogs.

SQL> alter database open resetlogs;

Step 4: Verify Data on Standby

Check the standby logs to confirm the automatic flashback process.

SQL> select count(*) from odbatab;

Testing Flashback in READ ONLY WITH APPLY State

Repeat the above steps but perform these actions with the Standby in READ ONLY WITH APPLY mode.