FAST-START FAILOVER OBSERVER (FSFO) in Oracle 11g DATA GUARD


     • Primary database PROD 
     • Standby database PRODDR

1) Enable Data Guard Broker Start on the Primary and Standby databases

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

2) Next create from within the DGMGRL the configuration

[oracle@cab1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@PROD
Connected.

DGMGRL> create configuration CAB_PROD AS PRIMARY DATABASE IS PROD CONNECT IDENTIFIER IS PROD;
Configuration "cab_prod" created with primary database "prod"

Add the standby to the configuration and check it:

DGMGRL> ADD DATABASE PRODDR AS CONNECT IDENTIFIER IS PRODDR MAINTAINED AS PHYSICAL;
Database "proddr" added

DGMGRL> SHOW CONFIGURATION;
Configuration - cab_prod
  Protection Mode: MaxPerformance
  Databases:
    prod   - Primary database
    proddr - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

DGMGRL> SHOW DATABASE VERBOSE PRODDR;
Database - proddr
  Role:            PHYSICAL STANDBY
  Intended State:  OFFLINE
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Real Time Query: OFF
  Instance(s):
    PROD
  Properties:
    DGConnectIdentifier             = 'proddr'
    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           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/PROD/, /u01/app/oracle/oradata/PROD/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/PROD/, /u01/app/oracle/oradata/PROD/'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'PROD'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cab2.parthi.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRODDR_DGMGRL)(INSTANCE_NAME=PRODDR)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/arch/PROD/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'
Database Status:
DISABLED

DGMGRL> show database verbose PROD;
Database - prod
  Role:            PRIMARY
  Intended State:  OFFLINE
  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           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'PROD'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cab1.parthi.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD_DGMGRL)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/arch/PROD/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'
Database Status:
DISABLED

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;
Configuration - cab_prod
  Protection Mode: MaxPerformance
  Databases:
    prod   - Primary database
    proddr - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> enable database PRODDR;
Enabled.

DGMGRL> SHOW DATABASE VERBOSE PRODDR;
Database - proddr
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    PROD
  Properties:
    DGConnectIdentifier             = 'proddr'
    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           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/PROD/, /u01/app/oracle/oradata/PROD/'
    LogFileNameConvert              = '/u01/app/oracle/oradata/PROD/, /u01/app/oracle/oradata/PROD/'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'PROD'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cab2.parthi.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRODDR_DGMGRL)(INSTANCE_NAME=PRODDR)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/arch/PROD/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'
Database Status:
SUCCESS

3) Enabling Fast Start Failover and the Observer

Ensure standby redologs are configured on primary & standby databases.

on primary:

set lines 200
col member for a50
SELECT TYPE,MEMBER FROM V$LOGFILE;
TYPE    MEMBER
------- --------------------------------------------------
ONLINE  /u01/app/oracle/oradata/antony/redo03.log
ONLINE  /u01/app/oracle/oradata/antony/redo02.log
ONLINE  /u01/app/oracle/oradata/antony/redo01.log
STANDBY /u01/app/oracle/oradata/antony/redoby04.log
STANDBY /u01/app/oracle/oradata/antony/redoby05.log
STANDBY /u01/app/oracle/oradata/antony/redoby06.log

On standby:

set lines 200
col member for a50
SELECT TYPE,MEMBER FROM V$LOGFILE;
TYPE       MEMBER
---------- --------------------------------------------------
ONLINE     /u01/app/oracle/oradata/john/redo03.log
ONLINE     /u01/app/oracle/oradata/john/redo02.log
ONLINE     /u01/app/oracle/oradata/john/redo01.log
STANDBY    /u01/app/oracle/oradata/john/redoby04.log
STANDBY    /u01/app/oracle/oradata/john/redoby05.log
STANDBY    /u01/app/oracle/oradata/john/redoby06.log

4) Configure the LogXptMode Property is set to SYNC.

DGMGRL> EDIT DATABASE PROD SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

DGMGRL> EDIT DATABASE PRODDR SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

5) Configure the FastStartFailoverTarget property

DGMGRL> EDIT DATABASE PROD SET PROPERTY FastStartFailoverTarget='PRODDR';
Property "faststartfailovertarget" updated

DGMGRL> EDIT DATABASE PRODDR SET PROPERTY FastStartFailoverTarget='PROD';
Property "faststartfailovertarget" updated


6) Upgrade the protection mode to MAXAVAILABILITY, if necessary.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

7) To enable the Flashback Mode on both databases.

SQL> alter system set db_recovery_file_dest_size=2G;( depends upon the database size)
System altered.
SQL> alter system set db_recovery_file_dest='/arch/PROD';
System altered.
SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
System altered.

SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> conn / as sysdba

SQL> startup mount;

SQL> ALTER DATABASE FLASHBACK ON;

select FLASHBACK_ON from v$database;

alter database open;

8) Enable fast start failover

[oracle@cab1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@PROD;
Connected.

DGMGRL> show configuration verbose;
Configuration - cab_prod
  Protection Mode: MaxAvailability
  Databases:
    prod   - Primary database
    proddr - Physical standby database
  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> show database PRODDR;
Database - proddr
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    PROD
Database Status:
SUCCESS

DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.

9) start the observer

Start the observer from a third server on background.

Mkdir –p /u01/FSFO
Cd /u01/FSFO
vi startobserver
chmod 700 startobserver

