DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, June 25, 2018

DG Broker Configuration for Physical Standby & Switch OVER


It is assumed that Primary and Standby is already configured and both are sync where archive transfer and apply is working fine.

1- On both Primary and standby databases, set the DG_BROKER_START=TRUE to start the DG Broker process:

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

2- Edit the Listener.ora file on both nodes and add DGMGRL static listener entry to avoid ORA-12154 which can be observed on startup of the standby database after performing a switchover.

Ensure that the GLOBAL_NAME is set to the db_unique_name_dgmgrl

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
        (GLOBAL_DBNAME = HPFMDM_dgmgrl)
        (ORACLE_HOME = /u01/app/oracle/HPFMDMuct/11.2.0/dbhome_1)
        (SID_NAME = HPFMDM)
    )
   )
  
3- In TNSNAMES.ORA on both sides, use already added to_primary & to_standby tns entries

Create Configuration:

4- Connect DGMGRL on Primary and connect using SYS/****

[oracle@HPFMDMdb agent12c]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit HPFMDMuction

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL> connect sys/********
Connected.

DGMGRL> create configuration 'HPFMDM' as primary database is 'HPFMDM' connect identifier is 'to_primary';
Configuration "HPFMDM" created with primary database "HPFMDM"
DGMGRL>

5- ADD the STANDBY database to the configuration:

DGMGRL> ADD DATABASE 'DRHPFDMDDB' as CONNECT IDENTIFIER IS 'to_standby';
Database "DRHPFDMDDB" added
DGMGRL>
DGMGRL>

6- Check the configuration

DGMGRL> show configuration

Configuration - HPFMDM

  Protection Mode: MaxPerformance
  Databases:
    HPFMDM   - Primary database
    DRHPFDMDDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL>

7- Enable the configuration

DGMGRL> enable configuration
Enabled.

DGMGRL> show configuration

Configuration - HPFMDM

  Protection Mode: MaxPerformance
  Databases:
    HPFMDM   - Primary database
    DRHPFDMDDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

8- View the Primary and standby database properties

For Primary:

DGMGRL> show database 'HPFMDM'

Database - HPFMDM

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

Database Status:
SUCCESS

DGMGRL>

For Standby:

DGMGRL> show database 'DRHPFDMDDB'

Database - DRHPFDMDDB

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      342.00 KByte/s
  Real Time Query: OFF
  Instance(s):
    DRHPFDMDDB

Database Status:
SUCCESS

DGMGRL>
DGMGRL> show database verbose  'HPFMDM'

Database - HPFMDM

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

  Properties:
    DGConnectIdentifier             = 'to_primary'
    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               = '+DATA/DRHPFDMDDB, +DATA/HPFMDM'
    LogFileNameConvert              = '+DATA/DRHPFDMDDB, +DATA/HPFMDM'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'HPFMDM'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.160)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=HPFMDM_dgmgrl.HBMSU.AC.AE)(INSTANCE_NAME=HPFMDM)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '+REC/HPFMDM/archivelog/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL>

***** LogXptMode='ASYNC' means DataGuard is in MaxPerformance mode. If it required to be changed from MaxPerformance to MaxAvailability Mode, then LogXptMode must be changed to 'SYNC'  using:

DGMGRL> edit database standby set property logxptmode=SYNC;
Property "logxptmode" updated
DGMGRL>


9- Monitoring the DG Broker configuration

DGMGRL> show database 'HPFMDM' statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database 'DRHPFDMDDB' statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database 'HPFMDM' logxptstatus
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS
                HPFMDM               DRHPFDMDDB                     

DGMGRL>
DGMGRL> show database 'HPFMDM' InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE


10- Performing a switchover

Note: In this case, currently the Primary Database is HPFMDM and the Standby database is DRHPFDMDDB.

DGMGRL> switchover to 'DRHPFDMDDB'
Performing switchover NOW, please wait...
New primary database "DRHPFDMDDB" is opening...
Operation requires shutdown of instance "HPFMDM" on database "HPFMDM"
Shutting down instance "HPFMDM"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "HPFMDM" on database "HPFMDM"
Starting instance "HPFMDM"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "DRHPFDMDDB"

DGMGRL> SHOW CONFIGURATION;

Configuration
  Name:                HPFMDM
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    DRHPFDMDDB - Primary database
    HPFMDM - Physical standby database

Fast-Start Failover: DISABLED

Current status for "HPFMDM":
SUCCESS

No comments: