SWITCHOVER USING DGMGRL BROKER IN ORACLE DATAGUARD

Switchover is an activity which acts as a role reversal with guaranteed protection to data.

Here primary database acts as a standby and standby database acts as a primary!!

We can perform switchover in situations like maintenance of primary database like patching,migration of a database from one server to another etc..

Switchover using broker is easy process compared to manual switchover. One command in broker and switchover is completed.

Prechecks:

Check the primary database status and parameters

DGMGRL> show database verbose dbx00

Database - dbx00

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

  Properties:
    DGConnectIdentifier             = 'dbx00'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbx00_DGMGRL)(INSTANCE_NAME=dbx00_1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

Check standby database status

DGMGRL> show database dbx01

Database - dbx01

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 11.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    dbx01

Database Status:
SUCCESS

Validate the database for switchover readiness of primary and standby database. Thanks to 12c feature. Below error on standby can be safely ignored for standby redologs as it is a reported bug.

DGMGRL> validate database dbx00

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    dbx00:  Off

DGMGRL> validate database dbx01

  Database Role:     Physical standby database
  Primary Database:  dbx00

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    dbx00:  Off
    dbx01:  On

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (dbx00)                 (dbx01)
    1         8                       3                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (dbx01)                 (dbx00)
    1         8                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on dbx00

Check the configuration at last

DGMGRL> show configuration

Configuration - dgb

  Protection Mode: MaxPerformance
  Members:
  dbx00 - Primary database
    dbx01 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 29 seconds ago)

While switchover:

Issue the below command for a hassle free switchover

DGMGRL> switchover to dbx01
Performing switchover NOW, please wait...
Operation requires a connection to instance "dbx01" on database "dbx01"
Connecting to instance "dbx01"...
Connected as SYSDBA.
New primary database "dbx01" is opening...
Oracle Clusterware is restarting database "dbx00" ...
Switchover succeeded, new primary is "dbx01"
DGMGRL> show configuration

Configuration - dgb

  Protection Mode: MaxPerformance
  Members:
  dbx01 - Primary database
    dbx00 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 111 seconds ago)

To switch back to old primary database, issue switchover command to primary database

DGMGRL> switchover to dbx00
Performing switchover NOW, please wait...
Operation requires a connection to instance "dbx00_1" on database "dbx00"
Connecting to instance "dbx00_1"...
Connected as SYSDBA.
New primary database "dbx00" is opening...
Operation requires start up of instance "dbx01" on database "dbx01"
Starting instance "dbx01"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.197)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbx01_DGMGRL)(INSTANCE_NAME=dbx01)(SERVER=DEDICATED)))
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 to finish switchover:
        start up and mount instance "dbx01" of database "dbx01"

For completeness of switchback, start the standby database in mount state and enable MRP if not enabled

kish@STDBY>startup mount;
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             989859744 bytes
Database Buffers          503316480 bytes
Redo Buffers               13848576 bytes
Database mounted.
kish@STDBY>alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Check the synchronization between primary and standby

kish@STDBY>select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING             108
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                109
RFS       IDLE                  0
RFS       IDLE                  0
MRP0      APPLYING_LOG        109

9 rows selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s