Tuesday, April 8, 2014

sqlplus hangs indefinetly --- ORA-01075



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


source: http://oradbatips.blogspot.com/2011/06/tip-92-resolve-ora-01075.html

https://community.oracle.com/thread/3544149