Generate Custom Report From OEM Cloud Control 13c

OEM cloud control (12c/13c) gives you amazing facility to generate custom reports like , database status report of all target databases, Backup detail of database targets from Catalog database etc.
Repository database contains all the target details and metric information . So best way is to run query against the repository database.

Here we have provided steps for generating database consolidation report. REPORTING module of OEM can be used to create any build-in or custom reports.

Enterprise à Reports à Information publisher Reports

Now you have "Information Publisher Reports" window to start to create custom reports.

ON GENERAL TAB:

CATEGORY – Compliance

SUBCATEGORY – Database Targets

TARGETS –   Select the repository database

PRIVILEGES – Run with SYSMAN privilege/ Any Super Administrator's Privillege

ELEMENTS TAB:

Select ADD :

SELECT TABLE FROM SQL:

Select Database Instance

Then, Select "Continue"

 Here paste the sql query,

COMPLETE QUERY:

SELECT A.HOST_NAME,

        A.DATABASE_NAME,

 TO_CHAR(A.STARTUP_TIME,'DD-MON-YYYY') "STARTUP_DATE" ,

        TO_CHAR(A.creation_date,'DD-MON-YYYY') "CREATION_DATE",

        A.LOG_MODE,A.CHARACTERSET,A.DBVERSION,

        AVAILABILITY_STATUS,E.SGASIZE,

 G.HOME_LOCATION "ORACLE_HOME",

 F.property_value "PORT",

 C.cpu_count,

 C.CPU_CORE_COUNT,

 SUBSTR(D.OS_SUMMARY,1,40) "OS PLATFORM"

 FROM  SYSMAN.MGMT$DB_DBNINSTANCEINFO A,

 SYSMAN.MGMT$AVAILABILITY_CURRENT B,

 SYSMAN.MGMT$DB_CPU_USAGE C ,  

 sysman.mgmt$os_hw_summary D,

 sysman.mgmt$db_sga_all E ,

 SYSMAN.MGMT$TARGET_PROPERTIES F  ,

 SYSMAN.MGMT$ORACLE_SW_ENT_TARGETS G

 WHERE B.TARGET_TYPE='oracle_database' and

 A.TARGET_NAME=B.TARGET_NAME AND

 A.TARGET_NAME=C.TARGET_NAME AND

 A.HOST_NAME=D.HOST_NAME and

 a.target_name=E.target_name  AND

 E.SGANAME='Total SGA (MB)'  AND

   A.TARGET_NAME=F.TARGET_NAME AND

   a.target_name=G.TARGET_NAME and  

   F.PROPERTY_NAME='Port'

ORDER BY host_name desc;

Note: This query you can customize as per your requirement:

Select Continue. Then select “Schedule” Tab.

Now schedule it:

You can preview the report also. Once the preview is fine, you can save it. HTML formatted report will be sent the mentioned email addresses.

Note: Check “Save copies of report” and “E-Mail Report” check boxes.

Now you can see your mail report as you scheduled.