In the SQL Server world, there is a stored procedure called sp_BlitzBackups from the "First Responder Kit" that helps out a database administrator to compute a Recovery Time Objective and a Recovery Point Objectif from database backup history (in summary).

I was wondering if a stored procedure of the same kind would eventually exist and if not, what would be the base tables which I could rely to ?

  • I haven't looked into it, but I thought setting the RPO was as simple as setting an RMAN parameter Feb 25, 2022 at 15:16
  • Well, the point is to get an auto-computed estimation of the RTO and RPO and to check if, in reality, these RMAN settings are in agree with what is asked/defined. It's a means of control. Feb 25, 2022 at 16:50

1 Answer 1


I don't know of any Oracle facility that can estimate RTO. I use the timing of drills (GB of datafile backup per min and GB of archivelog backup per min) to estimate .

For RPO, it depends on your assumption what redo information is lost.

Redo is like what is called Transaction Log or Write Ahead Log in other DBMS. Oracle cycles through several redo log files. When an online redo log is full, or when Oracle likes to, or when Oracle is instructed to, Oracle makes a copy of the redo log, which is called archivelog (abbreviation of archived redo log). The archivelogs are then backed up by DBA.

So the data lost depends on what redo information is lost:

  1. If online redo logs + all backups are intact, then is no data lost
  2. If online redo logs are lost, but archive logs + all backups are intact, the data lost is up to the latest archive log.
  3. If online and archived redo logs are lost, but all backups are intact, the data lost is up to the latest backed up archive log.

The data loss can be estimated with the following SQL for cases 2 and 3:

select nvl2(name, 'N', 'Y') backed_up, max(next_time) data_lost_since
from v$archived_log
group by nvl2(name, 'N', 'Y');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.