Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
http://www.free-online-exams.com
Problem:
SQL Query To Get History Information Of A Metric From Repository Database
in Grid Control
Solution:
You have to write custom query for the requirement specified. Please refer
the following note that provides an example on how to create custom report:
Note 557372.1 - How to create a tabular Report for CPU / Memory usage of Hosts over a period of time
Note 557372.1 - How to create a tabular Report for CPU / Memory usage of Hosts over a period of time
"MGMT$METRIC_DAILY" is the table used to store the metric details of the
target. You have to query the table "MGMT$METRIC_DAILY" to get the required
information.
For example, for CPU usage% per database, you can use the metric "Average Instance CPU (%)" available under database metrics and following query can be used in the report to get CPU% per database instance:
SELECT rollup_timestamp "Date", average "CPU Usage"
FROM MGMT$METRIC_DAILY
WHERE target_name ='<database instance name>'
AND metric_name = 'wait_bottlenecks'
AND metric_column = 'avg_user_cpu_time_pct'
AND rollup_timestamp <= <date>;
Similarly you can verify the metric that will give the desired output and can query the MGMT$METRIC_DAILY for getting the required information.
For available database metrics, refer the following link:
http://download.oracle.com/docs/cd/B16240_01/doc/em.102/b25986.pdf
Also you can use any of the repository views available in the following documentation to get the required results: This is available at :
http://download-east.oracle.com/docs/cd/B16240_01/doc/nav/portal_booklist.htm
Oracle® Enterprise Manager Extensibility 10g Release 5 (10.2.0.5),
Chapter 9 - Management Repository Views
To get the correct metric name, metric column details related to database:
1. Login to grid control.
2. Navigate to Targets --> Databases.
3. Go to respective database home page.
3. Click on "All Metrics" link under 'Related Links".
4. Click on the required metric, the following link will be displayed in the browser:
https://hostname.oracle.com:1159/em/console/monitoring/metricDetail$
ctxType=Databases$keyValue=MGMT*_TABLESPACE$target=agni$metric=problemTbsp$
pageType=byDay$type=oracle*_database$metricColumn=pctUsed
From this link, it is clear that:
target type = oracle_database
target name = agni
metric name = problemTbsp
metric column = pctUsed
Key Value = MGMT_TABLESPACE
Similar steps can be followed to retrieve the other metric information related to other target types as well.
MGMT_VIEW user is responsible for generating reports in grid control. MGMT_VIEW user has access to the repository views as specified in the following document:
http://download.oracle.com/docs/cd/B16240_01/doc/em.102/b40007/toc.htm
Reference: Chapter 9 - Management Repository Views
To create reports on other tables, grant permissions to MGMT_VIEW user as MGMT_VIEW is responsible for creating reports in grid control.
SQL> GRANT SELECT on SYSMAN.<table_name> to MGMT_VIEW;
And then create the reports in the grid control.
For example, for CPU usage% per database, you can use the metric "Average Instance CPU (%)" available under database metrics and following query can be used in the report to get CPU% per database instance:
SELECT rollup_timestamp "Date", average "CPU Usage"
FROM MGMT$METRIC_DAILY
WHERE target_name ='<database instance name>'
AND metric_name = 'wait_bottlenecks'
AND metric_column = 'avg_user_cpu_time_pct'
AND rollup_timestamp <= <date>;
Similarly you can verify the metric that will give the desired output and can query the MGMT$METRIC_DAILY for getting the required information.
For available database metrics, refer the following link:
http://download.oracle.com/docs/cd/B16240_01/doc/em.102/b25986.pdf
Also you can use any of the repository views available in the following documentation to get the required results: This is available at :
http://download-east.oracle.com/docs/cd/B16240_01/doc/nav/portal_booklist.htm
Oracle® Enterprise Manager Extensibility 10g Release 5 (10.2.0.5),
Chapter 9 - Management Repository Views
To get the correct metric name, metric column details related to database:
1. Login to grid control.
2. Navigate to Targets --> Databases.
3. Go to respective database home page.
3. Click on "All Metrics" link under 'Related Links".
4. Click on the required metric, the following link will be displayed in the browser:
https://hostname.oracle.com:1159/em/console/monitoring/metricDetail$
ctxType=Databases$keyValue=MGMT*_TABLESPACE$target=agni$metric=problemTbsp$
pageType=byDay$type=oracle*_database$metricColumn=pctUsed
From this link, it is clear that:
target type = oracle_database
target name = agni
metric name = problemTbsp
metric column = pctUsed
Key Value = MGMT_TABLESPACE
Similar steps can be followed to retrieve the other metric information related to other target types as well.
MGMT_VIEW user is responsible for generating reports in grid control. MGMT_VIEW user has access to the repository views as specified in the following document:
http://download.oracle.com/docs/cd/B16240_01/doc/em.102/b40007/toc.htm
Reference: Chapter 9 - Management Repository Views
To create reports on other tables, grant permissions to MGMT_VIEW user as MGMT_VIEW is responsible for creating reports in grid control.
SQL> GRANT SELECT on SYSMAN.<table_name> to MGMT_VIEW;
And then create the reports in the grid control.
References:
Note 557372.1 - How to create a tabular Report for CPU / Memory usage of
Hosts over a period of time
Note: 828994.1 - Sample SQL Query To Get History Information Of A Metric From
Repository Database
No comments:
Post a Comment