Re: Need help on MTS configuration

Syltrem wrote:

> Hi
>
> Oracle 8174 on Alpha OpenVMS 7.2-1
>
> I'm working on the configuration of MTS (multithreaded / shared server).
> I was able to get it to work rather quickly yesterday, but I had a problem
> with clients connecting through CMAN. The dispatcher would just froze
> (don`t ask me why but that`s the way it was) and the db had to be bounced.
> Any client already connmected to a shared server would freeze as soon as a
> client connecting through CMAN, tried to connect to the shared server.
>
> Anyway...
>
> I looked in the documentation some more and found that some of the
> parameters (namely mts_service and mts_listener_address) I was using were
> deprecated in 817, so I tried changing the config to use the new
> parameters

You only need two parameters to make MTS work: MTS_SERVERS and MTS_DISPATCHERS. Everything else should just work, provided the database was created properly in the first place.

>
> I ended up with this (in INIT.ORA):
> db_name = "ZZZRCS"
> instance_name = ZZZRCS

Don't need to set that. It should pick up ORACLE_SID, and that will determine the instance name under which it registers with the Listener.

> mts_dispatchers =
> "(PROTOCOL=TCP)(HOST=MYVMS)(PORT=1526)(SERVICE=MTS_ZZZRCS)"
Why is your listener running on a non-default port? I seem to recall 1526 was the old version 7 listener default address! Anyway, get rid of the port here. Simply set local_listener=XXXX, where XXXX is a tnsnames alias that resolves to port 1526, stored on the server's own O_H/network/admin directory.

> mts_max_dispatchers = 3
> mts_servers = 4
> db_domain = world

Oh dear. Right, the database *wasn't* created properly in the first place. A database should always be given a proper domain extension at creation time, because it makes these sorts of exercises so much easier. Live with it for now. But get into the habit of inventing domain names at database creation time in the future if no real domain name is to hand, (for example ".local").

> service_names = ZZZRCS, MTS_ZZZRCS

No. One service name. Otherwise this gets very messy. I know what you write below about desiring two service names, but don't go there via this route.

> local_listener = "(ADDRESS=(PROTOCOL=TCP)(HOST=MYVMS)(PORT=1526))"

Yuk. You'll find it easier to manage in the long run if you put a tnsnames alias in here that maps to this sort of information as contained in a server-side tnsnames.ora. But given this line, you definitely don't need the port number in your dispatchers setting.

> Upon starting the db, it registers with the listener (or so it appears,see
> below) but I can't connect to any of the services defined in parameters
> service_names or mts_dispatcher.

Not surprisingly. Read the lsnrctl output again:

Service "MTS_ZZZRCS.world"              has 1 instances.
     Instance "ZZZRCS"
       Status: READY  Total handlers: 2  Relevant handlers: 2

Note the name of that *service*. MTS_ZZZRCS.WORLD. Now look at your tnsnames:

MTS_ZZZRCS.WORLD =
[snip]

     (CONNECT_DATA =
       (SERVICE_NAME = MTS_ZZZRCS)
       (SERVER = SHARED)

You are attempting to connect to a world-less service name. Never mind that you've tacked .world onto the end of the *alias*. That is irrelevant. What does that alias get thunked down to? A connection to a service_name=MTS_ZZRCS. No .world. No connection, because a service of that name doesn't exist.  

> The listener sure sees something (see below) but it won`t let me connect.
>
> I tried defining the ZZZRCS service in LISTENER.ORA and reload the
> listener, and that allowed me to connect. So I don`t understand the part
> about "database will autoregister to the listener" if I need to manually
> enter everything in LISTENER.ORA.

You don't need to manually enter anything in the listener.ora. You just need to request a connection to something the listener is actually aware of. Reading the output from lsnrctl services carefully is important.

You *will* need to manually enter a SID_LIST in the listener.ora if you want to administer this thing via Enterprise Manager (and hence use an intelligent agent). But it is not required for simple client-server connections, provided you connect accurately.

> That`s for one.
>
> Now I want the MTS_ZZZRCS service to connect me through a SHARED server.
> It won`t let me do that because:
> 1) the registration with the listener does not really seem to work right
> (same problem as with service ZZZRCS)

It has nothing to do with the registration process. It has everything to do with your connection information...

> Message when attempting to connect to service ZZZRCS: "ORA-12514:
> TNS:listener could not resolve SERVICE_NAME given in connect descriptor"

.... as, in fact, the error message makes abundantly clear!

> 2) even If I add the info in the listener it will think it`s for dedicated
> server connections
> Message when attempting to connect to service MTS_ZZZRCS: "ORA-12520:
> TNS:listener could not find available handler for requested type of
> server"
>
> So this new config with the "new" parameters is even worse as it does not
> work at all.

Stop blaming the registration process, the parameters, the listener or whatever else. It's you. It's your tnsnames.ora. It isn't correct, and it needs fixing.

> I'd like to have 2 services for this db, one that connects to a dedicated
> server, one that connects to a shared server.

No, you don't. You have one service (ie, one instance). What you want is to be able to connect to it in two different ways. Fine then: create a tnsnames.ora that has within it two aliases, both requesting connection to the same ZZZRCS.WORLD service (and that, therefore, should be the sole value for the service_names parameter in your init.ora).

For one of those aliases, you add one line: SERVER=DEDICATED. For the other, you can leave that line out: if you are silent on the nature of the connection you want, and there are dispatchers available, you will get a shared connection. It is usually considered a bit restrictive to explicitly state SERVER=SHARED, because in that case, if no dispatchers are available, you simply won't be able to connect at all -which might be want you want, but quite often people would like their clients at least to be able to connect, even if it is with the "wrong" server type.

In other words, don't confuse what it is that you are connecting to with the way you want to connect to it.

For the rest, tidy it up. Simplify it. And read output from commands carefully.

Regards
HJR
> And be able to connect
> clients to the shared server (service MTS_ZZZRCS) even if they connect
> through CMAN (because those are the ones I mainly want to use the shared
> servers == web site users).
>
> I hope someone can help me sort this all out !
>
> Thanks
> Syltrem
> -------------
>
> LSNRCTL command output:
> -------------------------------
> Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=MYVMS)(PORT=1526))
> Services Summary...
> Service "MTS_ZZZRCS.world" has 1 instances.
> Instance "ZZZRCS"
> Status: READY Total handlers: 2 Relevant handlers: 2
> Class: ORACLE
> DEDICATED established:0 refused:0 current:0 max:0 state:ready
>
> (ADDRESS=(PROTOCOL=BEQ)(PROGRAM=IVA$EXE_ALPHA:[ORACLE8174.NETWORK.ADMIN]
> ORASRV_NETV2_ZZZRCS.COM)(ARGV0=oracleZZZRCS)(ARGS='(DESCRIPTION=(LOCAL=no
(A
> DDRE
> SS=(PROTOCOL=BEQ)))'))
> Session: NS
> D000 established:0 refused:0 current:0 max:1022 state:ready
> (ADDRESS=(PROTOCOL=tcp)(HOST=MYVMS)(PORT=4060))
> Session: NS
> Service "ZZZRCS.world" has 1 instances.
> Instance "ZZZRCS"
> Status: READY Total handlers: 2 Relevant handlers: 1
> Class: ORACLE
> DEDICATED established:0 refused:0 current:0 max:0 state:ready
>
> (ADDRESS=(PROTOCOL=BEQ)(PROGRAM=IVA$EXE_ALPHA:[ORACLE8174.NETWORK.ADMIN]
> ORASRV_NETV2_ZZZRCS.COM)(ARGV0=oracleZZZRCS)(ARGS='(DESCRIPTION=(LOCAL=no
(A
> DDRE
> SS=(PROTOCOL=BEQ)))'))
> Session: NS
>
> TNSNAMES.ORA
> --------------------
> ZZZRCS.WORLD =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = MYVMS)(PORT = 1526))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = ZZZRCS)
> (SERVER = DEDICATED)
> )
> )
>
> MTS_ZZZRCS.WORLD =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = MYVMS)(PORT = 1526))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = MTS_ZZZRCS)
> (SERVER = SHARED)
> )
> )
>
Received on Fri Oct 22 2004 - 15:17:13 CDT