Friday 13 August 2021

DGMGRL : Failover and Reinstate from primary database to standby database using dgmgrl

DGMGRL : Failover and Reinstate from primary database to standby database using dgmgrl prompt 

Primary database : ZABBIX 

hostname: srv3.example.com

version :  12.2.0.1.0


Physical standby database : ZABBIXDR

hostname: srv4.example.com

version :  12.2.0.1.0

  • Enable flashback ON  and create restore point before failover on both primary and standby database 

  
 
[oracle@srv3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 13 16:40:44 2021

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> CREATE RESTORE POINT before_switchover GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>

  
         

  • Similarly Enable flashback ON  and create restore point before failover on standby database 
    • NOTE: stop mrp process on standby before enabling flashback on on DR  


      

    SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
    FROM
    (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
    (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
    WHERE
    ARCH.THREAD# = APPL.THREAD#
    ;  2    3    4    5    6    7

        Thread Last Sequence Received Last Sequence Applied Difference
    ---------- ---------------------- --------------------- ----------
             1                    348                   348          0

    SQL> select flashback_on from v$database;

    FLASHBACK_ON
    ------------------
    NO

    SQL> alter database flashback on;
    alter database flashback on
    *
    ERROR at line 1:
    ORA-01153: an incompatible media recovery is active


    SQL> alter database recover managed standby database cancel;

    Database altered.

    SQL> alter database flashback on;

    Database altered.

    SQL> alter database recover managed standby database disconnect from session;

    Database altered.

    SQL> select flashback_on from v$database;

    FLASHBACK_ON
    ------------------
    YES

    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> alter database recover managed standby database cancel;

    Database altered.

    SQL> CREATE RESTORE POINT before_switchover GUARANTEE FLASHBACK DATABASE;

    Restore point created.

    SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
      2  FROM
      3  (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
    (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
      4    5  WHERE
      6  ARCH.THREAD# = APPL.THREAD#
    ;  7

        Thread Last Sequence Received Last Sequence Applied Difference
    ---------- ---------------------- --------------------- ----------
             1                    348                   348          0

    SQL>


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

     

  • Shutdown abort Primary database for creating failover scenario.

    • SQL> shut abort; ORACLE instance shut down.


  • Connect to physical standby database using dgmrl and perform the failover.

    •   

    [oracle@srv4 ~]$ dgmgrl sys/system123@zabbixdr
    DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Aug 13 16:58:28 2021

    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

    Welcome to DGMGRL, type "help" for information.
    Connected to "ZABBIXDR"
    Connected as SYSDBA.

    DGMGRL> show configuration;

    Configuration - zabbix

      Protection Mode: MaxPerformance
      Members:
      zabbix   - Primary database
        Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

        zabbixdr - Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    ERROR   (status updated 0 seconds ago)

    DGMGRL> FAILOVER TO zabbixdr;
    Performing failover NOW, please wait...
    Failover succeeded, new primary is "zabbixdr"
    DGMGRL> SHOW CONFIGURATION;

    Configuration - zabbix

      Protection Mode: MaxPerformance
      Members:
      zabbixdr - Primary database
        zabbix   - Physical standby database (disabled)
          ORA-16661: the standby database needs to be reinstated

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS   (status updated 23 seconds ago)

    DGMGRL> SHOW DATABASE zabbix

    Database - zabbix

      Role:               PHYSICAL STANDBY
      Intended State:     APPLY-ON
      Transport Lag:      (unknown)
      Apply Lag:          (unknown)
      Average Apply Rate: (unknown)
      Real Time Query:    OFF
      Instance(s):
        ZABBIX

    Database Status:
    ORA-16661: the standby database needs to be reinstated

    DGMGRL> SHOW DATABASE zabbixdr

    Database - zabbixdr

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

    Database Status:
    SUCCESS

    DGMGRL>

  • Start old primary database in mount stage for preparing in reinstate.


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

    SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 13 17:02:43 2021

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

    Connected to an idle instance.

    SQL> STARTUP MOUNT;
    ORACLE instance started.

    Total System Global Area 3221225472 bytes
    Fixed Size                  8797928 bytes
    Variable Size             855638296 bytes
    Database Buffers         2348810240 bytes
    Redo Buffers                7979008 bytes
    Database mounted.
    SQL>

  • Before reinstate check the current status in dgmrl comand prompt.


    • [oracle@srv4 ~]$ dgmgrl sys/system123@zabbixdr
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Aug 13 16:58:28 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "ZABBIXDR"
Connected as SYSDBA.

DGMGRL> show configuration;

Configuration - zabbix

  Protection Mode: MaxPerformance
  Members:
  zabbix   - Primary database
    Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    zabbixdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 0 seconds ago)

DGMGRL> FAILOVER TO zabbixdr;
Performing failover NOW, please wait...
Failover succeeded, new primary is "zabbixdr"
DGMGRL> SHOW CONFIGURATION;

Configuration - zabbix

  Protection Mode: MaxPerformance
  Members:
  zabbixdr - Primary database
    zabbix   - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 23 seconds ago)

DGMGRL> SHOW DATABASE zabbix

Database - zabbix

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      (unknown)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    ZABBIX

Database Status:
ORA-16661: the standby database needs to be reinstated

DGMGRL> SHOW DATABASE zabbixdr

Database - zabbixdr

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

Database Status:
SUCCESS

DGMGRL> exit
[oracle@srv4 ~]$ dgmgrl sys/system123@zabbixdr
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Aug 13 17:03:34 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "ZABBIXDR"
Connected as SYSDBA.
DGMGRL> REINSTATE DATABASE ZABBIX;
Reinstating database "zabbix", please wait...
Reinstatement of database "zabbix" succeeded
DGMGRL>

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 50 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: 4.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)