I have 2 non-production databases than run on the same server. Both
are 8.1.7.4. Both configured very similarly. All parameters are very
similar, except for some memory variables and SID specific variables.
Each has a corresponding listener listening on a different port
(1521/1524); listener is always started first (although, this might be
necessary anymore). The sid's are DEV1 and PROD.

The machine connecting to them has entries that are identical in
tnsnames.ora file, except for SID/port (tried service_name too).
However, DEV1 accepts shared/MTS connections, but PROD doesn't. The
listener shows that dispatchers are registered with it. When I try to
'foce' MTS connection by setting SERVER=MTS, connection goes through,
but goes through as DEDICATED (checked through v$session). In fact, a
quick query of mts views shows that no mts connection has ever been
made (listener services confirms that as well).

I am lost.. what to do? I tried to do TNS tracing, but can't make much
of it.

Thanks for any help
........
We use Oracle 8.1.7.4 on Solaris 2.7 boxes
remove NSPAM to email

"NetComrade" <andreyNSPAM@bookexchange.net> wrote in message
news:40e1d8d2.696633415@localhost...
> I have 2 non-production databases than run on the same server. Both
> are 8.1.7.4. Both configured very similarly. All parameters are very
> similar, except for some memory variables and SID specific variables.
> Each has a corresponding listener listening on a different port
> (1521/1524); listener is always started first (although, this might be
> necessary anymore). The sid's are DEV1 and PROD.
>
> The machine connecting to them has entries that are identical in
> tnsnames.ora file, except for SID/port (tried service_name too).
> However, DEV1 accepts shared/MTS connections, but PROD doesn't. The
> listener shows that dispatchers are registered with it. When I try to
> 'foce' MTS connection by setting SERVER=MTS, connection goes through,
> but goes through as DEDICATED (checked through v$session). In fact, a
> quick query of mts views shows that no mts connection has ever been
> made (listener services confirms that as well).
>
> I am lost.. what to do? I tried to do TNS tracing, but can't make much
> of it.
>
> Thanks for any help

Why do you use two listeners? As soon as you have more than one listener
running on the same box, you're asking for trouble, because one of them
doesn't have the default characteristics. One listener is more than capable
of doing listening duty for both of your instances.

There is not really enough detail in your post to help. You mention two
listeners, but you don't show the lsnrctl services output for both of them.
You say dispatchers are registered with "it", but you don't show output
proving the point. For the 1524 listener, dispatchers can only possibly have
registered their existence if you have set local_listener for the relevant
instance, or have set LISTENER= parameters as part of the MTS_DISPATCHERS
parameter. But you don't mention doing any of that, so one presumes you
didn't.

Then there's the fact that you seem to be blindly flailing around using SID
and SERVICE_NAME, but not quite sure which. Inconsistency in the use of SID
or SERVICE can make all the difference between successful connections and
not, so it's important to get it right (hint: you should stick with
SERVICE_NAME).

To help, unless we just blather around in the dark and strike it lucky,
we'll need to see listener.ora, sqlnet.ora, tnsnames.ora, the actual output
of lsnrctl services for both listeners, and your MTS_ parameters, together
with LOCAL_LISTENER if it's set. Oh -and an explanation for requiring more
than one listener.

Regards
HJR

andreyNSPAM@bookexchange.net (NetComrade) wrote in message news:<40e1d8d2.696633415@localhost>...
> I have 2 non-production databases than run on the same server. Both
> are 8.1.7.4. Both configured very similarly. All parameters are very
> similar, except for some memory variables and SID specific variables.
> Each has a corresponding listener listening on a different port
> (1521/1524); listener is always started first (although, this might be
> necessary anymore). The sid's are DEV1 and PROD.
>
> The machine connecting to them has entries that are identical in
> tnsnames.ora file, except for SID/port (tried service_name too).
> However, DEV1 accepts shared/MTS connections, but PROD doesn't. The
> listener shows that dispatchers are registered with it. When I try to
> 'foce' MTS connection by setting SERVER=MTS, connection goes through,
> but goes through as DEDICATED (checked through v$session). In fact, a
> quick query of mts views shows that no mts connection has ever been
> made (listener services confirms that as well).
>
> I am lost.. what to do? I tried to do TNS tracing, but can't make much
> of it.
>
> Thanks for any help
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email

