Building a Backup and Disaster Recovery Readiness Checker with Oracle AI Database 26ai

Abdulrawof Totakhil — Sat, 07 Nov 2026 09:46:15 +0000

Hands-on SQL tutorial using Oracle FreeSQL, Analytic functions, Risk Readiness and JSON.

By Abdulrawof Totakhil

DBA | Oracle ACE Apprentice | Oracle Certified Professional

Introduction:

How can you quickly determine whether your backups and disaster recovery monitoring is in a state that needs attention?

Your backups could be out-of-date. Or you could have too much apply lag on your standby database. Or worst CASE, your monitoring is no longer providing any data.

Each of which should be handled differently.

I have created an original SQL Backup and Disaster Recovery Readiness Toolkit using Oracle AI Database 26ai and Oracle FreeSQL.

The aim is to create a small lab that will take the simulated monitoring data and provide a meaningful readiness status.

As this is using fictional data, you can follow along without needing access to actual systems.

Please note this is a learning project and will not connect to RMAN, Oracle Data Guard, perform any recovery actions or prove any disaster recovery capabilities.

Understanding the Problem

As a DBA, you should ask yourself.

Do you have a successful backup? Is it out of date? Is your apply lag on standby within the acceptable limits? Are your monitoring samples recent enough?

You should not just receive a green report.

For example, if your monitoring collector last reported a 5 minute apply lag but it has been several hours since it was reported. Although the apply lag was an acceptable figure, it is no longer recent.

This is why I have created this project to ensure we are getting the right status and information.

Lab Design

I have designed 3 tables.

Table Description

ACE26_DR_ASSETS_P7 Fictional assets with monitoring policies.

ACE26_DR_BACKUPS_P7 Simulated successful and failed backups.

ACE26_DR_APPLY_P7 Simulated apply lag, transport lag and sample times.

There are 7 fictional assets in our lab (DEMO_A to DEMO_G).

We have added 9 synthetic backups and 7 replication monitoring samples.

These tables are used to create a view ACE26_DR_HEALTH_P7, which will provide us with the readiness statuses. We can then use this in a dashboard query, risk summary, DBA action list and JSON.

All data has been created for this learning project.

Monitoring Limits

For this demonstration, we have set the following example limits.

Metric Example Limit

Successful Backup Age 24 Hours

Standby Apply Lag 15 Minutes

Monitoring Sample Age 30 Minutes

These are for our lab only. In production you should decide the monitoring limits based on your business recovery requirements, database design, workloads, processes and risk.

Most Recent Successful Backup

One of the key parts of this design is finding the most recent successful backup.

I used Oracle SQL Analytic function ROW_NUMBER().


SELECT

asset_id,

backup_id,

backup_end_time,

ROW_NUMBER() OVER (

PARTITION BY asset_id

ORDER BY backup_end_time DESC, backup_id DESC

) AS backup_rank

FROM ace26_dr_backups_p7

WHERE backup_status = 'SUCCESS';

We use PARTITION BY asset_id to separate the backups BY asset. ORDER BY backup_end_time DESC will ORDER the most recent backup first. Finally we have backup_id DESC to tie break.

The ROW_NUMBER() function then ranks the most recent successful backup as 1.

In our full view we can select backup_rank = 1 to get this record.

We only want to get successful records so we can filter on this.

There is also a limitation to this. We are only finding the most recent successful backup, meaning we won’t automatically be alerted to any failed backups newer than this. In the future it would be good to monitor the most recent backup job result.

Readiness Status

I have created 7 readiness statuses.

Asset Readiness Priority

DEMO_A HEALTHY 3

DEMO_B BACKUP_STALE 2

DEMO_C APPLY_LAG_BREACH 2

DEMO_D MULTIPLE_BREACHES 1

DEMO_E NO_SUCCESSFUL_BACKUP 1

DEMO_F NO_REPLICATION_SAMPLE 1

DEMO_G STALE_METRICS 1

Priority 1 means we need to urgently investigate based on our fictional policies. Priority 2 means there is an issue to investigate. Priority 3 means everything is ok.

The ORDER is important.

If there is no successful backup, we will get this in our report. We will not check if the replication samples are acceptable.

If there is a stale sample, we will get STALE_METRICS.

These were created using CASE statements in Oracle SQL.

DBA Actions

Not only is it useful to know there is an issue but we can make our report more useful BY providing recommendations.

I have created a SQL action list to provide recommendations based on the readiness status.


SELECT

asset_tag,

risk_status,

priority_rank,

CASE risk_status

WHEN 'BACKUP_STALE'

THEN 'Review backup schedule and latest successful backup'

WHEN 'APPLY_LAG_BREACH'

THEN 'Investigate archive transport, apply process and network conditions'

