Dbvisit Standby Log Gap Report
- Anton Els (Deactivated)
Introduction to the Dbvisit Standby Log Gap Report
Dbvisit Standby can inspect the primary and standby databases and report on the latest log sequence numbers. It includes reporting on the:
- Archive log gap which is difference between the last archived sequence on the primary and the last applied sequence on the standby database.
The archive log gap should be near 0 (except when lag is used where the standby database should deliberately be behind the primary based on a lag set by the DBA using the parameter APPLY_DELAY_LAG_MINUTES - specified in the DDC file).
- Transfer log gap which is the difference between the last archived sequence on the primary and the last sequence transferred to the standby server.
The transfer log gap should be near 0.
Dbvisit Standby log gap data is automatically collected every time Dbvisit Standby is run from the primary server. The data collected is stored in the DDR for reporting purposes. It is no longer required to schedule the log gap report.
The Log Gap Report in Dbvisit Standby version 7 is the same as was used in the latest versions of Dbvisit Standby version 6
The Log Gap Report Explained
The Dbvisit Standby Log Gap Report is explained in detail in this section using 4 steps:
- Run Dbvisit Standby on the primary server
- Running the Log Gap Report on primary server
- Run Dbvisit Standby on the standby server
- Run the Log Gap Report and review
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.
Step 2. Run the Log Gap report
The log gap report is run by executing the command “dbvisit –i <DDC>”
An example of an execution is shown below
The line numbers are indicated below on the left with "1. -->", "2. -->" etc, which is just for documentation purpose to help explain the report and is not included in the actual report
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 =============================================================
The report line numbers are explained in more detail below:
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, 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.
Running the Log Gap Report from the GUI (Dbvserver)
Home > Run > Run Interactive > Primary Server tab > select Database from drop-down menu > select Default from Run Action drop-down menu > Run
The log sequence number from the primary database will always be one or more sequences ahead of the last log sequence that Dbvisit Standby will have transferred. This is because the log sequence shown for the primary database is the latest log sequence that is not archived yet (redo log).
This reporting is non-intrusive. No log switches are performed and no logs are transferred.
When this command is run, the information is also inserted into the Dbvisit Standby database repository (DDR) table called dbv_sequence_log. This table can be used for log gap analysis over time.
Note: The standby database must be available in standby mode or in READ ONLY mode when this command is executed. If the standby database is not available, Dbvisit Standby will automatically start the standby database if AUTO_START_STANDBY_DB=Yes.
Scheduling the Dbvisit Standby log gap report
Note: This is no longer required if using Dbvisit Standby 5.3.12 and above.
This command can be scheduled to run automatically to email the Dbvisit Standby log gap report. The following example shows how to schedule this command in Scheduled Tasks on an hourly basis:
Where w102n is the name of the database
Emailing the Dbvisit Standby log gap report
The Dbvisit Standby log gap report will be emailed by default to the email address specified by the parameter ADMINS in the DDC file.
If the email report layout is not the same as what is printed on screen, please change the Encoding setting in the email client to Western European (Windows).
To disable emailing the Dbvisit Standby log gap report, set EMAIL_LOG_GAP_REPORT = No in the DDC file.
Dbvisit Standby log gap report alerting
Dbvisit Standby will alert if the log gap from the Dbvisit Standby log gap report (dbvisit -i) exceeds the following values (set to 0 to turn off alerting)
- ARCHIVE_LOG_GAP_THRESHOLD
This is the difference between the last archived sequence on the primary and the last applied sequence on the standby database.
The archive log gap should be near 0 (except when APPLY_DELAY_LAG_MINUTES is used).
- TRANSFER_LOG_GAP_THRESHOLD
This is the difference between the last archived sequence on the primary and the last sequence transferred to the standby server.
The transfer log gap should be near 0.
The dbvisit -i <database> command must be scheduled for these settings to be effective.
Example:
ARCHIVE_LOG_GAP_THRESHOLD = 4
Once the archive log gap exceeds 4, then an alert email will be sent.
Example:
TRANSFER_LOG_GAP_THRESHOLD = 3
Once the transfer log gap exceeds 3, then an alert email will be sent.