Andrey,

gotcha covered. (8.1.7.4 at the time)
I never really needed 2 listeners on a server except for one
particular time.
as a benchmark, a site was going to have 100 users login at the same
time, 2 sessions each via a fat client, 2 tiered app.
actually, I didn't know that they were going to perform this test, I
heard about it after they attempted it. (this was not my call)
It was analogous to benchmarking the onramp to a 4 lane freeway, not
the interstate itself.

we were using dedicated server connections on a platform that did not
support pre-spawning of sessions.
4 cpu server, 1 listener, unsuccessful test.
4 listeners, sucessful test.

the key thing here is to use dynamic registration, but have the
instance (pmon) register with all the listeners.
this is what the parameter local_listener is for.
prepare a tns alias for all of your listeners, fully qualified,
e.g. "all_listeners.mydomain.com" in your tnsnames.ora file for the
oracle home for both your instance and your listener home (in case
they are different).

in the init.ora set:

local_listener=all_listeners.mydomain.com

the instance will dynamically register with all the listeners.
the listeners will know about the dispatchers.

it worked for me - can provide more detail tomorrow (wed) if you like.

Pd

On Wed, 30 Jun 2004 08:04:23 +1000, "Howard J. Rogers"
<hjr@dizwell.com> wrote:

>
>"NetComrade" <andreyNSPAM@bookexchange.net> wrote in message
>news:40e1d8d2.696633415@localhost...

>
>Why do you use two listeners? As soon as you have more than one listener
>running on the same box, you're asking for trouble, because one of them
>doesn't have the default characteristics. One listener is more than capable
>of doing listening duty for both of your instances.

I always stuck with 1 listener per 1 database for stability issues.
(this is no 'myth', listener can get hammerred enough by a 'rogue'
app, I've seen it). I have a server with 12 databases and 12
listeners, never had any trouble.

>There is not really enough detail in your post to help. You mention two
>listeners, but you don't show the lsnrctl services output for both of them.
>You say dispatchers are registered with "it", but you don't show output
>proving the point. For the 1524 listener, dispatchers can only possibly have
>registered their existence if you have set local_listener for the relevant
>instance, or have set LISTENER= parameters as part of the MTS_DISPATCHERS
>parameter. But you don't mention doing any of that, so one presumes you
>didn't.

I 'mentioned' because i 'checked', I didn't pull it out of my ass. I
don't use the 'local_listener' parameter

Parameters:

DEV1:
mts_dispatchers="tcp,10"
mts_max_dispatchers=10
mts_servers=5
mts_max_servers=30
mts_service=DEV1
mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(host =melbourne)(port=1521))"


PROD:
mts_dispatchers="tcp,3"
mts_max_dispatchers=6
mts_servers=4
mts_max_servers=10
mts_service=PROD
mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(host =melbourne)(port=1524))"

Services output:

[DEV1@melbourne pfile]$lsnrctl services dev1_listener

LSNRCTL for Solaris: Version 8.1.7.4.0 - Production on 30-JUN-2004
10:37:52

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=DEV1)))
Services Summary...
DEV1 has 11 service handler(s)
DEDICATED SERVER established:0 refused:0
LOCAL SERVER
DISPATCHER established:12 refused:0 current:10 max:1022
state:ready
D000 <machine: melbourne, pid: 1142>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32794 ))
DISPATCHER established:12 refused:0 current:12 max:1022
state:ready
D001 <machine: melbourne, pid: 1144>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32795 ))
DISPATCHER established:12 refused:0 current:12 max:1022
state:ready
D002 <machine: melbourne, pid: 1146>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32796 ))
DISPATCHER established:12 refused:0 current:12 max:1022
state:ready
D003 <machine: melbourne, pid: 1148>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32797 ))
DISPATCHER established:12 refused:0 current:10 max:1022
state:ready
D004 <machine: melbourne, pid: 1150>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32798 ))
DISPATCHER established:12 refused:0 current:10 max:1022
state:ready
D005 <machine: melbourne, pid: 1152>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32799 ))
DISPATCHER established:11 refused:0 current:11 max:1022
state:ready
D006 <machine: melbourne, pid: 1154>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32800 ))
DISPATCHER established:11 refused:0 current:11 max:1022
state:ready
D007 <machine: melbourne, pid: 1156>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32801 ))
DISPATCHER established:11 refused:0 current:11 max:1022
state:ready
D008 <machine: melbourne, pid: 1158>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32802 ))
DISPATCHER established:11 refused:0 current:10 max:1022
state:ready
D009 <machine: melbourne, pid: 1160>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32803 ))
PROD has 1 service handler(s)
DEDICATED SERVER established:0 refused:0
LOCAL SERVER
DEV1 has 1 service handler(s)
DEDICATED SERVER established:129 refused:0
LOCAL SERVER
The command completed successfully


