Thursday, April 18, 2013

ORA-01012: not logged on ……!!

Technorati Tags: ,

Today, while I was shutting down one of Oracle RAC Databases instances, I came across this issue ORA-01012: not logged on. Let me explain the tiny background of this issue, how does it arouse, during the work. As I was busy with work and in parallel while discussing with my colleague, I pressed CTRL+C  after issuing “shutdown database” through srvctl, which lead this. Then I tried to start the database via SRVCTL, I ended with below alert logs messages.

Instance shutdown cancelled
Thu Apr 18 05:35:25 2013
License high water mark = 15
USER (ospid: 26519): terminating the instance
Instance terminated by USER, pid = 26519.

From console, I see below informative message,

starting up instance, please be patient, this takes some time......
PRCR-1013 : Failed to start resource ora.test.db
PRCR-1064 : Failed to start resource ora.test.db on node test003
CRS-5017: The resource action "ora.test.db start" encountered the following error:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/u01/app/".

Now, I thought to verify the semaphores and shared segments where they still persist or not and further I saw oracle process which are alive. Here I go

test0003:oracle> (test: /u01/app/oracle/admin/test/bin
$ sysresv

IPC Resources for ORACLE_SID "test" :
Shared Memory:
ID              KEY
20217874        0x00000000
20250643        0x00000000
20283412        0x00000000
20316181        0x00000000
20348950        0x00000000
20381719        0x00000000
20414488        0x00000000
20447257        0x00000000
20480026        0x00000000
20512795        0x00000000
20545564        0x00000000
20578333        0x713b9410
ID              KEY
5668870         0x926d2ee8
Oracle Instance alive for sid "test"

I have cleaned up the existing process manually and further the instance came with out any issues. It’s looks weird when error message states that “Not logged on” …. logically at first instance we might think the existing sqlplus session is aborted (or) didn’t getting interact IPC call to server process.  Some times, we can try in such situations to retry with “shutdown abort” so that existing process would get cleaned that helps to resolve the issue.

I hope it would helpful to some one who would experiencing similar kind of issue..!!