Re: Instance crash periodically, file locked by another peocess?

oldcar_at_hotmail.com (bcjm) wrote in message news:<6e9fcca4.0308061831.16bcf657_at_posting.google.com>...
> Win NT 4.0, 8.1.6.0.0 single instance.
>
> My instance has been crashing about once a day for the past couple of
> days. Every time it happens, it says the control file is locked by
> another process. Oracle support told me it might be because the
> AntiVirus or 3rd party backup utility lock the file. I am pretty sure
> there was nothing like that running at the time of crashing. It is
> indicated in the alert file that the CKPT process failed to lock the
> control file. Once I restart the instance I have to do a recover
> database. It will run for another day or two. Has anyone seen this
> before? Is there any way to tell which process is locking the file?
>
> Thanks
> Bob

Bob,

The most simple way to prevent this is to disable access to all folders containing oracle datafiles, online redo logs and control files.

If no other process (especially those running as localsystem) has the ability to access the files, it cannot possibly lock them.

This is so simple of an idea, it might seem that it could not possibly work.

It can work, it does work.

While the instances are shutdown, services stopped:

You will have to run the oracle service and oracle listener service(s) under a local account that is both local admin and local ORA_DBA. remove access to all the other groups/accounts and take ownership of the files.

restart the oracle services (now running under a local account) and attempt to access the files by a non ORA_DBA account. you won't be able to.

problem solved.

IF - you still want your backup software to be able to access the *live* oracle files (to perform a cold backup, still a bad idea) - create the local group ORA_OPER and grant it read/list/execute on the files owned exclusively by the ORA_DBA group. But you are far better off to use hot/exp/rman backup scripts to create backup sets in a staging area - and allow the backup software access to those directories - and prevent all other access to the live oracle files.

The other method is to enable filesystem auditing of all access to the affected files. that should provide some info as to who is the culprit (post mortem).

Paul Received on Thu Aug 07 2003 - 09:49:44 CDT