Create a custom backup report with BI Publisher for Oracle Enterprise Manager/Cloud Control 13c

Recently I was tasked to build out an OEM 13c instance to manage a few Oracle Database servers and their associated instances. As a part of the setup, I needed to generate a daily email report to confirm the backup status of all databases in the OEM inventory. Unfortunately, it seems Oracle has overlooked this in the default reports, so I had to create a custom report. Here's how this was accomplished.

The EM Repository database keeps track of the last database backup timestamp for each database. This is shown on the home page for any database. We can use this information to create a daily report and confirm that each database has been backed up in the last 24 hours.

1. Login the BI publisher site https://oemserver.yourdomain:9851/xmlpserver with your account or sysman.
2. Near the top right corner click “catalog”.
3. In the tree on the left create a new folder under “Shared Folders” called custom reports.
   
4. Within the custom reports folder create a new folder called “custom data models”.

5. Create a new data model by going to the shortcut in the top right.
   
6. Within the new data model window create a new SQL query
   
7. Paste the following in to the SQL query Window and click ok.
   

   select  "MGMT$DB_DBNINSTANCEINFO"."TARGET_NAME" as "TARGET_NAME",
     "MGMT$DB_DBNINSTANCEINFO"."DBVERSION" as "DBVERSION",
     "MGMT$HA_BACKUP"."STATUS" as "STATUS",
     "MGMT$HA_BACKUP"."INPUT_TYPE" as "INPUT_TYPE",
     "MGMT$HA_BACKUP"."OUTPUT_BYTES_DISPLAY" as "OUTPUT_SIZE",
     "MGMT$HA_BACKUP"."START_TIME" as "START_TIME",
     "MGMT$HA_BACKUP"."END_TIME" as "END_TIME" 
    from "MGMT_VIEW"."MGMT$HA_BACKUP" "MGMT$HA_BACKUP",
    "MGMT_VIEW"."MGMT$DB_DBNINSTANCEINFO" "MGMT$DB_DBNINSTANCEINFO" 
    where   "MGMT$DB_DBNINSTANCEINFO"."TARGET_GUID"="MGMT$HA_BACKUP"."TARGET_GUID"
     and   "MGMT$HA_BACKUP"."START_TIME" > (SYSDATE - 1)

8. Again within the new data model window create a new SQL query

   SELECT t1."TARGET_NAME" as "TARGET_NAME", t1."DBVERSION" as "DBVERSION"
   FROM "MGMT_VIEW"."MGMT$DB_DBNINSTANCEINFO" t1
   LEFT JOIN MGMT$HA_BACKUP t2 ON t2.TARGET_GUID = t1.TARGET_GUID
   WHERE t2.TARGET_GUID IS NULL OR t2."START_TIME" < (SYSDATE - 1)

9. The resulting data model should appear as shown. Click save and save it to the custom data models folder.
   
   
10. We are now required to “Create Sample Data” before we can create a report. Click on “View Data” near the top right of your screen and then click the “view” button on the next screen. Finally click “Save as Sample Data” to complete this step.
11. On the main catalog page create a new report by going to the shortcut in the top right.
    
12. On the window that opens, use the magnifying glass to browse to your data model that we previously created. Then click next.
    
13. On the select layout page, chose Landscape, Page Header and Table for Layout. Click next.
    
14. On Create table page Add the fields from the G_1 Data source with drag and drop. Then click next.
    
15. On the save report page choose “customize report layout”, click finish and save to the custom reports folder.
16. We need to add the second table. In the editor click Insert -> Data Table. This will create a new table at the bottom.

17. Now drag and drop the fields from the G_2 query source into the new table.
    

18. Save the report and run it. The result should be as follows:
    

19. You can also get a bit fancy in the editor with some colouring. Lookup “conditional formatting" in BIPublisher.

20. Once you’re ready, you can schedule it as a daily email report from the top right corner.