Today I decided to spend some time providing you with more detail on how you can monitor Dbvisit Standby. But before I get into the details, I would like to recommend, first of all,  that you always keep your version up to date. 

New Dbvisit Standby updates are released on a regular basis and the upgrade process is quick and easy, with no downtime required. I know what the time pressure constraints are like for production DBA’s and System Administrators, but the last thing you want is to find yourself on an old version of software that is not supported anymore.

However, I do urge you to follow your company’s change control process whenever you install any updates, whether it is for Dbvisit Standby or Oracle patching. Keeping up to date with the latest fixes is important, but do not take shortcuts in your change control process.  But enough about this for the moment - let’s get into monitoring of Dbvisit Standby.

Below I will show you a few examples, and here I will be making use of the latest Dbvisit Standby version, 6.0.44, which was released 26 February 2013 NZST.  The test environment consists of Oracle Linux 5.7 for the Operating System (OS) and Oracle 11.2.0.3 for the database software.

Quick note about the Dbvisit Install directory

I know you may be tempted to install Dbvisit into non-standard locations, but best practice is to install Dbvisit Standby into /usr/local/dbvisit when using Unix environments.  Over the last few months we have seen a variety of locations employed, and in most cases they will work fine, but my recommendation is that you use the default /usr/local/dbvisit in your next installation.

Dbvisit Monitoring:  The Log Gap report

You have implemented Dbvisit Standby and would like to know if it is working; that is, is my standby database up to date?  How do I check this?  The best way to explain this is by making use of an example, and you can do this either via the GUI or command line.  I will demonstrate with the command line in the examples below.

On my Primary server I have a database called “prod”, with my DDC name being the same -  “prod”.  (Note that my DDC configuration file is named dbv_prod.env, and this is the file that contains my entire Dbvisit Standby configuration).

Step 1:  Run Dbvisit on Primary to send logs to standby

The first step is to run Dbvisit Standby on the primary server to send logs to the DR server.   Running the default command “dbvisit <DDC> does exactly this.  For example:

oracle@dbvlin601[/usr/local/dbvisit/standby]: ./dbvisit prod
=============================================================
Dbvisit Standby Database Technology (6.0.44.10534) (pid 26625)
dbvisit started on dbvlin601: Wed Feb 27 20:03:32 2013 ()
=============================================================
Obtaining information from standby database (RUN_INSPECT=Y)...
Checking Dbvisit Standby configurational differences between dbvlin601 and dbvlin602...
No Dbvisit Standby configurational differences found between dbvlin601 and dbvlin602.

Log file(s) for prod will be transferred from dbvlin601 to dbvlin602...
Transferring o1_mf_1_813_8lvcxqsc_.arc.gz to host dbvlin602:o1_mf_1_813_8lvcxqsc_.arc.gz
Transferring o1_mf_1_814_8lvcxt1n_.arc.gz to host dbvlin602:o1_mf_1_814_8lvcxt1n_.arc.gz
Transferring o1_mf_1_815_8lvcxx2m_.arc.gz to host dbvlin602:o1_mf_1_815_8lvcxx2m_.arc.gz
Transferring o1_mf_1_816_8lvcxx2q_.arc.gz to host dbvlin602:o1_mf_1_816_8lvcxx2q_.arc.gz
Transferring o1_mf_1_817_8lvcxx6g_.arc.gz to host dbvlin602:o1_mf_1_817_8lvcxx6g_.arc.gz
Transferring o1_mf_1_818_8lvcxx6j_.arc.gz to host dbvlin602:o1_mf_1_818_8lvcxx6j_.arc.gz
Transferring o1_mf_1_819_8lvcxxb9_.arc.gz to host dbvlin602:o1_mf_1_819_8lvcxxb9_.arc.gz
Transferring o1_mf_1_820_8lvcxxbd_.arc.gz to host dbvlin602:o1_mf_1_820_8lvcxxbd_.arc.gz
201302272003 - 8 Log transfers to dbvlin602 for prod completed.
Last sequence was 820.
Dbvisit Archive Management Module (AMM)
(Number to keep: 0) (Days to keep: 7) (Archive backup count: 0) (Diskspace full threshold: 80%)
Current Disk percent full (/u01/app/oracle/fast_recovery_area)      : 33%
Current Disk percent full (FRA)      : 16%
Number of archive logs deleted         : 0
=============================================================
dbvisit ended on dbvlin601: Wed Feb 27 20:04:10 2013
=============================================================

