How to setup Data Guard Broker and enable Fast Start Failover in 11g? (Author: Ashwani kumar)

Environment: 11g,

initial primary database: orcl

standby database: ‘orcldr’

Here are the major steps:

1.Setup primary db and standby db
2.using spfile instead of pfile for both standby db and primary db.
3.set DG_BROKER_START = true

4.Set parameters of data guard broker configuration files

Primary:
$ sqlplus / as sysdba

alter system set dg_broker_start=true scope=spfile;

shutdown immediate

startup

On the server you want to setup the broker, in my case, it is the primary db server

alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dgbroker/orcl1db1prim.dat';

alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/dbhome_1/dgbroker/orcl2db1prim.dat';

Standby:
$ sqlplus / as sysdba
SQL>

alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dgbroker/orcl1db1std.dat';

alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/dbhome_1/dgbroker/orcl2db1std.dat';

alter system set dg_broker_start=false scope=spfile;

Note : This is to prevent the ORA-12514 error which can be observed on startup of the standby database after performing a switchover.

4) Create Configuration:

Connect to the DGMGRL and run the below statements to create and enable the DataGuard configuration. Verify that the DG Broker is configured correctly and it has all the databases and instances registered as expected.

PRIMARY:

– create configuration:
$ dgmgrl

DGMGRL> connect  sys/oracle_4U@orcl

DGMGRL> create configuration 'my_conf' as primary database is 'orcl' connect identifier is orcl ;

– Add standby database on Primary

DGMGRL> add database 'orcldr' as connect identifier is orcldr maintained as physical;

5) Enable Configuration:
The "DR_config" setup is disabled, which means it is not under the control of the Data Guard broker. When you finish configuring the databases into a broker configuration and setting any necessary database properties, you must enable the configuration to allow the Data Guard broker to manage it.

You can enable:
The entire configuration, including all of its databases
A standby database

DGMGRL> enable configuration

DGMGRL> show configuration

Configuration - my_conf

  Protection Mode: MaxPerformance

  Databases:

    orcl   - Primary database

    orcldr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> show database verbose orcl

Database - orcl

  Role:            PRIMARY

  Intended State:  TRANSPORT-ON

  Instance(s):

    orcl

  Properties:

    DGConnectIdentifier             = 'orcl'

    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               = '/u01/app/oracle/oradata/orcldr/, /u01/app/oracle/oradata/orcl/'

    LogFileNameConvert              = '/u01/app/oracle/oradata/orcldr/, /u01/app/oracle/oradata/orcl/'

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'db12c-prod.example.com'

    SidName                         = 'orcl'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db12c-prod.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL> show database verbose orcldr

Database - orcldr

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    orcldr

  Properties:

    DGConnectIdentifier             = 'orcldr'

    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          = '8'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = '/u01/app/oracle/oradata/orcl, /u01/app/oracle/oradata/orcldr'

    LogFileNameConvert              = '/u01/app/oracle/oradata/orcl, /u01/app/oracle/oradata/orcldr'

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'db12c-dr.example.com'

    SidName                         = 'orcldr'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db12c-dr.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcldr_DGMGRL)(INSTANCE_NAME=orcldr)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

Verification in STANDBY side:

DGMGRL> connect sys/oracle_4U@orcldr

Connected.

DGMGRL> show configuration;

Configuration - my_conf

  Protection Mode: MaxPerformance

  Databases:

    orcl   - Primary database

    orcldr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

6.Switchover
DGMGRL> switchover to orcldr
Performing switchover NOW, please wait…
New primary database “orcldr” is opening…
Operation requires shutdown of instance “upup” on database “upup1”
Shutting down instance “upup”…
ORA-01031: insufficient privileges

You are no longer connected to ORACLE
Please connect again.
Unable to shut down instance “upup”
You must shut down instance “upup” manually
Operation requires startup of instance “upup” on database “upup1”
You must start instance “upup” manually
Switchover succeeded, new primary is “upup”

Now orcl is primary and in open status, I need to shutdown orcldr and startup mount
conn sys/oracle_4U@orcldr as sysdba
SQL> shutdown immediate

7.Check DGMGRL status again
DGMGRL> show configuration

Configuration
  Name:                my_dg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    upup  – Primary database
    upup1 – Physical standby database

Fast-Start Failover: DISABLED

Current status for “my_dg”:
SUCCESS

8.Setup Fast failover
–Enable flashback on both db
SQL> startup mount
SQL> alter system set db_recovery_file_dest_size=1G;
SQL> alter system set db_recovery_file_dest=’/u01/app/oracle/oradata’;

–set fast failover target
DGMGRL> EDIT DATABASE orcl SET PROPERTY FastStartFailoverTarget =orcldr;
DGMGRL> EDIT DATABASE orcldr SET PROPERTY FastStartFailoverTarget = orcl;

–set log mode and protection mode
DGMGRL> EDIT DATABASE  'orcl' SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT DATABASE  'orcldr' SET PROPERTY LogXptMode=SYNC;

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 10;

–enable fash failover
DGMGRL> enable fast_start failover
DGMGRL> connect sys/oracle_4U@orcldr
Connected.
DGMGRL> start observer
Observer started
…<to be continue>…

–connect another session
dgmgrl
DGMGRL> show fast_start failover

Fast-Start Failover: ENABLED
  Threshold:           10 seconds
  Target:              upup1
  Observer:            rac1.baby.com
  Lag Limit:           30 seconds (not in use)
  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)

Make sure standby db is ready to fast failover
SQL>  select  FS_FAILOVER_STATUS,FS_FAILOVER_OBSERVER_PRESENT   from v$database;

FS_FAILOVER_STATUS     FS_FAIL
———————- ——-
SYNCHRONIZED           YES

conn primary db, simulate primary failure
SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

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

ORCL     READ WRITE           PRIMARY

SQL> shutdown abort

ORACLE instance shut down.

DGMGRL> connect sys/oracle_4U@orcldr

Connected.

DGMGRL> start observer

Observer started

21:54:33.64  Wednesday, November 02, 2016

Initiating Fast-Start Failover to database "orcldr"...

Performing failover NOW, please wait...

Failover succeeded, new primary is "orcldr"

21:54:50.43  Wednesday, November 02, 2016

22:00:18.75  Wednesday, November 02, 2016

Initiating reinstatement for database "orcl"...

Reinstating database "orcl", please wait...

Operation requires shutdown of instance "orcl" on database "orcl"

Shutting down instance "orcl"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "orcl" on database "orcl"

Starting instance "orcl"...

Unable to connect to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the REINSTATE command:

        start up and mount instance "orcl" of database "orcl"

22:00:53.61  Wednesday, November 02, 2016

–as indicated, start orcldr
SQL> startup mount

DGMGRL> connect sys/oracle_4U@orcldr

DGMGRL> reinstate database orcl

---------------------------------------------------END-----------------------------------------------------------------------

DGMGRL> show database verbose orcl

Database - orcl

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    orcl

  Properties:

    DGConnectIdentifier             = 'orcl'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'sync'

    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               = 'C:\app\ashwanik\oradata\orcldr\, C:\app\ashwanik\oradata\orcl\'

    LogFileNameConvert              = 'C:\app\ashwanik\oradata\orcldr\, C:\app\ashwanik\oradata\orcl\'

    FastStartFailoverTarget         = 'orcldr'

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = '2242ASHWAN1525D'

    SidName                         = 'orcl'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2242ASHWAN1525D)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'ARC%S_%R.%T'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS