Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

ORA-01102: cannot mount database in EXCLUSIVE

ORA-01102: cannot mount database in EXCLUSIVE mode


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


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…


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.

March 19, 2009 Posted by | oracle | Leave a comment

CleanUp Temporary Segments Occupying Permanent Tablespace

CleanUp Temporary Segments Occupying Permanent Tablespace

There are situations when we see “temporary segments” in permanent tablespaces hanging around and not getting cleaned up.
These temporary segments in permanent tablespace can be created by DDL operations like CTAS and “alter index..rebuild” because the new object is created as a temporary segment in the target tablespace.
These temporary segments take actual disk space when SMON fails to perform its assigned job to cleanup stray temporary segments.
Following query finds out these segments:

SQL > select tablespace_name, owner, sum(bytes/1024/1024) from dba_segments
where segment_type = ‘TEMPORARY’ group by tablespace_name, owner;

TABLESPACE_NAME          OWNER              SUM(BYTES/1024/1024)
——————-                    ————        ——————–
xxxx_DATA                             BLD                     137706
KPMGRT_INDEX                    BLD                       8910
KPMGRT_DATA                      BLD                  25284.875
BILL_INDEX                             BLD                        .25
CELL_DATA                              BLD                        798
CELL_INDEX                            BLD                        208
CSC_DATA                                BLD                   69642.25
CSC_INDEX                              BLD                   956.4375

Here we can see that tablespace KPMGRT_DATA, CSC_INDEX and CSC_DATA have large temporary segments.
To find out if any DDL is active which can create temporary segments we can use the following:

SQL> conn / as sysdba
SQL> select owner FROM dba_segments WHERE segment_name='<name>’;
SQL> select pid from v$process where username=’owner from above query’;
SQL> alter session set tracefile_identifier=’TEMPORARY_SEGMENTS’;
SQL> oradebug setorapid
SQL> oradebug dump errorstack 3
SQL > oradebug tracefile_name

Above  will produce the tracefile name, open that file and check for the “current sql”
If it is a DDL like CTAS or index rebuild, then wait for the operation to complete. If there is no pid
returned then these segments are “stray segements” and needs to cleaned up manually.
There are two ways to force the drop of temporary segments:

1. Using event DROP_SEGMENTS

2. Corrupting the segments and dropping these corrupted segments.

A. Using DROP_segments:

Find out the tablespace number (ts#) which contains temporary segments:

SQL> select ts# from sys.ts$ where name = ‘tablespace name’;

Suppose it comes out to be 10, use the following command to cleanup temporary segments:

SQL> alter session set events ‘immediate trace name DROP_SEGMENTS level 11’;

level is ts#+1 i.e 10+1=11 in this case.

B. Corrupting temporary segments for drop:

For this following procedures are used:

– DBMS_SPACE_ADMIN.TABLESPACE_VERIFY
– DBMS_SPACE_ADMIN.SEGMENT_CORRUPT
– DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT
– Verify the tablespace that contains temporary segments (In this case it is KPMGRT_DATA)

SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY(‘KPMGRT_DATA’);– Corrupt the temporary segments in tablespace KPMGRT_DATA
SQL> select ‘exec DBMS_SPACE_ADMIN.SEGMENT_CORRUPT(‘ || chr(39) || tablespace_name || chr(39) || ‘,’ || HEADER_FILE || ‘,’ || HEADER_BLOCK || ‘);’  from dba_segments where SEGMENT_TYPE like ‘TEMP%’ and tablespace_name = ‘KPMGRT_DATA’;– Drop the corrupted temporary segments
SQL> select ‘exec DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT (‘ || chr(39) || tablespace_name || chr(39) || ‘,’ || HEADER_FILE || ‘,’ || HEADER_BLOCK || ‘);’ from dba_segments where SEGMENT_TYPE like ‘TEMP%’ and tablespace_name = ‘KPMGRT_DATA’;– Verify the tablespace again to update the new dictionary information:
SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_VERIFY(‘KPMGRT_DATA’);This will remove temporary segments from permanent tablespace.

March 5, 2009 Posted by | oracle | 1 Comment

Resolving Shutdown Immediate Hang Situations

Resolving Shutdown Immediate Hang Situations

Many times Oracle DBA’s are faced with a situation where shutting down the instance with shutdown immediate seems to take infinite time and gives a impression that the session is hung. In most of the cases this is not a hang. Hang can be defined as a scenario when few sessions/processes are waiting on some action and in turn blocking other session/processes to complete their tasks. Now the original sessions could be waiting because of resource issues or Oracle Bugs.
Shutdown immediate can take long time to complete (appear to be hung) because of three reasons:

1. Uncommitted transactions are being rolled back.
2. SMON is cleaning temp segments or performing delayed block cleanouts.
3. Processes still continue to be connected to the database and do not terminate.

1. Uncommitted transactions are being rolled back:

This is the case when the message ‘Waiting for smon to disable tx recovery’ is posted in the alert log after we issue shutdown immediate.
There are two reasons for this:
– A large query was running at the time of shutdown immediate.
-A large transaction was running at the time of shutdown immediate.
For large queries:

SQL > select count(*) from v$session_longops where time_remaining>0;

If it returns a value > 0 then we can do a shutdown abort and then startup restrict and then again shutdown immediate.
For large transactions:

SQL > select sum(used_ublk) from v$transaction;

If it returns a large value then we have to wait for a long time for shutdowm to get completed.
If the large transaction is aborted and then shutdown is issued then we have to query v$fast_start_transactions and v$fast_start_server, we will not see anything in v$transaction at this time.
At this particular moment transaction recovery is going on and the count(*) will keep on decreasing:

SQL > select count(*) from v$fast_start_transaction;

Decreasing count will show that recovery is going on and when the recovery is completed the database will be shutdown.
But it is not desirable under some circumstances such as, when we have very short maintance window and we need to perform a shutdown immediate to do some work, in those cases we can use the following event and set in the init.ora file TEMPERORARLY To disable transaction recovery:
event=”10513 trace name context forever, level 2′
and bounce the instance and issue shutdown immediate to get complete without transaction recovery.SMON will not do a transaction recovery untill this event is set in the init.ora file so it is necessary to remove this event whenever you get a chance to shutdown the database again, this time shutdown immediate can even take 3-5 hours(Just remove this event from pfile).

2. SMON is cleaning temp segments or performing delayed block cleanouts:
During a SHUTDOWN IMMEDIATE and SHUTDOWN NORMAL, SMON cleans up extents which are no longer needed and marking them as freed. It means that count from uet$ will decrease and count in fet$ will increase.
To verify that the temporary segments are decreasing have an active session available in SQL during the SHUTDOWN IMMEDIATE. Run query to ensure the database is not hanging, but is actually perform extent cleanup:

SQL> select count(block#) from fet$;
COUNT(BLOCK)
———-
115

SQL> select count(block#) from uet$;
COUNT(BLOCK)
———-
713

After some time, issue the query again and check the results:
SQL> select count(block#) from fet$;
COUNT(BLOCK)
———-
210

SQL > select count(block#) from uet$;
COUNT(BLOCK)
———-
512

If you do not have sufficient time to wait for this cleanup then you can set the following event and bounce the database and reissue shutdown immediate to skip this cleanup:
event=”10061 trace name context forever, level 10′
It allows you to prevent SMON from cleaning up temporary segments. Again it is not recommended to set this event event forever. Whenever you have large downtime remove this event and allow SMON to do its work.

3. Processes still continue to be connected to the database and do not terminate:
After issuing shutdown immediate, If we see entries in alert log file as:

Tue Feb  8 12:00:27 2009
Active call for process 10071 user ‘oracle’ program ‘oracle@server.domain.abc (J001)’
SHUTDOWN: waiting for active calls to complete.
Tue Feb  8 12:00:57 2009

SHUTDOWN: Active sessions prevent database close operationIt shows that there are some active calls at program ‘oracle@server.domain.abc (J001)’ which pmon is not able to clear up.This message is due to the fact that database is waiting for pmon to clean up processes, but pmon is unable to clean them. The client connections to the server are causing the shutdown immediate or normal to hang. Do the following in this case:

1. Before shutdown immediate, shutdown the listener:
$ lsnrctl stop
2. Now check if there are any connection present at the database as:
$ ps -eaf | grep LOCAL
It will give you the OSPIDs of the client connected to database.
3 Manually kill them as:
# Kill -9 <OSPID>
4. Issue shutdown immediate now.
Do not forget to bring up the listener after startup
In addition to this you can set 10046 event in the session used to shutdown the instance. This will help to tell the event on which session is waiting
SQL>alter session set events ‘10046 trace name context forever, level 12’

SQL>Shutdown immediate;
Look for the trace file in user_dump_dest location. Also look at the alert.log for any other messages. They might be helpful in case the shutdown is experiencing hang situation.

March 5, 2009 Posted by | oracle | Leave a comment