The above output shows that archive logs were shipped from the primary to the standby server. I then run the log gap report.

Step 2.  Run the Log Gap report

Now let’s have a look at what the status of the configuration is.  Have all the archive logs been transferred to the standby server, and how up to date is my standby database?  This can be easily discovered by running “dbvisit –i <DDC>”:

oracle@dbvlin601[/usr/local/dbvisit/standby]: ./dbvisit -i prod
=============================================================
Dbvisit Standby Database Technology (6.0.44.10534) (pid 27017)
dbvisit started on dbvlin601: Wed Feb 27 20:05:59 2013 ()
=============================================================
Dbvisit Standby log gap report for prod at 201302272005:
-------------------------------------------------------------
1. ->   Standby database on dbvlin602 is at sequence: 812.
2. ->   Primary database on dbvlin601 is at log sequence: 824.
3. ->   Primary database on dbvlin601 is at archived log sequence: 823.
4. ->   Dbvisit Standby last transfer log sequence: 820.
5. ->   Dbvisit Standby last transfer at: 201302272003.

6. ->   Archive log gap for prod:  11.
7. ->   Transfer log gap for prod: 3.
8. ->   Standby database time lag (HH:MI:SS): 00:03:57.
No Mail sent as SEND_MAIL_FLAG = N
=============================================================
dbvisit ended on dbvlin601: Wed Feb 27 20:06:03 2013
=============================================================

Note: The above output was modified to add the line numbers 1 to 8 to assist in explanation below. There are 3 Key things to take note of when reviewing the log gap report:

Line 1 – Standby database on dbvlin602 is at sequence: 812. Shows the last log sequence (812) that was applied to the standby database.

Line 2 – Primary database on dbvlin601 is at log sequence: 824 Shows the current log sequence on the primary (824). Line 3 – Primary database on dbvlin601 is at archived log sequence: 823. Shows the latest archive log sequence (823) available on the primary database. Line 4 – Dbvisit Standby last transfer log sequence: 820. Shows the last archive log sequence (820) that was transferred to the standby server. Line 5 – Dbvisit Standby last transfer at: 201302272003. The timestamp (YYYYMMDDHH24MI) of the last transfer. Line 6 – Archive log gap for prod:  11.

This is one the most important lines to look at.  It shows the Archive Log Gap, which means how many archive logs still need to be applied to the standby database.  In this example the value is 11.  This indicates that my standby database is 11 logs behind the primary.  If this value is 0 it means all available Archive logs from the primary have been applied to the standby, and it is up to date.  Before doing a Graceful Switchover operation it is important to make sure this value is 0, with the exception when RAC is used, then one of the instances may have a value of 1.  To reduce this value, run Dbvisit Standby on the standby database.

Line 7 – Transfer log gap for prod: 3.

The second most important line to look at is the value for the Transfer Log Gap, and in this example the value is 3.  This value indicates the number of logs that still need to be transferred to the standby server. To resolve this gap you need to run Dbvisit Standby on the primary server again to ship the latest available logs.

Line 8 -  Standby database time lag (HH:MI:SS): 00:03:57.

The time displayed here provides you with an indication of how far behind in “Time” the standby database is from the primary.  This value is calculated by looking at the current SCN number on the standby database, compared to the SCN number on the primary database.  These numbers are converted to timestamps and the difference is then displayed. As we have 3 more logs to be transferred from the primary I will execute dbvisit on the primary first.

