New views in Oracle Data Guard 23c

Oracle Data Guard 23c comes with many nice improvements for observability, which greatly increase the usability of Data Guard in environments with a high level of automation.

For the 23c version, we have the following new views.V$DG_BROKER_ROLE_CHANGE

This view tracks the last role transitions that occurred in the configuration. Example:

The event might be a Switchover, Failover, or Fast-Start Failover.

In the case of Fast-Start Failover, you will see the reason (typically “Primary Disconnected” if it comes from the observer, or whatever reason you put in DBMS_DG.INITIATE_FS_FAILOVER.

No more need to analyze the logs to find out which database was primary at any moment in time!

V$DG_BROKER_PROPERTY

Before 23c, the only possible way to get a broker property from SQL was to use undocumented (unsupported) procedures in the fixed package DBMS_DRS. I’ve blogged about it in the past, before joining Oracle.

Now, it’s as easy as selecting from a view, where you can get the properties per member or per configuration:

The example selects just three columns, but the view is rich in detailing which properties apply to which situation (scope, valid_role):

The monitorable properties can be monitored using DBMS_DG.GET_PROPERTY(). I’ll write a blog post about the new PL/SQL APIs in the upcoming weeks.

I wish I had this view when I was a DBA 🙂

V$FAST_START_FAILOVER_CONFIG

If you have a Fast-Start Failover configuration, this view will show its details:

This view replaces some columns currently in v$database, that are therefore deprecated:

V$FS_LAG_HISTOGRAM

This view is useful to calculate the optimal FastStartFailoverLagTime.

It shows the frequency of Fast-Start Failover lags and the most recent occurrence for each bucket.

LAG_TIME is the upper bound of the bucket, e.g.

  • 5 -> between 0 and 5 seconds
  • 10 -> between 5 and 10 seconds
  • etc.

It’s refreshed every minute, only when Fast-Start Failover is enabled (also in observe-only mode).

V$FS_FAILOVER_OBSERVERS

This view is not new, however, its definition now contains more columns:

This gives important additional information about the observers, for example, the last time a specific observer was able to ping the primary or the target (in seconds).

Also, the path of the log file and runtime data file are available, making it easier to find them on the observer host in case of a problem.

Conclusion

These new views should greatly improve the experience when monitoring or diagnosing problems with Data Guard. But they are just a part of many improvements we introduced in 23c. Stay tuned for more 🙂

Ludovico

New in Data Guard 21c and 23c: Automatic preparation of the primary

Oracle Data Guard 21c came with a new command:

The command prepare database for data guard automatically sets parameters and creates standby redo logs according to best practices.This command prepares a database to become primary in a Data Guard configuration.

It sets many recommended parameters:

Sets the RMAN archive deletion policy, enables flashback and force logging, creates the standby logs according to the online redo logs configuration, and creates an spfile if the database is running with an init file.

If you tried this in 21c, you have noticed that there is an automatic restart of the database to set all the static parameters. If you weren’t expecting this, the sudden restart could be a bit brutal approach.

In 23c, we added an additional keyword “restart” to specify that you are OK with the restart of the database. If you don’t specify it, the broker will complain that it cannot proceed without a restart:

If you specify it, it will proceed with the restart:

Notice that if you already have these static parameters set, the broker will just set the missing dynamic parameters without the need for a restart:

This new command greatly simplifies the preparation of a Data Guard configuration!

Before 21c, you had to do everything by hand.

Ludo

Does FLASHBACK QUERY work across incarnations or after a Data Guard failover?

Short answer: yes.

Let’s just see it in action.

First, I have a Data Guard configuration in place. On the primary database, the current incarnation has a single parent (the template from which it has been created):

Just to make room for some undo, I increase the undo_retention. On a PDB, that requires LOCAL UNDO to be configured (I hope it’s the default everywhere nowadays).

Then, I update some data to test flashback query:

At this point, I can see the current data, and the data as it was 1 hour ago:

Now, I kill the primary database and fail over to the standby database:

After connecting to the new primary, I can see the new incarnation due to the open resetlogs after the failover.

And I can still query the data as of a previous timestamp:

Or flash back the table, if required:

So yes, that works. The caveat is still that you need to retain enough data in the undo tablespace to rebuild the rows in their previous state.

Ludo

When it comes to using Oracle, trust Oracle…

A month ago, I saw this article published on the AWS architecture blog:

Disaster Recovery for Oracle Database on Amazon EC2 with Fast-Start Failover

I love seeing people suggesting Oracle Data Guard Fast-Start Failover for high availability. Nevertheless, there are a few problems with the architecture and steps proposed in the article.

I sent my comments via Disqus on the AWS blogging platform, but after a month, my comment was rejected, and the blog content hasn’t changed.

For this reason, I don’t have other places to post my comment but here…

  1. The link to the setup procedure is from 2009.
    We have official documentation that we keep up to date. The Fast-Start Failover part:
    https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/using-data-guard-broker-to-manage-switchovers-failovers.html#GUID-D26D79F2-0093-4C0E-98CD-224A5C8CBFA4
    and the Best Practices guide:
    https://docs.oracle.com/en/database/oracle/oracle-database/19/haovw/oracle-data-guard-best-practices.html#GUID-C3A78B07-6584-4380-8D53-E5B831A5894C
  2. The part about cascading standbys references a step-by-step guide from an external blog written many years ago for 11gR2.
  3. The DBMS_SERVICE doc is from 12cR1, while other links are from 21c doc or 19c doc. As of today, most implement 19c. That’s probably the version to use.
    https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SERVICE.html#GUID-C11449DC-EEDE-4BB8-9D2C-0A45198C1928
  4. The steps used to create the database service do not include any HA property, which will make most efforts useless. (see Table 153-6 in the link above).
  5. The article talks about TAF, but no steps exist to configure it. We don’t recommend TAF since 12c anyway. Today (19c), the recommendation is TAC (Transparent Application Continuity).
    https://www.oracle.com/docs/tech/application-checklist-for-continuous-availability-for-maa.pdf
  6. But, most important, TAF (or Oracle connectivity in general) does NOT require a host IP change! There is no need to change the DNS when using the recommended connection string with multiple address_lists.
  7. Some RedoRoutes examples are not correct. In this video I explain how they work and how to set them up:
    https://www.youtube.com/watch?v=huG8JPu_s4Q
  8. The diagram shows the master observer together with the standby database, which is a bad practice. I explain why and how here:
    https://www.youtube.com/watch?v=e81UPLfnLi0

The central message is:

If you need to implement a complex architecture using a software solution, pay attention that the practices suggested by the partner/integrator/3rd party match the ones from the software vendor. In the case of Oracle Data Guard, Oracle knows better 😉

Cheers

Ludovico

 

Video: Where should I put the Observer in a Fast-Start Failover configuration?

The video explains best practices and different failure scenarios for different observer placements. It also shows how to configure high availability for the observer.

Here’s the summary:

  • Always try to put the observer(s) on an external site.
  • If you don’t have any, put it where the primary database is, and have one ready on the secondary site after the role transition.
  • Don’t put the observer together with the standby database!
  • Configure multiple observers for high availability, and use the PreferredObserverHosts Data Guard member property to ensure you never run the observer where the standby database is.

 

Find Ludovico at Oracle Cloud World 2022!

Are you attending OCW, and do you want to find me and know more about how to avoid downtime and data loss? Or how to optimize your application configuration to make the most out of MAA technologies? Or any database, or technology-related topic?

Maybe you prefer just a chat and discussing life? Over a coffee, or tea? (or maybe beer?)

👇This is where you can find me during OCW.👇

Monday, October 17, 2022

6:30 PM – 10:00 PM – Customer Appreciation Event

Where: Mandalay Bay Shark Reef

This is an invitation-only event. If you are one of the lucky customers that possess an invitation, let’s meet there! It will be fun to discuss technology, business, and life while watching sharks and enjoying a drink together.

Tuesday, October 18, 2022

2:00 PM – 4:30 PM – Oracle Maximum Availability Architecture with Oracle RAC and Active Data Guard

Where: CloudWorld Hub, Database booth DB-01

Come together and ask anything Data Guard, Active Data Guard, RAC, FPP, or High Availability! See some products in action, and get some insights from my colleagues and me. The booth will be open during the whole exhibition time, but I will be certainly there on Tuesday for these two hours.

4:00 PM – 5:30 PM – Protect Your Business Using Oracle Full Stack Disaster Recovery Service –  Interactive Hands-On-Lab [HOL4089]

Where: Bellini 2003, The Venetian, Level 2

I will help my colleague Suraj Ramesh run the hands-on lab of this brand-new (actually, still to be released!) service for general-purpose Disaster Recovery in the cloud.

After HOL4089 until – 7:00 pm – Welcome Reception

Where: CloudWorld Hub, Database booth DB-01

I will probably join to say hello during the Welcome Reception. Maybe you can spot me there 🙂

Wednesday, October 19, 2022

10:00 AM – 12:00 PM – Oracle Maximum Availability Architecture with Oracle RAC and Active Data Guard

Where: CloudWorld Hub, Database booth DB-01

I will be there once again to answer all your questions and show some fancy stuff 🙂

1:15 PM – 2:00 PM – Oracle Data Guard—Active, Autonomous, and Always Protective [LRN3528]

Where: San Polo 3403, The Venetian, Level 3

I will talk about Data Guard, Active Data Guard, and what I consider the most important features today. Come to the session to know more!

3:00 PM – 4:30 PM – Protect Your Data with Oracle Active Data Guard – Interactive Hands-On-Lab [HOL4054]

Where: Bellini 2003, The Venetian, Level 2

I will run this hands-on lab. You will have an Active Data Guard 19c configuration in the cloud at your fingertips and you will play with role changes, corruption detection and reparation, and other features. I will be there to explain insights, hints, and recommendations on how to implement it in your work environment.

Thursday, October 20, 2022

11:40 AM – 12:00 PM – The Least-Known Facts About Oracle Data Guard and Oracle Active Data Guard [LIT4029]

Where: Ascend Lounge, CloudWorld Hub, The Venetian

This will be great! I bet you will discover MANY things that you did not know about Data Guard and Active Data Guard. Come to know more!

 

See you there!

Ludovico

Check, check… Does the mic still work? #JoelKallmanday

Update PHP:
Update WordPress:
New content:

It’s almost six months without blogging from my side. What a bad score!
It’s not a coincidence that I’m blogging today during #JoelKallmanDay.
A day that reminds the community how important it is to share. Knowledge, mostly. But also good and bad experiences, emotions…

A bittersweet day, at least for me.
On the bitter side: it reminds me of Joel, Pieter, and other friends that are not there anymore. That as a Product Manager, I have to wear big shoes, and it does not matter how good I try to do; I will always feel that it’s not good enough for the high expectations that I set for myself. Guess what! Being PM is way more complicated than I expected when I applied for the position two years ago. So many things to do or learn, so many requests, and so many customers! And being PM at Oracle is probably twice as complicated because it does not matter how good I (or we as a team) try to do; there will always be a portion of the community that picks on the Oracle technology for one reason or another.

On the bright side: it reminds me that I am incredibly privileged to have this role, working in a great team and helping the most demanding customers to get the most out of incredible technology. I love sharing, teaching, giving constructive feedback, producing quality content, and improving the customer experience. This is the sweet part of the job, where I am still taking baby steps when comparing myself to the PM legends we have in our organization. They are always glad to explain our products to the community, the customers, and colleagues! And they are all excellent mentors, each with a different style, background, and personal life.

And knowing people personally is, at least for me, the best thing about being part of a community (outside Oracle) and team (inside Oracle). We all strive for the best technical solutions, performance, developer experience, or uptime for the business. But we are human first of all. And this is what #JoelKallmanDay stands for me—trying to be a better human as a goal so that everything else comes naturally, including being a great colleague, community servant, or friend.

Far Sync and Fast-Start Failover Protection modes

Oracle advertises Far Sync as a solution for “Zero Data Loss at any distance”. This is because the primary sends its redo stream synchronously to the Far Sync, which relays it to the remote physical standby.

There are many reasons why Far Sync is an optimal solution for this use case, but that’s not the topic of this post 🙂

Some customers ask: Can I configure Far Sync to receive the redo stream asynchronously?

Although a direct standby receiving asynchronously would be a better idea, Far Sync can receive asynchronously as well.

And one reason might be to send asynchronously to one Far Sync member that redistributes locally to many standbys.

It is very simple to achieve: just changing the RedoRoutes property on the primary.

This will work seamlessly. The v$dataguard_process will show the async transport process:

 

What about Fast-Start Failover?

Up to and including 19c, ASYNC transport to Far Sync will not work with Fast-Start Failover (FSFO).

ASYNC redo transport mandates Maximum Performance protection mode, and FSFO supports that in conjunction with Far Sync only starting with 21c.

Before 21c, trying to enable FSFO with a Far Sync will fail with:

So if you want FSFO with Far Sync in 19c, it has to be MaxAvailability (and SYNC redo transport to the FarSync).


If you don’t need FSFO, as we have seen, there is no problem. The only protection mode that will not work with Far Sync is Maximum Protection:

If FSFO is required, and you want Maximum Performance before 21c, or Maximum Protection, you have to remove Far Sync from the redo route.

Ludovico

Can a physical standby database receive the redo SYNC if the Far Sync instance fails?

The answer is YES.

In the following configuration, cdgsima_lhr1pq (primary) sends synchronously to cdgsima_farsync1 (far sync), which forwards the redo stream asynchronously to cdgsima_lhr1bm (physical standby):

But if cdgsima_farsync1 is not available, I want the primary to send synchronously to the physical standby database. I accept a performance penalty, but I do not want to compromise my data protection.

I just need to set up the Redoroutes as follows:

This is defined the second part of the RedoRoutes rules:

Let’s test. If I shutdown abort the farsync instance:

I can see the new SYNC destination being open almost instantaneously (because the old destination fails immediately with ORA-03113):

Indeed, I can see the new NSS process (synchronous redo transport) spawn at that time:

Ludo