Oracle RAC database flash back to SCN in dataguard environment

A scenario where we need to do some changes on database but want to revert back to the previous state of the database and also fix the dataguard instance after reverting to previous state.

There are simple 13 steps to achieve this and those are:

1. Make sure that the FLASHBACK is ON under Primary and DR. Then get the SCN on the database from any of the PRIMARY nodes.
2. Turn off the log transport.
3. Shutdown Primary database and DR database.
4. Start up mount database on one of the nodes under primary side.
5. Log in to database and flashback database to SCN captured in step 1.
6. Shutdown database on Primary side and Start up database on all nodes on primary side.
7. SQLPLUS to primary and get the Resetlog SCN .
8. Turn the log transport on and wait for 5 mins for logs to be shipped to DR side.
9. Start the database on DR.
10. From primary side connect to dgmgrl and show configuration.
11. On DR side create a sqlplus session and flashback database to Resetlog SCN taken in step 7.
12. Shutdown DR and start is up.
13. You are done, have a cup of coffee :)

Blow are the actual commands that I have used in my environment to achieve this outcome:

Make sure that the FLASHBACK is ON under Primary and DR. Then get the SCN on the database from any of the PRIMARY nodes.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL>
SQL> select dbms_flashback.get_system_change_number begin_scn from dual;
            BEGIN_SCN
---------------------
             20778833
SQL>

Turn off the log transport.

[oracle@rac2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/******@RAC
Connected.
DGMGRL>
DGMGRL> show database 'RAC'    
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RAC1
    RAC2
Database Status:
SUCCESS
DGMGRL> show database verbose 'RAC'
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    RAC1
    RAC2
  Properties:
    DGConnectIdentifier             = 'RAC'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA/rac_stby, +DATA/rac'
    LogFileNameConvert              = '+FRA/rac_stby, +FRA/rac, +DATA/rac_stby, +DATA/rac'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value
Database Status:
SUCCESS
DGMGRL>
DGMGRL> edit database 'RAC' set state=TRANSPORT-OFF;
Succeeded.
DGMGRL> show database 'RAC'
Database - RAC
  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF
  Instance(s):
    RAC1
    RAC2
Database Status:
SUCCESS
DGMGRL>

Shutdown Primary database and DR database.

[oracle@rac1 ~]$ srvctl stop database -d RAC
[oracle@racdr1 ~]$ srvctl stop database -d RAC_STBY

Start up mount database on one of the nodes under primary side.

srvctl start database -d RAC -o mount -i RAC1

Log in to database and flashback database to SCN captured in step 1.

SQL> flashback database to scn 20778833;
Flashback complete.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>

Shutdown database on Primary side and Start up database on all nodes on primary side.

[oracle@rac1 ~]$ srvctl stop database -d RAC
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl start database -d RAC
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node rac1
Instance RAC2 is running on node rac2
[oracle@rac1 ~]$

SQLPLUS to primary and get the Resetlog SCN .

SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
         20778835
SQL>

Turn the log transport on and wait for 5 mins for logs to be shipped to DR side.

DGMGRL> edit database 'RAC' set state=TRANSPORT-ON;
Succeeded.
DGMGRL>

Start the database on DR.

[oracle@racdr1 ~]$ srvctl start database -d RAC_STBY
[oracle@racdr1 ~]$  srvctl status database -d RAC_STBY
Instance RAC_STBY1 is running on node racdr1
[oracle@racdr1 ~]$

From primary side connect to dgmgrl and show configuration.

DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC      - Primary database
      Error: ORA-16810: multiple errors or warnings detected for the database
    RAC_STBY - Physical standby database
      Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>

On DR side create a sqlplus session and flashback database to Resetlog SCN taken in step 7.

SQL> flashback database to SCN 20778835;
Flashback complete.
SQL>

Shutdown DR and start is up.

[oracle@racdr1 ~]$ srvctl stop database -d RAC_STBY
[oracle@racdr1 ~]$
[oracle@racdr1 ~]$ srvctl start database -d RAC_STBY
DGMGRL> show configuration
Configuration - racdg
  Protection Mode: MaxPerformance
  Databases:
    RAC      - Primary database
    RAC_STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> 

You are done have a break and get a cup of coffee :)