Using Flashback in a Data Guard Environment

Using Flashback in a Data Guard Environment

If Logical Mistakes happen, we can address them with the Flashback techniques, introduced in Oracle Database 10g already, even if in an Data Guard Environment. In case of “Flashback Table To Timestamp” or “Flashback Table To Before Drop”, there is nothing special to take into account regarding the Standby Database. It will simply replicate these actions accordingly.

If we do “Flashback Database” instead, that needs a special treatment of the Standby Database. This posting is designed to show you how to do that:

DGMGRL> show configuration

Configuration

 Name:                mycf

 Enabled:             YES

 Protection Mode:     MaxAvailability

 Databases:

 prima - Primary database

 physt - Physical standby database

Fast-Start Failover: DISABLED

Current status for "mycf":

SUCCESS

This is an 11g Database, but the shown technique should work the same with 10g also. Prima & Physt are both creating Flashback Logs:

SQL> connect sys/oracle@prima as sysdba

Connected.

SQL> select database_role,flashback_on from v$database;

DATABASE_ROLE    FLASHBACK_ON

---------------- ------------------

PRIMARY          YES

SQL> connect sys/oracle@physt as sysdba

Connected.

SQL> select database_role,flashback_on from v$database;

DATABASE_ROLE    FLASHBACK_ON

---------------- ------------------

PHYSICAL STANDBY YES

I will now introduce the “Logical Mistake” on the Primary Database:

SQL> select * from scott.dept;

 DEPTNO DNAME          LOC

---------- -------------- -------------

 10 ACCOUNTING     NEW YORK

 20 RESEARCH       DALLAS

 30 SALES          CHICAGO

 40 OPERATIONS     BOSTON

SQL> drop user scott cascade;

User dropped.

The Redo Protocol gets transmitted with SYNC to the Standby Database and is applied there with Real-Time Apply. In other words: The Logical Mistake has already reached the Standby Database. We could have configured a Delay in the Apply there to address such scenarios. But that is somewhat “old fashioned”; the modern way is to go with flashback. The background behind that is, that in case of a Disaster, hitting the Primary Site, a Delay would cause a longer Failover time. I will now flashback the Primary to get back Scott:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  313860096 bytes

Fixed Size                  1299624 bytes

Variable Size             230689624 bytes

Database Buffers           75497472 bytes

Redo Buffers                6373376 bytes

Database mounted.

SQL> flashback database to timestamp systimestamp - interval '15' minute;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.dept;

 DEPTNO DNAME          LOC

---------- -------------- -------------

 10 ACCOUNTING     NEW YORK

 20 RESEARCH       DALLAS

 30 SALES          CHICAGO

 40 OPERATIONS     BOSTON

There he is again! Until now, that was not different from a Flashback Database Operation without Data Guard. But now my Standby Database is no longer able to do Redo Apply, because it is “in the future of the Primary Database”. We are in step 2) of the below picture now that I added to illustrate the situation.

Now I need to put the Standby it into a time, shortly before the present time of the Primary, in order to restart the Redo Apply successfully:

DGMGRL> show configuration

Configuration

 Name:                mycf

 Enabled:             YES

 Protection Mode:     MaxAvailability

 Databases:

 prima - Primary database

 physt - Physical standby database

Fast-Start Failover: DISABLED

Current status for "mycf":

Warning: ORA-16607: one or more databases have failed

DGMGRL> show database physt statusreport

STATUS REPORT

 INSTANCE_NAME   SEVERITY ERROR_TEXT

 *      ERROR ORA-16700: the standby database has diverged                         from the primary database

 *      ERROR ORA-16766: Redo Apply is stopped

Please notice that the show statusreport clause is a new feature of 11g. In 10g, you need to look into the Broker Logfile to retrieve that problem.

SQL> connect sys/oracle@prima as sysdbaConnected.SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#

-----------------

 294223

SQL> connect sys/oracle@physt as sysdba

Connected.

SQL> flashback database to scn 294221;

Flashback complete.

I subtracted 2 from the Resetlogs Change No. above to make sure that we get the Standby close before the present time of the Primary. Now we need to restart the Redo Apply again:

DGMGRL> show configuration

Configuration

 Name:                mycf

 Enabled:             YES

 Protection Mode:     MaxAvailability

 Databases:

 prima - Primary database

 physt - Physical standby database

Fast-Start Failover: DISABLED

Current status for "mycf":

Warning: ORA-16607: one or more databases have failed

DGMGRL> edit database physt set state=apply-on;

Succeeded.

DGMGRL> show configuration

Configuration

 Name:                mycf

 Enabled:             YES

 Protection Mode:     MaxAvailability

 Databases:

 prima - Primary database

 physt - Physical standby database

Fast-Start Failover: DISABLED

Current status for "mycf":

SUCCESS

That was it!
Conclusion: Flashback is the natural counterpart of Real-Time Apply. You can address Logical Mistakes easily with it and you do not need to Delay the actualization of the Standby Database.

11.2.0.4 on oraclelinux 5.8

Primary 上做flashback database后，standby 上根据情况也需要做flashback

 

1 primary上做flashback

select current_scn from $database;

current_scn

84593407611

Standby上查看current_scn

select current_scn from $database;

current_scn

84593406833

在primary上flashback至scn 84593407000

 

Flashback database to scn 84593407000

Alter database open resetlogs;

 

2 standby数据库上的动作

如果flashback的scn是后于standby的current_scn，那么standby不需要特别做动作，直接开启MRP程序即可。

查看primaryflashback后的resetlogs_change#

select resetlogs_change# from v$database;

84593407002

在这里如果standby的current_scn<=primary 的resetlogs_change#-2那么就不用再在standby上做flahsback database了

在standby上开启mrp程序

Alter database recover managed standby databasedisconnect;

 

在alert log上也看到，standby察觉到primary incarnation已经改变了

 

Managed Standby Recovery not using RealTime Apply

2016-07-16 10:21:37.858000 +08:00

Media Recovery start incarnationdepth : 1, target inc# : 7, irscn : 84593407001

Waiting for all non-current ORLs to bearchived...

All non-current ORLs have been archived.

Media Recovery Log/u01/app/oracle/oradata/sales/1_9_917282484.dbf

2016-07-16 10:21:38.931000 +08:00

Completed: alter database recover managedstandby database disconnect