第一章 Oracle hang 之sqlplus -prelim使用方法

很多情况下，Oracle hang导致sqlplus无法连接，从而无法获得Oracle系统和进程状态，使得定位问题缺少强有力的依据。所幸的是Oracle 10g推出了sqlplus -prelim选项，在Oracle挂起时依然能使用sqlplus，从而能获得数据库状态。

使用方法如下

引用

$ sqlplus -prelim "/as sysdba"

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

在prelim模式下，不可以查询数据字典,但可以关闭数据库

引用

SQL> select status from v$instance;

select status from v$instance

ERROR at line 1:

ORA-01012: not logged on

引用

SQL> shutdown abort

ORACLE instance shut down.

但可以使用oradebug，但对于系统诊断，已经足够了

dump系统状态

引用

SQL> oradebug setmypid

Statement processed.

SQL> oradebug unlimit

Statement processed.

SQL> oradebug dump systemstate 266

Statement processed.

或者dump hanganalyze

引用

SQL> oradebug hanganalyze 3

Hang Analysis in /oracle/app/oracle/admin/ora10g/udump/ora10g_ora_52642.trc

对于rac

引用

SQLPLUS> oradebug setmypid

SQLPLUS>oradebug setinst all

SQLPLUS>oradebug -g def hanganalyze 3

或者dump 进程状态

引用

SQL> oradebug dump processstate 10

Statement processed.

进一步，如果有10g客户端，数据库是9i，依然可以用-prelim

引用

$ sqlplus -prelim /nolog

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

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)

· Force the variable PGA to be initialized:

sqlplus -prelim '/ as sysdba'

oradebug setmypid

oradebug unlimit

oradebug call ksmcpg

oradebug dump systemstate 10

--测试环境11g,获lgwr的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

--另启一会话窗口,暂挂lgwr进程

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.

--发生日志切换hang住

SQL> alter system switch logfile;

System altered

--新建一个会话窗口

create table t_h(a int);--语句hang住

--解挂lgwr进程,上述lgwr及create操作顺利执行完毕

SQL> oradebug resume

Statement processed.

小结：如果日志切换时,会暂住一切所有的数据库前端事务create and dml

About Me

....................................................................................................................................................

本文来自于微信公众号转载文章，若有侵权，请联系小麦苗及时删除

ITPUB BLOG：http://blog.itpub.net/26736162

QQ：642808185 若加QQ请注明您所正在读的文章标题

....................................................................................................................................................