New Oracle 23ai SQL Views for Data Guard Monitoring: Smarter Visibility, Faster Decisions

Modern database environments demand real-time visibility, faster troubleshooting, and simplified monitoring—especially when dealing with high availability solutions like Oracle Data Guard. With the release of Oracle Database 23ai, Oracle introduces a powerful set of new SQL views and enhancements that significantly improve how DBAs monitor and manage Data Guard environments.

These updates eliminate the need for complex log analysis and provide direct, SQL-based insights into broker configurations, failover behavior, and role transitions.

Let’s explore these new capabilities and how they add real business value.

---

Why These New Views Matter

Oracle Data Guard has always been a cornerstone for disaster recovery. However, monitoring often required:

• Deep log analysis
• Broker CLI commands
• Multiple tools for full visibility

Oracle 23ai changes this by bringing everything closer to the database layer using simple SQL queries.

This means:

• Faster diagnostics
• Better automation
• Improved operational efficiency

---

1. V$DG_BROKER_PROPERTY: Full Configuration Visibility

One of the most useful additions is:

V$DG_BROKER_PROPERTY

This view provides:

• Broker configuration properties
• Details of all members in the configuration

Why It’s Powerful

Previously, retrieving broker properties required using:

• DGMGRL (Data Guard Broker CLI)

Now, you can simply run:

SELECT * FROM V$DG_BROKER_PROPERTY;

Key Benefits

• Centralized visibility inside the database
• Easier integration with monitoring tools
• Simplified scripting and automation

---

2. V$FAST_START_FAILOVER_CONFIGURE: Modern Failover Insights

Another major addition is:

V$FAST_START_FAILOVER_CONFIGURE

This view provides detailed information about:

• Fast-start failover configuration
• Current mode and status
• Target thresholds

Important Change

Columns related to fast-start failover in:

• V$DATABASE → Now deprecated

Instead, Oracle recommends using this new view.

Why This Matters

• Cleaner architecture
• More detailed insights
• Future-proof monitoring

---

3. V$DG_BROKER_ROLE_CHANGE: No More Guesswork

Tracking role transitions has always been tricky.

V$DG_BROKER_ROLE_CHANGE

This view provides:

• History of role changes
• Switchover events
• Failover events
• Fast-start failover actions
• Reasons for each transition

Key Advantage

• Stores up to 10 recent role changes
• Eliminates the need to scan logs manually

Real-World Impact

DBAs can now instantly answer:

• Which database was primary at a given time?
• Why did a failover happen?

---

4. V$FAST_START_FAILOVER_LAG_HISTOGRAM: Smarter Failover Tuning

Performance tuning is critical for failover strategies.

V$FAST_START_FAILOVER_LAG_HISTOGRAM

This view shows:

• Frequency distribution of failover lag
• Time buckets (e.g., 0–5 sec, 5–10 sec)
• Recent occurrences

How It Works

• Updated every minute
• Active only when fast-start failover is enabled

Why It’s Useful

• Helps determine optimal lag thresholds
• Improves failover decision accuracy
• Supports proactive performance tuning

---

5. Enhanced V$FS_FAILOVER_OBSERVER: Better Observer Monitoring

Oracle also enhanced the existing:

V$FS_FAILOVER_OBSERVER

New columns include:

• LAST_PING_PRIMARY → Time since last ping to primary
• LAST_PING_TARGET → Time since last ping to standby
• LOG_FILE → Full path to observer log
• STATE_FILE → Runtime data file path
• CURRENT_TIME → Timestamp of query

Why This Is Important

These additions provide:

• Better visibility into observer health
• Easier troubleshooting
• Faster issue resolution

Practical Example

If failover doesn’t trigger:

• Check ping times
• Verify observer connectivity
• Locate logs instantly

---

Real Business Value of These Enhancements

These new views are not just technical improvements—they directly impact business outcomes.

1. Faster Troubleshooting

• No need to dig through logs
• Immediate access to critical information

2. Improved Automation

• SQL-based monitoring enables scripting
• Easy integration with tools like Grafana, Prometheus

3. Better High Availability

• Accurate failover tuning
• Real-time insights into system health

4. Reduced Downtime

• Faster root cause analysis
• Proactive issue detection

---

Practical Use Cases

Monitoring Dashboard Integration

You can build dashboards using:

• V$DG_BROKER_PROPERTY for configuration
• V$FAST_START_FAILOVER_CONFIGURE for failover state
• V$FS_FAILOVER_OBSERVER for observer health

Incident Analysis

Use:

• V$DG_BROKER_ROLE_CHANGE to track failover history

Performance Optimization

Use:

• V$FAST_START_FAILOVER_LAG_HISTOGRAM to fine-tune thresholds

---

A Step Toward Autonomous Monitoring

These enhancements align with Oracle’s vision of:

• Autonomous databases
• AI-driven operations
• Simplified DBA workflows

By exposing critical Data Guard information through SQL:

• Monitoring becomes more accessible
• Systems become easier to manage
• Human effort is reduced

---

Final Thoughts

Oracle Database 23ai introduces a new level of transparency and control for Data Guard environments.

With views like:

• V$DG_BROKER_PROPERTY
• V$FAST_START_FAILOVER_CONFIGURE
• V$DG_BROKER_ROLE_CHANGE
• V$FAST_START_FAILOVER_LAG_HISTOGRAM
• Enhanced V$FS_FAILOVER_OBSERVER

DBAs can now:

• Monitor smarter
• Troubleshoot faster
• Optimize failover strategies with confidence

These features are not just incremental—they represent a shift toward intelligent, SQL-driven database operations.