WHEN 'STALE_METRICS'

THEN 'Restore monitoring collection and verify data freshness'

WHEN 'HEALTHY'

THEN 'Continue routine monitoring'

ELSE 'Manual investigation required'

END AS recommended_action

FROM ace26_dr_health_p7

ORDER BY priority_rank, asset_tag;

Below is an example of the full action list.

There are additional recommendations for no backups, no samples and multiple breaches.

The idea is to take the monitoring data and provide a plan for investigation.

These are just examples and should not replace any DBA diagnosis or approved processes.

JSON Readiness Report

I also wanted to show how we can convert relational SQL data into structured JSON.

Oracle provides SQL/JSON functions such as JSON_OBJECT and JSON_ARRAYAGG.

Using these, we can create a report showing each asset status, priority, backup age, apply lag, transport lag and sample age.


SELECT JSON_ARRAYAGG(

JSON_OBJECT(

'assetTag' VALUE asset_tag,

'riskStatus' VALUE risk_status,

'priority' VALUE priority_rank,

'backupAgeHours' VALUE backup_age_hours,

'applyLagMinutes' VALUE apply_lag_minutes,

'transportLagMinutes' VALUE transport_lag_minutes,

'sampleAgeMinutes' VALUE sample_age_minutes

)

ORDER BY priority_rank, asset_tag

RETURNING CLOB

) AS dr_readiness_json

FROM ace26_dr_health_p7;

This will produce a JSON array showing our current readiness status.

This shows how Oracle SQL can be used to format data for external reporting. Using the RETURNING CLOB clause means that we can return character large object data.

Testing

On 30th October 2026 I ran the main demonstration in Oracle FreeSQL.

This involved creating tables, inserting synthetic data, creating readiness view, dashboard report, action recommendations, JSON and final verification.

We got the following results.

Verification Outcome

Total Fictional Assets 7

Assets to Investigate 6

Validation Errors 0

Final SQL Verification PASS

This verified that we had the right amount of assets, unique asset IDs and that the readiness statuses matched the priority.

This returned a PASS.

However, this only verifies that the SQL checks for readiness statuses are correct. This does not prove that a real database is recoverable or that Data Guard is available.

Evidence of the run can be seen in the public GitHub repo.

Key Takeaways
A successful backup is not the same as a successful restore.

Whilst monitoring backups is useful, we need to actually test restores.

Monitoring samples should be recent.

Old monitoring samples can give a false sense of security. You should consider how fresh your data is.

Your Readiness Report should have clear priorities.

Operational teams need to know what to investigate.

You can use SQL for decision making monitoring.

Using analytic functions, CASE statements, aggregations and JSON we can create a structured operational report from relational data.

Be careful with time based checks.

Our lab uses LOCALTIMESTAMP. As we are calculating how old the monitoring samples are during the query run, some statuses will change over time.

We noticed the 7 original statuses shortly after inserting the synthetic data. If we were to run the report again later, we may see different results. This is expected.

Limitations / Future Improvements

This has been kept simple and safe for learning.

This does not validate RMAN restores, connect to Oracle Data Guard, check the status of Data Guard Broker, measure true RPO/RTO or failover.

Future improvements could include tracking the most recent failed backup job, adding trend analysis, additional transport lag policies and monitoring adapter for an authorised test environment.

If deployed in reality you would need to ensure security was reviewed, data was collected, limits were set for your environment and recovery tested.

Source Code

The full project can be found on GitHub.

Oracle AI Database 26ai Backup and DR Readiness SQL Toolkit

This contains the SQL scripts, sample data, testing files, documentation and original Oracle FreeSQL screenshots.

You can recreate our lab using the instructions in the README file using a disposable Oracle FreeSQL 26ai schema.

Conclusion

Creating this project has allowed me to look into how we can use Oracle SQL to check simulated backup and disaster recovery monitoring.

The main takeaway is that you need to consider more than if there is monitoring data. You should consider if it is recent, if there are any breaches, missing data and how important each issue is to operations.

I hope this example helps other Oracle DBA’s and learners look into monitoring using SQL.

Any technical feedback or suggestions are welcome.

References:

Oracle SQL ROW_NUMBER() Documentation

https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ROW_NUMBER.html

Oracle SQL/JSON Generation Documentation

https://docs.oracle.com/en/database/oracle/oracle-database/26/adjsn/overview-JSON-generation.html

GitHub Repo

https://github.com/abdulrawof-totakhil/oracle-26ai-DR-readiness-toolkit

By Abdulrawof Totakhil

Oracle ACE Apprentice | Oracle Certified Professional

DEV Community: Abdulrawof Totakhil

Building a Backup and Disaster Recovery Readiness Checker with Oracle AI Database 26ai