Written By

Introduction

 This article is focused on how to do troubleshoot the Logical standby database and monitoring the logical standby database apply, In detail we will go through how the SQL Apply process actually works and walk through with various views to monitor logical standby database.

Logical Standby Database

To be frank, in real world very few customers will consider using logical standby and many of many use Physical standby database when it comes to High availability solutions. Indeed for Logical standby we should not use term as "High availability Solutions" like Physical standby. Logical standby introduced with read and write capability so that DDL, DML on Logical standby and also recovery will be in progress from primary database. You must know that logical standby and primary databases Logical standby is no more considered as mirror to the primary database. When it comes with Physical standby database it is considered and meant for only Recovery/MRP and with the Active Data Guard feature of 11g you can offload reports/job to standby from primary so that resources can be saved on primary database.

 Key points of Logical standby are monitoring redo transport service and SQL apply service. As we know the troubleshooting or monitoring the logical standby can be performed various ways like referring to alert log, trace files or through EM or through DGMGRL(broker) and various views depending on the availability of tools and options.

 Using the alert log content you cannot judge whether the logical standby SQL apply is working fine or not, because if for some reason the SQL apply process was killed at OS level and those changes are not visible from alert log file, Of course alert log is great source to check for monitoring the mining of log files and recovery progress. In my opinion the very first source to review is the alert log of logical standby and also primary database to review the current status or to check any errors now or prior to ensure there are no abnormal errors.

 Now we will see how to monitor or troubleshoot the logical standby using several fixed and dictionary views. If you would like to know the brief status of the logical standby sql apply status, we can query "v$logstdby_state" this view provides consolidated information from views v$logstdby and v$logstdby_stats.

 

SQL> select * from v$logstdby_state;

   PRIMARY_DBID PRIMARY_CON_DBID     SESSION_ID REALTIME_APPLY STATE             CON_ID

--------------- ---------------- --------------- --------------- -------- ---------------

     3793852408       3793852408               1 Y               IDLE                   1

SQL>

If you see from the above output, it shows whether the Real-Time apply is enabled or not and also the current status of SQL apply which is "IDLE" it means SQL apply has applied all changes which ever caught up from primary database and if in case any transactions are writing actively and you can check the status as changing. For example below the state is "APPLYING".

SQL> select * from v$logstdby_state;

   PRIMARY_DBID PRIMARY_CON_DBID     SESSION_ID REALTIME_APPLY STATE             CON_ID

--------------- ---------------- --------------- --------------- -------- ---------------

     3793852408       3793852408               1 Y               APPLYING               1

SQL>

If in case for long time the state is IDLE and no changes and if you want to ensure that the redo transport and SQL apply is working properly, then you can perform small test like below and review the views. First gather the maximum sequence of primary database and it's 83.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

           83

SQL>

Logical standby owns a very good dictionary view i.e. dba_logstdby_log which shows the information of the registered log files. So from logical standby the latest sequence of primary 83 shows on standby with the status as "APPLIED" and that mean logical standby is up to date with primary database.

SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED     BLOCKS BLOCK_SIZE

---------- ------------- ------------ --------- -------- ---------- ----------

       76       2815207     2816929 30-JUN-15 YES           70842       512

       77       2816929     2816948 30-JUN-15 YES             17       512

       78       2816948     2817230 30-JUN-15 YES             163       512

       79       2817230     2818492 30-JUN-15 YES           2033       512

       80       2818492     2818591 30-JUN-15 YES             76       512

       81       2818591     2819488 30-JUN-15 YES             520       512

       82       2819488     2819957 30-JUN-15 YES             270       512

       83       2819957     2820247 30-JUN-15 YES             354       512

8 rows selected.

SQL>

The above example is only determination but to ensure it we can enforce log switch on primary database and check whether the new generated sequence of primary is received on logical standby or not. From primary we have performed log switch and the latest sequence on primary is 85.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

           85

SQL>

 

Again we will use query dba_logstdby_log to monitor, if we see from below output it clears that sequence number 85 applied on standby.

SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED     BLOCKS BLOCK_SIZE

---------- ------------- ------------ --------- -------- ---------- ----------

       76       2815207     2816929 30-JUN-15 YES           70842       512

       77       2816929     2816948 30-JUN-15 YES             17      512

       78       2816948     2817230 30-JUN-15 YES             163       512

       79       2817230     2818492 30-JUN-15 YES           2033       512

       80       2818492     2818591 30-JUN-15 YES             76       512

       81       2818591     2819488 30-JUN-15 YES             520       512

       82       2819488     2819957 30-JUN-15 YES             270       512

       83       2819957     2820247 30-JUN-15 YES             354       512

       84       2820247    2863222 01-JUL-15 YES           70895       512

       85       2863222     2863229 01-JUL-15 YES               4       512

