Monday, July 7, 2008

Resolving Ora-01102 Error

I was working on some of locking issues with one of my production databases and found out that most of the sessions are being blocked with Sessions from DBSNMP i.e Agent so I tried to kill them but this could not do much to me.So after agreement with Application owner I brought down the Instance down and found the following error in the alert log

Mon Jul 7 21:40:38 2008
ALTER DATABASE CLOSE NORMAL
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Mon Jul 7 21:42:18 2008

Later when I tried to brought up the instance I was not able to bring up the instance and found the following error in alert.log

Mon Jul 7 21:40:16 2008
starting up 1 shared server(s) ...
Mon Jul 7 21:40:17 2008
ALTER DATABASE MOUNT
Mon Jul 7 21:40:17 2008
sculkget: failed to lock /sfprd1/oracle/products/10.2/dbs/lkSFPRD11 exclusive
sculkget: lock held by PID: 13537
Mon Jul 7 21:40:17 2008
ORA-09968: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 13537
Mon Jul 7 21:40:17 2008
ORA-1102 signalled during: ALTER DATABASE MOUNT...


Solution
---------

This issue occurs due to the following reasons:-
- there is still an "sgadef.dbf" file in the "ORACLE_HOME/dbs" directory
- the processes for Oracle (pmon, smon, lgwr and dbwr) still exist
- shared memory segments and semaphores still exist even though the database has been shutdown
- there is a "ORACLE_HOME/dbs/lk" file
So this time I removed the file "/sfprd1/oracle/products/10.2/dbs/lkSFPRD11" and did
--shutdown immediate
again
--startup open;

And this worked for me.

No comments: