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/126.96.36.199/grid/log/test003/agent/crsd/oraagent_oracle/oraagent_oracle.log".
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:188.8.131.52_test) /u01/app/oracle/admin/test/bin
IPC Resources for ORACLE_SID "test" :
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..!!