sp_BlitzBackups alternative for Oracle Database



  • In the SQL Server world, there is a stored procedure called sp_BlitzBackups from the " https://github.com/zmking888/SQL-Server-First-Responder-Kit/blob/master/sp_BlitzBackups.sql " 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 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');
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2