A misleading ORA-16047

The problem

Last week I was teaching a Data Guard course and ran a demo how to setup a physical standby database on a Real Application Clusters (RAC) database. Normally all my demos run smoothly but not this time. First I received an ORA-16052 error:

Note: Nothing was changed on my demo environment except upgrading from 11.1.0.6.0 to 11.1.0.7.0 But that isn’t considered a change is it?

Secondly by the time the standby should receive redo from the primary nothing happened. No matter how often I switched redo logs on the primary, the standby did not receive a single redo log entry. Looking in the alert.log on the standby showed:

Thus the standby tells me that the primary connected successfully whilst the primary tells me it can not connect to the standby due to an ORA-16047 error. Here is the description of an ORA-16047:

$ oerr ora 16047
16047, 00000, "DGID mismatch between destination setting and standby"
// *Cause: The DB_UNIQUE_NAME specified for the destination does not match
// the DB_UNIQUE_NAME at the destination.
// *Action: Make sure the DB_UNIQUE_NAME specified in the LOG_ARCHIVE_DEST_n
// parameter defined for the destination matches the DB_UNIQUE_NAME
// parameter defined at the destination.

You already saw that I specified tutor1d_nledu02 as the DB_UNIQUE_NAME for LOG_ARCHIVE_DEST_2 and here is the prove that my standby database has indeed the correct DB_UNIQUE_NAME.

The solution

As you can imagine I was completely puzzled by this conflicting information and having students awaiting your actions doesn’t give you much time for troubleshooting. So I decided to continue my demo and see what would happen if the Data Guard Broker was given controll over my setup. Much to my surprise redo started to flow once the broker was in charge! My first reaction was “What can the broker do what I can’t?” I did not have time to figure this out during class but I did during the weekend following.

The good news is that I was able to reproduce it but I still could not get redo flowing myself. I re-created my RAC database and suddenly my demo worked fine. The joy was only short because the second time I ran the demo the same problem showed up again. This led to the conclusion that it was probably database parameter related and peeking in the RAC spfile revealed that log_archive_config was configured. I did not set it when I re-created my RAC database nor did I set it in my demo. .It turned out that it was set by the broker and that I did not clean things up after running the demo. Thus all I had to do was setup log_archive_config in my demo to fix it permanently.

SQL> alter system set log_archive_config
2 = 'dg_config=(tutor1d,tutor1d_nledu02)';
System altered.

Had I paid more attention to the first ORA-16052 error it would have safe me a lot of time, because the error description is pretty clear:

$ oerr ora 16052
16052, 00000, "DB_UNIQUE_NAME attribute is required"
// *Cause: The DB_UNIQUE_NAME attribute is required when DG_CONFIG is enabled.
// *Action: Use the DB_UNIQUE_NAME attribute to specify a valid Data Guard
// Name for the destination. The list of valid DB_UNIQUE_NAMEs can
// be seen with the V$DATAGUARD_CONFIG view.

Conclusion

Sometimes things are not what they seems! In this case the primary database tried to connect to the standby database to verify its DB_UNIQUE_NAME. But the missing log_archive_config parameter on the standby database prevented the primary from logging in. Resulting in a misleading ORA-16047.

In order to prevent lengthy troubleshooting sessions one should either look error messages up or use the Data Guard Broker ;-)-Harald

Like this:

LikeLoading...

Related

This entry was posted on February 19, 2009 at 17:28 and is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

praveensaid

I got the advice to use this parameter to avoid message “Standby mount ID 0xf5cc6ffe not found ” which was continuously dumped into arc process trace file. And yes, I found your supporting article also.

Aseemsaid

Jeroen Rijssaid

Thanks for this great tip! I had a simillar problem when building a standby database. At the standby database, the setting of log_archive_config was blank (default) and it was not blank at the primary site. After changing this setting, the shipment without using the broker, started. The mistake was caused by creating and editing parameter files for primary and standby databases in the wrong order.