[DEV1@melbourne pfile]$lsnrctl services prod_listener

LSNRCTL for Solaris: Version 8.1.7.4.0 - Production on 30-JUN-2004
10:38:12

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=PROD)))
Services Summary...
tcp,3 has 3 service handler(s)
DISPATCHER established:0 refused:0 current:0 max:1022 state:ready
D000 <machine: melbourne, pid: 1475>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32824 ))
DISPATCHER established:0 refused:0 current:0 max:1022 state:ready
D001 <machine: melbourne, pid: 1477>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32825 ))
DISPATCHER established:0 refused:0 current:0 max:1022 state:ready
D002 <machine: melbourne, pid: 1479>
(ADDRESS=(PROTOCOL=tcp)(HOST=melbourne)(PORT=32826 ))
PROD has 1 service handler(s)
DEDICATED SERVER established:66 refused:0
LOCAL SERVER
The command completed successfully

>Then there's the fact that you seem to be blindly flailing around using SID
>and SERVICE_NAME, but not quite sure which. Inconsistency in the use of SID
>or SERVICE can make all the difference between successful connections and
>not, so it's important to get it right (hint: you should stick with
>SERVICE_NAME).

I know that SERVICE_NAME is newer. I recall having problems with it at
one point or another, SID works fine. If I upgrade and it doesn't
work, I'll fix it.

>To help, unless we just blather around in the dark and strike it lucky,
>we'll need to see listener.ora, sqlnet.ora, tnsnames.ora, the actual output
>of lsnrctl services for both listeners, and your MTS_ parameters, together
>with LOCAL_LISTENER if it's set. Oh -and an explanation for requiring more
>than one listener.

Um.. I don't work for Omlet or afficilated with Don , please be
nicer.

Please consider that these databases have been around for years, and
have been upgraded starting from 7.3.4 up to 8i. If you see old(er)
parameters, they are still there b/c they work and didn't require
changes.

listener.ora

DEV1_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = DEV1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = melbourne)(PORT = 1521))
)
)
)

SID_LIST_DEV1_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dev1.world)
(ORACLE_HOME = /opt/oracle/product/817)
(SID_NAME = DEV1)
)
)


################################################## ##############################
PROD_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = PROD))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = melbourne)(PORT = 1524))
)
)
)

SID_LIST_PROD_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD.world)
(ORACLE_HOME = /opt/oracle/product/817)
(SID_NAME = PROD)
)
)


There is no sqlnet.ora file

tnsnames.ora file: (notice, the oen commended out I tried as well)

#vcrs_dev =
# (DESCRIPTION =
# (ADDRESS = (PROTOCOL= TCP)(Host=melbourne(Port= 1524))
# (CONNECT_DATA = (SID = PROD))
# )

vcrs_dev =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host=melbourne)(Port= 1524))
(CONNECT_DATA = (SERVICE_NAME = prod.world)(SERVER=MTS))
)

DEV1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host=melbourne)(Port= 1521))
(CONNECT_DATA = (SID = DEV1))
)

........
We use Oracle 8.1.7.4 on Solaris 2.7 boxes
remove NSPAM to email

"NetComrade" <andreyNSPAM@bookexchange.net> wrote in message
news:40e2c7d5.757820207@localhost...


> Um.. I don't work for Omlet or afficilated with Don , please be
> nicer.
>
> Please consider that these databases have been around for years, and
> have been upgraded starting from 7.3.4 up to 8i. If you see old(er)
> parameters, they are still there b/c they work and didn't require
> changes.

Ah. I see. You weren't actually asking for help or advice, just trying to be
smart.

Fine.

HJR