Wanted to make sure your Oracle databases were secured and be informed in case of backup problems?
This blog describes how to quickly get a report sent out on a daily-basis and make it work in Enterprise Manager 13cR5.
1st Activate gathering of additional metrics
We first need to activate the automatic gathering of additional metrics.
From Targets -> Databases -> Click on the target database
On the database home page “Cluster Database” drop down menu, navigate to Monitoring > Metrics and Collection Settings:
Click on the ‘Other Collected Items’ tab:
Scroll down and search for ‘High Availability Backup History‘ -> click on Disabled
Enable this metric and Set Frequency Type to “By Minutes” and Repeat Every to “30” Minutes -> Continue -> OK.
Before EM 13cR5, it might be worth to look at MyOracleSupport (Doc ID 2631059.1) EM 13c: Repository View Sysman.mgmt$db_backup_history Is Empty
To make sure this metric is gathered on a very regular basis, another mean is to create a monitoring template and have apply it by default on your databases.
2. Ensure the metric is gathered for each targets
Before creating any backup report, we first ensure the “High Availability Backup History” metric is gathered for each targets and accessible in the EM Cloud Control 13c Database Repository:
SQL> select count(*) from sysman.mgmt$db_backup_history;
COUNT(*)
----------
1385
1 row selected.
Backup types on the query and in the report are based on the columns INPUT_TYPE and INCREMENTAL_LEVEL of SYSMAN.MGMT$DB_BACKUP_HISTORY view.
Set lines 300
set pages 200
Col host_name for a35
Col target_name for a30
col full for a20
col incr for a20
col lev0 for a20
col lev1 for a20
SELECT
ROWNUM n
,host_name
,target_name
-- ,full_h
,CASE
WHEN (NVL(full_h,1000)=1000) THEN ' '
WHEN (NVL(full_h,1000)<24) THEN NVL(full_h,1000)||' hour(s)'
WHEN (NVL(full_h,1000)>=24) THEN TRUNC(NVL(full_h,1000)/24)|| ' day(s) ' || SUBSTR((NUMTODSINTERVAL(NVL(full_h,1000), 'HOUR')), 12,2) || ' hour(s)'
END full
-- ,incr_h
,CASE
WHEN (NVL(incr_h,1000)=1000) THEN ' '
WHEN (NVL(incr_h,1000)<24) THEN NVL(incr_h,1000)||' hour(s)'
WHEN (NVL(incr_h,1000)>=24) THEN TRUNC(NVL(incr_h,1000)/24)|| ' day(s) ' || SUBSTR((NUMTODSINTERVAL(NVL(incr_h,1000), 'HOUR')), 12,2) || ' hour(s)'
END incr
-- ,lev0_h
,CASE
WHEN (NVL(lev0_h,1000)=1000) THEN ' '
WHEN (NVL(lev0_h,1000)<24) THEN NVL(lev0_h,1000)||' hour(s)'
WHEN (NVL(lev0_h,1000)>=24) THEN TRUNC(NVL(lev0_h,1000)/24)|| ' day(s) ' || SUBSTR((NUMTODSINTERVAL(NVL(lev0_h,1000), 'HOUR')), 12,2) || ' hour(s)'
END lev0
-- ,lev1_h
,CASE
WHEN (NVL(lev1_h,1000)=1000) THEN ' '
WHEN (NVL(lev1_h,1000)<24) THEN NVL(lev1_h,1000)||' hour(s)'
WHEN (NVL(lev1_h,1000)>=24) THEN TRUNC(NVL(lev1_h,1000)/24)|| ' day(s) ' || SUBSTR((NUMTODSINTERVAL(NVL(lev1_h,1000), 'HOUR')), 12,2) || ' hour(s)'
END lev1
-- ,arch_h
,CASE
WHEN (NVL(arch_h,1000)=1000) THEN ' '
WHEN (NVL(arch_h,1000)<24) THEN NVL(arch_h,1000)||' hour(s)'
WHEN (NVL(arch_h,1000)>=24) THEN TRUNC(NVL(arch_h,1000)/24)|| ' day(s) ' || SUBSTR((NUMTODSINTERVAL(NVL(arch_h,1000), 'HOUR')), 12,2) || ' hour(s)'
END arch
,CASE
WHEN ( NOT (NVL(full_h,1000)<7*24 OR NVL(lev0_h,1000)<7*24 or NVL(incr_h,1000)<7*24 ) )
THEN 'WARNING: No full or level0 on last 7 days'
WHEN ( NOT (NVL(full_h,1000)<24 or NVL(lev0_h,1000)<24 or NVL(lev1_h,1000)<24 or NVL(incr_h,1000)<24) )
THEN 'WARNING: No full, incr, level0 or level1 on last 24 hours'
WHEN ( NOT NVL(arch_h,1000)<6 )
THEN 'WARNING: No backup archive on last 6 hours'
END info
FROM
(
SELECT
host_name
,target_name
,target_guid
,MAX(DECODE(typelevel, 'DB FULL', hours, NULL) ) full_h
,MAX(DECODE(typelevel, 'DB INCR', hours, NULL) ) incr_h
,MAX(DECODE(typelevel, 'DB INCR0', hours, NULL) ) lev0_h
,MAX(DECODE(typelevel, 'DB INCR1', hours, NULL) ) lev1_h
,MAX(DECODE(typelevel, 'ARCHIVELOG', hours, NULL) ) arch_h
FROM
(
SELECT
T.host_name
,T.TARGET_GUID
,UPPER(T.TARGET_NAME) target_name
,B.INPUT_TYPE || B.INCREMENTAL_LEVEL typelevel
,CEIL((sysdate - max(END_TIME))*24) hours
FROM SYSMAN.MGMT$TARGET T
,(select * from SYSMAN.MGMT$DB_BACKUP_HISTORY where status like 'COMPLETED%' ) B
-- WHERE B.TARGET_GUID (+) = T.TARGET_GUID
WHERE B.TARGET_GUID = T.TARGET_GUID
AND t.target_type in ('rac_database','oracle_database')
GROUP BY
T.host_name
,T.TARGET_GUID
,T.target_name
,B.INPUT_TYPE || B.INCREMENTAL_LEVEL
)
GROUP BY HOST_NAME, TARGET_GUID,TARGET_NAME
ORDER BY HOST_NAME, TARGET_NAME
);
3. Create the Backup Report
We are now ready to create the Backup Report:
Go to “Enterprise” menu -> Reports -> Information Publisher Reports
And click on CREATE
3.1 On the (first) “General” tab
- a. Enter Title= DBI-Backup Report
- b. Select the Category “A_DBI_Heartbeat”
In case it does not exist, click on “Add Category” and create a new one named “A_DBI_Heartbeat”. Obviously, no need to create new categories/subcategories. This only makes it easier to find. - c. Select the subcategory “A_DBI_Heartbeat”
- d. In case it does not exist, click on “Add Subcategory” and create a new one “A_DBI_Heartbeat”
- e. Use the specified target
Target= MYINFRACDB_MYEM3CPDB (actually our OEM database) - f. Check “Run report using target privileges of the report owner (SYSMAN)”
- g. Uncheck the “This report has a time period” box
On the “Elements” tab
- a. Click on “Add” and select “Styled Text”, click on Continue.
- b. Edit the “Styled Text” element and add in the filed “Message Text” : “This report lists the RMAN completed backups.”
- c. Click on “Add” and select “Separator”, click on Continue.
- d. Click on “Add” and select “Table from SQL”, click on Continue.
- e. Edit the “Table from SQL” element and add in the field “Header” : “Elapsed time since last completed backups”. Enter the following “Statement”:
SELECT
ROWNUM n
,host_name
,target_name
-- ,full_h
,CASE
WHEN (NVL(full_h,1000)=1000) THEN ' '
WHEN (NVL(full_h,1000)<24) THEN NVL(full_h,1000)||' hour(s)'
WHEN (NVL(full_h,1000)>=24) THEN TRUNC(NVL(full_h,1000)/24)|| ' day(s) ' || SUBSTR((NUMTODSINTERVAL(NVL(full_h,1000), 'HOUR')), 12,2) || ' hour(s)'
END full
-- ,incr_h
,CASE
WHEN (NVL(incr_h,1000)=1000) THEN ' '
WHEN (NVL(incr_h,1000)<24) THEN NVL(incr_h,1000)||' hour(s)'
WHEN (NVL(incr_h,1000)>=24) THEN TRUNC(NVL(incr_h,1000)/24)|| ' day(s) ' || SUBSTR((NUMTODSINTERVAL(NVL(incr_h,1000), 'HOUR')), 12,2) || ' hour(s)'
END incr
-- ,lev0_h
,CASE
WHEN (NVL(lev0_h,1000)=1000) THEN ' '
WHEN (NVL(lev0_h,1000)<24) THEN NVL(lev0_h,1000)||' hour(s)'
WHEN (NVL(lev0_h,1000)>=24) THEN TRUNC(NVL(lev0_h,1000)/24)|| ' day(s) ' || SUBSTR((NUMTODSINTERVAL(NVL(lev0_h,1000), 'HOUR')), 12,2) || ' hour(s)'
END lev0
-- ,lev1_h
,CASE
WHEN (NVL(lev1_h,1000)=1000) THEN ' '
WHEN (NVL(lev1_h,1000)<24) THEN NVL(lev1_h,1000)||' hour(s)'
WHEN (NVL(lev1_h,1000)>=24) THEN TRUNC(NVL(lev1_h,1000)/24)|| ' day(s) ' || SUBSTR((NUMTODSINTERVAL(NVL(lev1_h,1000), 'HOUR')), 12,2) || ' hour(s)'
END lev1
-- ,arch_h
,CASE
WHEN (NVL(arch_h,1000)=1000) THEN ' '
WHEN (NVL(arch_h,1000)<24) THEN NVL(arch_h,1000)||' hour(s)'
WHEN (NVL(arch_h,1000)>=24) THEN TRUNC(NVL(arch_h,1000)/24)|| ' day(s) ' || SUBSTR((NUMTODSINTERVAL(NVL(arch_h,1000), 'HOUR')), 12,2) || ' hour(s)'
END arch
,CASE
WHEN ( NOT (NVL(full_h,1000)<7*24 OR NVL(lev0_h,1000)<7*24 or NVL(incr_h,1000)<7*24 ) )
THEN 'WARNING: No full or level0 on last 7 days'
WHEN ( NOT (NVL(full_h,1000)<24 or NVL(lev0_h,1000)<24 or NVL(lev1_h,1000)<24 or NVL(incr_h,1000)<24) )
THEN 'WARNING: No full, incr, level0 or level1 on last 24 hours'
WHEN ( NOT NVL(arch_h,1000)<6 )
THEN 'WARNING: No backup archive on last 6 hours'
END info
FROM
(
SELECT
host_name
,target_name
,target_guid
,MAX(DECODE(typelevel, 'DB FULL', hours, NULL) ) full_h
,MAX(DECODE(typelevel, 'DB INCR', hours, NULL) ) incr_h
,MAX(DECODE(typelevel, 'DB INCR0', hours, NULL) ) lev0_h
,MAX(DECODE(typelevel, 'DB INCR1', hours, NULL) ) lev1_h
,MAX(DECODE(typelevel, 'ARCHIVELOG', hours, NULL) ) arch_h
FROM
(
SELECT
T.host_name
,T.TARGET_GUID
,UPPER(T.TARGET_NAME) target_name
,B.INPUT_TYPE || B.INCREMENTAL_LEVEL typelevel
,CEIL((sysdate - max(END_TIME))*24) hours
FROM SYSMAN.MGMT$TARGET T
,(select * from SYSMAN.MGMT$DB_BACKUP_HISTORY where status like 'COMPLETED%' ) B
-- WHERE B.TARGET_GUID (+) = T.TARGET_GUID
WHERE B.TARGET_GUID = T.TARGET_GUID
AND t.target_type in ('rac_database','oracle_database')
GROUP BY
T.host_name
,T.TARGET_GUID
,T.target_name
,B.INPUT_TYPE || B.INCREMENTAL_LEVEL
)
GROUP BY HOST_NAME, TARGET_GUID,TARGET_NAME
ORDER BY HOST_NAME, TARGET_NAME
)
- f. Click on “Continue”
- g. Click on “Continue”
- h. You should have the following elements:
3.3 On the “Schedule” tab
- a. Check the “Schedule Report” box.
- b. Select the “Repeating” schedule
- c. Select “By Days” and enter 1 in the “Repeat Every 1 Days” field to get the report every days
- d. Set the report date (i.e. Monday morning at 07:00AM)
- e. In the “E-mail Report” part, check the “E-mail report each time the scheduled report completes” box
- f. Enter the sender email address and addressee in the “To” field
- g. Enter the subject eg. “Daily Prod. Databases Backups Report”
- h. Click on OK to validate your report.
That’s it. Here is the kind of mail you should get:
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/11/CBL_web-min-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/MAW_web-min-scaled.jpg)
Salim
08.10.2024Hi, I don't see High Availability Backup History with target Oracle 11g.
Why ?
Clemens Bleile
10.10.2024Hi, according the documentation the "High Availability Backup History" is for 12c and later only.
Clemens