Re: Two instances with same SID in a same machine

From: Hans Forbrich <forbrich_at_yahoo.net>
Date: Tue, 31 Aug 2004 01:13:38 GMT
Message-ID: <6hQYc.77809$X12.49994@edtnps84>

Joel Garry wrote:

> Maybe there is "too much" intelligence in the listener.  Please post
> output of lsnrctl status, both homes.
> 
> jg

I think the Listener has just the right amount of intelligence. More will make configuring even harder :-Q

You will note from the log file from home 'B' that the listener simply does not have enough information to determine which specific instance ... there is NO HOME information transmitted from the client. And also note that the GLOBAL Database Name (and therefore the service) is identical for both instances.

Remember that a listener has the ability to listen on behalf of all services regardless of home (even non-DB services). Therefore, any environment that uses duplicate SIDs *and* duplicate Global DB Name, is asking for this kind of situation. Highly _not_ recomended.

However, here you go:

Listener 'A' from OH=ora904

oracle_at_fcc02:~> lsnrctl status

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 30-AUG-2004 18:52:03

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))) STATUS of the LISTENER

Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date                28-AUG-2004 19:44:34
Uptime                    1 days 23 hr. 7 min. 28 sec
Trace Level               off
Security                  OFF
SNMP                      OFF

Listener Parameter File
/opt/oracle/product/ora904/network/admin/listener.ora Listener Log File
/opt/oracle/product/ora904/network/log/listener.log Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcc02)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcc02)(PORT=8080))(Presentation=HTTP)(Session=RAW))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcc02)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "pts1.xyz.ca" has 3 instance(s).

  Instance "pts1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "pts1", status READY, has 1 handler(s) for this service...
  Instance "pts1", status READY, has 1 handler(s) for this service...

Service "pts1XDB.xyz.ca" has 2 instance(s). Instance "pts1", status READY, has 1 handler(s) for this service... Instance "pts1", status READY, has 1 handler(s) for this service... The command completed successfully
= = = = = = = = = = = = = = = = = =
Listener 'B' from OH=ora904b

oracle_at_fcc02:~> lsnrctl status

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 30-AUG-2004 18:55:14

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fcc02)(PORT=1531))) STATUS of the LISTENER

Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date                28-AUG-2004 19:48:26
Uptime                    1 days 23 hr. 6 min. 48 sec
Trace Level               off
Security                  OFF
SNMP                      OFF

Listener Parameter File
/opt/oracle/product/ora904b/network/admin/listener.ora Listener Log File
/opt/oracle/product/ora904b/network/log/listener.log Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcc02)(PORT=1531))) Services Summary...
Service "pts1.forbrichcomputing.ca" has 1 instance(s). Instance "pts1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

= = = = = = = = = = = = = = = = = = = = And, for your amusement, the log from 'B' ... (replacing IP and domain info)
Started with pid=13977 Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))) TNS-12542: TNS:address already in use TNS-12560: TNS:protocol adapter error TNS-00512: Address already in use Linux Error: 98: Address already in use

TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 28-AUG-2004 19:48:26

System parameter file is
/opt/oracle/product/ora904b/network/admin/listener.ora Log messages written to /opt/oracle/product/ora904b/network/log/listener.log Trace information written to
/opt/oracle/product/ora904b/network/trace/listener.trc Trace level is currently 0

Started with pid=14135
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcc02)(PORT=1531)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE 28-AUG-2004 19:48:26 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=fcc02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153093
120)) * status * 0
28-AUG-2004 19:50:35 *
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PTS1.XYZ.CA)(CID=(PROGRAM=C:\oracle\ora92\bin\sqlplusw.exe
)(HOST=FCC001L)(USER=Hans))) *
(ADDRESS=(PROTOCOL=tcp)(HOST=?.?.?.88)(PORT=1177)) * establish *
PTS1.XYZ.CA * 0
28-AUG-2004 20:01:29 *
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PTS1.XYZ.CA)(CID=(PROGRAM=C:\oracle\ora92\bin\sqlplusw.exe
)(HOST=FCC001L)(USER=Hans))) *
(ADDRESS=(PROTOCOL=tcp)(HOST=?.?.?.88)(PORT=1182)) * establish *
PTS1.XYZ.CA * 0
30-AUG-2004 18:55:14 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=fcc02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153093
120)) * status * 0
oracle_at_fcc02:/opt/oracle/product/ora904b/network/log> Received on Mon Aug 30 2004 - 20:13:38 CDT