Re: MTS problems with NT 8.1.7.4 Standard Edition

"Dom" <form999v_at_hotmail.com> wrote in message news:52400398.0305130302.31dda160_at_posting.google.com...
> After installing Oracle on a new machine I can't seem to get MTS
> working - I've added the relevant lines to init.ora and although
> querying several v$views would suggest it's working, I can't connect
> when server=shared is specified in tnsnames.ora (I get ORA-12510:
> TNS:listener could not find available handler for requested type of
> server), no problems if its commented out. Any suggestions would be
> appreciated (more details below, sorry if the output of the sql is
> hard to read) and I know its unusual to use the ip address but this is
> a cluster environment with fail over (a second machine picks up the
> same ip address in the event of failure).

Basically, it isn't working. If you specify server=shared, then you are *demanding* a shared server connection, and if you don't have any, then Oracle is forbidden to fall back on the good old technique of spawning you a dedicated server process (which is why, incidentally, that it's usually considered a little suspect to stick that line in, because if there *are* shared servers, without it Oracle will connect you to them by default. But with it, you can find users not being able to connect at all, as you've discovered).

> init.ora (partial)
> distributed_transactions = 250
>

mts_dispatchers="(address=(protocol=tcp)(host=172.19.9.63))(dispatchers=4)"

And the reason it isn't working is here: you have your dispatchers configured correctly for sure. But where's the MTS_SERVERS parameter? You don't appear to have asked the instance to pre-spawn any shared server processes at all! (I notice your output from v$parameter etc. shows one, but I'd prefer to see you configuring it yourself).

>
> compatible = 8.1.0
> sort_area_size = 655360
> sort_area_retained_size = 65536

I also don't notice a setting for large_pool_size. Which won't prevent MTS working, if it's not there. But it does mean all this sort_area_size will be being pinched from your shared pool, and that's not good for your hard parse rates, since it's the library cache that takes most of the hit from being deprived of memory.

The other thing you'll find helpful is to make sure that dynamic instance registration is working, with the database configured for dedicated server, before attempting to throw the switch and turn it into an MTS database. Your listener.ora looks rather odd, in that the 1521 TCP/IP reference is duplicated, which could be stuffing things up. Your listener.ora also has static declarations of SIDs, when what you really need are dynamic registration of services (though you can have static SID_LIST declarations, too, if you want to use OEM to manage the thing, for example).

If your Listener is called "listener", runs on 1521 using TCP/IP, then dynamic instance registration happens automatically. Otherwise, you need to set local_listener in the init.ora, setting it to the tnsnames.ora alias which contains the instructions for locating the listener. Your does appear to have all the default characteristics, however, so you shouldn't need to do that. So check it with lsnrctl services. You should see a line saying something like 'ora2 has 1 handler status UNKNOWN' (because of your static SID_LIST declarations), but also 'ora2 has 1 handler status READY', because of dynamic instance registration.

If you get dynamic instance registration happening, then it should just be a matter of converting it to MTS by throwing in the MTS_DISPATCHERS and MTS_SERVERS lines into the init.ora.

To check whether MTS is working properly, all you need to do is query v$circuit. If it returns even a single row, you have MTS configured correctly.

If none of the above helps, get back in touch, and we can go through it in detail.

Regards
HJR Received on Tue May 13 2003 - 14:16:50 CDT