REPORTING module can be used in OEM cloud control to generate custom reports like database status report of all target databases.

Repository database contains all the target details and metric information. So the best way is to run a query against the repository database.

Here we have provided steps for generating database consolidation report

ENTERPRISE -> REPORT -> INFORMATION PUBLISHER REPORT

 

Click on CREATE:

 

ON GENERAL TAB:

CATEGORY – Compliance

SUBCATEGORY – Database Targets

TARGETS –   Select the repository database

PRIVILEGES – Run with SYSMAN privilege

ELEMENTS TAB:

Select ADD :

SELECT TABLE FROM SQL:

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;

This query you can customize as per your requirement:

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 the mentioned email addresses.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez

Anuradha’s LinkedIn: https://www.linkedin.com/in/dbaanuradhamudgal/

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.