Working with Fast Start Failover (FSFO) in DATA GUARD

Working with Fast Start Failover (FSFO) in DATA GUARD

Fast start failover is a feature which allows a primary database to failover to one of the target standby terminal automatically in case any error conditions becomes true

Prerequisites of FSFO

# First we will check whether flashback is enabled or not on both primary and standby

# FastStartFailoverTarget property of broker should be set to a target standby terminal

# enable fast_start failover

# start the observer

__________________________________________________________________________

Lets check on primary database

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> @role

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

_____________________________________________

Lets check on standby database

SQL> @role

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

DGMGRL> connect sys@prod
Password:
Connected.
DGMGRL> enable database sby1;
Enabled.
DGMGRL>
DGMGRL>
DGMGRL> show configuration

Configuration - dgconf1

  Protection Mode: MaxPerformance
  Databases:
    prod - Primary database
    sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)

DGMGRL> enable fast_start failover condition '1578';
Succeeded.
DGMGRL>
DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    ORA-01578: ORACLE data block corrupted (file # %s, block # %s)


## We will simulate an insufficient privilege error and invoke fast start failover


DGMGRL> enable fast_start failover condition '1031';
Succeeded.
DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    ORA-01578: ORACLE data block corrupted (file # %s, block # %s)
    ORA-01031: insufficient privileges


DGMGRL> show database verbose prod

Database - prod

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    prod

  Properties:
    DGConnectIdentifier             = 'prod'
    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          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'node4.oracle.com'
    SidName                         = 'prod'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node4.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL>
DGMGRL> edit database prod set property 'FastStartFailoverTarget' = 'sby1';
Property "FastStartFailoverTarget" updated

DGMGRL> enable fast_start failover ;
Enabled.

Now we will simulate the failure and invoke error number 1031 (insufficient privileges)

SQL> conn scott/s
Connected.
SQL> alter system set log_checkpoints_to_alert=true;
alter system set log_checkpoints_to_alert=true
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> /
alter system set log_checkpoints_to_alert=true
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> /
alter system set log_checkpoints_to_alert=true
*
ERROR at line 1:
ORA-01031: insufficient privileges


DGMGRL>
DGMGRL> start observer
Observer started

14:08:19.81  Sunday, November 29, 2015
Initiating Fast-Start Failover to database "sby1"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "sby1"
14:08:21.11  Sunday, November 29, 2015


DGMGRL> connect sys@sby1
Password:
Connected.
DGMGRL> stop observer
Error: ORA-16636: fast-start failover target standby in error state, cannot stop observer

Failed.

DGMGRL> disable fast_start failover;
Error: ORA-16652: fast-start failover target standby database is disabled

Failed.
DGMGRL> disable fast_start failover force;
Disabled.
DGMGRL> stop observer
Done.
DGMGRL>
DGMGRL> reinstate database prod;
Reinstating database "prod", please wait...
Operation requires shutdown of instance "prod" on database "prod"
Shutting down instance "prod"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "prod" on database "prod"
Starting instance "prod"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "prod" ...
Reinstatement of database "prod" succeeded


DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                 YES
    Datafile Offline               YES

  Oracle Error Conditions:
    ORA-01578: ORACLE data block corrupted (file # %s, block # %s)
    ORA-01031: insufficient privileges


You can give all oracle error numbers for fast start failover conditions except ora-600 and ora-7445