Tuesday, 10 August 2021

DGMGRL - Switchover primary to standby database using dataguard

 Switchover primary to standby database using dataguard

Primary database : ZABBIX 

hostname: srv3.example.com

version :  12.1.0.2.0

Standby  Database - ZABBIXDR 

hostname: srv4.example.com

version :  12.1.0.2.0

  • Connect primary Database  and check both primary and dr configuration status:

 [oracle@srv3 ~]$ dgmgrl sys/system123@zabbix
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - zabbix

  Protection Mode: MaxPerformance
  Members:
  zabbix   - Primary database
    zabbixdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 9 seconds ago)

DGMGRL> show database zabbix

Database - zabbix

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

Database Status:
SUCCESS

DGMGRL> show database zabbixdr

Database - zabbixdr

  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: 2.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    ZABBIXDR

Database Status:
SUCCESS

DGMGRL>

  • Create restore point before switchover on both primary and standby database. Used in case of failure. 

    • [oracle@srv3 ~]$ sqlplus / as sysdba

    SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 19 17:30:32 2021

    Copyright (c) 1982, 2014, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    SQL> set lines 200
    SQL> select name, instance_name,db_unique_name, open_mode, database_role, flashback_on  current_scn,to_char(standby_became_primary_scn) failover_scn from v$database,v$instance;

    NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN
    --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
    ZABBIX    ZABBIX           ZABBIX                         READ WRITE           PRIMARY          NO                 0

    SQL>

    SQL> CREATE RESTORE POINT before_switchover GUARANTEE FLASHBACK DATABASE;

    Restore point created.

    SQL>

  • Create restore point before switchover on  standby database. Used in case of failure. 

    •    
      [oracle@srv4 ~]$ sqlplus / as sysdba

    SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 19 17:31:49 2021

    Copyright (c) 1982, 2014, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    SQL> set lines 200
    SQL> select name, instance_name,db_unique_name, open_mode, database_role, flashback_on  current_scn,to_char(standby_became_primary_scn) failover_scn from v$database,v$instance;

    NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN
    --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
    ZABBIX    ZABBIXDR         ZABBIXDR                       MOUNTED              PHYSICAL STANDBY NO                 0

    SQL> CREATE RESTORE POINT before_switchover GUARANTEE FLASHBACK DATABASE;
    CREATE RESTORE POINT before_switchover GUARANTEE FLASHBACK DATABASE
    *
    ERROR at line 1:
    ORA-38784: Cannot create restore point 'BEFORE_SWITCHOVER'.
    ORA-01153: an incompatible media recovery is active
    SQL>

  • NOTE : Disable  redo apply on data guard om dgmrl console . Create restore point on standby and apply on again .

    • DGMGRL> EDIT DATABASE zabbixdr SET STATE=APPLY-OFF;
    Succeeded.
    DGMGRL>

    SQL>  CREATE RESTORE POINT before_switchover GUARANTEE FLASHBACK DATABASE;

    Restore point created.

    SQL>

    DGMGRL> EDIT DATABASE zabbixdr SET STATE=APPLY-ON;
    Succeeded.

    DGMGRL> show database zabbixdr

    Database - zabbixdr

      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: 2.00 KByte/s
      Real Time Query:    OFF
      Instance(s):
        ZABBIXDR

    Database Status:
    SUCCESS

    DGMGRL>

  • Login primary database and check status before switchover 

    •  
    SQL> SELECT CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

    CLIENT_P PROCESS    SEQUENCE# STATUS
    -------- --------- ---------- ------------
    ARCH     ARCH              54 OPENING
    ARCH     ARCH               0 CONNECTED
    ARCH     ARCH              53 CLOSING
    ARCH     ARCH              55 CLOSING
    LNS      LNS               56 WRITING

    SQL>
  • Login standby database and check status before switchover 

    •  
    SQL> SELECT STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

    STATUS
    ------------
    APPLYING_LOG

    SQL>
  • After checking the status. login to dgmgrl console and perform switchover.
    •     
 DGMGRL> SWITCHOVER TO zabbixdr;
    Performing switchover NOW, please wait...
    New primary database "zabbixdr" is opening...
    Operation requires start up of instance "ZABBIX" on database "zabbix"

    Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv3.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ZABBIX_DGMGRL)(INSTANCE_NAME=ZABBIX)(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 "ZABBIX" of database "zabbix"

    DGMGRL> DGMGRL>

  • Start new DR database srv3 server  in mount stage  

    •  
    SQL> startup mount;
    ORACLE instance started.

    Total System Global Area 1795162112 bytes
    Fixed Size                  2925456 bytes
    Variable Size             620760176 bytes
    Database Buffers         1157627904 bytes
    Redo Buffers               13848576 bytes
    Database mounted.
    SQL> SELECT CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

    CLIENT_P PROCESS    SEQUENCE# STATUS
    -------- --------- ---------- ------------
    ARCH     ARCH               0 CONNECTED
    ARCH     ARCH              59 CLOSING
    ARCH     ARCH               0 CONNECTED
    ARCH     ARCH               0 CONNECTED
    UNKNOWN  RFS                0 IDLE
    LGWR     RFS               60 IDLE
    UNKNOWN  RFS                0 IDLE
    N/A      MRP0              60 APPLYING_LOG

    8 rows selected.

    SQL>
  • Check the status of new primary database after switchover
    •  
    DGMGRL> show configuration;

    Configuration - zabbix

      Protection Mode: MaxPerformance
      Members:
      zabbixdr - Primary database
        zabbix   - Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS   (status updated 0 seconds ago)

    DGMGRL> SHOW DATABASE zabbixdr;

    Database - zabbixdr

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

    Database Status:
    SUCCESS

    DGMGRL> SHOW DATABASE zabbix;

    Database - zabbix

      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: 107.00 KByte/s
      Real Time Query:    OFF
      Instance(s):
        ZABBIX

    Database Status:
    SUCCESS

    DGMGRL>


    Posted by at
    Labels:

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)