Shulou(Shulou.com)06/01 Report--

Starting with Oracle10g, sqlplus provides a parameter option-prelim, when the system is already hang. We can connect to the SGA instead of the database, which means that no session is created.

one。 You can obtain system information by following these steps:

Sqlplus-prelim / as sysdba

Oradebug setmypid

Oradebug unlimit

Oradebug dump systemstate 10

For the 9i database, the 9iR2 works fine through a client connection with 10g installed.

II. Characteristics of prelim parameters

1. First enable the 10046 event on the database:

SQL > alter system set events' 10046 trace name context forever'

System altered.

two。 First connect in a normal way:

Dvv / > 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

Connected.

SQL > exit

From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options disconnected

3. See what happens when you add the prelim parameter:

Dvv / > 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

Primary connection established

SQL > exit

Disconnect from ORACLE

As you can see from the above information, when using a 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 in the following ways:

[oracle@xty ~] $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. Take a look at the database's 10046 trace:

From the generated trace file, you can see that sqlplus automatically executes the following SQL after connecting to the database during a normal connection:

ALTER SESSION SET NLS_LANGUAGE= 'SIMPLIFIED CHINESE' NLS_TERRITORY=' CHINA' NLS_CURRENCY='¥'NLS_ISO_CURRENCY=' CHINA' NLS_NUMERIC_CHARACTERS='. 'NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT=' DD-MON-RR' NLS_DATE_LANGUAGE= 'SIMPLIFIED CHINESE' NLS_SORT=' BINARY' TIME_ZONE='+ 08 NLS_CALENDAR= 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

After sqlplus connects to the database using prelim, it does not generate a 10046 trace file, and does not seem to perform SQL, that is, does not perform any initialization actions and query the necessary information. Perhaps this is the origin of the so-called "primary connection".

Due to the use of prelim connection, no sql statements are executed, so in some cases where the hang of the database resides, you can connect to the database. For example, because library cache latch has been held for a long time and cannot be released, the hang caused by the SQL statement cannot be parsed. Some people will say that my app was hang as soon as it was connected without doing anything. This is only a superficial phenomenon, after connecting to the database, we will generally do some initialization operations, such as setting the environment and so on.

Sqlplus-prelim can connect to the database when the database hang resides, but it can only be said to be a connection, which does not mean that it can do a lot of operations. For example, execute a SQL query. In this case, perhaps the most useful thing is to use oradebug.

6. Oradebug introduction

Oradebug is a tool for debugging when oracle was originally designed, and then it was further developed to define traces through event, and then event developed to support sql trace, and sql trace is the event numbered 10046.

In other words, this is a process of development from low-level debugging to high-level support, and oracle is gradually improved. Oracle's events events support the definition and extended functionality of this system, which is very powerful. In fact, it has gradually developed from a tool for software developers to debug their own code to provide an interface for users to track and debug applications.

Supplement: http://blog.csdn.net/zhang123456456/article/details/77281608 oradebug operation reference

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.