oracle@dbvlin601[/usr/local/dbvisit/standby]: ./dbvisit prod
=============================================================
Dbvisit Standby Database Technology (6.0.44.10534) (pid 27071)
dbvisit started on dbvlin601: Wed Feb 27 20:18:48 2013 ()
=============================================================
Obtaining information from standby database (RUN_INSPECT=Y)...
Checking Dbvisit Standby configurational differences between dbvlin601 and dbvlin602...
No Dbvisit Standby configurational differences found between dbvlin601 and dbvlin602.
Log file(s) for prod will be transferred from dbvlin601 to dbvlin602...
Transferring o1_mf_1_821_8lvczqo4_.arc.gz to host dbvlin602:o1_mf_1_821_8lvczqo4_.arc.gz
Transferring o1_mf_1_822_8lvczrcz_.arc.gz to host dbvlin602:o1_mf_1_822_8lvczrcz_.arc.gz
Transferring o1_mf_1_823_8lvczw2y_.arc.gz to host dbvlin602:o1_mf_1_823_8lvczw2y_.arc.gz
201302272018 - 3 Log transfers to dbvlin602 for prod completed.
Last sequence was 823.
Dbvisit Archive Management Module (AMM)
(Number to keep: 0) (Days to keep: 7) (Archive backup count: 0) (Diskspace full threshold: 80%)
Current Disk percent full (/u01/app/oracle/fast_recovery_area)      : 33%
Current Disk percent full (FRA)      : 16%
Number of archive logs deleted         : 0
=======================================================
dbvisit ended on dbvlin601: Wed Feb 27 20:19:12 2013
=======================================================

After running Dbvisit Standby on the primary to send the 3 logs, we rerun the log gap report on the primary server and find the output as below:

oracle@dbvlin601[/usr/local/dbvisit/standby]: ./dbvisit -i prod
=============================================================
Dbvisit Standby Database Technology (6.0.44.10534) (pid 27291)
dbvisit started on dbvlin601: Wed Feb 27 20:21:57 2013 ()
=============================================================
Dbvisit Standby log gap report for prod at 201302272021:
-------------------------------------------------------------
Standby database on dbvlin602 is at sequence: 812.
Primary database on dbvlin601 is at log sequence: 824.
Primary database on dbvlin601 is at archived log sequence: 823.
Dbvisit Standby last transfer log sequence: 823.
Dbvisit Standby last transfer at: 201302272018.

Archive log gap for prod:  11.
Transfer log gap for prod: 0.
Standby database time lag (HH:MI:SS): 00:19:55.
No Mail sent as SEND_MAIL_FLAG = N
=======================================================
dbvisit ended on dbvlin601: Wed Feb 27 20:22:01 2013
=======================================================

We can now see from the this output that all archive logs have been shipped from the primary to the standby as the “Transfer Log Gap” is now 0.  But we still have an Archive Log Gap of 11, which means we now need to run Dbvisit Standby on the standby server to apply these logs.

Step 3.  Run Dbvisit Standby on the DR Server The archive logs have now been shipped to the standby server.  The next step is to apply the logs, and this can be done by running the command:  “dbvisit <DDC>”

oracle@dbvlin602[/usr/local/dbvisit/standby]: ./dbvisit prod
=============================================================
Dbvisit Standby Database Technology (6.0.44.10534) (pid 25613)
dbvisit started on dbvlin602: Wed Feb 27 20:05:19 2013 ()
=============================================================
Log file(s) for prod from dbvlin601 will be applied to dbvlin602...
201302272005 - Log seq 813 thread 1 applied to standby database prod.
201302272005 - Log seq 814 thread 1 applied to standby database prod.
201302272005 - Log seq 815 thread 1 applied to standby database prod.
201302272005 - Log seq 816 thread 1 applied to standby database prod.
201302272005 - Log seq 817 thread 1 applied to standby database prod.
201302272005 - Log seq 818 thread 1 applied to standby database prod.
201302272005 - Log seq 819 thread 1 applied to standby database prod.
201302272005 - Log seq 820 thread 1 applied to standby database prod.
201302272005 - Log seq 821 thread 1 applied to standby database prod.
201302272005 - Log seq 822 thread 1 applied to standby database prod.
201302272005 - Log seq 823 thread 1 applied to standby database prod.
Dbvisit Archive Management Module (AMM)
(Number to keep: 0) (Days to keep: 7) (Diskspace full threshold: 80%)
Processing /u01/app/oracle/archive/prod...
Archive log dir: /u01/app/oracle/archive/prod
Total number of archive files   : 196
Number of archive logs deleted         : 0
Current Disk percent full       : 33%
=======================================================
dbvisit ended on dbvlin602: Wed Feb 27 20:05:33 2013
=======================================================

From the above output we can now see that the 11 logs have been applied to the standby database, and the next step is to run the Log Gap Report again on the primary.

