Oracle's Sqlplus prelim parameter description (handles hang)

Starting with oracle10g, Sqlplus provides a parameter option-prelim, with this parameter, when the system is already hang. We can connect to the SGA instead of the database, i.e. no session is created.

A. The following steps allow you to obtain system information:
Sqlplus-prelim/as SYSDBA
Oradebug Setmypid
Oradebug Unlimit;
Oradebug Dump SystemState 10

For a 9i database, 9IR2 works correctly by installing a 10g client connection.

Two Prelim parameter Features
1. First enable the 10046 event on the database:
Sql> alter system set events ' 10046 Trace name context forever ';
System altered.

2. Connect in the normal way first:
D:/>sqlplus/nolog
Sql*plus:release 10.2.0.1.0-production on Saturday November 15 15:36:02 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Sql> Conn Sys/manage as Sysdba
is connected.
Sql> exit
From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production
With the partitioning, OLAP and Data Mining options Disconnect

3. Look at the conditions when adding prelim parameters:
D:/>sqlplus-prelim/nolog
Sql*plus:release 10.2.0.1.0-production on Saturday November 15 15:36:34 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Sql> Conn Sys/manage as Sysdba
The primary connection has been established
Sql> exit
Disconnecting from ORACLE
As you can see from the above information, when using the prelim connection, the prompt is "primary connection established" and exiting Sqlplus does not show banner.

4. You can also connect to the database in Prelim mode by using the following method:
[Email protected] ~]$ Sqlplus/nolog
Sql*plus:release 10.2.0.3.0-production on Tue Dec 2 07:04:28 2008
Copyright (c) 1982, 2006, Oracle. All rights Reserved.
Sql> set _prelim on
Sql> Connect/as SYSDBA
Prelim Connection established

5. Look at the 10046 trace of the database:
From the generated trace file, you can find that when you connect to the database on a normal connection, Sqlplus automatically executes the following SQL:
ALTER SESSION SET nls_language= ' Simplified Chinese ' nls_territory= ' China ' nls_currency= ' ￥ ' nls_iso_currency= ' China ' NL S_numeric_characters= '., ' nls_calendar= ' Gregorian ' nls_date_format= ' dd-mon-rr ' nls_date_language= ' SIMPLIFIED Chinese ' nls_sort= ' binary ' time_zone= ' +08:00′nls_comp= ' binary ' nls_dual_currency= ' ￥ ' nls_time_format= ' HH.MI. Ssxff AM ' nls_timestamp_format= ' dd-mon-rr hh.mi. Ssxff AM ' nls_time_tz_format= ' HH.MI. Ssxff AM TZR ' nls_timestamp_tz_format= ' dd-mon-rr hh.mi. Ssxff AM TZR '
Select value$ from props$ where name = ' Global_db_name '
Select Sys_context (' USERENV ', ' server_host '), Sys_context (' USERENV ', ' db_unique_name '), Sys_context (' USERENV ', ' Instance_name '), Sys_context (' USERENV ', ' service_name '), Instance_number, Startup_time, Sys_context (' USERENV ', ' db_ DOMAIN ') from V$instance where Instance_name=sys_context (' USERENV ', ' instance_name ')
Select decode (Failover_method, NULL, 0, ' BASIC ', 1, ' Preconnect ', 2, ' Preparse ', 4, 0), decode (Failover_type, NULL, 1, ' NONE ', 1, ' SESSION ', 2, ' SELECT ', 4, 1), failover_retries, Failover_delay, flags from service$ WHERE name =: 1

When Sqlplus uses prelim to connect to the database, it does not generate a 10046 trace file, and it does not appear to be executing SQL, that is, no initialization action is performed and the necessary information is queried. Perhaps this is the origin of what we call the "primary connection".
Due to the use of prelim connection, there is no SQL statement executed, so the database can be connected to the database under some hang-up conditions. For example, because the library cache latch is held for a long time cannot be released, cannot parse the SQL statement caused by hang. Some people will say, my application just did not do any operation on the hang up. This is only a superficial phenomenon, after connecting to the database, generally do some initialization operations, such as setting the environment.
Sqlplus-prelim can connect to a database in the case of a database hang, but it is only a connection and does not mean that it can do a lot of things. For example, execute a SQL query. In this case, it may be most useful to use Oradebug.

6. Oradebug Introduction
Oradebug is a tool that Oracle initially designed to debug, and later evolved to define traces through events (event), then the event evolved to support SQL Trace, and SQL Trace was an event numbered 10046

This means that this is a development process from the bottom up to the advanced support, and Oracle is gradually improving. Oracle's Events event supports the definition and extended functionality of this system, which is very powerful. In fact, from the software developers to debug their own code tools gradually developed to provide users with interface for tracking and debugging applications.

Supplement: http://blog.csdn.net/zhang123456456/article/details/77281608 Oradebug Operation reference

Oracle's Sqlplus prelim parameter description (handles hang)