How to Use sqlplus-prelim in Oracle hang

How to Use sqlplus-prelim in Oracle hang

In many cases, Oracle hang causes sqlplus to be unable to connect, and thus the Oracle system and process status cannot be obtained, which leads to a lack of a strong foundation for locating the problem. Fortunately, Oracle 10 Gb has introduced the sqlplus-prelim option. When Oracle is suspended, sqlplus can still be used to obtain the database status.
The usage is as follows:

Reference
$ Sqlplus-prelim "/as sysdba"

SQL * Plus: Release 10.2.0.4.0-Production on Sun Mar 28 06:40:21 2010

Copyright (c) 1982,200 7, Oracle. All Rights Reserved.

In prelim mode, you cannot query data dictionaries, but you can disable the database.

Reference
SQL> select status from v $ instance;
Select status from v $ instance
*
ERROR at line 1:
ORA-01012: not logged on

Reference
SQL> shutdown abort
ORACLE instance shut down.

However, you can use oradebug, but it is sufficient for system diagnosis.
Dump system status

Reference
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.

Or dump hanganalyze

Reference
SQL> oradebug hanganalyze 3
Hang Analysis in/oracle/app/oracle/admin/ora10g/udump/ora10g_ora_54242.trc

For rac

Reference
SQLPLUS> oradebug setmypid

SQLPLUS> oradebug setinst all

SQLPLUS> oradebug-g def hanganalyze 3

Or dump Process status

Reference
SQL> oradebug dump processstate 10
Statement processed.

Furthermore, if there is a 10g client and the database is 9i, you can still use-prelim

Reference
$ Sqlplus-prelim/nolog

SQL * Plus: Release 10.2.0.4.0-Production on Sun Mar 28 06:50:19 2010

Copyright (c) 1982,200 7, Oracle. All Rights Reserved.

SQL> conn sys/oracle @ ora9i as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
0

Sqlplus-prelim/as sysdba usage 14:20:47
Category: Oracle

For the original article, see the eygle blog.
In some cases, the database loses response and sqlplus cannot be connected. In this case, you can only kill the process.

However, we still want to obtain the database status information for future diagnosis.
Starting from Oracle10g, sqlplus provides a parameter option-prelim, which can be connected when sqlplus cannot be connected normally.
To obtain system information, follow these steps:

Sqlplus-prelim/as sysdba
Oradebug setmypid
Oradebug unlimit;
Oradebug dump systemstate 10

This method is very useful:

$ Sqlplus-prelim/as sysdba

SQL * Plus: Release 10.2.0.1.0-Production on Thu Oct 25 09:42:20 2007

Copyright (c) 1982,200 5, Oracle. All rights reserved.

SQL>

However, in Oracle 10.2.0.1, an error occurs when the preceding method is used:

$ Sqlplus-prelim/as sysdba

SQL * Plus: Release 10.2.0.1.0-Production on Thu Oct 25 09:38:14 2007

Copyright (c) 1982,200 5, Oracle. All rights reserved.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 10
ORA-03113: end-of-file on communication channel
ORA-24323: value not allowed

The following error message is recorded in the alert file:

Thu Oct 25 09:38:32 2007
System State dumped to trace file
Thu Oct 25 09:38:32 2007
Errors in file/opt/oracle/admin/test201/udump/test201_ora_1402.trc:
ORA-07445: exception encountered: core dump [kgldmp () + 1360] [SIGSEGV] [Address not mapped to object] [0x000000030] [] []

This is caused by a Bug. The Bug number is 5730231, which is fixed in 10.2.0.3.

The 9i method is also available.

-Prelim is feature of SQL * Plus 10g and latter.

So as long you have any SQL * Plus 10g or latter version (ex. from client installation) and a valid net service name to connect to your 9i database you shoshould be able to establish a "backdoor" connection.

You can do the following:

$ Sqlplus-prelim/nolog

And once you are in, just connect to the database using SYSDBA account

SQL> connect sys/password @ net_service_name as sysdba
Prelim connection established