A misleading ORA-16047
Posted by Harald van Breederode on February 19, 2009
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:
SQL> alter system set log_archive_dest_2 2 = 'SERVICE=tutor1d_nledu02 LGWR SYNC AFFIRM'; alter system set log_archive_dest_2 * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-16052: DB_UNIQUE_NAME attribute is required
The error message seemed pretty clear and was quickly corrected:
SQL> alter system set log_archive_dest_2 2 = 'SERVICE=tutor1d_nledu02 LGWR SYNC AFFIRM db_unique_name=tutor1d_nledu02'; System altered.
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:
Sun Feb 15 16:22:57 2009 Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[1]: Assigned to RFS process 25545 RFS[1]: Identified database type as 'physical standby' Primary database is in MAXIMUM PERFORMANCE mode RFS LogMiner: Client disabled from further notification
This seems to indicate that the primary database connects successfully to the standby and redo should come in. Looking in the alert.log on the primary however revealed:
Sun Feb 15 16:22:50 2009 ALTER SYSTEM SET log_archive_dest_2='SERVICE=tutor1d_nledu02 LGWR SYNC AFFIRM db_unique_name=tutor1d_nledu02' SCOPE=BOTH; Sun Feb 15 16:22:53 2009 ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** Sun Feb 15 16:22:54 2009 LNSb started with pid=41, OS id=5228 Errors in file /home/tutor1d/diag/rdbms/tutor1d/tutor1d1/trace/tutor1d1_lgwr_30073.trc: ORA-16047: DGID mismatch between destination setting and standby LGWR: Error 16047 creating archivelog file 'tutor1d_nledu02' LGWR: Failed to archive log 2 thread 1 sequence 35 (16047) Thread 1 advanced to log sequence 35 (LGWR switch) Current log# 2 seq# 35 mem# 0: /sd01/oradata/TUTOR1D/onlinelog/o1_mf_2__bbb38e65.log Thread 1 cannot allocate new log, sequence 36 Checkpoint not complete Current log# 2 seq# 35 mem# 0: /sd01/oradata/TUTOR1D/onlinelog/o1_mf_2__bbb38e65.log Sun Feb 15 16:23:02 2009 ARC0: Archivelog destination LOG_ARCHIVE_DEST_2 disabled: Data Guard configuration identifier mismatch
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
.
SQL> select db_unique_name from v$database; DB_UNIQUE_NAME ------------------------------ tutor1d_nledu02
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
Paul van Eldijk said
I’m glad you found the problem, because during class it was indeed puzzeling to see thsi behaviour. And I agree, DG broker is in many cases your friend :)
Paul
praveen said
Your article saved me a lot of confusion and frustration as I was getting the same error!
Thanks
Ingo Wevers said
Hi Harold,
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.
Thanks,
Ingo
Arun Kumar said
Hi Harold,
I have also the same prob. But i do not have RAC involved in this. Please help me out resolving this.
Please mail me.. thanks in advance
Regards
Arun Kumar
Aseem said
Hey Harald,
Thanks a ton…it worked for me as well :)
Jeroen Rijs said
Hi Harald,
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.
Regards,
Jeroen Rijs
Harald van Breederode said
Hi Jeroen,
Thanx for sharing this. I guess this is another reason for using the broker instead of manually managing your Data Guard environment ;-)
-Harald
Geroge said
Your article is continuously helping people to resolve such puzzle. You save my time to rebuild the test env.
George
clinique chiropratique Sherbrooke said
That is a good tip particularly to those fresh to the blogosphere.
Short but very precise information… Thanks for sharing this one.
A must read post!
jgm said
wow! this solved the same problem I was having!
Thanks for the post. Somehow the standby did not have the log_archive_config set