If Shutdown normal or shutdown immediate hangs and you try to connect as sys, it would get you connected either as idle instance or simply connected which is in reality a fake connection. In a particular scenario, I changed ORACLE_HOME from EM and then issued simple “SHUTDOWN” command which took a long time to complete. I tried to issue “shutdown immediate” and “shutdown abort” commands and found following errors.
C:\Users\ACE>sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 5 19:52:01 2011 Enter user-name: sys as sysdba Enter password: Connected. SQL> shutdown immediate ORA-24324: service handle not initialized ORA-24323: value not allowed ORA-01090: shutdown in progress - connection is not permitted
SQL> shutdown abort ORA-01031: insufficient privileges
SQL> select instance from v$instance; select instance from v$instance * ERROR at line 1: ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0
In this scenario it did “connect / as sysdba”, but in actually failed to connect. Then, when I
tried to shutdown abort, you got ORA-1031 cause you weren’t actually connected.
It’s interesting to note that if you connect as sysdba, and the connection is successful, you see something like:”Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit
Production With the Partitioning, OLAP and Data Mining options”If you fail to connect, cause process table is full, you’ll get: “Connected.”
So, “Connected.” means you *failed* to connect!
ORA-01012: not logged on (check this link for another reason why Oracle shows “not logged on” error)
Although shutdow abort causes instance recovery but it becomes necessary if you want to accomplish a repetitive task at a given time in a specific time. You can combine it with restricted startup and then again shutdown with immediate option and better run it through a script.
#!/bin/ksh # Source the db.env to set the Oracle environment export ORACLE_SID ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'` export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH shutdown abort; startup restrict; shutdown immediate;
Debugging a hung database
Starting with Oracle 11g, SQL*Plus has a new option called “prelim” . This option is very useful for running oradebug and other utilities that do not require a real connection to the database.
C:\ACE> sqlplus –prelim SQL> or SQL> set _prelim on SQL> connect / as sysdba
Run oradebug commands to diagnose a hung database issue using the
new hanganalyze utility:
SQL> oradebug hanganalyze Wait for some time to identify process state changes. SQL> oradebug hanganalyze Open a separate SQL session and immediately generate a system state dump. SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';
Mohib Alvi 