Step 4.  Re-Run Log Gap Report on Primary

Now that the standby server has been updated, a rerun of the Log Gap Report shows the following output:

oracle@dbvlin601[/usr/local/dbvisit/standby]: ./dbvisit -i prod
=======================================================
Dbvisit Standby Database Technology (6.0.44.10534) (pid 27339)
dbvisit started on dbvlin601: Wed Feb 27 20:30:00 2013 ()
=============================================================
Dbvisit Standby log gap report for prod at 201302272029:
-------------------------------------------------------------
Standby database on dbvlin602 is at sequence: 823.
Primary database on dbvlin601 is at log sequence: 824.
Primary database on dbvlin601 is at archived log sequence: 823.
Dbvisit Standby last transfer log sequence: 823.
Dbvisit Standby last transfer at: 201302272018.

Archive log gap for prod:  0.
Transfer log gap for prod: 0.
Standby database time lag (HH:MI:SS): 00:25:33.
No Mail sent as SEND_MAIL_FLAG = N
=======================================================
dbvisit ended on dbvlin601: Wed Feb 27 20:30:05 2013
=======================================================

We can now see that all available logs have been applied to the standby database and that the Archive Log Gap, as well as the Transfer Log Gap, are both 0.  This is the ideal situation.

But you might have noticed that the “Standby database time lag” is still showing the standby database is 25 minutes and 33 seconds behind the primary (Standby SCN is compared against current primary SCN time).  Remember that in the above case, the last available archive log that was generated on the primary was 25 min. 33 sec ago.  In this case I am running Dbvisit manually so to quickly reduce the time gap, I can run Dbvisit Standby, first on the primary, followed by running it the standby side. 

This will force a log switch on the primary, as there have not been any logs generated since the last dbvisit run.  If archive logs were generated since the last dbvisit run, a log switch will not be forced and the created logs will be shipped to the standby.  If you specify the LOGSWITCH=Y parameter in the DDC configuration file, Dbvisit Standby will force a log switch every time it runs.  In this scenario, the log will be shipped and applied to the standby. If you then run the log gap report you will see something like this:

oracle@dbvlin601[/usr/local/dbvisit/standby]: ./dbvisit -i prod
=============================================================
Dbvisit Standby Database Technology (6.0.44.10534) (pid 27349)
dbvisit started on dbvlin601: Wed Feb 27 20:30:00 2013 ()
=============================================================
Dbvisit Standby log gap report for prod at 201302272033:
-------------------------------------------------------------
Standby database on dbvlin602 is at sequence: 824.
Primary database on dbvlin601 is at log sequence: 825.
Primary database on dbvlin601 is at archived log sequence: 824.
Dbvisit Standby last transfer log sequence: 824.
Dbvisit Standby last transfer at: 201302272032.

Archive log gap for prod:  0.
Transfer log gap for prod: 0.
Standby database time lag (HH:MI:SS): 00:00:22.
No Mail sent as SEND_MAIL_FLAG = N
=======================================================
dbvisit ended on dbvlin601: Wed Feb 27 20:33:24 2013
=======================================================

The above indicates there are no gaps and that the time difference between the two databases (at the time of running the log gap report) was 22 seconds.

Summary

Using the Log Gap Report is not the only monitoring option; there are a few options available such as the Reporting tab in the GUI, which will also show you a graphical presentation of the Archive Log Gap report. Example:

As with most things in IT, there are more than one way to do this.  You can also make use of Nagios or other 3^rd party products to monitor your Dbvisit Standby environment.  Example, two useful scripts you can use in your monitoring can include the following:

On the Primary:

You can use the SQL below as a staring point to extract more details about what the sequence number is for the last archive log that was archived on the primary.

select dest_id, thread#, max(sequence#)
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by dest_id, thread#;

DEST_ID    THREAD# MAX(SEQUENCE#)
---------- ---------- --------------
1          1          825

On the Standby:

To get an indication on what the last applied sequence number is you can use the SQL below:

select thread#, max(sequence#)
from v$log_history
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#;

THREAD# MAX(SEQUENCE#)
---------- --------------
1          824

Hopefully you found the above examples useful in helping you to get a better understanding of how you can monitor the Dbvisit Standby environment.