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”.
  1. Create a new data model by going to the shortcut in the top right.
  2. Within the new data model window create a new SQL query
  3. 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)
  1. 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)
  1. The resulting data model should appear as shown. Click save and save it to the custom data models folder.

  2. 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.
  3. On the main catalog page create a new report by going to the shortcut in the top right.
  4. On the window that opens, use the magnifying glass to browse to your data model that we previously created. Then click next.
  5. On the select layout page, chose Landscape, Page Header and Table for Layout. Click next.
  6. On Create table page Add the fields from the G_1 Data source with drag and drop. Then click next.
  7. On the save report page choose “customize report layout”, click finish and save to the custom reports folder.
  8. We need to add the second table. In the editor click Insert -> Data Table. This will create a new table at the bottom.
  1. Now drag and drop the fields from the G_2 query source into the new table.


  1. Save the report and run it. The result should be as follows:
  1. You can also get a bit fancy in the editor with some colouring. Lookup “conditional formatting" in BIPublisher.

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

Comments

  1. Great, article. Can't believe Oracle don't have it as a standard report.

    ReplyDelete
  2. Really good post! Was trying to figure out something like this. thanks :)

    ReplyDelete
  3. Mgmt$ha_backup shows 1row per db. Any solution to overcome this issue??

    ReplyDelete
    Replies
    1. You would need to query the V$RMAN_BACKUP_JOB_DETAILS view in the target database directly, which would violate the BI Publisher license, unless you buy more.

      Best bet is to write custom SQL script and then run it against your databases in a loop. Example: http://www.dba-oracle.com/t_script_loop_between_multiple_servers_instances.htm

      Delete
  4. I am not able to save the data model after creating .Can you help me

    ReplyDelete
  5. Hello, is a special license required for using BIP? I am logged in as a sysman but whenever I try to open my_server_name:PORT/xmlpserver the page simply fails to load. Oracle EM Cloud Control 13c. Cheers!

    ReplyDelete
    Replies
    1. it is included with the install - https://docs.oracle.com/cd/E63000_01/EMADV/install_em_bip.htm#EMADV11883

      Maybe a firewall issue? Check your host OS to see which ports are listening.

      Delete
  6. This comment has been removed by a blog administrator.

    ReplyDelete

Post a Comment

Popular posts from this blog

Active Directory Authentication with an Oracle Database

Oracle Enterprise Manager notify when Recovery Area low