Re: Serious listener problem - can anyone help?

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Fri, 10 May 2013 21:52:07 +0200
Message-ID: <CALH8A927av9R+AM7VOtJnDTNmd6jcduCMtfDEXGBV+9ZLp0fjg_at_mail.gmail.com>

Norman,
I try to summarize what's going on in the different components involved.

oratab file:

---

the oratab file is (only) important when connecting sqlplus directly to the instance. (especially at startup!)
it is not read by sqlplus directly but by oraenv. So if you set ORACLE_HOME and ORACLE_SID manually, oratab is not involved at all. Everytime sqlplus tries to directly connects to an instance it calculates a hash value from ORACLE_HOME and ORACLE_SID and connects to this value (I'm quite sure it's the semaphor which is the magic piece here, but I can't prove it right now).
This is done also at startup - and ALL subsequent started proceses for this instance are using this ORACLE_HOME & ORACLE_SID tuple.

listener:

---

every time (MTS / shared server is different => out of scope here) a client wants to connect to an instance using a listener the listener does quite the same as sqlplus does described ago: calculating a hash value for the particular instance, creates a new process which connects to this instance and link the clients network socket to this particular process (this step is not needed for local sqlplus).
The listener can get informations about ORACLE_HOME & ORACLE_SID from listener.ora (in case of SID_LIST) or pmon (in case of local_listener registration). - pmon is no problem here - it knows for sure it's own ORACLE_HOME and ORACLE_SID.
But if the SID_LIST entry does not match the ORACLE_HOME & ORACLE_SID tuple at _INSTANCE STARTUP_ it is exactly the same problem as a "sqlplus / as sysdba" with different ORACLE_HOME/ORACLE_SID.

I hope this explanation helps a little bit. I took some shortcuts but I did not leave anything important for this particular issue.

br
 Martin

On Fri, May 10, 2013 at 8:41 PM, Norman Dunbar <oracle_at_dunbar-it.co.uk>wrote:

> On 10/05/13 18:35, David Barbour wrote:
> > Probably not the answer, but you may have an extra slash in your oratab
> > entry.
> You get the prize along with _at_martinberx on Twitter! It was indeed an
> extra slash in oratab.
>
> The solution is as follows:
>
> 1. Shut down liatener and database using current oratab versions - with
> extra slash.
>
> 2. Edit oratab, remove trailing slash from Oracle Homes.
>
> 3. Restart listeners. restart databases.
>
> 4. Connect via listener, it works perfectly.
>
> An extra trailing slash, for crying out loud.
>
> My deepest thanks to everyone who took p[art in this problem solving
> exercise. I spend the best part of today on it, you guys toom mere
> minutes. I am humbled by your awsome-ness!
>
> Thanks all.
>
>
> Cheers,
> Norm.
>
>
> --
> Norman Dunbar
> Dunbar IT Consultants Ltd
>
> Registered address:
> Thorpe House
> 61 Richardshaw Lane
> Pudsey
> West Yorkshire
> United Kingdom
> LS28 7EL
>
> Company Number: 05132767
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 10 2013 - 21:52:07 CEST