Flashback Oracle DataGuard Database

May 6, 2020 6-minute read

Oracle • DataGuard • Flashback

Introduction

Sometimes, in an application that is using an Oracle DataGuard Primary database, things may go wrong and we will be requested to rollback the database to a previous point in time. If only few hour has passed, we may avoid an RMAN long recovery by using the FLASHBACK functionality.

We are going to test if we can flashback the Primary instance to a previous restore point and do the same with the Standby instance to avoid rebuilding it.

Environment

We are using a DataGuard environment with a Primary instance and one Physical Standby on Oracle 19c with one pluggable database.

Prerequisites

First we need to validate both Primary and Standby has flashback turned on:

[oracle@patodgprmy ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 6 13:52:36 2020
Version 19.3.0.0.0

SQL> select name, database_role, flashback_on from v$database;

NAME      DATABASE_ROLE    FLASHBACK_ON
--------- ---------------- ------------------
PRMYDB    PRIMARY          YES

[oracle@patodgstby trace]$ sqlplus / as sysdba

SQL> select name, database_role, flashback_on from v$database;

NAME      DATABASE_ROLE    FLASHBACK_ON
--------- ---------------- ------------------
PRMYDB    PHYSICAL STANDBY YES

Preparing Flashback

Create Restore Point in Primary

Before the activity that may destroy the data we want to setup a safe point to which we may flashback the database:

[oracle@patodgprmy ~]$ sqlplus / as sysdba

SQL> create restore point FLASH_POINT GUARANTEE FLASHBACK DATABASE;

Restore point created.

Validate Replication is in Sync

Last sequence in Primary instance:

[oracle@patodgprmy ~]$ sqlplus / as sysdba
SELECT ARCH.THREAD# Thread, ARCH.SEQUENCE# LastSequenceReceived, APPL.SEQUENCE# LastSequenceApplied, (ARCH.SEQUENCE# - APPL.SEQUENCE#) Difference FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    THREAD LASTSEQUENCERECEIVED LASTSEQUENCEAPPLIED DIFFERENCE
---------- -------------------- ------------------- ----------
         1                    7                   7          0

Last sequence in Standby instance:

[oracle@patodgstby ~]$ sqlplus / as sysdba
SELECT ARCH.THREAD# Thread, ARCH.SEQUENCE# LastSequenceReceived, APPL.SEQUENCE# LastSequenceApplied, (ARCH.SEQUENCE# - APPL.SEQUENCE#) Difference FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    THREAD LASTSEQUENCERECEIVED LASTSEQUENCEAPPLIED DIFFERENCE
---------- -------------------- ------------------- ----------
         1                    7                   7          0

OK our instances are in sync.

Destroy some Data

In this example our user will connect to the pluggable database and drop one table and delete the contents of a second table:

[oracle@patodgprmy ~]$ sqlplus /nolog

SQL> conn pato@taller;
Enter password:
Connected.
SQL> drop table t1 ;

Table dropped.

SQL> delete from t2 ;

5 rows deleted.

SQL> commit ;

Commit complete.

Validate Data is Lost in Standby

Disable replication and open the Standby in read only mode to validate:

[oracle@patodgstby ~]$ sqlplus / as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> ALTER PLUGGABLE DATABASE taller OPEN;

Pluggable database altered.

SQL> alter session set container = taller;

Session altered.

SQL> select count(*) from pato.t1 ;
select count(*) from pato.t1
                          *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select count(*) from pato.t2 ;

  COUNT(*)
----------
         0

Yes the table and data are gone also here. Let’s reenable replication and go back to Primary:

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610609888 bytes
Fixed Size                  9135328 bytes
Variable Size             956301312 bytes
Database Buffers          637534208 bytes
Redo Buffers                7639040 bytes
Database mounted.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Flashback DataGuard

Flashback Primary Database

Validate we have a restore point and get the SCN for later use in Standby:

[oracle@patodgprmy ~]$ sqlplus / as sysdba

SQL> select NAME, SCN from v$restore_point;

NAME                   SCN
--------------- ----------
FLASH_POINT        2990133

We need to shutdown the instance, then startup mount in order to be able to flashback database and then we will open the database with resetlogs.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610609888 bytes
Fixed Size                  9135328 bytes
Variable Size             989855744 bytes
Database Buffers          603979776 bytes
Redo Buffers                7639040 bytes
Database mounted.

SQL> flashback database to restore point FLASH_POINT;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Validate Data is Back on Primary Database

[oracle@patodgprmy ~]$ sqlplus /nolog

SQL> conn pato@taller;
Enter password:
Connected.
SQL> select count(*) from t1;

  COUNT(*)
----------
         2

SQL> select count(*) from t2;

  COUNT(*)
----------
         5

OK the table and data were recovered. Now let’s create a new table to later check if replication is working ok after we flashback the Standby:

SQL> create table t3 (i3 int, c3 varchar(20), d3 timestamp) ;

Table created.

SQL> insert into t3 values (1,'After flashback',current_timestamp);

1 row created.

SQL> commit ;

Commit complete.

Flashback Standby Database

As restore points are not replicated, neither we setup a restore point in the Standby, we are going to flashback to a particular SCN. From Primary restore point we know we need to go back to SCN 2990133, so we need to validate if that SCN is within flashback range:

[oracle@patodgstby ~]$ sqlplus / as sysdba

SQL> select OLDEST_FLASHBACK_SCN from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN
--------------------
             2989744

Our desired flashback point SCN is greater than the oldest, so we are within the range and can continue.

Stop the replication, flashback the Standby and reenable recovery:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> flashback standby database to SCN 2990133;

Flashback complete.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Validate Data is Recovered in Standby

Let’s give a time for the Standby to resync and then cancel recovery and open the instance in read only:

[oracle@patodgstby ~]$ sqlplus / as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> ALTER PLUGGABLE DATABASE taller OPEN;

Pluggable database altered.

then we validate:

SQL> alter session set container = taller;

Session altered.

SQL> select count(*) from pato.t1 ;

  COUNT(*)
----------
         2

SQL> select count(*) from pato.t2 ;

  COUNT(*)
----------
         5

SQL> select * from pato.t3 ;

        I3 C3                   D3
---------- -------------------- --------------------------------
         1 After flashback      06-MAY-20 02.25.41.879045 PM

As we can see the lost table and data are recovered, but also the new table generated after Primary flashback has been replicated as well.

Cleanup

Don’t forget to drop the restore point used for this test:

[oracle@patodgprmy ~]$ sqlplus / as sysdba

SQL> DROP RESTORE POINT FLASH_POINT ;

Restore point dropped.

Conclusion

As we can see it is possible to flashback the databases (Primary and Standby) belonging to a Data Guard environment. We just need to ensure both databases has flashback turned on and that our SCN point in time is within the available flashback range.