"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