TIP 92#: Resolve ORA-01075

SVC disk on a client crashed and as the result Oracle database crashed. After resolving disk isue, I was getting ORA-01075 error on sqlplus to startup DB.


> sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01075: you are currently logged on



Checking the process, nothing from Oracle user was up and running.
Eventually, It turned out that the issue is related to stuck shared memory and semaphore.
To check out if this is the case for you, follow below steps :


1. Login as Oracle
(or owner of software)

2. Make sure there is no process up as oracle
(Assume Oralce is the owner of oracle binaries)

3.run ipcs
* Check for any shared memory segment for Oracle
* Check for any semaphore for Oracle

4. Kill shared memory segment or semaphore
which is owned by oracle
* ipcrm -m shmid_from_step_3
(to cleanup shared memory segment)
* ipcrm -s semid_from_step_3
(to cleanup semaphore from step 3)

5. Retry sqlplus


5 comments:

Anonymous said...

How can you tell the shared memory segments and semaphores apart? I've got 4 databases running, but 5 Oracle segments and semaphores, and I'm getting this error on a database I'm trying to recreate. How do I know which ones to kill?

Anonymous said...

Same Anon guyu here. I answer my own question. Use "ipcs -a", pick the shared memory segment with an NATTCH of zero, and the semaphore with the same CTIME:

ipcs -a
IPC status from as of Thu Aug 30 15:59:00 GMT 2012
T ID KEY MODE OWNER GROUP CREATOR CGROUP CBYTES QNUM QBYTES LSPID LRPID STIME RTIME CTIME
Message Queues:
T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME
Shared Memory:
m 1879048297 0x8ec662dc --rw-r----- oracle dba oracle dba 23 805314560 28974 16519 15:38:15 15:38:15 15:33:44
m 1275068464 0xea207a34 --rw-r----- oracle dba oracle dba 0 805314560 21404 16973 15:46:27 15:46:27 13:48:04
m 1258291284 0x426442a4 --rw-r----- oracle dba oracle dba 17 536879104 11957 15197 15:00:26 15:00:27 10:03:22
m 1426063485 0x8a97dce4 --rw-r----- oracle dba oracle dba 61 2147491840 24484 15225 15:56:44 15:58:13 11:05:02
m 1426063482 0x4c3b6690 --rw-r----- oracle dba oracle dba 18 536879104 24366 15191 15:00:11 15:00:12 11:04:36
T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME
Semaphores:
s 50331760 0x6917bf68 --ra-r----- oracle dba oracle dba 154 15:58:42 15:33:45
s 50331736 0x4bcfb58c --ra-r----- oracle dba oracle dba 154 15:41:46 13:48:05
s 50331716 0x3e589658 --ra-r----- oracle dba oracle dba 154 15:58:54 10:03:23
s 33554493 0x2c6e550c --ra-r----- oracle dba oracle dba 154 15:58:38 11:05:04
s 33554489 0x10890c74 --ra-r----- oracle dba oracle dba 154 15:58:53 11:04:37

> ipcrm -m 1275068464
> ipcrm -s 50331736
> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 30 16:00:15 2012

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to an idle instance.

PS. these captchas a tough!

Anonymous said...

Hey, thanks a lot. It worked perfectly. It took sometime for NATTCH to turn to 0 initially.

Leszek Liberda said...

There is tool that seems to show reserved semaphores and memory for particular database on multidatabase server:

oracle@db# export ORACLE_SID=oradb
oracle@db# $ORACLE_HOME\bin\sysresv

Isuru said...

This worked like a charm! Thanks