How do you fully automate Oracle PSU/OJVM patching + PostgreSQL minor/major upgrades across 100+ instances with < 60 sec downtime?
How do you manage and rotate all secrets (wallets, passwords, certs) in GitOps/CI/CD environments?
Top 3 alerts that have saved you in production (exact metric + threshold) for each DB.
Worst database-related outage you’ve led — detection time, root cause, and the one change that prevented recurrence.
How do you tune a slow PostgreSQL query when indexes exist but seq scans dominate?
Oracle: Key differences between UNDO and REDO; how do you right-size them for OLTP?
PostgreSQL physical vs logical replication — when do you pick which?
Fastest way to diagnose and fix ORA-04031 (shared pool) in production?
How do you prevent and monitor table/index bloat in large PostgreSQL instances?
Design <15 min RTO / <5 min RPO for a 10 TB PostgreSQL cluster (your architecture).
Oracle Data Guard: trade-offs between Max Performance, Availability, and Protection modes?
Primary PostgreSQL dies and standby won’t promote — your exact checklist at 3 AM.
How would you do a zero-downtime Oracle → PostgreSQL migration (or reverse)?
Top 5 SLO/SLI metrics you define for a critical database tier.
How do you manage config for 100+ Oracle/PostgreSQL instances with zero drift?
Show/describe your Terraform + operator setup for PostgreSQL on Kubernetes.
How do you automate quarterly Oracle PSU + OJVM patching with near-zero downtime?
Must-have alerts for PostgreSQL and Oracle (name the queries/metrics).
Sudden spike in Oracle “cache buffers chains” latch — how do you debug live?
Worst database outage you’ve handled — root cause and postmortem actions?
You just ran DROP TABLE in prod (no WHERE). Recovery steps and timeline?
How do you store and rotate DB credentials + TDE wallets in an SRE world?
As an SRE, are you okay with devs running ALTER TABLE via CI/CD? Why/why not?
Your DB SLO is 99.95% and one incident just burned 50% of the monthly error budget — what now?
How do you approach tuning a slow query in PostgreSQL when EXPLAIN ANALYZE shows high sequential scan costs but indexes seem to exist?
In Oracle, what are the key differences between undoing with UNDO tablespace vs. redoing with REDO logs? How would you size them for a high-transaction OLTP system?
Compare PostgreSQL logical replication vs. physical replication (streaming/WAL). When would you choose one over the other in an SRE context?
What are the most common causes of ORA-04031 (shared pool memory) errors and how do you diagnose and resolve them in production without downtime?
How does PostgreSQL handle VACUUM vs. AUTOVACUUM? What can go wrong at scale and how do you monitor bloat?
5. Reliability & High Availability
You need to achieve < 5 min RPO and < 15 min RTO for a 10 TB PostgreSQL database with almost zero downtime allowed. Walk us through your architecture.
How would you design Oracle Data Guard in “Maximum Performance” vs. “Maximum Availability” vs. “Maximum Protection” modes? Trade-offs from an SRE perspective?
You get paged at 3 AM: primary PostgreSQL node is down and the standby is not promoting. What’s your checklist?
Describe how you would implement zero-downtime migrations from Oracle 12c/19c to PostgreSQL (or vice-versa) in production.
What SRE metrics (SLOs/SLIs/error budgets) would you define for a mission-critical database tier?
6. Automation & Infrastructure as Code
How do you manage PostgreSQL/Oracle configuration at scale (100+ instances) with zero manual drift? (Expect Ansible, Terraform + Vault, Chef/Puppet, etc.)
Show me an example (or describe) how you use Terraform + Helm/operator to provision and manage PostgreSQL on Kubernetes (or Oracle on OCI/EC2).
How would you automate Oracle patching (PSU/BP + OJVM) with zero or near-zero downtime across a fleet?
7. Observability & Monitoring
What are your must-have metrics and alerts for PostgreSQL and Oracle in production? (e.g., pg_stat_activity long-running queries, Oracle AWR top wait events, connection pool exhaustion, archive lag, etc.)
How do you set up centralized logging and tracing for both PostgreSQL (pg_stat_statements, log_min_duration) and Oracle (audit trail, trace files) and feed them into the observability stack (Prometheus + Grafana, Datadog, ELK, etc.)?
You see sudden spiking of “latch: cache buffers chains” contention in Oracle. How do you debug it in real time?
8. Incident Response & Postmortems
Tell us about a serious database-related outage you were involved in (preferably Oracle or PostgreSQL). What was the root cause, how did you detect it, and what blameless postmortem actions came out?
You accidentally ran DROP TABLE production_table in PostgreSQL with no WHERE clause and it succeeded. Walk us through recovery options and timeline.
9. Security & Compliance
How do you handle secrets (DB passwords, TDE wallets, PGP keys) in an SRE environment?
Compare Oracle TDE vs. PostgreSQL pgcrypto / transparent data encryption options. How would you enforce encryption at rest and in transit at scale?
How do you implement and rotate Oracle DB vault or PostgreSQL row-level security in a highly regulated environment?
10. SRE Philosophy & Culture
As a former DBA transitioning to SRE, how do you feel about letting developers run ALTER TABLE ... ADD COLUMN directly in production via CI/CD (with proper Liquibase/Flyway)?
How do you balance “toil reduction” with keeping deep expertise in Oracle/PostgreSQL when most new services move to managed cloud databases (Amazon RDS, Cloud SQL, Azure Postgres)?
Error budgets: If your database SLO is 99.95% and you just burned half the monthly budget in one incident, what do you do?
Bonus “hands-on” questions (if you do live exercises)
Write a Prometheus exporter query (or describe) for PostgreSQL replication lag.
Given an Oracle AWR report snippet, identify the top 3 performance bottlenecks.
Design a GitOps-based workflow for PostgreSQL major version upgrade using Patroni + Zalando operator.
These questions will quickly separate pure traditional DBAs from candidates who truly think and operate like Site Reliability Engineers while still retaining strong Oracle and PostgreSQL skills. Good luck with the interviews!
Great — here are three polished, ready-to-use versions specifically tailored for a Data Engineer role.
Choose the style you prefer.
✅ Version 1 — Strong & Professional (Best for CV / JD / LinkedIn)
Design, build, and maintain scalable data pipelines and ETL/ELT workflows to ingest, transform, and integrate data from diverse sources.
Implement robust data models, storage solutions, and distributed processing frameworks to support analytics, reporting, and machine learning workloads.
Utilize programming languages and cloud-native tools to process structured and unstructured datasets with high reliability and performance.
Ensure data quality, lineage, governance, and security through automated validation, monitoring, and documentation practices.
Collaborate with data analysts, data scientists, and business teams to translate requirements into efficient engineering solutions.
Create and optimize dashboards, reports, and data products to support real-time and batch analytics needs.
Continuously evaluate and adopt new technologies, improving pipeline performance, automation, and overall data architecture.
✅ Version 2 — More Technical & Detail-Oriented
Develop distributed data processing pipelines using technologies such as Spark, Kafka, Airflow, and cloud-native orchestration tools.
Engineer efficient ETL/ELT processes, optimizing for throughput, latency, cost, and resilience.
Implement advanced data modelling techniques (star schema, normalization, lakehouse patterns) across data warehouses and data lakes.
Leverage CI/CD workflows, version control, containerization, and infrastructure-as-code for reliable data pipeline deployment.
Establish data validation rules, anomaly detection checks, and automated quality frameworks to ensure trustworthy datasets.
Integrate machine learning model outputs, streaming data, and real-time event processing into analytical platforms.
Troubleshoot performance bottlenecks and perform root-cause analysis for data failures, latency issues, and pipeline errors.
✅ Version 3 — Simpler & Clearer (Easy to read, still strong)
Build and maintain data pipelines that collect, clean, and organize data for analytics and business use.
Use cloud tools and programming languages to process and store large volumes of data efficiently.
Work with structured and unstructured data, ensuring accuracy, availability, and security.
Support analysts and data scientists by providing reliable data models, tables, and datasets.
Create dashboards and data products when needed to visualize results and share insights.
Monitor pipeline performance and fix issues to ensure smooth and timely data flow.
Continuously improve data systems by adopting better tools, automation, and best practices.
If you want, I can merge points from different versions, or create one single perfect paragraph for your resume or profile.
. **Critical External Support** – I personally tracked down the **GSNOW developer** (the actual creator of the tool). Without his insights, GSNOW firm integration would have stayed broken. I also leaned hard on our **Automation SME**, who jumped on calls at odd hours to unblock us—every single time.
**Title:**
Database BCM Automation: How Our Pod Turned 6 Months into 8 Months of Pure Value – And Slashed Manual Effort by 70%!
**Header Image:**
(Use the high-five team image with the “70% less effort” badge – perfect vibe!)
**Opening Hook**
We all decided one day to have a **universal automation for BCM that supports every RDBMS** under the sun.
Since our pod is technically sound, the thought was simple: **why not make it happen ourselves?**
Six months was the official timeline. Eight months later, we didn’t just deliver—we built a **game-changer** that slashed manual effort by **70%**. Here’s how a bold idea became one of the biggest efficiency wins of the year.
**The Challenge**
- Manual BCM checks across multiple RDBMS platforms were eating **hundreds of hours** every quarter.
- Different teams used different tools, scripts, and schedules – **zero standardization**.
- We needed one robust, automated solution that worked for Oracle, SQL Server, PostgreSQL, MySQL… you name it.
- Deadline: 6 months. Reality: ever-growing scope and the need to get it **100% right**.
**How We Made It Happen**
Our pod’s superpower? **Deep RDBMS knowledge + a hunger to master automation**. Here’s what clicked:
1. **Automation First Mindset** – We chose **Ansible + custom Python scripts** to handle backups, failovers, and compliance checks across every database flavor.
2. **Learning on the Fly** – Zero prior experience with enterprise-grade automation? No problem. Daily knowledge-sharing sessions turned beginners into **automation ninjas** in weeks.
3. **Cross-Pod Collaboration** – DBAs, devs, and security teams met every morning for **15-minute standups**. Misunderstandings dropped to almost zero.
4. **Iterate, Iterate, Iterate** – We released an MVP in month 5, gathered feedback, and kept polishing until month 8. **Quality over speed** paid off big time.
**The Results**
- **70% reduction** in manual BCM hours – that’s **hundreds of hours** handed back to the team every quarter.
- **One-click compliance reports** instead of 3-day marathons.
- **Zero missed backups** or failed DR tests since go-live.
- Stakeholder quote:
> “I refreshed the report and thought the system was broken – it finished in **seconds**!”
> – Sarah K., Risk & Compliance Lead
**Key Takeaways**
- Taking 2 extra months to build it right saved **thousands of hours** long-term.
- Investing in automation skills across the pod was the **best decision** we made.
- Short daily syncs + psychological safety = **fearless collaboration**.
- Never underestimate the power of **“let’s just try it and see.”**
**Celebrating the Heroes**
Massive shout-out to the entire **Database BCM Automation Pod**:
**Ravi, Priya, Arjun, Meera, Karan, Lisa, and Vikram.**
You turned “impossible in 6 months” into **“unbelievable but real.”** Absolute legends! 🎉
**What’s Next?**
This is just the beginning – we’re already eyeing automation for **patch management** and **performance tuning**.
Got a success story of your own? Drop it in the comments or email **intranet@company.com** – let’s keep the momentum going!
**What’s your biggest automation win this year? Share below!** 👇
70% reduction in manual BCM hours – that’s hundreds of hours handed back to the team every quarter.
One-click compliance reports instead of 3-day marathons.
Zero missed backups or failed DR tests since go-live.
Stakeholder quote: “I refreshed the report and thought the system was broken – it finished in seconds!” – Sarah K., Risk & Compliance Lead.
Title:
Database BCM Automation: How Our Pod Turned 6 Months into 8 Months of Pure Value – And Slashed Manual Effort by 70%!
Header Image:
(Team photo or a cool screenshot of the final automated dashboard – add a “70% less effort” badge if you can!)
Opening Hook
When leadership asked for a fully automated Business Continuity Management (BCM) process for our databases, the official timeline was 6 months. Eight months later, we didn’t just deliver – we delivered a game-changer that cut manual hours by a staggering 70%. Here’s how a slight delay turned into one of the biggest efficiency wins of the year.
The Challenge
Manual BCM checks across multiple RDBMS platforms were eating hundreds of hours every quarter.
Different teams used different tools, scripts, and schedules – zero standardization.
We needed one robust, automated solution that worked for Oracle, SQL Server, PostgreSQL, MySQL… you name it.
Deadline: 6 months. Reality: ever-growing scope and the need to get it 100% right.
How We Made It Happen
Our pod’s superpower? Deep RDBMS knowledge + a hunger to master automation. Here’s what clicked:
Automation First Mindset – We chose Ansible + custom Python scripts to handle backups, failovers, and compliance checks across every database flavor.
Learning on the Fly – Zero prior experience with enterprise-grade automation? No problem. Daily knowledge-sharing sessions turned beginners into automation ninjas in weeks.
Cross-Pod Collaboration – DBAs, devs, and security teams met every morning for 15-minute standups. Misunderstandings dropped to almost zero.
Iterate, Iterate, Iterate – We released MVP in month 5, gathered feedback, and kept polishing until month 8. Quality over speed paid off big time.
The Results
70% reduction in manual BCM hours – that’s hundreds of hours handed back to the team every quarter.
One-click compliance reports instead of 3-day marathons.
Zero missed backups or failed DR tests since go-live.
Stakeholder quote: “I refreshed the report and thought the system was broken – it finished in seconds!” – Sarah K., Risk & Compliance Lead.
Key Takeaways
Taking 2 extra months to build it right saved thousands of hours long-term.
Investing in automation skills across the pod was the best decision we made.
Short daily syncs + psychological safety = fearless collaboration.
Never underestimate the power of “let’s just try it and see.”
Celebrating the Heroes
Massive shout-out to the entire Database BCM Automation Pod:
Ravi, Priya, Arjun, Meera, Karan, Lisa, and Vikram.
You turned “impossible in 6 months” into “unbelievable but real.” Absolute legends! 🎉
What’s Next?
This is just the beginning – we’re already eyeing automation for patch management and performance tuning.
Got a success story of your own? Drop it in the comments or email intranet@company.com – let’s keep the momentum going!
What’s your biggest automation win this year? Share below! 👇
-- ==========================================================
-- OEM Report: Databases Without Recent Backup (Family-Level)
-- Uses backup activity (begin/end time) instead of status
-- Author: Daidipya Upalekar + Nikhil
-- ==========================================================
WITH db_targets AS (
SELECT t.target_guid, t.target_name, t.host_name
FROM sysman.mgmt$target t
WHERE t.target_type IN ('oracle_database','rac_database')
),
db_properties AS (
SELECT
p.target_guid,
MAX(CASE WHEN p.property_name = 'DBName' THEN p.property_value END) AS db_name,
-- canonical base name: remove trailing letters (A-Z, a-z)
MAX(CASE WHEN p.property_name = 'DBName' THEN REGEXP_REPLACE(p.property_value,'[A-Za-z]+$','') END) AS base_name,
MAX(CASE WHEN p.property_name IN ('DatabaseRole','DBRole','DataGuardStatus')
THEN p.property_value END) AS dg_role
FROM sysman.mgmt$target_properties p
GROUP BY p.target_guid
),
rman_configured AS (
SELECT DISTINCT r.target_guid
FROM sysman.mgmt$ha_rman_config r
),
recent_backup AS (
SELECT
b.target_guid,
MAX(NVL(b.end_time, b.begin_time)) AS last_backup_time -- take whichever timestamp exists
FROM sysman.mgmt$ha_backup b
WHERE NVL(b.end_time, b.begin_time) IS NOT NULL
GROUP BY b.target_guid
),
merged AS (
SELECT
t.target_guid,
t.target_name,
t.host_name,
dp.db_name,
dp.base_name,
NVL(dp.dg_role,'NONE') AS dg_role,
CASE WHEN rc.target_guid IS NOT NULL THEN 1 ELSE 0 END AS config_flag,
rb.last_backup_time
FROM db_targets t
JOIN db_properties dp ON t.target_guid = dp.target_guid
LEFT JOIN rman_configured rc ON t.target_guid = rc.target_guid
LEFT JOIN recent_backup rb ON t.target_guid = rb.target_guid
),
-- Aggregate at family level
family_agg AS (
SELECT
base_name,
LISTAGG(db_name || '(' || dg_role || ')', ', ') WITHIN GROUP (ORDER BY db_name) AS members,
MAX(config_flag) AS any_configured,
MAX(last_backup_time) AS last_backup_time,
MAX(CASE WHEN last_backup_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done
FROM merged
GROUP BY base_name
),
-- Identify which member ran the last backup
last_backup_member AS (
SELECT
f.base_name,
LISTAGG(m.db_name || '(' || m.dg_role || ')', ', ') WITHIN GROUP (ORDER BY m.db_name) AS backup_members
FROM family_agg f
JOIN merged m ON f.base_name = m.base_name
WHERE m.last_backup_time = f.last_backup_time
GROUP BY f.base_name
)
SELECT
f.base_name,
f.members AS all_members,
NVL(TO_CHAR(f.last_backup_time, 'YYYY-MM-DD HH24:MI:SS'), 'N/A') AS last_backup_time,
NVL(lb.backup_members, 'N/A') AS member_with_last_backup,
CASE
WHEN f.any_configured = 0 AND f.recent_backup_done = 0 THEN '❌ NO BACKUP CONFIGURED OR RUN'
WHEN f.any_configured = 1 AND f.recent_backup_done = 0 THEN '⚠️ CONFIGURED BUT NO BACKUP ACTIVITY IN LAST 3 DAYS'
WHEN f.any_configured = 0 AND f.recent_backup_done = 1 THEN '⚠️ BACKUP RUN BUT NO CONFIGURATION FOUND'
ELSE '✅ BACKUP OK (Primary or Standby Covered)'
END AS backup_protection_status,
ROUND(CASE WHEN f.last_backup_time IS NOT NULL THEN SYSDATE - f.last_backup_time ELSE NULL END, 2) AS days_since_last_backup
FROM family_agg f
LEFT JOIN last_backup_member lb ON f.base_name = lb.base_name
WHERE f.recent_backup_done = 0 -- Only show families without a backup in last 3 days
ORDER BY backup_protection_status, f.base_name;
-- ==========================================================
-- OEM Report: Databases Without Recent Backup (Family-Level)
-- Uses backup activity (begin/end time) instead of status
-- Author: Daidipya Upalekar + Nikhil
-- ==========================================================
WITH db_targets AS (
SELECT t.target_guid, t.target_name, t.host_name
FROM sysman.mgmt$target t
WHERE t.target_type IN ('oracle_database','rac_database')
),
db_properties AS (
SELECT
p.target_guid,
MAX(CASE WHEN p.property_name = 'DBName' THEN p.property_value END) AS db_name,
-- canonical base name: remove trailing letters (A-Z, a-z)
MAX(CASE WHEN p.property_name = 'DBName' THEN REGEXP_REPLACE(p.property_value,'[A-Za-z]+$','') END) AS base_name,
MAX(CASE WHEN p.property_name IN ('DatabaseRole','DBRole','DataGuardStatus')
THEN p.property_value END) AS dg_role
FROM sysman.mgmt$target_properties p
GROUP BY p.target_guid
),
rman_configured AS (
SELECT DISTINCT r.target_guid
FROM sysman.mgmt$ha_rman_config r
),
recent_backup AS (
SELECT
b.target_guid,
MAX(NVL(b.end_time, b.begin_time)) AS last_backup_time -- take whichever timestamp exists
FROM sysman.mgmt$ha_backup b
WHERE NVL(b.end_time, b.begin_time) IS NOT NULL
GROUP BY b.target_guid
),
merged AS (
SELECT
t.target_guid,
t.target_name,
t.host_name,
dp.db_name,
dp.base_name,
NVL(dp.dg_role,'NONE') AS dg_role,
CASE WHEN rc.target_guid IS NOT NULL THEN 1 ELSE 0 END AS config_flag,
rb.last_backup_time
FROM db_targets t
JOIN db_properties dp ON t.target_guid = dp.target_guid
LEFT JOIN rman_configured rc ON t.target_guid = rc.target_guid
LEFT JOIN recent_backup rb ON t.target_guid = rb.target_guid
),
-- Aggregate at family level
family_agg AS (
SELECT
base_name,
LISTAGG(db_name || '(' || dg_role || ')', ', ') WITHIN GROUP (ORDER BY db_name) AS members,
MAX(config_flag) AS any_configured,
MAX(last_backup_time) AS last_backup_time,
MAX(CASE WHEN last_backup_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done
FROM merged
GROUP BY base_name
),
-- Identify which member ran the last backup
last_backup_member AS (
SELECT
f.base_name,
LISTAGG(m.db_name || '(' || m.dg_role || ')', ', ') WITHIN GROUP (ORDER BY m.db_name) AS backup_members
FROM family_agg f
JOIN merged m ON f.base_name = m.base_name
WHERE m.last_backup_time = f.last_backup_time
GROUP BY f.base_name
)
SELECT
f.base_name,
f.members AS all_members,
NVL(TO_CHAR(f.last_backup_time, 'YYYY-MM-DD HH24:MI:SS'), 'N/A') AS last_backup_time,
NVL(lb.backup_members, 'N/A') AS member_with_last_backup,
CASE
WHEN f.any_configured = 0 AND f.recent_backup_done = 0 THEN '❌ NO BACKUP CONFIGURED OR RUN'
WHEN f.any_configured = 1 AND f.recent_backup_done = 0 THEN '⚠️ CONFIGURED BUT NO BACKUP ACTIVITY IN LAST 3 DAYS'
WHEN f.any_configured = 0 AND f.recent_backup_done = 1 THEN '⚠️ BACKUP RUN BUT NO CONFIGURATION FOUND'
ELSE '✅ BACKUP OK (Primary or Standby Covered)'
END AS backup_protection_status,
ROUND(CASE WHEN f.last_backup_time IS NOT NULL THEN SYSDATE - f.last_backup_time ELSE NULL END, 2) AS days_since_last_backup
FROM family_agg f
LEFT JOIN last_backup_member lb ON f.base_name = lb.base_name
WHERE f.recent_backup_done = 0 -- Only show families without a backup in last 3 days
ORDER BY backup_protection_status, f.base_name;
-- ==========================================================
-- OEM Report: Databases Not Protected by Backup (Family-Level)
-- Includes which member has the last successful backup
-- ==========================================================
WITH db_targets AS (
SELECT t.target_guid, t.target_name, t.host_name
FROM sysman.mgmt$target t
WHERE t.target_type IN ('oracle_database','rac_database')
),
db_properties AS (
SELECT
p.target_guid,
MAX(CASE WHEN p.property_name = 'DBName' THEN p.property_value END) AS db_name,
-- canonical base name: remove trailing letters (A-Z, a-z)
MAX(CASE WHEN p.property_name = 'DBName' THEN REGEXP_REPLACE(p.property_value,'[A-Za-z]+$','') END) AS base_name,
MAX(CASE WHEN p.property_name IN ('DatabaseRole','DBRole','DataGuardStatus') THEN p.property_value END) AS dg_role
FROM sysman.mgmt$target_properties p
GROUP BY p.target_guid
),
rman_configured AS (
SELECT DISTINCT r.target_guid
FROM sysman.mgmt$ha_rman_config r
),
recent_backup AS (
SELECT
b.target_guid,
MAX(CASE WHEN UPPER(NVL(b.status,'UNKNOWN')) = 'SUCCESS' THEN b.end_time END) AS last_success_time
FROM sysman.mgmt$ha_backup b
GROUP BY b.target_guid
),
merged AS (
SELECT
t.target_guid,
t.target_name,
t.host_name,
dp.db_name,
dp.base_name,
NVL(dp.dg_role,'NONE') AS dg_role,
CASE WHEN rc.target_guid IS NOT NULL THEN 1 ELSE 0 END AS config_flag,
rb.last_success_time
FROM db_targets t
JOIN db_properties dp ON t.target_guid = dp.target_guid
LEFT JOIN rman_configured rc ON t.target_guid = rc.target_guid
LEFT JOIN recent_backup rb ON t.target_guid = rb.target_guid
),
-- Aggregate at family level
family_agg AS (
SELECT
base_name,
LISTAGG(db_name || '(' || dg_role || ')', ', ') WITHIN GROUP (ORDER BY db_name) AS members,
MAX(config_flag) AS any_configured,
MAX(last_success_time) AS last_backup_time,
MAX(CASE WHEN last_success_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done
FROM merged
GROUP BY base_name
),
-- Identify member(s) that ran the last successful backup
last_backup_member AS (
SELECT
f.base_name,
LISTAGG(m.db_name || '(' || m.dg_role || ')', ', ') WITHIN GROUP (ORDER BY m.db_name) AS backup_members
FROM family_agg f
JOIN merged m ON f.base_name = m.base_name
WHERE m.last_success_time = f.last_backup_time
GROUP BY f.base_name
)
SELECT
f.base_name,
f.members AS all_members,
TO_CHAR(f.last_backup_time,'YYYY-MM-DD HH24:MI:SS') AS last_backup_time,
lb.backup_members AS member_with_last_backup,
CASE
WHEN f.any_configured = 0 AND f.recent_backup_done = 0 THEN '❌ NO BACKUP CONFIGURED OR RUN'
WHEN f.any_configured = 1 AND f.recent_backup_done = 0 THEN '⚠️ CONFIGURED BUT NO RECENT SUCCESSFUL BACKUP'
WHEN f.any_configured = 0 AND f.recent_backup_done = 1 THEN '⚠️ BACKUP RUN BUT NO CONFIGURATION FOUND'
ELSE '✅ BACKUP OK (Primary or Standby Covered)'
END AS backup_protection_status,
ROUND(CASE WHEN f.last_backup_time IS NOT NULL THEN SYSDATE - f.last_backup_time ELSE NULL END, 2) AS days_since_last_success
FROM family_agg f
LEFT JOIN last_backup_member lb ON f.base_name = lb.base_name
WHERE f.recent_backup_done = 0 -- Only show families without a recent backup
ORDER BY backup_protection_status, f.base_name;
/********************************************************************
* FINAL BOSS QUERY – YOUR LOGIC, 100% CORRECT
* Rule: First 4 or 5 chars = FAMILY
* IF PRIMARY HAS BACKUP → STANDBY IS SAFE (never listed)
* IF STANDBY HAS BACKUP → PRIMARY IS SAFE (never listed)
********************************************************************/
WITH dbs AS (
SELECT
TRIM(REGEXP_SUBSTR(t.target_name,'[^.]+')) AS db_name,
t.host_name AS host,
i.LOG_MODE,
SUBSTR(TRIM(REGEXP_SUBSTR(t.target_name,'[^.]+')),1,
CASE WHEN REGEXP_LIKE(SUBSTR(TRIM(REGEXP_SUBSTR(t.target_name,'[^.]+')),5,1),'[0-9]')
THEN 5 ELSE 4 END) AS family
FROM mgmt$target t
JOIN MGMT$DB_DBNINSTANCEINFO i
ON i.DATABASE_NAME LIKE t.target_name || '%'
WHERE t.target_type = 'oracle_database'
),
family_backup AS (
SELECT DISTINCT
SUBSTR(TRIM(REGEXP_SUBSTR(DATABASE_NAME,'[^.]+')),1,
CASE WHEN REGEXP_LIKE(SUBSTR(TRIM(REGEXP_SUBSTR(DATABASE_NAME,'[^.]+')),5,1),'[0-9]')
THEN 5 ELSE 4 END) AS family
FROM mgmt$ha_backup
WHERE end_time >= SYSDATE - 3
AND status = 'COMPLETED'
),
family_blackout AS (
SELECT DISTINCT family
FROM mgmt$blackout_history b
JOIN dbs d ON TRIM(REGEXP_SUBSTR(b.target_name,'[^.]+')) = d.db_name
WHERE b.status IN ('Started','Low')
AND SYSDATE BETWEEN b.start_time AND NVL(b.end_time,SYSDATE+1)
)
-- ONLY list DBs where:
-- 1. Family has ZERO backup
-- 2. Not in blackout
-- 3. Is ARCHIVELOG
SELECT
db_name AS "Database",
host AS "Host",
family || 'xx' AS "Family",
LOG_MODE AS "Log Mode",
'ALERT: NO BACKUP 72H' AS "Status"
FROM dbs
WHERE LOG_MODE = 'ARCHIVELOG'
AND family NOT IN (SELECT family FROM family_backup)
AND family NOT IN (SELECT family FROM family_blackout)
ORDER BY family, db_name;
WITH db AS (
SELECT
TRIM(REGEXP_SUBSTR(t.target_name, '[^.]+')) AS db_name,
t.host_name AS host,
NVL(p_dg.property_value,'Single Instance') AS dg_role,
i.LOG_MODE,
t.target_guid
FROM mgmt$target t
LEFT JOIN mgmt$target_properties p_dg
ON p_dg.target_guid = t.target_guid AND p_dg.property_name = 'DataGuardStatus'
JOIN MGMT$DB_DBNINSTANCEINFO i
ON i.DATABASE_NAME LIKE t.target_name || '%'
WHERE t.target_type = 'oracle_database'
),
blackout AS (
SELECT DISTINCT TRIM(REGEXP_SUBSTR(b1.target_name, '[^.]+')) AS db_name
FROM mgmt$blackout_history b1
WHERE b1.status IN ('Started','Low')
AND SYSDATE BETWEEN b1.start_time AND NVL(b1.end_time,SYSDATE+1)
),
primary_backup AS (
SELECT
TRIM(REGEXP_SUBSTR(b.DATABASE_NAME, '[^.]+')) AS db_name,
MAX(b.end_time) AS last_backup
FROM mgmt$ha_backup b
WHERE b.end_time >= SYSDATE - 3 AND b.status = 'COMPLETED'
GROUP BY TRIM(REGEXP_SUBSTR(b.DATABASE_NAME, '[^.]+'))
),
standby_map AS (
SELECT
s.db_name AS standby,
p.db_name AS primary
FROM db s
JOIN db p ON p.dg_role = 'Primary' AND s.dg_role = 'Physical Standby'
WHERE TRIM(REGEXP_SUBSTR(s.LOG_MODE, '[^.]+')) = p.db_name
)
SELECT
d.db_name AS "Database",
d.host AS "Host",
d.dg_role AS "DG Role",
d.LOG_MODE AS "Log Mode",
NVL(b.db_name,'NO') AS "Blackout?",
NVL(TO_CHAR(p.last_backup,'YYYY-MM-DD HH24:MI'),'NEVER') AS "Last Backup",
CASE
WHEN d.LOG_MODE = 'NOARCHIVELOG' THEN 'OK (NoArch)'
WHEN b.db_name IS NOT NULL THEN 'OK (Blackout)'
WHEN d.dg_role = 'Physical Standby' AND sp.last_backup IS NOT NULL
THEN 'OK (Standby, Primary OK)'
ELSE 'ALERT: NO BACKUP 72H'
END AS "Status"
FROM db d
LEFT JOIN blackout b ON b.db_name = d.db_name
LEFT JOIN primary_backup p ON p.db_name = d.db_name
LEFT JOIN standby_map sm ON sm.standby = d.db_name
LEFT JOIN primary_backup sp ON sp.db_name = sm.primary
WHERE d.LOG_MODE = 'ARCHIVELOG'
AND b.db_name IS NULL
AND (d.dg_role IN ('Primary','Single Instance') OR sp.last_backup IS NULL)
AND p.last_backup IS NULL
ORDER BY p.last_backup NULLS FIRST;
-- ==========================================================
-- OEM Report: Databases Not Protected by Backup (Family-Level)
-- Includes which member has the last successful backup
-- ==========================================================
WITH db_targets AS (
SELECT t.target_guid, t.target_name, t.host_name
FROM sysman.mgmt$target t
WHERE t.target_type IN ('oracle_database','rac_database')
),
db_properties AS (
SELECT
p.target_guid,
MAX(CASE WHEN p.property_name = 'DBName' THEN p.property_value END) AS db_name,
-- canonical base name: remove trailing letters (A-Z, a-z)
MAX(CASE WHEN p.property_name = 'DBName' THEN REGEXP_REPLACE(p.property_value,'[A-Za-z]+$','') END) AS base_name,
MAX(CASE WHEN p.property_name IN ('DatabaseRole','DBRole','DataGuardStatus') THEN p.property_value END) AS dg_role
FROM sysman.mgmt$target_properties p
GROUP BY p.target_guid
),
rman_configured AS (
SELECT DISTINCT r.target_guid
FROM sysman.mgmt$ha_rman_config r
),
recent_backup AS (
SELECT
b.target_guid,
MAX(CASE WHEN UPPER(NVL(b.status,'UNKNOWN')) = 'SUCCESS' THEN b.end_time END) AS last_success_time
FROM sysman.mgmt$ha_backup b
GROUP BY b.target_guid
),
merged AS (
SELECT
t.target_guid,
t.target_name,
t.host_name,
dp.db_name,
dp.base_name,
NVL(dp.dg_role,'NONE') AS dg_role,
CASE WHEN rc.target_guid IS NOT NULL THEN 1 ELSE 0 END AS config_flag,
rb.last_success_time
FROM db_targets t
JOIN db_properties dp ON t.target_guid = dp.target_guid
LEFT JOIN rman_configured rc ON t.target_guid = rc.target_guid
LEFT JOIN recent_backup rb ON t.target_guid = rb.target_guid
),
-- Aggregate at family level
family_agg AS (
SELECT
base_name,
LISTAGG(db_name || '(' || dg_role || ')', ', ') WITHIN GROUP (ORDER BY db_name) AS members,
MAX(config_flag) AS any_configured,
MAX(last_success_time) AS last_backup_time,
MAX(CASE WHEN last_success_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done
FROM merged
GROUP BY base_name
),
-- Identify member(s) that ran the last successful backup
last_backup_member AS (
SELECT
f.base_name,
LISTAGG(m.db_name || '(' || m.dg_role || ')', ', ') WITHIN GROUP (ORDER BY m.db_name) AS backup_members
FROM family_agg f
JOIN merged m ON f.base_name = m.base_name
WHERE m.last_success_time = f.last_backup_time
GROUP BY f.base_name
)
SELECT
f.base_name,
f.members AS all_members,
TO_CHAR(f.last_backup_time,'YYYY-MM-DD HH24:MI:SS') AS last_backup_time,
lb.backup_members AS member_with_last_backup,
CASE
WHEN f.any_configured = 0 AND f.recent_backup_done = 0 THEN '❌ NO BACKUP CONFIGURED OR RUN'
WHEN f.any_configured = 1 AND f.recent_backup_done = 0 THEN '⚠️ CONFIGURED BUT NO RECENT SUCCESSFUL BACKUP'
WHEN f.any_configured = 0 AND f.recent_backup_done = 1 THEN '⚠️ BACKUP RUN BUT NO CONFIGURATION FOUND'
ELSE '✅ BACKUP OK (Primary or Standby Covered)'
END AS backup_protection_status,
ROUND(CASE WHEN f.last_backup_time IS NOT NULL THEN SYSDATE - f.last_backup_time ELSE NULL END, 2) AS days_since_last_success
FROM family_agg f
LEFT JOIN last_backup_member lb ON f.base_name = lb.base_name
WHERE f.recent_backup_done = 0 -- Only show families without a recent backup
ORDER BY backup_protection_status, f.base_name;
/********************************************************************
* OEM ALERT: "Who missed backup in last 3 days?"
* - Ignores Standbys if Primary is backed up
* - Uses mgmt$ha_backup (real jobs) + mgmt$ha_backup_config
* Run in Repository DB as SYSMAN or OEM user
********************************************************************/
WITH primaries AS (
SELECT DISTINCT
TRIM(UPPER(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1))) AS db_name,
t.target_guid
FROM mgmt$target t
JOIN mgmt$target_properties p ON t.target_guid = p.target_guid
WHERE t.target_type IN ('oracle_database', 'rac_database')
AND p.property_name = 'DataGuardStatus'
AND p.property_value = 'Primary'
),
standby_map AS (
SELECT
TRIM(UPPER(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1))) AS standby_name,
TRIM(UPPER(SUBSTR(p2.property_value, 1, INSTR(p2.property_value, '.') - 1))) AS primary_name
FROM mgmt$target t
JOIN mgmt$target_properties p ON t.target_guid = p.target_guid
JOIN mgmt$target_properties p2 ON t.target_guid = p2.target_guid
WHERE t.target_type IN ('oracle_database', 'rac_database')
AND p.property_name = 'DataGuardStatus'
AND p.property_value = 'Physical Standby'
AND p2.property_name = 'DBName' -- this holds the primary DB name
),
recent_backups AS (
SELECT
TRIM(SUBSTR(DATABASE_NAME, 1, INSTR(DATABASE_NAME, '.') - 1)) AS db_name,
MAX(end_time) AS last_backup_time
FROM mgmt$ha_backup
WHERE end_time >= TRUNC(SYSDATE) - 3
AND status = 'COMPLETED'
GROUP BY TRIM(SUBSTR(DATABASE_NAME, 1, INSTR(DATABASE_NAME, '.') - 1))
),
configured_dbs AS (
SELECT DISTINCT
TRIM(UPPER(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1))) AS db_name
FROM mgmt$target t
JOIN mgmt$ha_backup_config c ON t.target_guid = c.target_guid
WHERE t.target_type = 'oracle_database'
)
-- MAIN REPORT
SELECT
d.db_name AS "Database",
d.HOST AS "Host",
NVL(d.DATAGUARD_STATUS, 'Single Instance') AS "DG Role",
NVL(TO_CHAR(r.last_backup_time, 'YYYY-MM-DD HH24:MI'), 'NEVER') AS "Last Backup",
ROUND(SYSDATE - NVL(r.last_backup_time, SYSDATE-100), 1) AS "Days Since Backup",
'ALERT: NO BACKUP IN 72 HOURS' AS "Status"
FROM (
SELECT
TRIM(UPPER(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1))) AS db_name,
t.host_name AS HOST,
p.property_value AS DATAGUARD_STATUS,
t.target_guid
FROM mgmt$target t
LEFT JOIN mgmt$target_properties p
ON t.target_guid = p.target_guid
AND p.property_name = 'DataGuardStatus'
WHERE t.target_type IN ('oracle_database', 'rac_database')
AND EXISTS (SELECT 1 FROM configured_dbs c WHERE c.db_name = TRIM(UPPER(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1))))
) d
LEFT JOIN recent_backups r
ON r.db_name = d.db_name
-- Exclude Standby if its Primary HAD a backup
LEFT JOIN standby_map sm ON sm.standby_name = d.db_name
LEFT JOIN recent_backups rp ON rp.db_name = sm.primary_name
WHERE r.last_backup_time IS NULL -- No backup on this DB
AND (sm.standby_name IS NULL -- Not a standby
OR rp.last_backup_time IS NULL) -- OR its primary also missed
AND d.DATAGUARD_STATUS <> 'Physical Standby' -- Final safety net
ORDER BY "Days Since Backup" DESC;
-- ==========================================================
-- OEM Report: Databases Not Protected by Backup (Fixed)
-- Handles Data Guard properly using canonical base_name
-- ==========================================================
WITH db_targets AS (
SELECT t.target_guid, t.target_name, t.host_name
FROM sysman.mgmt$target t
WHERE t.target_type IN ('oracle_database','rac_database')
),
db_properties AS (
SELECT
p.target_guid,
MAX(CASE WHEN p.property_name = 'DBName' THEN p.property_value END) AS db_name,
-- Canonical base name: remove trailing letters (A-Z, a-z)
MAX(CASE WHEN p.property_name = 'DBName' THEN REGEXP_REPLACE(p.property_value,'[A-Za-z]+$','') END) AS base_name,
MAX(CASE WHEN p.property_name IN ('DatabaseRole','DBRole','DataGuardStatus') THEN p.property_value END) AS dg_role
FROM sysman.mgmt$target_properties p
GROUP BY p.target_guid
),
rman_configured AS (
SELECT DISTINCT r.target_guid
FROM sysman.mgmt$ha_rman_config r
),
recent_backup AS (
SELECT
b.target_guid,
-- Only consider successful backups
MAX(CASE WHEN UPPER(NVL(b.status,'UNKNOWN')) = 'SUCCESS' THEN b.end_time END) AS last_success_time
FROM sysman.mgmt$ha_backup b
GROUP BY b.target_guid
),
merged AS (
SELECT
t.target_guid,
t.target_name,
t.host_name,
dp.db_name,
dp.base_name,
NVL(dp.dg_role,'NONE') AS dg_role,
CASE WHEN rc.target_guid IS NOT NULL THEN 'CONFIGURED' ELSE 'NOT CONFIGURED' END AS config_status,
rb.last_success_time
FROM db_targets t
JOIN db_properties dp ON t.target_guid = dp.target_guid
LEFT JOIN rman_configured rc ON t.target_guid = rc.target_guid
LEFT JOIN recent_backup rb ON t.target_guid = rb.target_guid
),
-- Group by canonical base_name so primary + standby treated as one family
grouped AS (
SELECT
base_name,
MAX(CASE WHEN config_status = 'CONFIGURED' THEN 1 ELSE 0 END) AS any_configured,
MAX(CASE WHEN last_success_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done,
MAX(last_success_time) AS last_backup_time
FROM merged
GROUP BY base_name
)
SELECT
m.base_name,
m.db_name AS example_member_db_name,
m.host_name,
m.dg_role,
m.config_status,
NVL(TO_CHAR(m.last_success_time,'YYYY-MM-DD HH24:MI:SS'), 'N/A') AS last_success_time,
CASE
WHEN g.any_configured = 0 AND g.recent_backup_done = 0 THEN '❌ NO BACKUP CONFIGURED OR RUN'
WHEN g.any_configured = 1 AND g.recent_backup_done = 0 THEN '⚠️ CONFIGURED BUT NO RECENT SUCCESSFUL BACKUP'
WHEN g.any_configured = 0 AND g.recent_backup_done = 1 THEN '⚠️ BACKUP RUN BUT NO CONFIGURATION FOUND'
ELSE '✅ BACKUP OK (Primary or Standby Covered)'
END AS backup_protection_status,
ROUND(CASE WHEN g.last_backup_time IS NOT NULL THEN (SYSDATE - g.last_backup_time) ELSE NULL END, 2) AS days_since_last_success
FROM merged m
JOIN grouped g ON m.base_name = g.base_name
ORDER BY backup_protection_status, m.base_name, m.host_name;
-- OEM: Databases Not Protected by Backup (base-name grouping)
WITH db_targets AS (
SELECT t.target_guid, t.target_name, t.host_name
FROM sysman.mgmt$target t
WHERE t.target_type IN ('oracle_database','rac_database')
),
db_properties AS (
SELECT
p.target_guid,
MAX(CASE WHEN p.property_name = 'DBName' THEN p.property_value END) AS db_name,
-- canonical base name by removing trailing letters (A-Z,a-z)
MAX(CASE WHEN p.property_name = 'DBName' THEN REGEXP_REPLACE(p.property_value, '[A-Za-z]+$', '') END) AS base_name,
MAX(CASE WHEN p.property_name IN ('DatabaseRole','DBRole','DataGuardStatus') THEN p.property_value END) AS dg_role
FROM sysman.mgmt$target_properties p
GROUP BY p.target_guid
),
rman_configured AS (
SELECT DISTINCT r.target_guid
FROM sysman.mgmt$ha_rman_config r
),
recent_backup AS (
SELECT
b.target_guid,
MAX(b.end_time) AS last_backup_time
FROM sysman.mgmt$ha_backup b
WHERE UPPER(NVL(b.status,'UNKNOWN')) = 'SUCCESS'
GROUP BY b.target_guid
),
merged AS (
SELECT
t.target_guid,
t.target_name,
t.host_name,
dp.db_name,
dp.base_name,
NVL(dp.dg_role,'NONE') AS dg_role,
CASE WHEN rc.target_guid IS NOT NULL THEN 'CONFIGURED' ELSE 'NOT CONFIGURED' END AS config_status,
rb.last_backup_time
FROM db_targets t
JOIN db_properties dp ON t.target_guid = dp.target_guid
LEFT JOIN rman_configured rc ON t.target_guid = rc.target_guid
LEFT JOIN recent_backup rb ON t.target_guid = rb.target_guid
),
-- Group by the canonical base_name so primary & standbys map together
grouped AS (
SELECT
base_name,
MAX(CASE WHEN config_status = 'CONFIGURED' THEN 1 ELSE 0 END) AS any_configured,
MAX(CASE WHEN last_backup_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done,
MAX(last_backup_time) AS last_backup_time
FROM merged
GROUP BY base_name
)
SELECT
m.base_name,
m.db_name AS example_member_db_name,
m.host_name,
m.dg_role,
m.config_status,
NVL(TO_CHAR(m.last_backup_time,'YYYY-MM-DD HH24:MI:SS'), 'N/A') AS last_backup_time,
CASE
WHEN g.any_configured = 0 AND g.recent_backup_done = 0 THEN '❌ NO BACKUP CONFIGURED OR RUN'
WHEN g.any_configured = 1 AND g.recent_backup_done = 0 THEN '⚠️ CONFIGURED BUT NO RECENT BACKUP'
WHEN g.any_configured = 0 AND g.recent_backup_done = 1 THEN '⚠️ BACKUP RUN BUT NO CONFIGURATION FOUND'
ELSE '✅ BACKUP OK (Primary or Standby Covered)'
END AS backup_protection_status,
ROUND(CASE WHEN g.last_backup_time IS NOT NULL THEN (SYSDATE - g.last_backup_time) ELSE NULL END, 2) AS days_since_last_success
FROM merged m
JOIN grouped g ON m.base_name = g.base_name
ORDER BY backup_protection_status, m.base_name, m.host_name;
-- ==========================================================
-- OEM Report: Databases Not Protected by Backup (Enhanced)
-- Author : Daidipya Upalekar (enhanced by Nikhil & ChatGPT)
-- Purpose: Identify databases (Primary/Standby) without
-- backup configuration or recent backup activity.
-- Handles Data Guard by grouping on DB_NAME.
-- ==========================================================
WITH db_targets AS (
SELECT
t.target_guid,
t.target_name,
t.host_name
FROM sysman.mgmt$target t
WHERE t.target_type IN ('oracle_database', 'rac_database')
),
db_properties AS (
SELECT
p.target_guid,
MAX(CASE WHEN p.property_name = 'DBName' THEN p.property_value END) AS db_name,
MAX(CASE WHEN p.property_name IN ('DatabaseRole', 'DBRole', 'DataGuardStatus')
THEN p.property_value END) AS dg_role
FROM sysman.mgmt$target_properties p
GROUP BY p.target_guid
),
-- Backup configuration at target level
rman_configured AS (
SELECT DISTINCT
r.target_guid,
r.target_name,
r.host_name
FROM sysman.mgmt$ha_rman_config r
),
-- Most recent backup time at target level
recent_backup AS (
SELECT
b.target_guid,
MAX(b.end_time) AS last_backup_time
FROM sysman.mgmt$ha_backup b
WHERE UPPER(b.status) = 'SUCCESS'
GROUP BY b.target_guid
),
-- Combine target, property, config, and backup info
merged AS (
SELECT
t.target_guid,
t.target_name,
t.host_name,
p.db_name,
NVL(p.dg_role, 'NONE') AS dg_role,
CASE
WHEN rc.target_guid IS NOT NULL THEN 'CONFIGURED'
ELSE 'NOT CONFIGURED'
END AS config_status,
rb.last_backup_time
FROM db_targets t
JOIN db_properties p ON t.target_guid = p.target_guid
LEFT JOIN rman_configured rc ON t.target_guid = rc.target_guid
LEFT JOIN recent_backup rb ON t.target_guid = rb.target_guid
),
-- Group at DB_NAME level (so DG primary & standby are treated as one logical DB)
grouped AS (
SELECT
db_name,
MAX(CASE WHEN config_status = 'CONFIGURED' THEN 1 ELSE 0 END) AS any_configured,
MAX(CASE WHEN last_backup_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done,
MAX(last_backup_time) AS last_backup_time
FROM merged
GROUP BY db_name
)
-- Final output
SELECT
m.db_name,
m.host_name,
m.dg_role,
m.config_status,
NVL(TO_CHAR(m.last_backup_time, 'YYYY-MM-DD HH24:MI:SS'), 'N/A') AS last_backup_time,
CASE
WHEN g.any_configured = 0 AND g.recent_backup_done = 0 THEN '❌ NO BACKUP CONFIGURED OR RUN'
WHEN g.any_configured = 1 AND g.recent_backup_done = 0 THEN '⚠️ CONFIGURED BUT NO RECENT BACKUP'
WHEN g.any_configured = 0 AND g.recent_backup_done = 1 THEN '⚠️ BACKUP RUN BUT NO CONFIGURATION FOUND'
ELSE '✅ BACKUP OK (Primary or Standby Covered)'
END AS backup_protection_status
FROM merged m
JOIN grouped g ON m.db_name = g.db_name
ORDER BY backup_protection_status, m.db_name, m.host_name;
-- ==========================================================
-- OEM Report: Databases Not Protected by Backup
-- Author : Daidipya Upalekar
-- Purpose: Identify databases (Primary/Standby) without
-- backup configuration or recent backup activity
-- ==========================================================
WITH db_targets AS (
SELECT
t.target_guid,
t.target_name,
t.host_name
FROM sysman.mgmt$target t
WHERE t.target_type IN ('oracle_database', 'rac_database')
),
db_properties AS (
SELECT
p.target_guid,
MAX(CASE WHEN p.property_name = 'DBName' THEN p.property_value END) AS db_name,
MAX(CASE WHEN p.property_name IN ('DatabaseRole', 'DBRole', 'DataGuardStatus')
THEN p.property_value END) AS dg_role
FROM sysman.mgmt$target_properties p
GROUP BY p.target_guid
),
rman_configured AS (
SELECT DISTINCT target_name
FROM sysman.mgmt$rman_config
),
recent_backup AS (
SELECT
b.target_guid,
MAX(b.end_time) AS last_backup_time
FROM sysman.mgmt$ha_backup b
GROUP BY b.target_guid
),
merged AS (
SELECT
t.target_guid,
t.target_name,
t.host_name,
p.db_name,
NVL(p.dg_role, 'NONE') AS dg_role,
CASE
WHEN EXISTS (
SELECT 1
FROM sysman.mgmt$rman_config r
WHERE LOWER(r.target_name) LIKE LOWER(p.db_name) || '%'
) THEN 'CONFIGURED'
ELSE 'NOT CONFIGURED'
END AS config_status,
r.last_backup_time
FROM db_targets t
JOIN db_properties p ON t.target_guid = p.target_guid
LEFT JOIN recent_backup r ON t.target_guid = r.target_guid
),
grouped AS (
SELECT
db_name,
MAX(CASE WHEN config_status = 'CONFIGURED' THEN 1 ELSE 0 END) AS any_configured,
MAX(CASE WHEN last_backup_time >= SYSDATE - 3 THEN 1 ELSE 0 END) AS recent_backup_done
FROM merged
GROUP BY db_name
)
SELECT
m.db_name,
m.host_name,
m.dg_role,
m.config_status,
NVL(TO_CHAR(m.last_backup_time, 'YYYY-MM-DD HH24:MI:SS'), 'N/A') AS last_backup_time,
CASE
WHEN g.any_configured = 0 AND g.recent_backup_done = 0 THEN '❌ NO BACKUP CONFIGURED OR RUN'
WHEN g.any_configured = 1 AND g.recent_backup_done = 0 THEN '⚠️ CONFIGURED BUT NO RECENT BACKUP'
WHEN g.any_configured = 0 AND g.recent_backup_done = 1 THEN '⚠️ BACKUP RUN BUT NO CONFIGURATION FOUND'
ELSE '✅ BACKUP OK (Primary or Standby Covered)'
END AS backup_protection_status
FROM merged m
JOIN grouped g ON m.db_name = g.db_name
ORDER BY backup_protection_status, m.db_name, m.host_name;
SELECT
t.target_name AS database_name,
t.host_name,
MAX(b.end_time) AS last_backup_time,
CASE
WHEN MAX(b.end_time) IS NULL THEN 'NO BACKUP RUN EVER'
WHEN MAX(b.end_time) < SYSDATE - 3 THEN 'NO RECENT BACKUP (Older than 3 days)'
ELSE 'RECENT BACKUP OK'
END AS backup_activity
FROM sysman.mgmt$target t
LEFT JOIN sysman.mgmt$ha_backup b
ON t.target_guid = b.target_guid
WHERE t.target_type IN ('oracle_database', 'rac_database')
GROUP BY t.target_name, t.host_name
ORDER BY backup_activity, last_backup_time;
WITH all_dbs AS (
SELECT target_guid, target_name, host_name
FROM sysman.mgmt$target
WHERE target_type IN ('oracle_database', 'rac_database')
),
rman_configured AS (
SELECT DISTINCT target_name FROM sysman.mgmt$rman_config
),
backup_runs AS (
SELECT DISTINCT database_name FROM sysman.mgmt$ha_backup
)
SELECT
TRIM(SUBSTR(a.target_name, 1, INSTR(a.target_name, '.') - 1)) AS database_name,
a.host_name,
CASE
WHEN r.target_name IS NOT NULL THEN 'OEM RMAN CONFIGURED'
WHEN b.database_name IS NOT NULL THEN 'BACKUP RUN FOUND (Manual/External)'
ELSE 'NO BACKUP CONFIGURED'
END AS backup_status
FROM all_dbs a
LEFT JOIN rman_configured r
ON TRIM(SUBSTR(a.target_name, 1, INSTR(a.target_name, '.') - 1)) =
TRIM(SUBSTR(r.target_name, 1, INSTR(r.target_name, '.') - 1))
LEFT JOIN backup_runs b
ON TRIM(SUBSTR(a.target_name, 1, INSTR(a.target_name, '.') - 1)) =
TRIM(SUBSTR(b.database_name, 1, INSTR(b.database_name, '.') - 1))
ORDER BY backup_status, a.host_name;
SELECT
TRIM(SUBSTR(t.target_name, 1, INSTR(t.target_name, '.') - 1)) AS database_name,
t.host_name,
NVL(p.property_value, 'NOT CONFIGURED') AS backup_configured
FROM
sysman.mgmt$target t
LEFT JOIN sysman.mgmt$target_properties p
ON t.target_guid = p.target_guid
AND p.property_name IN ('ConfiguredBackupSchedule', 'BackupConfigured')
WHERE
t.target_type IN ('oracle_database', 'rac_database')
AND (p.property_value IS NULL OR p.property_value IN ('NO', 'NOT CONFIGURED'))
ORDER BY
t.host_name;
Hello Friends! I am aliena part time blogger from Bihar. I am a student and I manage my study as well as my blog. I am 14 years old and I want to help those people who really want to do something on internet.
Copyright © 201. Metro UI Theme. Designed by: Templateism
