Tuesday, December 29, 2009

Instance_Number is busy Message during Standby Instance Startup

Recently I encountered a situation that demonstrates how, in an Oracle database, an error may stem from a very unrelated cause. A DBA was building a physical standby database for an upcominmg training. The two servers he was using were part of a RAC cluster; so the Oracle binaries were already there. He decided to use the same ORACLE_HOME for the new databases as well - a quite logical decision to savbe on space and administration issues. He created a primary database on server n1 and a standby database on the server n2. Follooing the usual manual procedure in building the standby database, he copied the pfile from primary database, modified the parameters and brought up the standby instance in nomount mode on the server n2.

SQL> startup nomount pfile=initSTBY.ora

But it refused to come up, with the following error:

ORA-00304: requested INSTANCE_NUMBER is busy

Alert log showed:

USER (ospid: 14210): terminating the instance due to error 304
Instance terminated by USER, pid = 14210

This was highly unusual. The primary and standby both were non-RAC; there was no instance number concept in a non-RAC database. By the way, the RAC instance on that server (or on the server n1) was not running; so there was no question of any conflict with the RAC instances either. The primary database was called PRIM while the standby was called STBY - eliminating the possibility of an instance name clash as well. And this error came while merely trying to start the instance, not even while mounting - eliminating the standby controlfile as a cause.

The error 304 showed:

00304, 00000, "requested INSTANCE_NUMBER is busy"
// *Cause: An instance tried to start by using a value of the
// initialization parameter INSTANCE_NUMBER that is already in use.
// *Action: Either
// a) specify another INSTANCE_NUMBER,
// b) shut down the running instance with this number
// c) wait for instance recovery to complete on the instance with
// this number.

Needless to say, being for a non-RAC database there was no "instance_number" parameter in the initialization parameter file of primary or the standby. So, the suggestions for the resolution seemed odd. MetaLink provided no help. All the ORA-304 errors were related to RAC with the instance_number mismatch.

As it always happens, it fell on my lap at this time. With just days to go live, I had to find a solution quickly. Long hours of troubleshooting, tracing the processes and examination of occasional trace files did not yield any clue. All the clue seemed to point to RAC, which this database was not. The Oracle Home was a RAC home, which meant the oracle binary was linked with the "rac" option.

So, the next logical step was to install a new Oracle Home without the rac option. After doing so, I tried to bring up the instance, using the new ORACLE_HOME and LD_LIBRARY_PATH variable; but, alas, the same error popped up.

Just to eliminate the possibility of some unknown bug, I decided to put an instance_number parameter, setting it to "1", from the default "0". The same error. I changed it to "2", again, the result was the same error.

Although this didn't help, at least it gave a clue that the error was not related to instance_number. The error message was clearly wrong. With this in mind, I went back to the basics. I went through the alert log with a fine toothed comb, scanning and analyzing each line.

The following line drew my attention:

DB_UNQIUE_NAME STBY is not in the Data Guard configuration

This was odd; the db_unique_name STBY was not defined in DG configuration. [BTW, note the spelling of "unique" in the message above. That is not what I typed; it was a copy and paste from the actual message in the alert log. Someone in Oracle Development should really pay atytention to typos in messages. This is clearly more than a nuisance; what if some process scans for db_unique_name related errors? It will not find the message at all!]

Checking the dg configuration, I found that the DBA has correctly defined the primary and standby names. In any case, Data Guard has not been started yet; this is merely at the instance startup - why is it complaining for data guard configuration at this time.

Perplexed, I resorted to a different approach. I renamed the pfile and all other relevant files. Then I built the standby myself, from scratch - using the same names - PRIM and STBY. And this time, everything worked fine. The instance STBY did come up.

While this solvbed the urgency problem, everyone, inclduing myself, wanted to know what the issue was in the earlier case where the DBA had failed to bring up the instance. To get the answer, I compared the files I created with the DBA created when tried and failed. Voila! The cause was immediately clear - the DBA forgot to put a vital parameter in the pfile of the standby instance:

db_unique_name = 'STBY'

This parameter was absent; so it took the default value as the db_name, which was "PRIM". This caused the instance to fail with a seemingly unrelated message - "ORA-304 Instance_number is busy"!

Learning Points
  1. In Oracle, most errors are obvious; but some are not. So, do not assume the error message is accurate. If all logic fails, assume the error messsage is wrong, or at least inaccurate.
  2. Instead of over-analyzing the process already followed, it may make sense to take a breather, wipe out everything and start fropm scratch. This is evben mor effective when someone else does it, offering a fresh approach and possibly not repeating the same mistakes.
  3. Finally, the issue at hand: if you do not define db_unique_name parameter in the standnby instance, you will receive ORA-304 during instance startup.

Hope this was helpful. Happy New Year, everybody.

16 comments:

Joel Garry said...

Sure enough:

