Home > Database > Mysql Tutorial > Oracle hang 之sqlplus -prelim使用方法

Oracle hang 之sqlplus -prelim使用方法

WBOY
Release: 2016-06-07 17:34:07
Original
1152 people have browsed it

很多情况下,Oracle hang导致sqlplus无法连接,从而无法获得Oracle系统和进程状态,使得定位问题缺少强有力的依据。所幸的是Orac

很多情况下,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

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

在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

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.
0

sqlplus -prelim/ as sysdba用法 2009-02-03 14:20:47
分类: Oracle


原文见:eygle博客
在某些情况下,数据库失去响应,sqlplus也无法连接,此时通常只能通过杀掉进程来解决。

但是我们仍然希望能够获得此时的数据库状态信息,以便用于事后诊断。
从Oracle10g开始,sqlplus提供了一个参数选项-prelim,可以在通常sqlplus无法连接的情况下进行连接。
通过以下步骤可以获取系统信息:

sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10

这种方法非常有用:

$ sqlplus -prelim / as sysdba

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

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>


但是需要注意的是,在Oracle 10.2.0.1中,使用如上方法会出现一个错误:

$ sqlplus -prelim / as sysdba

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

Copyright (c) 1982, 2005, 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


此时在alert文件中会记录如下错误提示:

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] [] []

这是由于一个Bug导致的,Bug号为5730231,该Bug在10.2.0.3中修正。

对于9i也有变通的方法使用

-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 should 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

linux

Related labels:
or
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template