Thinking Out Loud

January 30, 2016

Not Another Standby Monitoring Script

Filed under: 11g,Dataguard — mdinh @ 3:55 am

I know what you are thinking. Not another standby monitoring script, right?

There’s plenty of them out there already and what makes this one any different?

You are probably correct and there’s probably nothing different about this.

Having to work with environments that are inconsistent such as manual standby, dataguard without broker, dataguard with broker,
I have been obsessed to create standby SQL which can accommodate any environment and can be run from primary or standby.

From PRIMARY: Recovery is IDLE

oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:05 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  IDLE                    NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       2        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10153          0

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

From STANDBY: Recovery is IDLE

oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawksan
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawksan:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:11 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawksan        READ ONLY            PHYSICAL STANDBY ENABLED  NOT ALLOWED          ENABLED   hawklas

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   ARCH              0
       1       32 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   RFS               0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN_READ-ONLY  IDLE                                    NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       1        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 882 thread 1;

*** v$dataguard_stats ***

NAME                      VALUE              UNIT
------------------------- ------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval
apply lag                 +00 01:14:48       day(2) to second(0) interval

*** gv$managed_standby ***

no rows selected

ARROW:(SYS@hawksan):PHYSICAL STANDBY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

From DG Broker: SET STATE=’APPLY-ON’

Note: Apply Lag is NOT really 1 hour.

Look at the time difference between SQL*Plus sessions.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:11 2016
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:28 2016
oracle@arrow:hawksan:/media/sf_working/dataguard
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       1 hour(s) 17 minutes 16 seconds (computed 1 second ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> EDIT DATABASE hawksan SET STATE='APPLY-ON';
Succeeded.
DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 2 seconds ago)
  Apply Lag:       1 hour(s) 17 minutes 47 seconds (computed 2 seconds ago)
  Apply Rate:      465.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> exit

From PRIMARY: Recovery is STILL IDLE

oracle@arrow:hawksan:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawksan] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:20 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  IDLE                    NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       2        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10402          0

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

From STANDBY: Recovery is MANAGED REAL TIME APPLY

oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawksan
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawksan:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:28 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawksan        READ ONLY WITH APPLY PHYSICAL STANDBY ENABLED  NOT ALLOWED          ENABLED   hawklas

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   ARCH              0
       1       32 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   RFS               0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN_READ-ONLY  MANAGED REAL TIME APPLY                 NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 IN-MEMORY      886 29-JAN-2016 17:50:04         1 .0333333   --- STANDBY LAG
       1        1 YES            885 29-JAN-2016 17:50:02

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

NAME                      VALUE              UNIT
------------------------- ------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval
apply lag                 +00 00:00:00       day(2) to second(0) interval

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    4463        1        1 MRP0      N/A      APPLYING_LOG       887    10409          0

ARROW:(SYS@hawksan):PHYSICAL STANDBY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawksan:/media/sf_working/dataguard
$

Let’s check again.

From PRIMARY: Recovery is MANAGED REAL TIME APPLY

oracle@arrow:hawksan:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawksan] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:09:14 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  MANAGED REAL TIME APPLY NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         1 .0333333   --- STANDBY LAG
       2        1 YES            885 29-JAN-2016 17:50:02

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10512          0

ARROW:(SYS@hawklas):PRIMARY>

SQL can be download from my Public Google Drive.

Look all the way to the right under Menu or search page for PublicGoogleDrive

Advertisements

1 Comment »

  1. Nice post and set of scripts. Thank you for sharing
    Foued

    Comment by fouedgray — February 23, 2016 @ 4:11 pm | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: