FLASHBACK INSTEAD OF DELAYING APPLYING OF LOGS ON STANDBY

 If you have multiple standby sites, you may utilize the DELAY option in Data Guard to prevent physical/logical corruption or user errors in your primary database. 

For example If your primary database has only one standby database , a logical or physical corruption in the primary database will cause an immediate corruption in the standby database. 

 

To avoid such a pitfall, you can implement the ‘Delay’ option (introducing a delay of minutes or hours on the second standby database for applying archive log changes).

This will prevent the corruptions on the second standby database and allow recovery from a possible physical/logical corruption or user errors in the primary database.

You can issue the following command to accomplish this:

 

SQL> alter database recover managed standby database delay 60 disconnect;

Using Flashback Database in a Standby Database Configuration

However, in Oracle 10g, you can configure the standby database with Flashback Database to achieve the same benefit as the DELAY option.

Therefore, there is no need to implement a second standby database with the DELAY option.

Brief description:

 

On primary at 10 a.m a corruption occurs which is propagated immediately to the standby database.

 

At this point you can FLASHBACK the primary database to a time just before 10 a.m. and then flashback the standby as well so all data is recovered up until the point of corruption.

 

Worked Example:

 

PRIMARY MACHINE – DEVU014 –

 

It is necessary that FLASHBACK is  turned on in both PRIMARY and STANDBY databases to accomplish this.

 

[oracle@Hong ~]$ sqlplus /nolog

 

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 12 12:27:20 2011

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

SQL> conn / as sysdba

Connected.

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

YES

 

SQL> create table honcho (id number);

 

Table created.

 

SQL> insert into honcho values (1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> alter system switch logfile;

 

System altered.

 

Now on STANDBY MACHINE – TMPU008

 

 - Make sure logs are shipping and being applied-

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

YES

 

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

SQL> alter database open read only;

 

Database altered.

 

SQL> select * from honcho;

 

        ID

----------

         1

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

SQL> select open_mode from v$database;

 

OPEN_MODE

----------

MOUNTED

 

All good-

 

 

Now test the wrong update and how to flashback :

 

 

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

 

Session altered.

 

SQL> select sysdate from dual;

 

SYSDATE

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

2011-10-12 12:43:46

 

SQL> truncate table honcho;

 

Table truncated.

 

SQL> select * from honcho;

 

no rows selected

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> /

 

System altered.

 

Now make sure the wrong truncate has been propagated to the standby –

 

On standby machine :

 

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

SQL> alter database open read only;

 

Database altered.

 

SQL> select * from honcho;

 

no rows selected

 

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

Now that both databases are in sync you can use the flashback database option on the primary to get the database back to a time in the past.

 

 

 

ON PRIMARY  - FLASHBACK PRIMARY DATABASE :

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size             100664912 bytes

Database Buffers          180355072 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> flashback database to timestamp to_timestamp('2011-10-12 12:43:46','yyyy-mm-dd hh24:mi:ss');

 

Flashback complete.

 

SQL> alter database open resetlogs;

 

Database altered.

 

SQL> select * from honcho;

 

        ID

----------

         1

 

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/arch_prim

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

 

FLASHBACK OF STANDBY DATBASE

 

Flashing Back a Physical Standby Database to a Specific Point-in-Time

 

The following steps describe how to avoid re-creating a physical standby database after you issued the OPEN RESETLOGS statement on the primary database.

 

Step 1 Determine the SCN before the RESETLOGS operation occurred.

 

On the primary database, use the following query to obtain the value of the system change number (SCN) that is 2 SCNs before the RESETLOGS operation occurred on the primary database:

 

SQL> select to_char(resetlogs_change# -2 ) from v$database;

 

TO_CHAR(RESETLOGS_CHANGE#-2)

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

685935

 

Step 2 Obtain the current SCN on the standby database.

 

On the standby database, obtain the current SCN with the following query:

 

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            /u01/app/oracle/arch_stan

Oldest online log sequence     2

Next log sequence to archive   0

Current log sequence           241

SQL> select to_char(current_scn) from v$database;

 

TO_CHAR(CURRENT_SCN)

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

686105

 

Step 3 Determine if it is necessary to flash back the database.

 

If the value of CURRENT_SCN is larger than the value of resetlogs_change# - 2, issue the following statement to flash back the standby database.

 

SQL> FLASHBACK STANDBY DATABASE TO SCN resetlogs_change# -2;

 

SQL> flashback standby database to scn 685935;

 

Flashback complete.

 

This is the alert log of the STANDBY DATABASE after the command above is issued….

 

flashback standby database to scn 685935

Wed Oct 12 12:55:31 2011

Flashback Restore Start

Flashback Restore Complete

Flashback Media Recovery Start

Wed Oct 12 12:55:34 2011

Setting recovery target incarnation to 2

Wed Oct 12 12:55:34 2011

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_222_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_223_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_224_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_225_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_226_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_227_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_228_761824909.arc

Wed Oct 12 12:55:49 2011

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_229_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_230_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_231_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_232_761824909.arc

Wed Oct 12 12:56:03 2011

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_233_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_234_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_235_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_236_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_237_761824909.arc

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_238_761824909.arc

Wed Oct 12 12:56:14 2011

Flashback Media Recovery Log /u01/app/oracle/arch_stan/1_239_761824909.arc

Wed Oct 12 12:56:14 2011

Incomplete Recovery applied until change 685936

Flashback Media Recovery Complete

On standby :

 

Begin media recovery so logs being shipped from Primary are applied on standby.

 

recover managed standby database disconnect;

 

 

Now the dataguard configuration is back in place !

 

CHECK :

 

On PRIMARY

 

SQL> select * from honcho;

 

        ID

----------

         1

 

SQL>

 

On STANDBY

 

SQL> alter database recover managed standby database cancel;

alter database recover managed standby database cancel

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active

 

 

SQL> alter database open read only;

 

Database altered.

 

SQL> select * from honcho;

 

        ID

----------

         1

 

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

SQL>

 

Now on PRIMARY –

 

 

SQL> truncate table honcho;

 

Table truncated.

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> /

 

System altered.

 

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/arch_prim

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence           4

SQL> select count(*) from honcho;

 

  COUNT(*)

----------

         0

 

 

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

SQL> alter database open read only;

 

Database altered.

 

SQL> select count(*) from honcho;

 

  COUNT(*)

----------

         0

 

SQL>

 

Now restart  the log application process on STANDBY

 

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.