Re: After Creating New Dtabase...

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 22 Jun 2004 16:52:49 +1000
Message-ID: <40d7d725$0$18671$afc38c87@news.optusnet.com.au>

You lsnrctl output and network configuration files look fine (with a fairly significant quibble about your choice of service names which I'll detail below). So thanks for posting them. It is not clear to me why there should be any connection problems with such a setup, so I just want to be 100% clear on what the actual problem is. Your last-but-one post said:

"I am just informing you that for my firstdb sid is required but for seconddb no sid is need only through userid and password I can connect to my seconddb. But if I giving the sid "seconddb" then getting error [12514]"

Now, I'm not 1000% clear on what that actually means. I *think* it means that if you do this:

set ORACLE_SID=firstdb

Then a connection like this:

connect system/dizwell

....works. And you correctly end up connected to the FIRSTDB database.

Whereas, when you do this:

set ORACLE_SID=seconddb

Then a connect system/dizwell fails with a 'can't resolve service name' error.

However, you can connect correctly to the second instance by typing something like

connect system/dizwell_at_seconddb

Have I got that right?

If that is indeed the correct interpretation of your last post, I can't think why it should be so. If you can connect to it via the tnsnames, which itself references a service name of "SecondDB", then setting ORACLE_SID to that should work just as well. Are you absolutely positive you typed the thing correctly when you were setting ORACLE_SID? There are two "d"s in the middle there, after all! If you promise faithfully you are definitely typing things correctly, maybe someone else here will have a clue why it works with ORACLE_SID set to one value but not to another. Otherwise, I must bow out.

A couple of suggestions: why don't you cut-and-paste your SQL Plus/Command Prompt session where you show us exactly what you are typing that gets a connection to each instance or which generates an error. Then we don't just have to take your word for it that you are typing things correctly, or imagine what might be the problem, but can see it for ourselves. For example, I might post something like this:

C:\>set ORACLE_SID=win92
C:\>sqlplus system/dizwell
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jun 22 16:49:06 2004 Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> exit
C:\>set ORACLE_SID=xxxx
C:\>sqlplus system/dizwell

Now you can see what I'm typing, and whether a particular command produces a particular error or not. Why not try the same sort of thing with your two databases and let us see the results?

Second (and my main quibble with what you posted from your lsnrctl services output), when you created these databases, I suspect you didn't name them properly. Did you use the Database Creation Assistant to create them? If so, when you get to screen 3 of the assistant, it asks you for two elements: a global database name and a SID. The global database name should be something like firstdb.local, or seconddb.nodomain.com or mydb.imadethisup -a fully-qualified name in other words, not just 'firstdb', 'seconddb', 'mydb' and so on. It is the SID which should be the short-form 'firstdb' or 'seconddb'. You will find it easier to diagnose connection issues when the service name (the fully-qualified name with dots in it) is not the same as the SID. If you read that screen 3 very carefully, it does actually mention that the global database name should be in the form of 'name.domain' -and if you haven't got a domain, just make one up as I have shown you here.

Regards
HJR Received on Tue Jun 22 2004 - 01:52:49 CDT