oracle oradebug systemstate 的日常使用详解数据库

oracle oradebug systemstate 是dba分析instance的重要方法，需要勤加练习、多多分析。

数据库版本

$sqlplus system/oracleoracle 
 
SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 27 17:35:11 2018 
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved. 
 
 
Connected to: 
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 
 
SQL> 
SQL> set lines 200;  
SQL> set pages 200; 
SQL>  
SQL> select * from v$version; 
 
BANNER                                           CON_ID 
-------------------------------------------------------------------------------- ---------- 
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production          0 
PL/SQL Release 12.2.0.1.0 - Production                            0 
CORE    12.2.0.1.0  Production                                0 
TNS for Linux: Version 12.2.0.1.0 - Production                        0 
NLSRTL Version 12.2.0.1.0 - Production                            0 
 
SQL> 

创建测试表，插入测试数据

SQL> create table tmp_peiyb_20180427 ( a varchar2(100),b varchar2(100)); 
 
Table created. 
 
SQL> insert into tmp_peiyb_20180427(a,b) values('aa','bb'); 
 
1 row created. 
 
SQL> commit; 
 
Commit complete. 
 
SQL>  
SQL> 

session 1 更新，不commit

SQL> update tmp_peiyb_20180427 set b='cc' where a='aa'; 
 
1 row updated. 
 
SQL> 

session 2 更新，会处于wait状态

SQL> update tmp_peiyb_20180427 set b='cc' where a='aa'; 

session 3 使用sys执行 oradebug systemstate

SQL> oradebug setmypid; 
Statement processed. 
SQL> oradebug unlimit; 
Statement processed. 
SQL> oradebug dump systemstate 266;  
Statement processed. 
SQL> oradebug TRACEFILE_NAME; 
/u01/app/oracle/diag/rdbms/rac0/rac01/trace/rac01_ora_11369.trc 
SQL>  
SQL>  
SQL>  
SQL>  
SQL> oradebug dump systemstate 266; 
Statement processed. 
SQL> oradebug TRACEFILE_NAME; 
/u01/app/oracle/diag/rdbms/rac0/rac01/trace/rac01_ora_11369.trc 
SQL> oradebug CLOSE_TRACE; 
Statement processed.

session 3 rac环境下使用sys执行 oradebug systemstate

SQL> oradebug setmypid; 
Statement processed. 
SQL> oradebug unlimit; 
Statement processed. 
SQL> oradebug setinst all; 
Statement processed. 
SQL> oradebug -g def dump systemstate 266; 
Statement processed. 
SQL> oradebug TRACEFILE_NAME; 
/u01/app/oracle/diag/rdbms/rac0/rac01/trace/rac01_ora_13859.trc 
SQL>  
SQL>  
SQL>  
SQL> 
SQL> oradebug CLOSE_TRACE; 
Statement processed.

无法登录数据库，需要添加 -prelim 参数

sqlplus -prelim / as sysdba;