Automating Oracle RMAN Backup Reports with PL/SQL Using the RMAN Catalog
The reason for this blog post is to provide an alternative way on how to review RMAN backups for Oracle Database fleet. It is obvious that there are many monitoring tools available across markets ranging from open source Xymon and up to Oracle Enterprise Manager Cloud Control or IBM Tivoli Monitoring. And no doubts there are many in-house created custom scripts or tools supporting the need.
Quick sidestep from the main topic—based on my previous experience, Xymon is a very useful tool. Although it is open source, I have used it for local and remote system monitoring, starting from a small set of servers and databases up to enterprise level. Highly recommended.
Case details
But let us return to the main topic—how to review RMAN backups of Oracle Databases compactly.
My use case is:
- 100+ Oracle Database instances running on different versions
- Fleet divided into production/test/development/training databases
- Some number of production databases have DR sites
- Point of interest is production instances and corresponding production DR sites
- Oracle Enterprise Manager is in use but there is a need for a lightweight tool that follow up on RMAN backup runs for production databases and their DRs
- The key point is that Oracle RMAN Catalog is in use and used as the main data source.
Disclaimer
The setup and code provided below can be modified according to your wishes and needs.
Evaluate, use, optimize, modify, finetune code according to configuration, performance or any other specifics of your environment.
Daily reports retrieving successful and unsuccessful RMAN backup runs
UTL_MAIL and SMTP configuration
Before deploying custom code, make sure the UTL_MAIL package is installed and the SMTP server related configuration applied. SMTP configuration can be specific to the environment where the described solution is applied. In the example provided, UTL_MAIL and SMTP configuration reside in the same database where Oracle RMAN Catalog.
SQL> !ls -l utlmail.sql
-rw-r--r-- 1 oracle oinstall 7543 Mar 7 2018 utlmail.sql
SQL> @utlmail.sql
Package created.
Synonym created.
SQL> !ls -l prvtmail.plb
-rw-r--r-- 1 oracle oinstall 6327 Apr 17 2019 prvtmail.plb
SQL> @prvtmail.plb
Package created.
Package body created.
Grant succeeded.
Package body created.
No errors.
SQL> desc UTL_MAIL
PROCEDURE SEND
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SENDER VARCHAR2 IN
RECIPIENTS VARCHAR2 IN
CC VARCHAR2 IN DEFAULT
BCC VARCHAR2 IN DEFAULT
SUBJECT VARCHAR2 IN DEFAULT
MESSAGE VARCHAR2 IN DEFAULT
MIME_TYPE VARCHAR2 IN DEFAULT
PRIORITY BINARY_INTEGER IN DEFAULT
REPLYTO VARCHAR2 IN DEFAULT
PROCEDURE SEND_ATTACH_RAW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SENDER VARCHAR2 IN
RECIPIENTS VARCHAR2 IN
CC VARCHAR2 IN DEFAULT
BCC VARCHAR2 IN DEFAULT
SUBJECT VARCHAR2 IN DEFAULT
MESSAGE VARCHAR2 IN DEFAULT
MIME_TYPE VARCHAR2 IN DEFAULT
PRIORITY BINARY_INTEGER IN DEFAULT
ATTACHMENT RAW IN
ATT_INLINE BOOLEAN IN DEFAULT
ATT_MIME_TYPE VARCHAR2 IN DEFAULT
ATT_FILENAME VARCHAR2 IN DEFAULT
REPLYTO VARCHAR2 IN DEFAULT
PROCEDURE SEND_ATTACH_VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SENDER VARCHAR2 IN
RECIPIENTS VARCHAR2 IN
CC VARCHAR2 IN DEFAULT
BCC VARCHAR2 IN DEFAULT
SUBJECT VARCHAR2 IN DEFAULT
MESSAGE VARCHAR2 IN DEFAULT
MIME_TYPE VARCHAR2 IN DEFAULT
PRIORITY BINARY_INTEGER IN DEFAULT
ATTACHMENT VARCHAR2 IN
ATT_INLINE BOOLEAN IN DEFAULT
ATT_MIME_TYPE VARCHAR2 IN DEFAULT
ATT_FILENAME VARCHAR2 IN DEFAULT
REPLYTO VARCHAR2 IN DEFAULT
SQL> show parameter smtp_out_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
smtp_out_server string
SQL> ALTER SYSTEM SET smtp_out_server='127.0.0.1:25' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET smtp_out_server='127.0.0.1:25';
System altered.
SQL> show parameter smtp_out_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
smtp_out_server string 127.0.0.1:25
Code to generate daily RMAN backup reports
Code for RMAN backup report generation.
The provided example code resides in the same database with Oracle RMAN Catalog.
Ensure that the owner of the code has read permissions on underlying RMAN Catalog objects like:
RC_RMAN_BACKUP_JOB_DETAILS
RC_BACKUP_SET_DETAILS
RC_BACKUP_SET
The report is suitable for daily DBA operational needs. As per the use case, there are many production databases, and a number of these production databases have DR sites. It is more convenient to read through reports rather than check database by database manually. Below code can be modified according to individual needs or situation.
create
or replace PROCEDURE OWNER.BACKUP_REPORT_DAILY(v_report_type IN VARCHAR2) IS
v_attachment VARCHAR2(32767):= null;
v_repdate VARCHAR2(30):= null;
v_filename VARCHAR2(10):= null;
v_mail_status VARCHAR2(15):= null;
v_message_text VARCHAR2(200):= null;
CURSOR v_report_G IS
select
report_line
FROM
(
select
RPAD(db_name, 8, ' ')|| ' | ' || RPAD(db_unique_name, 15, ' ')|| ' | ' || RPAD(database_role, 7, ' ')|| ' | ' || RPAD(session_recid, 5, ' ')|| ' | ' || session_stamp || ' | ' || time_taken_display || ' | ' || RPAD(status, 24, ' ')|| ' | ' || input_type || ' | ' || start_time || ' | ' || end_time as report_line
from
(
select
J.DB_NAME,
s.db_unique_name,
s.database_role,
j.session_recid,
j.session_stamp,
j.time_taken_display,
j.status,
j.input_type,
to_char(
j.start_time, 'yyyy-mm-dd hh24:mi:ss'
) start_time,
to_char(
j.end_time, 'yyyy-mm-dd hh24:mi:ss'
) end_time
from
RMAN.RC_RMAN_BACKUP_JOB_DETAILS j
left outer join (
select
s.db_key,
s.site_key,
d.session_recid,
d.session_stamp,
sum(
case when d.controlfile_included = 'BACKUP' then d.pieces else 0 end
) CF,
sum(
case when d.controlfile_included = 'NO'
and d.backup_type || d.incremental_level = 'D' then d.pieces else 0 end
) DF,
sum(
case when d.backup_type || d.incremental_level = 'D0' then d.pieces else 0 end
) I0,
sum(
case when d.backup_type || d.incremental_level = 'I1' then d.pieces else 0 end
) I1,
sum(
case when d.backup_type = 'L' then d.pieces else 0 end
) L
from
RMAN.RC_BACKUP_SET_DETAILS d
join RMAN.RC_BACKUP_SET s on s.set_stamp = d.set_stamp
and s.set_count = d.set_count
where
s.input_file_scan_only = 'NO'
group by
d.session_recid,
d.session_stamp,
s.db_key,
s.site_key
) x on x.session_recid = j.session_recid
and x.session_stamp = j.session_stamp,
rman.rc_site s
where
(
j.start_time > TRUNC(SYSDATE)-1
AND j.start_time < TRUNC(SYSDATE)
)
and s.db_key = x.db_key
and s.site_key = x.site_key
order by
j.start_time desc
)
where
status NOT IN (
'FAILED', 'COMPLETED WITH ERRORS',
'RUNNING WITH ERRORS'
)
);
CURSOR v_report_B IS
select
report_line
FROM
(
select
RPAD(db_name, 8, ' ')|| ' | ' || RPAD(db_unique_name, 15, ' ')|| ' | ' || RPAD(database_role, 7, ' ')|| ' | ' || RPAD(session_recid, 5, ' ')|| ' | ' || session_stamp || ' | ' || time_taken_display || ' | ' || RPAD(status, 24, ' ')|| ' | ' || input_type || ' | ' || start_time || ' | ' || end_time as report_line
from
(
select
J.DB_NAME,
s.db_unique_name,
s.database_role,
j.session_recid,
j.session_stamp,
j.time_taken_display,
j.status,
j.input_type,
to_char(
j.start_time, 'yyyy-mm-dd hh24:mi:ss'
) start_time,
to_char(
j.end_time, 'yyyy-mm-dd hh24:mi:ss'
) end_time
from
RMAN.RC_RMAN_BACKUP_JOB_DETAILS j
left outer join (
select
s.db_key,
s.site_key,
d.session_recid,
d.session_stamp,
sum(
case when d.controlfile_included = 'BACKUP' then d.pieces else 0 end
) CF,
sum(
case when d.controlfile_included = 'NO'
and d.backup_type || d.incremental_level = 'D' then d.pieces else 0 end
) DF,
sum(
case when d.backup_type || d.incremental_level = 'D0' then d.pieces else 0 end
) I0,
sum(
case when d.backup_type || d.incremental_level = 'I1' then d.pieces else 0 end
) I1,
sum(
case when d.backup_type = 'L' then d.pieces else 0 end
) L
from
RMAN.RC_BACKUP_SET_DETAILS d
join RMAN.RC_BACKUP_SET s on s.set_stamp = d.set_stamp
and s.set_count = d.set_count
where
s.input_file_scan_only = 'NO'
group by
d.session_recid,
d.session_stamp,
s.db_key,
s.site_key
) x on x.session_recid = j.session_recid
and x.session_stamp = j.session_stamp,
rman.rc_site s
where
(
j.start_time > TRUNC(SYSDATE)-1
AND j.start_time < TRUNC(SYSDATE)
)
and s.db_key = x.db_key
and s.site_key = x.site_key
order by
j.start_time desc
)
where
status IN (
'FAILED', 'COMPLETED WITH ERRORS',
'RUNNING WITH ERRORS'
)
);
BEGIN
select
TO_CHAR(
SYSDATE, 'yyyy-mm-dd hh24:mi:ss'
) INTO v_repdate
from
dual;
select
TO_CHAR(SYSDATE, 'yyyy-mm-dd') INTO v_filename
from
dual;
IF v_report_type = 'G' THEN FOR V_REC IN V_REPORT_G LOOP v_attachment := v_attachment || V_REC.report_line || CHR(10);
END LOOP;
v_mail_status := 'SUCCESSFUL';
v_message_text := 'Review attachment reporting details of ' || v_mail_status || ' backups.';
END IF;
IF v_report_type = 'B' THEN FOR V_REC IN V_REPORT_B LOOP v_attachment := v_attachment || V_REC.report_line || CHR(10);
END LOOP;
v_mail_status := 'FAILED';
v_message_text := 'Review attachment report of ' || v_mail_status || ' backups. If there is no attachment, all backups are SUCCESSFUL.';
END IF;
UTL_MAIL.SEND_ATTACH_VARCHAR2(
sender => 'BACKUP-REPORT@domain.com',
recipients => 'DBA1@domain.com,DBA2@domain.com,USER1@domain.com,DistributionList1@domain.com',
cc => '',
bcc => '',
subject => 'Oracle ' || v_mail_status || ' backup report: ' || v_repdate,
message => v_message_text,
mime_type => 'text/plain; charset=us-ascii',
priority => null,
attachment => TO_CHAR(v_attachment),
att_inline => TRUE,
att_mime_type => 'text/plain; charset=us-ascii',
att_filename => v_filename || '-report.txt',
replyto => ''
);
END BACKUP_REPORT_DAILY;
Report output example
Below you will find an example of report output. The output is delivered as a text file attachment to an email. As per the below scheduling configuration, there will be two separate emails sent. One email will contain the successful backup runs and the second will contain failed runs. If there are no failed runs, email for failed backups arrives empty without attachment.
DB01 | DB01 | PRIMARY | 28039 | 1193695029 | 00:01:04 | COMPLETED WITH WARNINGS | DB INCR | 2025-02-21 21:57:31 | 2025-02-21 21:58:35
DB02 | DB02_DR | STANDBY | 19929 | 1193694956 | 00:24:48 | COMPLETED WITH WARNINGS | DB FULL | 2025-02-21 21:56:17 | 2025-02-21 22:21:05
DB03 | DB03 | PRIMARY | 17086 | 1193694603 | 00:06:21 | COMPLETED WITH WARNINGS | DB INCR | 2025-02-21 21:50:28 | 2025-02-21 21:56:49
19C | 19C_DR | STANDBY | 20214 | 1193694072 | 00:12:03 | COMPLETED WITH WARNINGS | DB INCR | 2025-02-21 21:41:41 | 2025-02-21 21:53:44
PROD | PROD_DR | STANDBY | 15314 | 1193693841 | 00:01:58 | COMPLETED WITH WARNINGS | DB INCR | 2025-02-21 21:37:48 | 2025-02-21 21:39:46
DATA | DATA | PRIMARY | 22222 | 1193691900 | 00:42:52 | COMPLETED WITH WARNINGS | DB INCR | 2025-02-21 21:05:34 | 2025-02-21 21:48:26
DEV | DEV_DR | STANDBY | 89343 | 1193691602 | 00:08:22 | COMPLETED WITH WARNINGS | DB INCR | 2025-02-21 21:01:17 | 2025-02-21 21:09:39
EBS | EBS_DR | STANDBY | 11360 | 1193691610 | 00:36:16 | COMPLETED | DB INCR | 2025-02-21 21:00:32 | 2025-02-21 21:36:48
TEST | TEST | PRIMARY | 72522 | 1193691609 | 00:03:59 | COMPLETED WITH WARNINGS | DB FULL | 2025-02-21 21:00:32 | 2025-02-21 21:04:31
QADB | QADB | PRIMARY | 8773 | 1193691602 | 00:02:21 | COMPLETED | DB INCR | 2025-02-21 21:00:11 | 2025-02-21 21:02:32
SANDBOX | SANDBOX | PRIMARY | 3141 | 1193684692 | 00:00:25 | COMPLETED WITH WARNINGS | DB INCR | 2025-02-21 19:04:58 | 2025-02-21 19:05:23
DEV21C | DEV21C | PRIMARY | 4575 | 1193684414 | 00:04:29 | COMPLETED WITH WARNINGS | DB INCR | 2025-02-21 19:00:19 | 2025-02-21 19:04:48
PRODCOPY | PRODCOPY | PRIMARY | 4600 | 1193684349 | 00:00:47 | COMPLETED WITH WARNINGS | DB INCR | 2025-02-21 18:59:17 | 2025-02-21 19:00:04
11GDB | 11GDB | PRIMARY | 4334 | 1193684204 | 00:02:14 | COMPLETED WITH WARNINGS | DB INCR | 2025-02-21 18:56:50 | 2025-02-21 18:59:04
SANDBOX | SANDBOX | PRIMARY | 13079 | 1193682736 | 00:24:16 | COMPLETED | DB FULL | 2025-02-21 18:32:24 | 2025-02-21 18:56:40
DB04 | DB04 | PRIMARY | 4256 | 1193682708 | 00:00:20 | COMPLETED | DB INCR | 2025-02-21 18:31:53 | 2025-02-21 18:32:13
DB100 | DB100 | PRIMARY | 15201 | 1193681211 | 00:24:39 | COMPLETED | DB FULL | 2025-02-21 18:06:59 | 2025-02-21 18:31:38
Limitations
I received a reasonable question regarding the report output format: is it possible to create HTML output?
The answer is yes—and no.
It solely depends on the amount of data that is reported. Let me explain in detail: the package UTL_MAIL has its limitations; the attachment size cannot exceed 32KB. Therefore, as HTML output considers lots of tagging characters, it can significantly increase the size of an attachment according to my tests. As a result, plain text output is more suitable. Newer versions of databases may have other limitations (my test case is built on version 19c.)
In addition, report output details can be modified as per individual needs. Therefore, in the case that the database list is short, HTML formatting may work well.
Scheduler setup for daily RMAN backup reports
The scheduler setup is documented below. Create a schedule according to your desired setup. Consider specifying attributes like job_name, start_date, repeat_interval, comments as per your own need.
Scheduler job for reporting failed backups for previous day
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"OWNER"."FAILED_RMAN_RUNS"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN OWNER.BACKUP_REPORT_DAILY(''B''); END;',
number_of_arguments => 0,
start_date => TO_TIMESTAMP_TZ('2024-02-07 00:05:00.000000000 -06:00','YYYY-MM-DD HH24:MI:SS.FF TZR'),
repeat_interval => 'freq=daily;byhour=00;byminute=30;bysecond=00',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Oracle database RMAN backup report - FAILED.');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"OWNER"." FAILED_RMAN_RUNS "',
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
DBMS_SCHEDULER.enable(
name => '"OWNER"." FAILED_RMAN_RUNS "');
END;
Scheduler job for reporting successful backups for previous day
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"OWNER"."GOOD_RMAN_RUNS"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN OWNER.BACKUP_REPORT_DAILY(''G''); END;',
number_of_arguments => 0,
start_date => TO_TIMESTAMP_TZ('2024-02-07 01:00:00.000000000 -06:00','YYYY-MM-DD HH24:MI:SS.FF TZR'),
repeat_interval => 'freq=daily;byhour=02;byminute=00;bysecond=00',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Oracle database RMAN backup report - SUCCESSFUL.');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"OWNER"."GOOD_RMAN_RUNS"',
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
DBMS_SCHEDULER.enable(
name => '"OWNER"."GOOD_RMAN_RUNS"');
END;
Conclusion
I hope the above material was useful and will help DBAs optimize their daily routine work.
The given solution can be used not only for backup reporting, but also be adopted for many different needs and use cases. In my case, it was essential to have a quick solution in place for better visibility over the database backups and to have a lightweight footprint of backup runs history.
Some of my additional concerns were:
- NOT TO spend a lot of time and effort on reorganizing Oracle Enterprise Manager setup and configuration that may result in a long initiative considering how big server fleet is.
- NOT TO introduce new instances of monitoring tools or software with underlying physical resources.
- Utilize existing resources and implement lightweight solutions to be able oversee the backup sector and have necessary input data for KPI reporting.
In the future, I’m planning on sharing some additional materials with a few tips on how to optimize daily operational work in database backup management.
Share this
You May Also Like
These Related Stories
No Comments Yet
Let us know what you think