SQL*Plus -prelim option
Jonathan Lewis pointed to an old blog from Arup Nanda, where he describes how he used sqlplus undocumented option (-prelim) to connect to “hanged” Oracle instance. Since I was not aware of this command I decided to try this out with 10g on Windows.
I tried this on Windows 2003:
C:\ORACLE>sqlplus -prelim /nolog SQL*Plus: Release 10.2.0.2.0 - Production on Tor Avg 11 13:48:35 2009 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. SQL> connect / as sysdba Prelim connection established SQL> oradebug setmypid Statement processed. SQL> oradebug hanganalyze 12 Hang Analysis in C:\oracle\admin\oraT\udump\oraT_ora_3336.trc SQL>
and sure enough it can also be done in another way, described by Virag Sharma:
C:\ORACLE>sqlplus /nolog SQL*Plus: Release 10.2.0.2.0 - Production on Tor Avg 11 13:40:35 2009 Copyright (c) 1982, 2005, Oracle. All Rights Reserved. SQL> set _prelim on SQL> connect / as sysdba Prelim connection established SQL> oradebug setmypid Statement processed. SQL> oradebug hanganalyze 12 Hang Analysis in c:\oracle\admin\oraT\udump\oraT_ora_3812.trc SQL>
Miladin Modrakovic also blogged about sqlplus -prelim option.
I can’t even remember when was the last time I have to troubleshoot (seemingly) hanged Oracle instance, but it’s good to know that Oracle gave us an option in 10g to knock on the back door and ask for a dump of SGA structures.
Posted on 11.08.2009, in Oracle and tagged oracle. Bookmark the permalink. Comments Off on SQL*Plus -prelim option.