Chapter one   Oracle hang And sqlplus -prelim Usage method  

In many cases ,Oracle hang Lead to sqlplus Unable to connect , So you can't get it Oracle System and process state , This makes the positioning problem lack of strong basis . fortunately Oracle 10g Launched sqlplus -prelim Options , stay Oracle Can still be used when suspended sqlplus, So you can get the database status .       
How to use it is as follows

quote

$ sqlplus -prelim "/as sysdba"       
     
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Mar 28 06:40:21 2010       
     
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

   
stay prelim In mode , You can't query the data dictionary , But you can shut down the database

quote

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

 

quote

SQL> shutdown abort       
ORACLE instance shut down.

   
But you can use oradebug, But for system diagnostics , That's enough       
dump System status

quote

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

   
perhaps dump hanganalyze

quote

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

   
about rac

quote

SQLPLUS> oradebug setmypid       
     
SQLPLUS>oradebug setinst all       
     
SQLPLUS>oradebug -g def hanganalyze 3

   
   
perhaps dump Process status

quote

SQL> oradebug dump processstate 10       
Statement processed.

   
   
further , If there is 10g client , The database is 9i, It can still be used -prelim

quote

$ sqlplus -prelim /nolog       
     
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Mar 28 06:50:19 2010       
     
Copyright (c) 1982, 2007, 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.

 

There are two ways to connect to sqlplus using a preliminary connection.

1. sqlplus -prelim / as sysdba

2. sqlplus /nolog        
set _prelim on        
connect / as sysdba

 

1. While running system state dump with "-prelim" option:

sqlplus -prelim / as sysdba        
     
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Dec 11 17:28:14 2006        
     
SQL> oradebug setmypid        
Statement processed.        
SQL> oradebug unlimit        
Statement processed.        
SQL> oradebug dump systemstate 266        
ORA-03113: end-of-file on communication channel        
ORA-24323: value not allowed

 

2. The following error is reported in the alert.log:

ORA-07445: exception encountered: core dump [kgllkd()+1254] [SIGSEGV] [Address not mapped to        
object] [0x000000030] [] []

   
   
The error did not crash background process or terminate the instance.

 

The issue is not resolved in any known patches.

Use any of the below workarounds:

· Connect / as sysdba (without -prelim)        
     
OR

· Force the variable PGA to be initialized:

 

sqlplus -prelim '/ as sysdba'        
oradebug setmypid        
oradebug unlimit        
oradebug call ksmcpg        
oradebug dump systemstate 10

-- Test environment 11g, a lgwr Of spid        
SQL> select * from v$process where username='SYSTEM' and program like '%LGWR%';        
         
ADDR                    PID SPID                     PNAME USERNAME           SERIAL# TERMINAL         PROGRAM                                                          TRACEID                                                                          TRACEFILE                                                                        BACKGROUND LATCHWAIT        LATCHSPIN        PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM        
---------------- ---------- ------------------------ ----- --------------- ---------- ---------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ---------------- ---------------- ------------ ------------- ---------------- -----------        
000007FF614CD768         11 17428                    LGWR  SYSTEM                   1 123-PC           ORACLE.EXE (LGWR)                                                                                                                                 d:\oracle11g_64bit\diag\rdbms\second\second\trace\second_lgwr_17428.trc          1                                                 5007762       5465314           131072     9921762

-- Open another conversation window , pending lgwr process       
SQL> oradebug setsospid 17428        
ORA-00070: command setsospid is not valid        
SQL> oradebug setospid 17428        
Oracle pid: 11, Windows thread id: 17428, image: ORACLE.EXE (LGWR)        
SQL> oradebug suspend        
Statement processed.

-- Log switch occurred hang live        
SQL> alter system switch logfile;        
         
System altered        
-- Create a new session window        
create table t_h(a int);-- sentence hang live

-- Jie hang lgwr process , Above lgwr And create The operation is completed successfully        
SQL> oradebug resume        
Statement processed.

Summary : If the log switches , All the database front-end transactions will be temporarily held create and dml

About Me