10 rows selected.

SQL>

The above query is specific to logical standby environment likewise we use v$managed_standby view in Physical standby database and also it can be used in Logical standby database for some extent to track the status of the processes.

SQL> select sequence#,process,pid,status,client_process,block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, pid;

SEQUENCE# PROCESS         PID STATUS       CLIENT_P     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS

---------- --------- ---------- ------------ -------- ---------- ------------- ------------

         0 ARCH           26524 CONNECTED   ARCH             0             0           0

         0 RFS           27258 IDLE         UNKNOWN           0             0           0

         0 RFS           27260 IDLE         UNKNOWN           0            0           0

         0 RFS           27262 IDLE         UNKNOWN           0             0           0

       85 ARCH           26520 CLOSING     ARCH             1             0           0

       13 ARCH           26522 CLOSING     ARCH          81920             0           0

       84 ARCH           26526 CLOSING     ARCH         69632             0           0

       86 RFS           27254 IDLE         LGWR           588             0           0

8 rows selected.

From the above small test we can confirm whether the transactions from primary is actively receiving and applying or not.

SQL Apply Process

Internally SQL apply process is divided into two parts i.e. Redo Mining and the Redo Apply. In this regards many of the processes will run background. Initially "READER" will reads the information from the standby redo log files and simultaneously "PREPARER" and "BUILDER" processes works in mining the redo and also the SQL apply processes will be running with other work processes such as "ANALYZER", "COORDINATOR" and "APPLIER" to apply changes to logical standby database. Using the view "v$logstdby_stats" we can check each statistics value. For example to check whether the coordinator is working or not, then we can track the status using this view.

SQL> select name,value from v$logstdby_stats where name='coordinator state';

NAME                           VALUE

------------------------------ ----------

coordinator state             IDLE

SQL> /

NAME                           VALUE

------------------------------ ----------

coordinator state             APPLYING

SQL>

To check the status of each process of redo mining and the sql apply, v$logstdby_prcess will helps to show the current status of the log apply services and the responsible sessions.

SQL> select sid,serial#,spid,type from v$logstdby_process;

       SID   SERIAL# SPID                     TYPE

---------- ---------- ------------------------ ------------------------------

       21         29 27256                   COORDINATOR

       64         21 27394                   ANALYZER

       66         3 27398                   APPLIER

       68         1 27407                   APPLIER

       70         1 27409                   APPLIER

       72         1 27411                   APPLIER

       74         1 27413                   APPLIER

       46         31 27264                   READER

       58         17 27266                   BUILDER

     62         5 27268                   PREPARER

10 rows selected.

SQL>

To know overall sql apply status with real time statistics we can merge views to extract the latest scn(highest SCN), applied scn on standby and the Read SCN(SCN that has been read and saved)

SQL> select a.latest_scn,a.latest_time,a.applied_scn,a.applied_time,b.read_scn,b.read_time from v$logstdby_progress a,dba_logstdby_progress b;

     LATEST_SCN LATEST_TIME             APPLIED_SCN APPLIED_TIME               READ_SCN READ_TIME

--------------- -------------------- --------------- -------------------- --------------- --------------------

       2867336 01-JUL-2015 15:20:23         2867331 01-JUL-2015 15:20:21         2867334 01-JUL-2015 15:20:22

SQL>

To verify the status of the log apply services which are currently running we can use another view v$logstdby but it is deprecated however still I can say it as useful view so that we can check each processes work currently running and which sequence is currently reading so on.

SQL> select type,status,high_scn from v$logstdby;

TYPE           STATUS                                                             HIGH_SCN

--------------- ------------------------------------------------------------ --------------

COORDINATOR     ORA-16116: no work available                                       2864778

ANALYZER       ORA-16116: no work available                                       2864499

APPLIER         ORA-16116: no work available                                       2864491

APPLIER         ORA-16116: no work available                                       2864499

APPLIER         ORA-16116: no work available                                        2863087

APPLIER         ORA-16116: no work available                                       2864472

APPLIER         ORA-16116: no work available                                       2864480

