Vishal desai’s Oracle Blog

April 18, 2014

Database Time Viewer

Filed under: Tools — vishaldesai @ 2:31 pm

DBTimeViewer is a great standalone tool created by Dominic Giles to monitor Oracle Database Time. It requires simple configuration file update which includes database connection information such as username, password, hostname, port and service. In large enterprise environment with hundreds of thousands of databases it becomes little difficult to update database.xml file every time you have to connect to different database. I have created simple combination of batch and SQL script which will automatically create database.xml.

Scripts:

Create SQL and Batch scripts with proper path settings:

Script dbtimemonitor.sql

set head off
set feed off
set linesize 200
set pages 9999
set feedback off
set verify off
set pagesize 0


set term off

spool C:\<dbtime path>\databases.xml

select '<?xml version = ' || '''' || '1.0' || '''' || ' encoding = ' || '''' || 'UTF-8' || '''' || '?>'  from dual
union all
select '<WaitMonitor Title="Monitored Databases" xmlns="http://www.dominicgiles.com/waitmonitor">' from dual
union all
select  '<MonitoredDatabase><ConnectString>//' || a.host_name || '<domain>:<port>/' || b.name || '</ConnectString><Comment></Comment><Username>' || '&1' || '</Username><Password>' || '&2' || '</Password></MonitoredDatabase>'  from gv$instance a, v$database b
union all
select '</WaitMonitor>' from dual;

spool off
set term on
exit


Script dbtime.bat

@ECHO off
SET DBUser=%1
SET DBPass=%2
SET DBTNS=%3

sqlplus -s "%DBUser%/%DBPass%@%DBTNS%" @C:\<path>\dbtimemonitor.sql %1 %2

cd C:\<path>
dbtimemonitor.bat

Output:

C:\<path>>dbtime.bat <dbuser> <dbpassword> <dbtns>

Capture4

Advertisements

Leave a Comment »

No comments yet.

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: