Tuesday, January 19, 2010

ORA-01102: cannot mount database in EXCLUSIVE mode

Problem Description
While I start oracle database instance it fails with following error.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 877574740 bytes
Fixed Size 651436 bytes
Variable Size 502653184 bytes
Database Buffers 263840000 bytes
Redo Buffers 10629120 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

Problem Investigation
After you hit ORA-01102 error you should immediately check your database alert log for further analysis. Following is the sample example error messages generated in the alert log after you hit ORA-01102.

Alert log Error Message Version 01
ALTER DATABASE MOUNT
Wed Oct 22 03:40:21 2009
scumnt: failed to lock /dba/oracle/product/920/dbs/lkARJU exclusive
Wed Oct 22 03:40:21 2009
ORA-09968: scumnt: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 26165
Wed Oct 22 03:40:29 2009
ORA-1102 signalled during: ALTER DATABASE MOUNT

Alert log Error Message Version 02
ALTER DATABASE MOUNT
Mon Mar 6 15:31:21 2009
scumnt: failed to lock /apps/oracle/product/9.2/dbs/lkARJU exclusive
Mon Mar 6 15:31:21 2009
ORA-09968: scumnt: unable to lock file
Compaq Tru64 UNIX Error: 13: Permission denied
Additional information: 1246156
Mon Mar 6 15:31:21 2009
ORA-1102 signalled during: ALTER DATABASE MOUNT...

Cause of the Problem
This ORA-01102 error indicates an instance tried to mount the database in exclusive mode, but some other instance has already mounted the database in exclusive or parallel mode. By default a database is started in EXCLUSIVE mode. The real cause of ORA-01102 would be found in the alert log file where you will find additional information. The common reasons causing error ORA-01102 are as follows.

1) The processes for Oracle (pmon, smon, lgwr and dbwr) still exist. You can search them by ps -ef |grep YOUR_DB_NAME_HERE.

2) Shared memory segments and semaphores still exist even though the database has been shutdown.

3) There exists a file named "$ORACLE_HOME/dbs/lk{db_name}" where db_name is your actual database name.

4) A file named "$ORACLE_HOME/dbs/sgadef{sid}.dbf" exists where sid is your actual database SID.

5) You have two databases in your host. Now starting anyone of these causes error ORA-01102 if the other one is already started. If one is shutdown, the other database can be started successfully. This happened as while starting up, both the databases are trying to lock the same file. This is obvious if within the parameter files for these databases have the same entries for control_files and db_name. For example you have two databases named dba1 and dba2. Now inside the spfile/pfile of both databases that is inside initDBA1.ora and initDBA2.ora (in case of pfile) you have the similar entries like below.

...
*.control_files='xxx/control01.ctl','xxx/control02.ctl','xxx/control03.ctl'
*.db_name=DBA1
...


Solution of the Problem
1) Verify that there are no background processes owned by "oracle"
$ ps -ef | grep ora_ | grep $ORACLE_SID

If background processes exist, remove them by using the Unix "kill" command.
For example to kill a process ID number 7818 issue,
$ kill -9 7818

2) Verify that no shared memory segments and semaphores that are owned by "oracle" still exist.
Verify by command,
$ ipcs -b

If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments.
To remove shared memory segment issue,
$ ipcrm -m Shared_Memory_ID_Number
where Shared_Memory_ID_Number must be replace by shared memory id number.

To remove the semaphores issue,
$ ipcrm -s Semaphore_ID_Number
where Semaphore_ID_Number must be replaced by your Semaphore ID Number.

3) Verify that file $ORACLE_HOME/dbs/lk{db_name} does not exist where db_name is your actual database name.

4) Verify that file "$ORACLE_HOME/dbs/sgadef{sid}.dbf" does not exist where sid is your actual database SID.

5) If you see you have several databases in your machine and both of them uses have same entry in the parameter control_files and db_name then use correct values belonging to the individual databases.

In the sql*plus nomount stage you can issue,
show parameter db_name;
show parameter control_files;

in order to verify the entry.

6) From alert log if you see error like "Compaq Tru64 UNIX Error: 13: Permission denied" then ensure that in the file/directory oracle has permission and ensure that oracle is owner of the file. With chmod and chown you can change permission and ownership respectively.

Note that The "lk{db_name}" and "sgadef{sid}.dbf" files are used for locking shared memory. It may happen that even though no memory is allocated, Oracle thinks memory is still locked. By removing the "sgadef" and "lk" files you remove any knowledge oracle has of shared memory that is in use. So after removing those two file you can try to startup database.

Related Documents
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/11/ora-01033-oracle-initialization-or.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-00444.html
http://arjudba.blogspot.com/2008/05/database-startup-fails-with-errors-ora.html
http://arjudba.blogspot.com/2008/04/ora-01034-ora-27101-shared-memory-realm.html

1 comment:

Kamran Agayev A. said...

Nice article Arju. Thanks for sharing. Actually, when I get this error I delete lk* files from $ORACLE_HOME/dbs directory and kill all processes by running dbshut command (after editing /etc/oratab file and changing the last letter "N" to "Y")