SQL*Plus 'prelim' connection

A little known feature of SQL*Plus is the 'prelim' connection, which allows you to connect to an Oracle instance's SGA without alloctaion a session. This is very handy when you are dealing with a completely hung instance that is no longer accepting logins, even as SYSDBA.

You can use the prelim connection to obtain diagnostics from within your own session or in an already-running session in the database. You can take diagnostic traces like hanganalyze and systemstate, or query x$ structs.

Here's an example of dumping the contents v$wait_chains (a new view in 11g) using the prelim connection:

$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production
SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug direct_access enable trace
Statement processed.
SQL> oradebug direct_access disable reply
Statement processed.
SQL> oradebug direct_access set content_type = 'text/plain'
Statement processed.
SQL> oradebug direct_access select * from x$ksdhng_chains
Statement processed.
SQL> oradebug tracefile_name
/opt/oracle/diag/rdbms/test01/test01/trace/test01_ora_3025.trc

Here's an example of dumping hanganalyze and systemstate from within he already-running DIAG process:

$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production
SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setorapname diag
Oracle pid: 4, Unix process pid: 9837, image: oracle@host (DIAG)
SQL> oradebug dump hanganalyze 1
Statement processed.
SQL> oradebug dump systemstate 267
Statement processed.
SQL> oradebug tracefile_name
/opt/oracle/diag/rdbms/test01/test01/trace/test01_diag_9837.trc

I believe prelim was quietly introduced in 10g. It does not appear to be available in 9i. This feature is extremely important for obtaining diagnostics upon the first occurrence of a hang. It prevent encountering that hang over and over, never finding the cause.

Thanks, and be sure to check out my whitepapers and articles at http://www.ora-600.net.

Jeremiah