dbi Blog

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:

Post Views: 557

How to create a Backup Report in EM Cloud Control 13c

1st Activate gathering of additional metrics

2. Ensure the metric is gathered for each targets

3. Create the Backup Report

3.1 On the (first) “General” tab

On the “Elements” tab

3.3 On the “Schedule” tab

Frank Bléger

Hafedh

Leave a Reply:

Related blog articles

1st Activate gathering of additional metrics

2. Ensure the metric is gathered for each targets

3. Create the Backup Report

3.1 On the (first) “General” tab

On the “Elements” tab

3.3 On the “Schedule” tab

Frank Bléger

Hafedh

Leave a Reply:

Related blog articles

A first look at openSUSE Leap Micro 6.0

Another file system for Linux: bcachefs (3) – Mirroring/Replicas

Another file system for Linux: bcachefs (2) – multi device file systems

Another file system for Linux: bcachefs (1) – basics