READER         ORA-16242: Processing log file (thread# 1, sequence# 86)           2864778

BUILDER         ORA-16116: no work available                                       2864518

PREPARER       ORA-16116: no work available                                       2864517

10 rows selected.

SQL>

We will walk through with one more view which shows the history on logical standby apply activity and also it can be used to check failures of the redo apply on logical standby databases, Especially the status column which gives key information regarding the current activity of the processes or the information why apply is stopped so on.

SQL> select event_time,commit_scn, current_scn,status from dba_logstdby_events order by event_time;

EVENT_TIM COMMIT_SCN CURRENT_SCN STATUS

--------- ---------- ----------- --------------------------------------------------------------------------------

30-JUN-15                       ORA-16111: log mining and apply setting up

30-JUN-15                       Apply LWM 2816936, HWM 2816936, SCN 2816936

30-JUN-15                        ORA-16128: User initiated stop apply successfully completed

30-JUN-15                       APPLY_UNSET: RECORD_SKIP_ERRORS

30-JUN-15                       APPLY_UNSET: RECORD_APPLIED_DDL

30-JUN-15                       APPLY_UNSET: RECORD_SKIP_DDL

30-JUN-15                       APPLY_UNSET: RECORD_SKIP_ERRORS

30-JUN-15                       APPLY_UNSET: MAX_SGA

30-JUN-15                       APPLY_UNSET: RECORD_APPLIED_DDL

30-JUN-15                       APPLY_UNSET: RECORD_SKIP_DDL

30-JUN-15                       APPLY_UNSET: MAX_EVENTS_RECORDED

30-JUN-15                       APPLY_UNSET: PRESERVE_COMMIT_ORDER

30-JUN-15                       APPLY_UNSET: MAX_SERVERS

30-JUN-15                       APPLY_UNSET: MAX_EVENTS_RECORDED

30-JUN-15                       APPLY_UNSET: PRESERVE_COMMIT_ORDER

30-JUN-15                       ORA-16111: log mining and apply setting up

30-JUN-15                       Apply LWM 2816936, HWM 2816936, SCN 2816936

04-JUL-15                        Shutdown acknowledged

04-JUL-15                       ORA-16246: User initiated abort apply successfully completed

04-JUL-15                       APPLY_UNSET: MAX_SERVERS

04-JUL-15                       APPLY_UNSET: MAX_SGA

04-JUL-15                      APPLY_UNSET: RECORD_APPLIED_DDL

04-JUL-15                       APPLY_UNSET: RECORD_SKIP_DDL

04-JUL-15                       APPLY_UNSET: RECORD_SKIP_ERRORS

04-JUL-15                       APPLY_UNSET: PRESERVE_COMMIT_ORDER

04-JUL-15                      APPLY_UNSET: MAX_EVENTS_RECORDED

04-JUL-15                       APPLY_UNSET: RECORD_SKIP_DDL

04-JUL-15                       APPLY_UNSET: RECORD_SKIP_ERRORS

04-JUL-15                       APPLY_UNSET: PRESERVE_COMMIT_ORDER

04-JUL-15                        APPLY_UNSET: MAX_EVENTS_RECORDED

04-JUL-15                       APPLY_UNSET: RECORD_APPLIED_DDL

04-JUL-15                       Apply LWM 3020638, HWM 3020638, SCN 3020827

04-JUL-15                       ORA-16111: log mining and apply setting up

04-JUL-15   3054931     3054928 ORA-16226: DDL skipped due to lack of support

Apart from monitoring the Logical standby from SQL* Plus, we can also check the status of logical standby complete status like Apply Lag, Transport Lag.

DGMGRL> show database india;

Database - india

Role:             LOGICAL STANDBY

Intended State:   APPLY-ON

Transport Lag:     4 minutes 23 seconds (computed 11 seconds ago)

Apply Lag:         4 minutes 23 seconds (computed 11 seconds ago)

Apply Rate:       1.93 GByte/s

Instance(s):

   drmcdb

Database Status:

SUCCESS

DGMGRL>

There is no surprise that EM also can monitor the logical standby but there are limitations with it. Of course still we can check the upper level status of the Data Guard configuration status, the Lag with the primary database and ability to alter the configuration with logical standby database such as changes in NET_TIMEOUT, DELAY_MINS so on.

Conclusion

We've seen how to monitor and troubleshoot the issue while working with logical standby database using much with SQL Plus and also finished touching using Broker and EM. We have also seen few key views/dictionary views in order to check the status of each work process during the redo mining and the apply status, to check the historical events recorded with logical standby and beautiful feature of to compare the Latest SCN, applied SCN and the Read SCN so that we can monitor how fast the transactions are recovering on logical standby database.