Flashback Primary Database In Physical Standby Configuration

1. Check open mode in Primary Database :

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> archive log list;
Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence         122
Next log sequence to archive       124
Current log sequence               124
 

SQL> alter system switch logfile;
SQL> alter system switch logfile;

2. Check Archive log status in Physical Standby Database :

SQL> alter database recover managed standby database cancel;
SQL> archive log list;
Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence         123
Next log sequence to archive       0
Current log sequence               124

3. Insert Rows in Primary Database :

SQL> conn sys/oracle@db11g as sysdba
SQL> archive log list;
Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence         124
Next log sequence to archive       126
Current log sequence               126
 

SQL> connect oracle/oracle@db11g
SQL> select * from oracle;

        NO
----------
         1
         3
         2

SQL> conn sys/oracle@db11g as sysdba
SQL> select current_scn from v$database;

CURRENT_SCN
------------
    1444475

SQL> connect oracle/oracle@db11g
SQL> insert into oracle values (4);
1 row created.

SQL> insert into oracle values (5);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from oracle;

        NO
----------
         1
         3
         4
         5
         2

SQL> conn sys/oracle@db11g as sysdba
Connected.
 

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> archive log list;
Database log mode                Archive Mode
Automatic archival               Enabled
Archive destination              USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence       126
Next log sequence to archive     128
Current log sequence             128
 

Note : Check archive log files are applied to physical standby database.

4. Shutdown the Primary Database :

SQL> shu immediate;
 

5. Startup Primary Database in Mount Stage :

SQL> startup mount;

6. Flashback Primary Database to specified SCN

SQL> FLASHBACK DATABASE TO SCN 1444475;

Flashback complete.
 

7. Open Primary Database with Reset logs.

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

SQL> connect oracle/oracle@db11g
Connected.
 

SQL> select * from oracle;

        NO
----------
         1
         3
         2

Note : Check the rows @Specified SCN

SQL> conn sys/oracle@db11g as sysdba
Connected.
 

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4

8. Cancel media recovery on the Physical Standby Database :

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    1445567

SQL> archive log list;

SQL> conn sys/oracle@db11gstd as sysdba
Connected.

SQL> alter database open read only;
Database altered.

SQL> conn oracle/oracle@db11gstd;
Connected.
 

SQL> select * from oracle;

        NO
----------
         1
         3
         4
         5
         2

SQL> conn sys/oracle@db11gstd as sysdba
Connected.
 

9. Flashback Physical Standby Database & Check the rows

SQL> FLASHBACK STANDBY DATABASE TO SCN 1444475;
Flashback complete.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

SQL> alter database open read only;
Database altered.

SQL> conn oracle/oracle@db11gstd;
Connected.
 

SQL> select * from oracle;

        NO
----------
         1
         3
         2

SQL> conn sys/oracle@db11gstd as sysdba
Connected.
 

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   0
Current log sequence           4

10. After FlashBack, Again insert rows in Primary Database and check Physical Standby Database Configuration

SQL> conn sys/oracle@db11g as sysdba
Connected.
 

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
 

SQL> sho user
USER is "SYS"
 

SQL> conn oracle/oracle@db11g
Connected.
 

SQL> select * from oracle;

        NO
----------
         1
         3
         2

SQL> insert into oracle values (4);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from oracle;

        NO
----------
         1
         3
         4
         2

SQL> conn sys/oracle@db11g as sysdba
Connected.
 

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
 

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6

11. Check the rows in Physical Standby Database:

SQL> conn sys/oracle@db11gstd as sysdba
Connected.
 

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   0
Current log sequence           4

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   0
Current log sequence           6
 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY            PHYSICAL STANDBY

SQL> conn oracle/oracle@db11gstd;
Connected.
 

SQL> select * from oracle;

        NO
----------
         1
         3
         4
         2