Share this
by Uldis Augulis on Mar 7, 2025 8:39:54 AM
The blog post is an addition to my earlier blog post Automating Oracle RMAN Backup Reports with PL/SQL Using the RMAN Catalog providing few additional ideas on how to optimize daily work on managing and monitoring backups of Oracle database fleet.
Case details
The use case is pretty much the same as in my previous blog (referenced above):
- 100+ Oracle Database instances running on different versions
- Fleet divided into production / test / development / training databases
- Some number of production databases has DR sites
- Oracle Enterprise Manager is in use but there are some restrictions that OEM introduces:
- Blackouts—during blackouts monitoring targets under blackout not reported by OEM
- OEM agents may be down or hanging what can cause missing actual data
- The key point is that Oracle RMAN Catalog is in use and is used as the main data source.
Disclaimer
Setup and code below can be modified according to your wishes and needs.
Evaluate, use, optimize, modify, finetune the code according to configuration, performance, or any other specifics of your environment.
Views representing failed runs
Below is code to create a view based on RMAN Catalog dictionary to reflect all failed backups ('FAILED', 'COMPLETED WITH ERRORS') from PREVIOUS MONTH. You can include, exclude or reorder columns as needed as well as join other sources to present more data.
CREATE
OR
REPLACE FORCE EDITIONABLE VIEW "OWNER"."RMAN_FAILURE_PREVMONTH" ("DB_UNIQUE_NAME", "DATABASE_ROLE", "INC_LEVEL", "DB_KEY", "DB_NAME", "SESSION_KEY", "SESSION_RECID", "SESSION_STAMP", "COMMAND_ID", "START_TIME", "END_TIME", "INPUT_BYTES", "OUTPUT_BYTES", "STATUS_WEIGHT", "OPTIMIZED_WEIGHT", "INPUT_TYPE_WEIGHT", "OUTPUT_DEVICE_TYPE", "AUTOBACKUP_COUNT", "BACKED_BY_OSB", "AUTOBACKUP_DONE", "STATUS", "INPUT_TYPE", "OPTIMIZED", "ELAPSED_SECONDS", "COMPRESSION_RATIO", "INPUT_BYTES_PER_SEC", "OUTPUT_BYTES_PER_SEC", "INPUT_BYTES_DISPLAY", "OUTPUT_BYTES_DISPLAY", "INPUT_BYTES_PER_SEC_DISPLAY", "OUTPUT_BYTES_PER_SEC_DISPLAY", "TIME_TAKEN_DISPLAY") AS
SELECT s.db_unique_name,
s.database_role,
'DB LVL'||x.incremental_level AS inc_level,
j."DB_KEY",
j."DB_NAME",
j."SESSION_KEY",
j."SESSION_RECID",
j."SESSION_STAMP",
j."COMMAND_ID",
j."START_TIME",
j."END_TIME",
j."INPUT_BYTES",
j."OUTPUT_BYTES",
j."STATUS_WEIGHT",
j."OPTIMIZED_WEIGHT",
j."INPUT_TYPE_WEIGHT",
j."OUTPUT_DEVICE_TYPE",
j."AUTOBACKUP_COUNT",
j."BACKED_BY_OSB",
j."AUTOBACKUP_DONE",
j."STATUS",
j."INPUT_TYPE",
j."OPTIMIZED",
j."ELAPSED_SECONDS",
j."COMPRESSION_RATIO",
j."INPUT_BYTES_PER_SEC",
j."OUTPUT_BYTES_PER_SEC",
j."INPUT_BYTES_DISPLAY",
j."OUTPUT_BYTES_DISPLAY",
j."INPUT_BYTES_PER_SEC_DISPLAY",
j."OUTPUT_BYTES_PER_SEC_DISPLAY",
j."TIME_TAKEN_DISPLAY"
FROM rman.rc_rman_backup_job_details j
left outer join
(
SELECT s.db_key,
s.site_key,
d.incremental_level,
d.session_recid,
d.session_stamp
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'
AND s.backup_type = 'I'
GROUP BY d.session_recid,
d.session_stamp,
s.db_key,
s.site_key,
d.incremental_level) x
ON x.session_recid = j.session_recid
AND x.session_stamp = j.session_stamp,
rman.rc_site s
WHERE (
j.start_time >= add_months(trunc(SYSDATE,'mm'), -1)
AND j.start_time < trunc(SYSDATE,'MM'))
AND j.status IN ('FAILED',
'COMPLETED WITH ERRORS',
'RUNNING WITH ERRORS')
AND s.db_key = x.db_key
AND s.site_key = x.site_key
ORDER BY j.start_time DESC;
Same view for failures for CURRENT MONTH.
CREATE
OR
REPLACE FORCE EDITIONABLE VIEW "OWNER"."RMAN_FAILURE_THISMONTH" ("DB_UNIQUE_NAME", "DATABASE_ROLE", "INC_LEVEL", "DB_KEY", "DB_NAME", "SESSION_KEY", "SESSION_RECID", "SESSION_STAMP", "COMMAND_ID", "START_TIME", "END_TIME", "INPUT_BYTES", "OUTPUT_BYTES", "STATUS_WEIGHT", "OPTIMIZED_WEIGHT", "INPUT_TYPE_WEIGHT", "OUTPUT_DEVICE_TYPE", "AUTOBACKUP_COUNT", "BACKED_BY_OSB", "AUTOBACKUP_DONE", "STATUS", "INPUT_TYPE", "OPTIMIZED", "ELAPSED_SECONDS", "COMPRESSION_RATIO", "INPUT_BYTES_PER_SEC", "OUTPUT_BYTES_PER_SEC", "INPUT_BYTES_DISPLAY", "OUTPUT_BYTES_DISPLAY", "INPUT_BYTES_PER_SEC_DISPLAY", "OUTPUT_BYTES_PER_SEC_DISPLAY", "TIME_TAKEN_DISPLAY") AS
SELECT s.db_unique_name,
s.database_role,
'DB LVL'||x.incremental_level AS inc_level,
j."DB_KEY",
j."DB_NAME",
j."SESSION_KEY",
j."SESSION_RECID",
j."SESSION_STAMP",
j."COMMAND_ID",
j."START_TIME",
j."END_TIME",
j."INPUT_BYTES",
j."OUTPUT_BYTES",
j."STATUS_WEIGHT",
j."OPTIMIZED_WEIGHT",
j."INPUT_TYPE_WEIGHT",
j."OUTPUT_DEVICE_TYPE",
j."AUTOBACKUP_COUNT",
j."BACKED_BY_OSB",
j."AUTOBACKUP_DONE",
j."STATUS",
j."INPUT_TYPE",
j."OPTIMIZED",
j."ELAPSED_SECONDS",
j."COMPRESSION_RATIO",
j."INPUT_BYTES_PER_SEC",
j."OUTPUT_BYTES_PER_SEC",
j."INPUT_BYTES_DISPLAY",
j."OUTPUT_BYTES_DISPLAY",
j."INPUT_BYTES_PER_SEC_DISPLAY",
j."OUTPUT_BYTES_PER_SEC_DISPLAY",
j."TIME_TAKEN_DISPLAY"
FROM rman.rc_rman_backup_job_details j
left outer join
(
SELECT s.db_key,
s.site_key,
d.incremental_level,
d.session_recid,
d.session_stamp
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'
AND s.backup_type = 'I'
GROUP BY d.session_recid,
d.session_stamp,
s.db_key,
s.site_key,
d.incremental_level) 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,'MM')
AND j.status IN ('FAILED',
'COMPLETED WITH ERRORS',
'RUNNING WITH ERRORS')
AND s.db_key = x.db_key
AND s.site_key = x.site_key
ORDER BY j.start_time DESC;
Create several views representing time intervals as per your needs.
While writing this blog, I started thinking about the following enhancements that can be introduced.
For example:
- Automate creation of view. Use code to specify what:
- Columns should be retrieved.
- Time interval should be covered.
- Ordering to apply for data retrieved.
- Grants to be applied to control access to data.
- Use DBMS scheduler to run view creation code where attributes and parameters can be used.
- Use APEX to add visual representation of data.
Retrieve data from views created above. Ensure necessary grants are provisioned.
SELECT * FROM OWNER.RMAN_FAILURE_THISMONTH;
SELECT * FROM OWNER.RMAN_FAILURE_PREVMONTH;
Output example provided below:
Function to retrieve failed run details
Below is code to create a function based on RMAN Catalog dictionary to retrieve RMAN backup output for backup run.
The database key, as well as session key, should be provided as input parameters. The database key and session key can be retrieved from the views described earlier in this blog post.
CREATE OR REPLACE FUNCTION OWNER.rman_output(v_db_key NUMBER, v_session_key NUMBER)
RETURN CLOB sql_macro AS stmt clob;
BEGIN
stmt:=
'select output from RMAN.RC_RMAN_OUTPUT where db_key = v_db_key and session_key = v_session_key order by recid';
RETURN stmt;
END rman_output;
Retrieve data from views created above. Ensure necessary grants are provisioned.
SET PAGESIZE 100
SELECT * FROM OWNER.RMAN_OUTPUT(DB_KEY, SESSION_KEY);
Output example provided below. I have intentionally trimmed output to avoid pasting blankets of text but provided enough to understand what output will look like.
OUTPUT
connected to target database: DB01 (DBID=823212874)
connected to recovery catalog database
recovery catalog schema version 19.23.00.00. is newer than RMAN version
sql statement: alter system set _backup_disk_bufcnt=64 scope=memory
sql statement: alter system set _backup_disk_bufsz=1048576 scope=memory
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/PATH/backup1/DB01/%d_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/PATH/backup1/DB01/%d_%F';
new RMAN configuration parameters are successfully stored
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters are successfully stored
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECO/DB01/snapcf_DB01.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECO/DB01/snapcf_DB01.f';
new RMAN configuration parameters are successfully stored
allocated channel: ch01
channel ch01: SID=346 device type=DISK
allocated channel: ch02
channel ch02: SID=60 device type=DISK
allocated channel: ch03
channel ch03: SID=348 device type=DISK
allocated channel: ch04
channel ch04: SID=58 device type=DISK
allocated channel: ch05
channel ch05: SID=376 device type=DISK
allocated channel: ch06
channel ch06: SID=57 device type=DISK
executing command: SET COMMAND ID
Starting backup at 21-FEB-25
channel ch01: starting incremental level 0 datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/DB01/DATAFILE/gd_data.573.1106664573
backing up blocks 1 through 4194304
channel ch01: starting piece 1 at 21-FEB-25
channel ch02: starting incremental level 0 datafile backup set
channel ch02: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/DB01/DATAFILE/ed_data.564.1106664563
........
........
........
channel ch01: backup set complete, elapsed time: 00:02:58
channel ch01: starting incremental level 0 datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00013 name=+DATA/DB01/DATAFILE/sysaux.571.1106664571
input datafile file number=00010 name=+DATA/DB01/DATAFILE/ref_data.570.1106664571
input datafile file number=00012 name=+DATA/DB01/DATAFILE/process_data.574.1106664573
channel ch01: starting piece 1 at 21-FEB-25
channel ch05: finished piece 4 at 21-FEB-25
piece handle=/PATH/backup1/DB01/DB01_np3iadt7_66297_4_1 tag=DB01_21FEB25 comment=NONE
channel ch05: backup set complete, elapsed time: 00:01:32
channel ch05: starting incremental level 0 datafile backup set
channel ch05: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATA/DB01/DATAFILE/bd_data.569.1106664571
input datafile file number=00001 name=+DATA/DB01/DATAFILE/system.563.1106664563
input datafile file number=00007 name=+DATA/DB01/DATAFILE/users.568.1106664569
channel ch05: starting piece 1 at 21-FEB-25
channel ch03: finished piece 5 at 21-FEB-25
piece handle=/PATH/backup1/DB01/DB01_np3iadt7_66297_5_1 tag=DB01_21FEB25 comment=NONE
channel ch03: backup set complete, elapsed time: 00:01:12
channel ch05: finished piece 1 at 21-FEB-25
piece handle=/PATH/backup1/DB01/DB01_p93iagec_66345_1_1 tag=DB01_21FEB25 comment=NONE
channel ch05: backup set complete, elapsed time: 00:01:05
channel ch01: finished piece 1 at 21-FEB-25
piece handle=/PATH/backup1/DB01/DB01_p83iagec_66344_1_1 tag=DB01_21FEB25 comment=NONE
channel ch01: backup set complete, elapsed time: 00:01:25
channel ch02: finished piece 1 at 21-FEB-25
piece handle=/PATH/backup2/DB01/DB01_no3iadt7_66296_1_1 tag=DB01_21FEB25 comment=NONE
channel ch02: backup set complete, elapsed time: 01:51:53
channel ch04: finished piece 1 at 21-FEB-25
piece handle=/PATH/backup2/DB01/DB01_nq3iadt7_66298_1_1 tag=DB01_21FEB25 comment=NONE
channel ch04: backup set complete, elapsed time: 01:51:53
channel ch06: finished piece 1 at 21-FEB-25
piece handle=/PATH/backup2/DB01/DB01_ns3iadt7_66300_1_1 tag=DB01_21FEB25 comment=NONE
channel ch06: backup set complete, elapsed time: 01:51:53
Finished backup at 21-FEB-25
Starting backup at 21-FEB-25
RMAN-06820: warning: failed to archive current log at primary database
cannot connect to remote database
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=63157 RECID=2011 STAMP=1193580212
input archived log thread=1 sequence=63158 RECID=2013 STAMP=1193582371
input archived log thread=1 sequence=63159 RECID=2015 STAMP=1193584813
input archived log thread=1 sequence=63160 RECID=2017 STAMP=1193587337
input archived log thread=1 sequence=63161 RECID=2019 STAMP=1193588841
input archived log thread=1 sequence=63162 RECID=2021 STAMP=1193590498
input archived log thread=1 sequence=63163 RECID=2023 STAMP=1193592241
channel ch01: starting piece 1 at 21-FEB-25
channel ch02: starting archived log backup set
channel ch02: specifying archived log(s) in backup set
........
........
channel ch05: starting piece 1 at 21-FEB-25
channel ch06: starting archived log backup set
channel ch06: specifying archived log(s) in backup set
input archived log thread=1 sequence=63185 RECID=2067 STAMP=1193625311
input archived log thread=1 sequence=63186 RECID=2069 STAMP=1193625583
input archived log thread=1 sequence=63187 RECID=2071 STAMP=1193625785
input archived log thread=1 sequence=63188 RECID=2073 STAMP=1193627244
channel ch06: starting piece 1 at 21-FEB-25
channel ch05: finished piece 1 at 21-FEB-25
piece handle=/PATH/backup1/DB01/DB01_pe3iakf8_66350_1_1 tag=DB01_21FEB25 comment=NONE
channel ch05: backup set complete, elapsed time: 00:02:05
channel ch05: deleting archived log(s)
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63164.268.1193593761 RECID=2025 STAMP=1193593814
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63164.2362.1193593761 RECID=2024 STAMP=1193593814
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63165.2658.1193596743 RECID=2027 STAMP=1193596798
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63165.2961.1193596743 RECID=2026 STAMP=1193596798
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63166.3178.1193597079 RECID=2031 STAMP=1193597085
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63166.506.1193597079 RECID=2030 STAMP=1193597085
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63167.3102.1193597081 RECID=2029 STAMP=1193597081
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63167.2292.1193597081 RECID=2028 STAMP=1193597081
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63168.2197.1193598709 RECID=2033 STAMP=1193598769
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63168.480.1193598709 RECID=2032 STAMP=1193598769
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63169.3242.1193600283 RECID=2035 STAMP=1193600352
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63169.2914.1193600283 RECID=2034 STAMP=1193600352
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63170.2289.1193601643 RECID=2037 STAMP=1193601704
archived log file name=+RECO/DB01/ARCHIVELOG/2025_02_20/thread_1_seq_63170.3073.1193601643 RECID=2036 STAMP=1193601704
........
........
ERROR section we needed
=> RMAN-03009: failure of backup command on ch06 channel at 02/21/2025 06:09:24
=> ORA-19510: failed to set size of 30454283 blocks for file /PATH/backup2/DB01/DB01_pf3iakf8_66351_1_1 (bl
=> ORA-27059: could not reduce file size
=> Linux-x86_64 Error: 1: Operation not permitted
=> Additional information: 2
channel ch06 disabled, job failed on it will be run on another channel
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=63185 RECID=2067 STAMP=1193625311
input archived log thread=1 sequence=63186 RECID=2069 STAMP=1193625583
input archived log thread=1 sequence=63187 RECID=2071 STAMP=1193625785
input archived log thread=1 sequence=63188 RECID=2073 STAMP=1193627244
channel ch01: starting piece 1 at 21-FEB-25
channel ch01: finished piece 1 at 21-FEB-25
piece handle=/PATH/backup1/DB01/DB01_pg3iau8k_66352_1_1 tag=DB01_21FEB25 comment=NONE
channel ch01: backup set complete, elapsed time: 00:01:05
channel ch01: deleting archived log(s)
........
........
Finished backup at 21-FEB-25
Starting Control File and SPFILE Autobackup at 21-FEB-25
piece handle=/PATH/backup1/DB01/DB01_c-823212874-20250221-00 comment=NONE
Finished Control File and SPFILE Autobackup at 21-FEB-25
specification does not match any backup in the repository
specification does not match any archived log in the repository
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 61 days
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 489884004 21-DEC-24
Backup Piece 489884048 21-DEC-24 /PATH/backup1/DB01/DB01_n33d6umh_58083_34_1
Backup Set 489884004 21-DEC-24
Backup Piece 489884049 21-DEC-24 /PATH/backup2/DB01/DB01_n33d6umh_58083_33_1
Backup Set 489884004 21-DEC-24
Backup Piece 489884051 21-DEC-24 /PATH/backup1/DB01/DB01_n33d6umh_58083_32_1
Backup Set 489884004 21-DEC-24
Backup Piece 489884052 21-DEC-24 /PATH/backup2/DB01/DB01_n33d6umh_58083_31_1
........
........
backup piece handle=/PATH/backup2/DB01/DB01_n53d6umh_58085_5_1 RECID=57995 STAMP=1188265457
deleted backup piece
backup piece handle=/PATH/backup2/DB01/DB01_n33d6umh_58083_22_1 RECID=57973 STAMP=1188265179
deleted backup piece
backup piece handle=/PATH/backup2/DB01/DB01_n43d6umh_58084_4_1 RECID=57989 STAMP=1188265392
deleted backup piece
backup piece handle=/PATH/backup2/DB01/DB01_n43d6umh_58084_2_1 RECID=57986 STAMP=1188265336
deleted backup piece
backup piece handle=/PATH/backup2/DB01/DB01_n83d6umh_58088_1_1 RECID=57964 STAMP=1188264669
deleted backup piece
backup piece handle=/PATH/backup2/DB01/DB01_n43d6umh_58084_6_1 RECID=57990 STAMP=1188265407
deleted backup piece
backup piece handle=/PATH/backup2/DB01/DB01_n43d6umh_58084_1_1 RECID=57963 STAMP=1188264670
Deleted 23 objects
deleted backup piece
backup piece handle=/PATH/backup1/DB01/DB01_ok3d6vg0_58132_1_1 RECID=57997 STAMP=1188265472
Deleted 5 objects
deleted backup piece
backup piece handle=/PATH/backup1/DB01/DB01_c-823212874-20241221-00 RECID=58003 STAMP=1188265647
Deleted 9 objects
deleted backup piece
backup piece handle=/PATH/backup2/DB01/DB01_ol3d6vg0_58133_1_1 RECID=57996 STAMP=1188265472
Deleted 6 objects
deleted backup piece
backup piece handle=/PATH/backup2/DB01/DB01_n63d6umh_58086_1_1 RECID=57965 STAMP=1188264670
Deleted 9 objects
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 61 days
no obsolete backups found
released channel: ch01
released channel: ch02
released channel: ch03
released channel: ch04
released channel: ch05
released channel: ch06
Below statement is needed to enable run function from another user than owner.
GRANT INHERIT PRIVILEGES ON USER OWNER TO ANOTHERUSER;
This is important to remember that retention of data in RC_RMAN_OUTPUT is controlled by a statement where n is the number of day's output needs to be preserved.
CONFIGURE RMAN OUTPUT TO KEEP FOR n DAYS;
I would suggest configuring the output to be the same as the retention policy of the recovery window. By doing that, you ensure that you have backup run details and can troubleshoot failed backups within time of recovery window.
Conclusion
I hope those small tips described above will help you organize daily routine work more efficiently.
The case doesn't matter: automation has not been implemented so far. No Oracle Enterprise Manager or any other monitoring solution has been used; there is a need for customized data representation for reporting, KPI data, management reports—simple solutions deliver quick time to market result.
Provided solutions can be modified to do more than just report failures of backups. It can easily be converted to report time taken by backups, which may indicate potential performance problems, or to report the amount of data backed up that may help to forecast storage capacity needs.
No Comments Yet
Let us know what you think