For certain incident when even DBA / SYSDBA user can’t log-on, note that in 10g and above, there’s a lightweight sqlplus connection introduced. It’s called “preliminary connection”.
Two method to establish the preliminary connection in sqlplus :-
i)
/usr/oracle $ sqlplus -prelim “/ as sysdba”
SQL*Plus: Release 10.2.0.3.0 – Production on Wed Jan 26 01:56:48 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL>
ii)
/usr/oracle $ sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 – Production on Wed Jan 26 01:56:54 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> set _prelim on
SQL> conn / as sysdba
Prelim connection established
SQL>
Take note that in preliminary connection, we could not perform any query on regular tables or even v$ views as this lightweight connection has not have all the proper structures setup to run query execution. However, the good thing about this is that we could use this lightweight connection to run oradebug which comes handy when performing system statedumps and hanganalyze.