Let’s talk about physical backups and how to get a report based on your own “safety criteria” about the successfull backups needed to ensure a safe recovery scenario.
In many places, they get reports when backups end successfully, or sometimes only when backup fails, and if everything works as expected, you probably don’t need anything else.
But, what happens if you want to get a daily report of your databases backups status, and score the impact of backups needed to perform a recovery.
For example, as long as you have the archivelogs placed in the host, and a valid backup to apply them, you shouldn’t worry too much if an archivelog backup failed.
Imagine you have dozens of databases, and some backups fail often.
Well, then this “Daily Backup Report” may be really useful to qualify how risky your environments are.
And so, if everything is fine, you will have a “Backup Report” ready made with a green pie graph!
This is a set of two queries I use to show up the status of the backups.
I took the initial query from Gavin Soorma’s website and improve it to produce a detailed report for every database in the RMAN catalog. Here is the original query:
The idea is to use Oracle Analytics Publisher to create a report and use the two scripts as source.
Here is the link to the GitHub repository containing the code. Please feel free to use it, copy it, and even improve it if you like!
https://github.com/CafeDatabase/Backup-Report
With the “backup-report.sql” you get a detailed list of the last successfull backup of every type.
With the “backup-report-graph.sql” you get the total of ‘Warning/Ok!/Critical’ databases.
As you may see, the criteria may be different for each script, so I use the two criterias I consider clearer to understand the backup situation of the databases.
The idea is getting the last DB Full backup, the last archivelogs backup, the last incremental level 0 and incremental level 1, and then stablish a scoring based on the ages you consider reasonable in your environment.
So, you have to run those scripts in the RMAN catalog database, or in the OEM repository database (in which case you need to create a dblink to the catalog owner of the RMAN repository).
Here are the steps:
1- Download all files in the OMS repository database or the RMAN catalog database.
backup-report.sql
backup-report-graph.sql
2- If you’re running those scripts in the OEM repository, you should have a database link pointing to the RMAN catalog owner like this:
SQL> CREATE DATABASE LINK RMAN CONNECT TO <rman_catalog_owner> IDENTIFIED BY <password> USING '<RMAN_CONNECTION_STRING>';
NOTE: If you have issues, you should ensure you can reach the RMAN catalog like this:
sqlplus <rman_catalog_owner>/<password>@<RMAN_CONNECTION_STRING>
3- Run the scripts in sqlplus and check the results.
4- Now you can use those SQL scripts as data sources for your Oracle Analytics Publisher, create a report like this one and send it to you by mail daily!.
Enjoy!