-------------------------------------------------------
#!/bin/ksh
# startobserver
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export BASE_PATH=/u01/FSFO:/opt/CTEact/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/bin:/etc:/usr/local/maint/oracle:/usr/ccs/bin:/usr/openwin/bin:/usr/dt/bin:/usr/local/bin:.
export PATH=$ORACLE_HOME/bin:$BASE_PATH
dgmgrl << eof
connect sys/oracle@PROD
START OBSERVER;
eof
-------------------------------------------------------

[oracle@cab ~]$ nohup ./startobserver &
nohup: appending output to `nohup.out'
[1] 27392
10)    Verify the fast-start failover configuration.

[oracle@cab ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@PROD
Connected.

DGMGRL> show configuration verbose
Configuration - cab_prod
  Protection Mode: MaxAvailability
  Databases:
    prod   - Primary database
    proddr - (*) Physical standby database
  (*) Fast-Start Failover target
  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
Fast-Start Failover: ENABLED
  Threshold:        30 seconds
  Target:           proddr
  Observer:         cab3.parthi.com
  Lag Limit:        30 seconds (not in use)
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE
Configuration Status:
SUCCESS

DGMGRL> show database PROD
Database - prod
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    PROD
Database Status:
SUCCESS

DGMGRL> show database PRODDR
Database - proddr
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    PROD
Database Status:
SUCCESS

Primary and Standby database status should be success.

11) EXECUTE THE SWITCHOVER:

DGMGRL> SWITCHOVER TO PRODDR;

Performing switchover NOW, please wait...
New primary database "proddr" is opening...
Operation requires shutdown of instance "PROD" on database "prod"
Shutting down instance "PROD"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
    shut down instance "PROD" of database "prod"
    start up and mount instance "PROD" of database "prod"


DGMGRL> show configuration verbose
Configuration - cab_prod
  Protection Mode: MaxAvailability
  Databases:
    proddr - Primary database
    prod   - (*) Physical standby database
  (*) Fast-Start Failover target
  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
Fast-Start Failover: ENABLED
  Threshold:        30 seconds
  Target:           prod
  Observer:         cab3.parthi.com
  Lag Limit:        30 seconds (not in use)
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE
Configuration Status:
SUCCESS

DGMGRL> show database PROD
Database - prod
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    PROD
Database Status:
SUCCESS

12) In case your standby database went read-only mode accidently, you can prevent user’s connection on standby database by using this below trigger.

exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'PROD',network_name => 'PROD', failover_method => 'BASIC',failover_type => 'SELECT',failover_retries => 180,failover_delay => 1);
PL/SQL procedure successfully completed.

CREATE OR REPLACE TRIGGER manage_dgservice
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('PROD');
END IF;
END;
/
Trigger created.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

On tnsnames.ora define this entry

PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cab1.parthi.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = cab2.parthi.com)(PORT = 1522))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA=
(SERVICE_NAME=PROD)
)
)

On sqlnet.ora add this line
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 3

13) Manual Switchover and Switchback using sqlplus prompt :

Primary:

alter database commit to switchover to physical standby with session shutdown ;
shut immediate;
startup mount;

Standby:

alter database commit to switchover to primary ;
shut immediate;
startup;

Comments

  1. pcoraJanuary 23, 2014 at 12:22 AM

    Thanks for sharing.

    Is flashback mandatory with FSF?
    i.e. What if we are ok with rebuilding the standby after a failover and do not need a reinstantiation? In that case, can we have the FSF without enabling Flashback?

    All docs just say that enable Flashback, none say if that is mandatory or optional.

    Thanks

    ReplyDelete
  2. rahul wankhedeJanuary 28, 2014 at 9:03 AM

    what if Prim database crash, will FSFO failover standby to new primary , will Incarnation get changed ??

    ReplyDelete

Post a Comment

Popular posts from this blog

[INS-40718] Single Client Access Name (SCAN): could not be resolved. ( LDOMS & Zones)

Cause The name you provided as the SCAN could not be resolved using TCP/IP host name lookup. Action-Provide name to use for the SCAN for which the domain can be resolved Global Zone : ============= # svccfg svc:> select name-service/switch svc:/system/name-service/switch> setprop config/host = astring: "files dns" svc:/system/name-service/switch> select system/name-service/switch:default svc:/system/name-service/switch:default> refresh svc:/system/name-service/switch:default> validate svc:/system/name-service/switch:default> exit # svcadm refresh name-service/switch # svcadm restart /system/name-service-cache; then, I can resolve host using telnet/ping.
Read more

Bare Metal Restore Procedure for Compute Nodes on an Exadata Environment

Remove the Failed Database Server from the Cluster 1. Disable the listener that runs on the failed database server: [oragrid@surviving ~]$ srvctl disable listener -n dm01db01 [oragrid@surviving ~]$ srvctl stop listener -n dm01db01 PRCC-1017 : LISTENER was already stopped on dm01db01 PRCR-1005 : Resource ora.LISTENER.lsnr is already stopped [oragrid@surviving ~]$ 2. Delete the Inactive Instance using DBCA via ( Instance management ) and Oracle Home from the Oracle inventory: [oracle@surviving ~]$ dbca ( vncserver ) [oracle@surviving ~]$ cd $ORACLE_HOME/oui/bin [oracle@surviving bin]$ ./runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 "CLUSTER_NODES=dm01db02" Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB.   Actual 23984 MB    Passed The inventory pointer is located at /etc/oraInst.loc The inventory is located at /u01/app/oraInventory 'UpdateNodeList' was successful. [or...
Read more