Tags

,

Oracle provides the OMR analysis tools EMDIAG and repvfy so we can perform detailed management repository analysis and maintenance just like the pros.

Kellyn has her typical clear instructions on how to install the tools at http://dbakevlar.com/2013/02/using-the-em-diagnostic-kit-with-em12c/.

Werner de Gruyter (Yoda), the father and caretaker for EMDIAG, has provided a custom Information Publisher report that you can use to manage your system on a regular basis.

Create EMDIAG Report

EM CLI allows you to create an IP report by importing an XML definition of the report.  Create and save the XML file listed at the bottom of this post and execute the following EM CLI commands to create this report:

emcli login -username=sysman
emcli sync
emcli import_report -files=rpt_last_run.xml

This Information Publisher report can be found under a new EMDIAG category.

emdiag_report2

Because I’m me, I had to make a couple changes to the report:  scheduled it for weekly refresh (emailed to my team each Sunday), added it to my Public Reports page, and switched the bottom two sections of the report so the Top Five appears above the complete listing.

Run repvfy Regularly

That gets you halfway there – the report shows results the latest run of repvfy, so you need to keep it current.  I’ve cronned this simple script on one of my OMR database servers:

#!/bin/bash
# ------------------------------------------------------------------------
# File name = refresh_repvfy.sh
# Purpose = Update OMR health status analysis
# Paramenters = None
# ------------------------------------------------------------------------
export ORACLE_HOME=/home/oracle/product/11.2.0.4
export EMDIAG=${ORACLE_HOME}/emdiag
# Cleanup logs from older executions
find ${EMDIAG}/log/repvfy* -type f -mtime 2 -exec rm -f {} \;
# Execute the program
${EMDIAG}/bin/repvfy diag all

I have it scheduled to run daily.

The Report

The report consists of four sections: EMDIAG Details, Scorecard, Violations, and the Top Five Violations in each category.

In order to use this report effectively you need to understand that the ‘Test’ numbers indicate the priority/importance of the individual test.  Tests in the 1000 series have more impact than tests in the 8000 series, etc.  Click on the column heading to sort tabular data.

emdiag_report

 

File rpt_last_run.xml

<?xml version = '1.0' encoding = 'UTF-8'?>
<ReportDefinition title="EMDIAG - Results of Last Run" internal_only="0" system_report="0" category="EMDIAG" sub_category="Diagnostics" show_navigation="0" generate_context="0" add_toc="0" product_name="EM" is_jit_multi_target="0" is_jit_target="0" style="BLAF" oms_version="12.1.0.2.0" xmlns="http://www.oracle.com/DataCenter/ReportDefinition">
 <ReportWideParameters parameterName="oracle.sysman.eml.ip.render.elem.TimePeriodOption" parameterValue="false"/>
 <ReportWideParameters parameterName="oracle.sysman.eml.ip.render.elem.TimePeriodParam" parameterValue="0:0"/>
 <ReportWideParameters parameterName="oracle.sysman.eml.ip.render.elem.TimezoneRegion" parameterValue="PST8PDT"/>
 <ReportWideParameters parameterName="oracle.sysman.eml.ip.render.elem.TimePeriodUserCust" parameterValue="false"/>
 <ReportElement element_row="1" suppress_render="0" element_name_nlsid="IPMSG_USER_TABLE_FROM_SQL" element_type_nlsid="IPMSG_ANY_TARGET_TYPE" element_order="0">
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.TableRender.maxNumberOfRowsAllowed" parameterValue="10"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql" parameterValue="false"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.headerParam" parameterValue="EMDIAG Details"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.sqlStatement" parameterValue="SELECT component,info FROM sysman.em$diag_info_ext UNION ALL SELECT 'Last Run' component,TO_CHAR(last_verify,'DD-MON-YYYY HH24:MI:SS') info FROM sysman.em$diag_config_ext"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow" parameterValue="10"/>
 </ReportElement>
 <ReportElement element_row="1" suppress_render="0" element_name_nlsid="IPMSG_USER_TABLE_FROM_SQL" element_type_nlsid="IPMSG_ANY_TARGET_TYPE" element_order="1">
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.TableRender.maxNumberOfRowsAllowed" parameterValue="10"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql" parameterValue="false"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.headerParam" parameterValue="Scorecard"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.sqlStatement" parameterValue="SELECT category,TO_CHAR(DECODE(SIGN(100-SUM(score)),-1,0,100-SUM(score)),'90.90') score,COUNT(*) tests,SUM(cnt) violations FROM sysman.em$diag_latest_score_ext WHERE category!='Other' GROUP BY category ORDER BY category"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow" parameterValue="10"/>
 </ReportElement>
 <ReportElement element_row="2" suppress_render="0" element_name_nlsid="IPMSG_SEPARATOR" element_type_nlsid="IPMSG_NO_TARGET_TYPE" element_order="2"/>
 <ReportElement element_row="3" suppress_render="0" element_name_nlsid="IPMSG_USER_TABLE_FROM_SQL" element_type_nlsid="IPMSG_ANY_TARGET_TYPE" element_order="3">
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.TableRender.maxNumberOfRowsAllowed" parameterValue="2000"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql" parameterValue="false"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.headerParam" parameterValue="Violations"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.sqlStatement" parameterValue="SELECT module,TO_CHAR(id,'0000') test,label,vfy_cnt violations FROM sysman.em$diag_latest_results_ext ORDER BY module,id"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow" parameterValue="50"/>
 </ReportElement>
 <ReportElement element_row="4" suppress_render="0" element_name_nlsid="IPMSG_SEPARATOR" element_type_nlsid="IPMSG_NO_TARGET_TYPE" element_order="4"/>
 <ReportElement element_row="5" suppress_render="0" element_name_nlsid="IPMSG_USER_TABLE_FROM_SQL" element_type_nlsid="IPMSG_ANY_TARGET_TYPE" element_order="5">
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.TableRender.maxNumberOfRowsAllowed" parameterValue="50"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql" parameterValue="false"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.headerParam" parameterValue="Top-5 Violations per Category"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.sqlStatement" parameterValue="SELECT category,module,TO_CHAR(id,'0000') test,label,TO_CHAR(score,'90D0') score,cnt violations FROM (SELECT category,module,id,label,ROUND(score,1) score,cnt,rank() OVER (PARTITION BY category ORDER BY score DESC) rk FROM sysman.em$diag_latest_score_ext) WHERE rk&lt;6 AND category!='Other' ORDER BY category,score DESC,cnt,module,id"/>
 <ReportElementParameters parameterName="oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow" parameterValue="25"/>
 </ReportElement>
</ReportDefinition>