Advanced Reporting Options
IMPORTANT
Please take note that if you have performed an upgrade from earlier versions (versions 5 and 6) of Dbvisit Standby to version 7, the Repository owner/schema will still be called "dbvisit" and not "dbvisit7".
If you have a new version 7 installation, the schema name will be called "dbvisit7".
The Dbvisit Standby Repository
The Dbvisit Standby Database Repository (DDR) is a small repository located in the primary database server (under the dbvisit7 schema) and should not take more than about 10MB in space.
The DDR is not automatically purged and may grow over time. A purging maintenance process can be setup to remove old entries.
The only two tables which may be purged or maintained by removing older entries include: DBV_TRANSFER_LOG and DBV_SEQUENCE_LOG
The repository contains the following tables which may be queried for reporting purpose.
DBV_TRANSFER_LOG | Used by Dbvisit Standby to maintain list of archive logs that have been sent to standby database. |
DBV_SEQUENCE_LOG | Used by Dbvisit Standby to record the log sequences at specific times. |
IMPORTANT
Do not update any entries or modify the structure of these tables.
Using "dbv_functions"
Using the Dbvisit Standby supplied utility called dbv_functions. The option -N will extract information from the DDR. Type dbv_functions -h for help on the command and to show the different options.
Example:
dbv_functions -N dbvisitp
Dbvisit Database configuration (DDC) file dbv_dbvisitp.env.
<<<<DBV_TRANSFER_LOG>>>>
oracle_sid => dbvisitp
sequence# => 3718
thread_num =>
log_id => 562150315
archive_name => /oracle/oraarch/dbvisitp/1_3718_0562150315.log.gz
process_id => 19547
process => TRANSFER
timestamp => 200901221633
datestamp => 2009/01/22:16:33
start_time_date => 2009/01/22:16:34
end_time_date => 2009/01/22:16:34
source_host => dbvisit12
destination_host => dbvisit11
checksum => 1186970607
size_in_bytes => 22944
size_in_mb => .02
process_completed => Y
Where dbvisitp is the name of the database. If no sequence number is given as part of the argument, then the last archive sequence is displayed.
Using SQL Statements to Query the Repository
The following query will report on the duration of the transfer and compress steps each time Dbvisit Standby has executed. The result of this query may be graphed and used for trend analysis:
SELECT datestamp,
oracle_sid,
process,
round(decode(size_in_bytes,-1,size_in_bytes,size_in_bytes/1024/1024),1) Size_MB,
(end_time_date-start_time_date)*246060 duration_seconds
FROM dbvisit7.dbv_transfer_log
ORDER BY
datestamp,
process
The column "process" specifies the Dbvisit Standby step.
This can be COMPRESS for the log compress step or TRANSFER for the transfer of the log from the primary server to the standby server.
If column "size_in_bytes" is -1, then this means this information is not available.
The following query will report on the log archive log gap over time. Note that the archive log gap information is only saved if the dbvisit -i command (inspect) is scheduled on a regular basis:
SELECT oracle_sid,datestamp,archive_log_gap FROM dbvisit7.dbv_sequence_log ORDER BY datestamp;