Oracle Error indicates that the lock manager ran out of resources

[Mahmoud_1]

Dear Sir,
I have a problem with one of my customers, They have two AS 8200 clusterd running an old OpenVMS version 6.2-1h3 because the Application is Oracle forms 3 and Oracle database version 7.1.5.2.3.
Every day one of the Oracle instances crashed two to three times.
A case with oracle was created and they told us its OS problem.
If you please find attached file.
For All friends please its an URGENT case because its an URGENT site.
IT WILL BE APPRECIATED IN ADVANCED
Mahmoud AD-Dabbas

[Ian Miller.]

PQL_MENQLM is limited to 32767 but the RUN command allows a higher value. If you can find the RUN command that starts the processes and add /enqueue=65635

[Mahmoud_1]

Dear Miller,
Will you please tell me in more detales.
Do you mean that I have to look in the startup procedures for this process?
Regards

[Mahmoud_1]

Dear All,
I tried to find all RUN commands in the Oracle home but I didn't find any related to the lock_manager.
So are ther any SET command I can use to maximize this value on the running process.
Best Regards
Mahmoud

[Kris Clippeleyr]

Hi,
The lock manager is not a process. So you won't find a RUN command for the lock manager.
What Ian was trying to tell you, is that you have to find out how the crashing process is started (probably with a RUN command from inside a DCL command procedure).
With the RUN command you can specify how many ENQLM the process is granted (see HELP RUn Process /ENQUEUE_LIMIT ). If this quota is sufficiently high, the process will not crash.
Regards,
Kris (aka Qkcl)

[Marc Van den Broeck]

Hi,

we had similar lock manager problems with Oracle 7 long time ago. We had to increase the system parameter CLUSTER_CREDITS from 10 to 128.
You can try this if the Oracle instances on the cluster are running in parallel server mode.

Rgds
Marc

[Marc Van den Broeck]

Hi,

setting process quotas for Oracle background processes can be done by adding some defines in the Oracle startup procedure. Some examples:
$ ! set background process quotas
$DEFINE/SYSTEM ORA_xxx_PQL$_PGFLQUOTA 250000
$DEFINE/SYSTEM ORA_xxx_PQL$_WSQUOTA 5000
$DEFINE/SYSTEM ORA_xxx_PQL$_WSEXTENT 32000
$DEFINE/SYSTEM ORA_xxx_DBWR_PQL$_BYTLM 150000

where xxx is the name of your Oracle sid

Rgds
Marc

[Ian P]

Hi all,

I had this problem with Oracle8, the *only* way to fix it is to mod PQL_MENQLM, no other method created an ENQLM greater than 32768 - it seemed to be an Oracle imposed limit. The PQL parameter forced an override of that limit since it takes effect at process creation time.

Hope this helps...
Ian

[Hein van den Heuvel]

>> A case with oracle was created and they told us its OS problem.

It can perhaps be labelled as an OS problem, but is it 99.99% sure to be simple, well known, os restriction, not a bug

It is an Oracle problem, and it could be labelled a bug that they fail to react gracefully to the restriction.

It is an application /implementation bug that they pushed the restriction without seeing the restriction.

It is a management bug, or a calculated risk, to lock down a system on unsupported release if (apparently) the usage is not stable. They saved money over all these years, now they will probably have to pay dearly to get Oracle and OpenVMS consulting services to try to help them on time/material basis. If I want them , then I'd charge the absolute maximum rate for this case.

Compare with a city road. It was designed for 1000 cars / hour. For years it worked just fine. But new houses were built, traffic lights were added beyond the road. The city decided to keep the road because 'it was still working fine'. But now, daily the max load is exceeded and it completely lock up. The traffic control, instead of just refusing the last load on the first warning signs just keep waving to new cars: 'move it, move it'.

So who's to blame? The road (VMS)? The original design? The traffic control (they could just slow down entering traffic, or divert some)? The cars?
How come no one saw this coming? Who was monitoring the road? This probably did not happen overnight (though it could, as a new business opens). For a while the solution might be to allow some traffic on an emergency lane, but eventually that will fill as well (enqueue quotas). Of more structural solution might be to wonder why the cars are not getting through the road quickly enough. Maybe is just needs a little 'allow to pass' zone ( cluster credits). Maybe one-lane traffic light at its very end which can be opened to two lanes (more disks, extra IO capacity). Maybe a car pooling system can be set up (db block size increase, or other tuning). Maybe a store can be moved to the other end of the road (cache tuning).

Back to your case.
- What changed? It used to work right?
- Is that still changing or is a new stable level likely?
- Lock are held to protect data while changing.
- It there more data to protect?
- Are there more processes protecting?
- Are the locks required to be held longer now, causing more to be needed?

Directions for solutions
- Increase quota if not at absolute max. Let's hope the quotas were not maxed out (Marc, Ian) and that a little more will solve it for ever.

- reduce number of lock needed by one process (DB tuning)

- reduce number of processes active (traffic control)

- reduce duration of locks (IO tuning)

- get some consulting support (Oracle, OpenVMS, independent)

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Jon Pinkley · Accepted solution]

Ian Miller>>>>PQL_MENQLM is limited to 32767 but the RUN command allows a higher value. If you can find the RUN command that starts the processes and add /enqueue=65635<<<<

Ian Miller,

Are you sure you aren't thinking about the UAF ENQLM value instead of the PQL_MENQLM sysgen parameter? What version of VMS are you referring to? On 7.2-2 (oldest version I have available), the sysgen help has "-1" as the maximum, although testing shows that using that value acutally doesn't maximize the value of the enqlm used by a processes. However, it can be increased to 65535. It is a dynamic parameter, and can be changed "on the fly".

So it does appear that points should go to "Ian P".

Ian P Apr 23, 2007 13:22:51 GMT "I had this problem with Oracle8, the *only* way to fix it is to mod PQL_MENQLM, no other method created an ENQLM greater than 32768 - it seemed to be an Oracle imposed limit. The PQL parameter forced an override of that limit since it takes effect at process creation time."

--------------------------

Mahmoud,

It appears you currently have your PQL_MENQLM set to 32768, try changing to 65535 prior to starting your Oracle processes.

See attached for example of how to change, and of the testing I did on 7.2-2 to verify that it really had the effect expected.

And read Hein's response. You really need to address the problem.

Increasing the PQL_MENQLM parameters affects all processes created after the change, and is a poor work around. However, since it is a dynamic parameter, you could increase the value, start your processes, and then reset it to something more reasonable. Note: increasing the value doesn't in inself use more resources, it just raises the limits on the amount of resouces that a process can potentially use, similar to a credit limit on a credit card.

Another possibility would be to use DECAMDS to increase the ENQLM quota for the process, although I don't know if that would work with 6.2 (I think it does).

An "URGENT" site shouldn't be running software that has not been supported for many years.

Good luck,

Jon