sistescope monitor oracle database

使用sitescope监控oracle 数据库时，经常涉及到用户权限问题，下面就将sitescope 监控的原理贴出来一下

Solution  
 
 How to Troubleshoot the Oracle Database Monitor
How the Monitor Works
The Oracle Database Monitor makes connection to the database sever via JDBC.
 It then issues the queries that are in the SiteScope/Templates.Applications/commands.oraclejdbc file.
  The monitor brings back the information to the monitor in a tree structure.

How to Troubleshoot

1) Make sure that that syntax for the JDBC connection URL is correct. Example:
jdbc:oracle:thin:@IP Address:1521:Database

2) Make sure that you have downloaded the Oracle thin driver (e.g. classes12.zip) and placed it in the correct location:
>>SiteScope 7.9.5 and below:  SiteScope/java/lib/ext/ directory
>>SiteScope 8.0.0 and above:  SiteScope/WEB-INF/lib/ directory
When specifing this database driver (classes12.zip) in the monitor, it's name is oracle.jdbc.driver.OracleDriver
Note: It is possible to load more than one driver to this directory.
SiteScope will load all drivers and use whichever one is specified for each monitor.

- it is possible to load more than one driver to this directory.
SiteScope will load all drivers and use whichever one is appropriate for each monitor.
3) Verify that you can issue the queries from the SiteScope/Templates.
Applications/commands.oraclejdbc file out side of SiteScope with the user credentials that are provided in the monitor.

4) Make certain that the database's port is available from the SiteScope server.
Go to a command prompt and telnet to the oracle port of the remote machine. Example:
telnet
Port by default is 1521.

5) The user must have at least read access to the V$ tables, specifically the capability to execute the following queries:

SELECT instance_name FROM V$INSTANCE
SELECT name FROM V$SYSSTAT
SELECT STATISTIC#,NAME FROM V$STATNAME
SELECT sid,username,terminal,program FROM V$SESSION WHERE (osuser <> 'SYSTEM') AND (username IS NOT NULL) AND
(type <> 'BACKGROUND') and (paddr in (select addr from v$process))
SELECT NAME,VALUE FROM V$SYSSTAT
SELECT SID,STATISTIC#,VALUE FROM V$SESSTAT
FREETABLESPACEQUERY=select total.tablespace_name tsname, round((1- nvl(sum(free.bytes),0)/total.bytes)*100) pctusd
 from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) total,
 dba_free_space free where total.tablespace_name = free.tablespace_name(+) and
total.tablespace_name = 'USERS'group by total.tablespace_name, total.bytes order by (1-nvl(sum(free.bytes),0)/total.bytes)*100 asc

6) The FREETABLESPACEQUERY query above currently returns USERS.
 This query can be changed to return tablespace for others in addition to USERS.
For example users and system. You can change the jdbc commandline in the SiteScope/Templates.Applications/commands.oraclejdbc file:

FREETABLESPACEQUERY=select total.tablespace_name tsname, round((1- nvl(sum(free.bytes),0)/total.
bytes)*100) pctusd from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) total,
dba_free_space free where total.tablespace_name = free.tablespace_name(+) and
 total.tablespace_name in ('USERS','SYSTEM') group by total.tablespace_name,
 total.bytes order by (1-nvl(sum(free.bytes),0)/total.bytes)*100 asc

7) In some cases, when you have verified connectivity to the Oracle database
 but the counters are all returning 'n/a' it may be necessary to "Reload Counters" in the monitor setup --> get counters screen.

When the monitor is initially setup, the server is queried for available counters
and this information is stored (cached) in an xml file on the SiteScope server.
 By "reloading counters" it forces the counters list in the xml file to be recreated
 



SiteScope needs to have access to the
 V$SYSSTAT
 V$SESSTAT
tables.

When selecting from a system view (such as V$SESSTAT) the privilege to select from the view must be granted to the user directly and not via a role.
 This is the case if you can select from the view without problem
but then get this error when trying to create a view on it (which is what SiteScope is doing).

For additional, detailed troubleshooting for the Oracle Database (formerly Oracle JDBC) monitor,
please reference KM189298 - Troubleshooting the Oracle Database (formerly Oracle JDBC) Monitor