/oracle/app/oracle/product/10.2.0/db_1/bin$ grep UNQIUE oracle
Using log_archive_dest parameter standby_archive_dest valueLOG_ARCHIVE_DUPLEX_DESTWarning: LOG_ARCHIVE_DUPLEX_DEST invalidLOG_ARCHIVE_DUPLEX_DEST destination string exceeds length limitLOG_ARCHIVE_DUPLEX_DEST destination cannot be translatedWarning: LOG_ARCHIVE_DUPLEX_DEST deferredLOG_ARCHIVE_DUPLEX_DEST destination duplicates %sbuffer for concatenated stringskcrrdini.1kcrrdini.2Archive destination %s invalidatedDB_UNQIUE_NAME %s is not in the Data Guard configurationDB_UNIQUE_NAME attribute is requiredenablealternateUsing %s parameter default value as %*s%s: Warning; less destinations available than specifiedby LOG_ARCHIVE_MIN_SUCCEED_DEST init.ora parameterUsing %s parameter default value as %.*sUsing %s parameter default value as %sLOCATION=LOG_ARCHIVE_CALLOUT buffer_LOG_ARCHIVE_CALLOUTChanging _LOG_ARCHIVE_CALLOUT from '%s' to '%s'

word: dimagie

The Human Fly said...

Interestingly, few weeks ago, I had similar story but in a different way than yours. I was trapped into similar situation when I was trying to restore a database twice on two different nodes of our 6 node RAC environment with similar db name. After wasting couple of hours, I set the db_unique_name to the second restore and it worked perfectly. I must agree with you that the ORA error definitely misleads.

Happy holidays.

Jaffar

20123 said...

我們不是因為快樂而歌唱,而是唱歌使我們快樂 ..................................................

Anonymous said...

很期待新的內容,幫你推個文^^ ........................................

daspeac said...

you can quickly reproduce the import .sql into mssql process with another utility for data recovery

data recovery ohio said...

Just wanted to drop a comment and say I am new to your blog and really like what I am reading.Some recovery programs have interactive and intuitive user interface, easy to use and thus allows you to have successful recovery without any trouble.A good recovery program is capable of recovering all sorts of lost files from all file versions of Mac OS X and windows.

meldaresearchusa said...

Our non plagiarized college essays writers have been 14 years experience in the field where they have assisted thousands of returning clients in attaining high scores in their college research paper services.

Smart Research USA said...

If you are searching for Custom Research Writing Services then you can opt for our Affordable Writing Services Online because we provide students across the globe with Academic Essay Writing Services that are original and authentic.

chanee said...

A good website is useful.
search-football.com
ladasport.cz

มโน เอาเอง said...

Welcome to My Blog
inmourning.net
astronomyvacations.com

chanee said...

Online casino recipes spin for money
แนะนำเว็บสล็อตน่าเล่น 2020
วิธีเล่นสล็อต ให้ได้กำไรกลับมา
สล็อต ออนไลน์ เกมการเดินของนักพนัน เล่นจริงได้จริง
สูตรสล็อต พารวย

bansal tirkey said...

They can disrobe before you. If you need to recruit them for joy by blending with prominent Call Girls in Chanakyapuri, you can appreciate the night with a solitary call or WhatsApp message. On the off chance that you are frugal yet need to appreciate pleasurable closeness proceed to visit the spot while our agency furnish you with respectable and proficient Call Girls in Chanakyapuri to excite your remain. Our escort agency, Chanakyapuri Call Girls have connections to Call Girls in Chanakyapuri with the most blazing, sizzling and enchanting highlights. At long last, we know about some deceitful demonstrations submitted by most Call Girls in Chanakyapuri. You are really supposed to go for Call Girls in Chanakyapuri if you really want to have the best escort. You will surely be astonished to explore their natural beauty and looks. The Call Girls in Chanakyapuri always thinks of their clients.

bansal tirkey said...

We have actual scenarios that cause your purpose to double or treble. An escort maidservant is your partner for every action you want to do with them, similar to the Escorts Service in Chanakyapuri do. For your convenience, Eager for any of this young lady’s room, then you will be meeting with an independent, and clever young Escorts Service in Chanakyapuri committed to suit you an incredible social and suggestive experienceThe availability of Top Escorts Service in Chanakyapuri and other forms of Escorts, such as those associated with VIPs, makes it a popular choice amongst those who cannot afford their companion. Escorts Service in Chanakyapuri For Booking of Escorts Service in Chanakyapuri Delhi. Each and every detail that I have narrated here about my bodily assets is fully authentic and reliable We attempt the best Prostitutes in Chanakyapuri to be capable it give you the best assistance from our Escorts Service in Chanakyapuri. We have each sort of the young lady that can give you harmony and body love when you will invest enough energy with that young lady. But at the end of the day, human behavior is unpredictable and often goes against the tenets of probity and established norms. In this article, we will be delving a bit deep into the world of Escorts Service in Chanakyapuri

슬롯사이트 said...

Of course, your article is good enough, 슬롯사이트 but I thought it would be much better to see professional photos and videos together. There are articles and photos on these topics on my homepage, so please visit and share your opinions.

Rudra Digital Marketing Services said...

Thanks for the valauble information shared here, which is very useful https://powerbipros.in/

lemsay said...

Hi, I’m binnil. I’m a social media manager living in newyork. I am a fan of ,health,beauty,fitness. I’m also interested in reading and entrepreneurship. You can read my blog with a click on the bu…

READ MORE : bizautomotive

Translate