数据库启动报ORA-600 kcbgtcr_13处理

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:数据库启动报ORA-600 kcbgtcr_13处理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库发生故障,经过第三方处理过,接手之后,尝试open库报ORA-01190错误

Thu Apr 20 16:51:25 2023
alter database open upgrade
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_4818.trc:
ORA-01190: control file or data file 3 is from before the last RESETLOGS
ORA-01110: data file 3: '/data/topprod/undotbs01.dbf'
ORA-1190 signalled during: alter database open upgrade...

这个问题是由于resetlogs的时候有文件遗漏导致resetlogs scn和其他数据文件/ctl不匹配导致,以前类似处理文章:
bbed解决ORA-01190
12C sysaux 异常恢复—ORA-01190错误恢复
Oracle Recovery Tools 解决ORA-01190 ORA-01248等故障

数据库启动报ORA-600 kcbgtcr_13错

</tmp> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 20 17:05:24 2023

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database ;
Media recovery complete.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [],
[], [], [], [], [], []
Process ID: 5492
Session ID: 861 Serial number: 19

alert日志报错

Thu Apr 20 17:05:37 2023
SMON: enabling cache recovery
[5492] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:800184 end:800294 diff:110 (1 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_smon_4770.trc  (incident=2390097):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u2/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_2390097/xifenfei_smon_4770_i2390097.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_5492.trc  (incident=2390129):
ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u2/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_2390129/xifenfei_ora_5492_i2390129.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Block recovery from logseq 2, block 56 to scn 8615223701
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: /u2/oracle/oradata/xifenfei/redo02.log
Block recovery completed at rba 2.60.16, scn 2.25289110
Block recovery from logseq 2, block 56 to scn 8615223600
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: /u2/oracle/oradata/xifenfei/redo02.log
Block recovery completed at rba 2.59.16, scn 2.25289009
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_smon_4770.trc:
ORA-01595: error freeing extent (3) of rollback segment (5))
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_5492.trc:
ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u2/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_5492.trc:
ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 5492): terminating the instance due to error 600
Thu Apr 20 17:05:40 2023
Instance terminated by USER, pid = 5492
ORA-1092 signalled during: alter database open upgrade...
opiodr aborting process unknown ospid (5492) as a result of ORA-1092

这个错误比较明显是由于undo异常导致,规避掉undo问题,数据库启动成功

SQL> startup mount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 4.2758E+10 bytes
Fixed Size                  2237776 bytes
Variable Size            3.7447E+10 bytes
Database Buffers         5234491392 bytes
Redo Buffers               74444800 bytes
Database mounted.
SQL> alter database open ;

Database altered.

然后逻辑方式导出数据,导入到新库即可,对于此类问题在2014年处理过类似的case:
记录一次ORA-600 kccpb_sanity_check_2和ORA-600 kcbgtcr_13 错误恢复

win平台 UtilSession 失败: Prerequisite check “CheckActiveFilesAndExecutables” failed. 处理

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:win平台 UtilSession 失败: Prerequisite check “CheckActiveFilesAndExecutables” failed. 处理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

win平台打patch,提示UtilSession 失败: Prerequisite check “CheckActiveFilesAndExecutables” failed.

C:\Users\Administrator\Desktop\nmgOracle补丁\34474433>E:\app\Administrator\produ
ct\11.2.0\dbhome_1\OPatch\opatch apply ./
Oracle 临时补丁程序安装程序版本 11.2.0.3.40
版权所有 (c) 2023, Oracle Corporation。保留所有权利。


Oracle 主目录       :E:\app\ADMINI~1\product\112~1.0\dbhome_1
主产品清单:C:\Program Files\Oracle\Inventory
   来自           :
OPatch 版本    :11.2.0.3.40
OUI 版本       :11.2.0.4.0
日志文件位置:E:\app\ADMINI~1\product\112~1.0\dbhome_1\cfgtoollogs\opatch\opatch
2023-04-20_18-05-20下午_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:

Following active files/executables/libs are used by ORACLE_HOME :E:\app\ADMINI~1
\product\112~1.0\dbhome_1
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraxml11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orahasgen11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraocr11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraocrb11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraocrutl11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oran11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orantcp11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oranl11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orancrypt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orapls11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orageneric11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraclient11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oracommon11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\rdbms\mesg\diaus.msb
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraplc11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraplp11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orannzsbb11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orazt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraztkg11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraldapclnt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oracore11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oracell11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraasmclnt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orannzsbb11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orazt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraztkg11.dll

UtilSession 失败: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: E:\app\ADMINI~1\product\112~1.0\dbhome_1\cfgtoollogs\opatch\o
patch2023-04-20_18-05-20下午_1.log

OPatch failed with error code = 73

通过tasklist命令找出来占用的进程名称和进程号

E:\app\Administrator\product\11.2.0\dbhome_1\OPatch>tasklist /M ora*

映像名称                       PID 模块
========================= ======== ============================================
esfdaemon.exe                 1296 oracleclientsvc.dll, oracleengine.dll,
                                   oracledatasource.dll, oraclecore.dll,
                                   OraClient11.Dll, oracore11.dll,
                                   oranls11.dll, oraunls11.dll, orauts.dll,
                                   oracommon11.dll, orasnls11.dll,
                                   orageneric11.dll, oraxml11.dll, oran11.dll,
                                   oranl11.dll, orantcp11.dll, oranldap11.dll,
                                   orannzsbb11.dll, oraldapclnt11.dll,
                                   orancrypt11.dll, orazt11.dll, oranro11.dll,
                                   oranhost11.dll, orancds11.dll,
                                   orantns11.dll, oraztkg11.dll, orapls11.dll,
                                   oraslax11.dll, oravsn11.dll, oraplp11.dll,
                                   orahasgen11.dll, oraocr11.dll,
                                   oraocrb11.dll, oraasmclnt11.dll,
                                   oracell11.dll, oraocrutl11.dll,
                                   orasql11.dll, OraPlc11.Dll, oranbeq11.dll
plsqldev.exe                 29840 OraClient11.Dll, oracore11.dll,
                                   oranls11.dll, oraunls11.dll, orauts.dll,
                                   oracommon11.dll, orasnls11.dll,
                                   orageneric11.dll, oraxml11.dll, oran11.dll,
                                   oranl11.dll, orantcp11.dll, oranldap11.dll,
                                   orannzsbb11.dll, oraldapclnt11.dll,
                                   orancrypt11.dll, orazt11.dll, oranro11.dll,
                                   oranhost11.dll, orancds11.dll,
                                   orantns11.dll, oraztkg11.dll, orapls11.dll,
                                   oraslax11.dll, oravsn11.dll, oraplp11.dll,
                                   orahasgen11.dll, oraocr11.dll,
                                   oraocrb11.dll, oraasmclnt11.dll,
                                   oracell11.dll, oraocrutl11.dll,
                                   orasql11.dll, OraPlc11.Dll

可以在任务管理器中直接把相关进程kill掉,也可以通过taskkill /F /im 进程名方式杀掉进程,然后再次检查占用情况,无任何进程占用的情况下,就可以直接打patch

E:\app\Administrator\product\11.2.0\dbhome_1\OPatch>tasklist /M ora*
信息: 没有运行的任务匹配指定标准。

Oracle Recovery Tools快速恢复断电引起的无法正常启动数据库(ORA-01555,MISSING000等问题)

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:Oracle Recovery Tools快速恢复断电引起的无法正常启动数据库(ORA-01555,MISSING000等问题)

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

由于异常断电,数据库启动报错ORA-01113和ORA-01110,ORA-00322和ORA-00312以及ORA-00314和ORA-00312错误

Mon Apr 17 09:35:04 2023
ALTER DATABASE OPEN
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_10192.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1113 signalled during: ALTER DATABASE OPEN...
Mon Apr 17 09:35:05 2023
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_9788.trc:
ORA-00322: 日志 6 (用于线程 1) 不是最新副本
ORA-00312: 联机日志 6 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO06.LOG'
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_9788.trc:
ORA-00314: 日志 7 (用于线程 1) 要求的 sequence# 15257 与 15248 不匹配
ORA-00312: 联机日志 7 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO07.LOG'

客户那边技术尝试强制拉库之后,报ORA-00704、ORA-01555等错误,数据库启动失败

Mon Apr 17 10:10:49 2023
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 3159276957
Resetting resetlogs activation ID 1558785994 (0x5ce92fca)
Mon Apr 17 10:11:11 2023
Setting recovery target incarnation to 3
Mon Apr 17 10:11:11 2023
Assigning activation ID 1662170916 (0x6312b724)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Mon Apr 17 10:11:11 2023
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Apr 17 10:11:12 2023
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.bc4ebda4):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9324.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 10 (名称为 "_SYSSMU10_3905543278$") 过小
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9324.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 10 (名称为 "_SYSSMU10_3905543278$") 过小
Error 704 happened during db open, shutting down database
USER (ospid: 9324): terminating the instance due to error 704
Instance terminated by USER, pid = 9324
ORA-1092 signalled during: alter database open resetlogs...

然后尝试一系列操作,比如重建ctl,再次拉库等等,均未绕过该错误.对于ORA-704 ORA-01555错误,使用开发的小工具Oracle Recovery Tools可以一键式解决,参考处理过的类似文章:Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障,不过最近发布的版本中增加了直接修改内存SCN功能,可以更加快速的解决给问题
20230417161757


修复之后,数据库open成功

SQL> alter database open;

数据库已更改。

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile='d:/pfile.txt' mount;
ORACLE 例程已经启动。

Total System Global Area 1.7170E+10 bytes
Fixed Size                  2262088 bytes
Variable Size            1.3153E+10 bytes
Database Buffers         3959422976 bytes
Redo Buffers               54947840 bytes
数据库装载完毕。
SQL> select name from v$datafile where name like '%MISSING%';

NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00012
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00013

已选择2行。

很不幸技术在尝试重建ctl的恢复的过程中遗漏了两个数据库文件,通过Oracle Recovery Tools再次进行恢复
20230417230141


在sqlplus中恢复这两个文件成功

SQL> alter database rename file 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00012' 
   2 to 'D:\oradata\XIFENFEI_04.DBF';

数据库已更改。

SQL> alter database rename file 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00013' 
   2 to 'D:\oradata\XIFENFEI_5.DBF';

数据库已更改。


SQL> alter database datafile 12,13 online;

数据库已更改。

SQL>
SQL>
SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-01113: 文件 12 需要介质恢复
ORA-01110: 数据文件 12: 'D:\ORADATA\XIFENFEI_04.DBF'


SQL> recover datafile 12;
完成介质恢复。
SQL> recover datafile 13;
完成介质恢复。
SQL> alter database open;

数据库已更改。

至此数据库基本上恢复完成,剩余工作就是逻辑导出导入.
软件下载:OraRecovery工具
使用说明:Oracle_Recovery_Tools说明

login trigger导致ORA-16191问题

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:login trigger导致ORA-16191问题

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

主库突然报ORA-16191错,主库无法传输日志到备库

at Apr 08 19:56:26 2023
Thread 1 advanced to log sequence 32548 (LGWR switch)
  Current log# 1 seq# 32548 mem# 0: /u01/app/oracle/oradata/orcl/redo01_a.rdo
  Current log# 1 seq# 32548 mem# 1: /oracle/fast_recovery_area/orcl/redo01_b.rdo
Sat Apr 08 19:56:26 2023
Archived Log entry 61796 added for thread 1 sequence 32547 ID 0x5fc1b26f dest 1:
Sat Apr 08 19:56:27 2023
LNS: Standby redo logfile selected for thread 1 sequence 32548 for destination LOG_ARCHIVE_DEST_2
Sat Apr 08 20:21:29 2023
NSA:  Error 3135 archiving log 1 to 'orcldg'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_nsa2_26205.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 1 thread 1 sequence 32548 (3135)
Sat Apr 08 20:26:26 2023
Thread 1 advanced to log sequence 32549 (LGWR switch)
  Current log# 2 seq# 32549 mem# 0: /u01/app/oracle/oradata/orcl/redo02_a.rdo
  Current log# 2 seq# 32549 mem# 1: /oracle/fast_recovery_area/orcl/redo02_b.rdo
Sat Apr 08 20:26:26 2023
Archived Log entry 61798 added for thread 1 sequence 32548 ID 0x5fc1b26f dest 1:
Sat Apr 08 20:26:55 2023
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------
PING[ARC2]: Heartbeat failed to connect to standby 'orcldg'. Error is 16191.
Sat Apr 08 20:27:57 2023
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------
PING[ARC2]: Heartbeat failed to connect to standby 'orcldg'. Error is 16191.

备库报错

Sat Apr 08 19:29:09 2023
Media Recovery Waiting for thread 1 sequence 32548 (in transit)
Recovery of Online Redo Log: Thread 1 Group 12 Seq 32548 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl/std_redo12.log
Sat Apr 08 21:52:19 2023
RFS[9]: Possible network disconnect with primary database

检查主备库remote_login_passwordfile参数,都是为EXCLUSIVE,确认没有问题

--备库
SQL> show parameter pass;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

--主库
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> show parameter pass;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

检查主备库密码文件,证明主备库密码文件一致,也不是密码文件引起不同步

--主库
[oracle@oracle1 trace]$ cd $ORACLE_HOME/dbs
[oracle@oracle1 dbs]$ echo $ORACLE_SID
orcl
[oracle@oracle1 dbs]$ ls -ltr orapw*
-rw-r-----. 1 oracle oinstall 1536 Sep 16  2022 orapworcl
[oracle@oracle1 dbs]$ md5sum orapworcl 
d27c234b5131b4d2e13b1eeb4388f0eb  orapworcl

--备库
[oracle@oracle2 trace]$ cd $ORACLE_HOME/dbs
[oracle@oracle2 dbs]$ echo $ORACLE_SID
orcl
[oracle@oracle2 dbs]$ ls -l orapw*
-rw-r----- 1 oracle oinstall 1536 Sep 16  2022 orapworcl
[oracle@oracle2 dbs]$ md5sum orapworcl 
d27c234b5131b4d2e13b1eeb4388f0eb  orapworcl

查看数据库有logon触发器

SQL> select owner,trigger_name from dba_triggers where triggering_event LIKE '%LOGON%';

OWNER                          TRIGGER_NAME
------------------------------ ------------------------------
DBTOOLS                        TRIGGER_LOGINLIMIT

根据Error 1017 / ORA-16191 In Standby Alertlog (Doc ID 2225190.1)中描述logon触发器可能导致dg传输日志失败.
trigger-ORA-16191


V$Wait_Chains定位阻塞源头

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:V$Wait_Chains定位阻塞源头

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

从11.1开始,查询会话阻塞,V$Wait_Chains是一个非常好的视图,通过他可以快速找到阻塞源头.进行一个简单的测试
测试表create table t1 as select * from dba_objects;
会话1

SQL> delete from t1 where object_id>100 and object_id<10000;

9712 rows deleted.

会话2

SQL> delete from t1 where object_id<200;
---hang住

会话3

SQL> delete from t1 where object_id>88 and object_id<150;
--hang住

会话4进行查询分析

SQL> set lines 150
SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30) FROM v$wait_chains; 

  CHAIN_ID NUM_WAITERS IN_WAIT_SECS OSID                      BLOCKER_OSID              SUBSTR(WAIT_EVENT_TEXT,1,30)
---------- ----------- ------------ ------------------------- ------------------------- ----------------------------------
         1           0          438 17072                     17005                     enq: TX - row lock contention
         1           1          483 17005                     16930                     enq: TX - row lock contention
         1           2          505 16930                                               SQL*Net message from client

SQL> set pages 1000
SQL>  set lines 120
SQL>  set heading off
SQL>  column w_proc format a50 tru
SQL>  column instance format a20 tru
SQL>  column inst format a28 tru
SQL>  column wait_event format a50 tru
SQL>  column p1 format a16 tru
SQL>  column p2 format a16 tru
SQL>  column p3 format a15 tru
SQL>  column Seconds format a50 tru
SQL>  column sincelw format a50 tru
SQL>  column blocker_proc format a50 tru
SQL>  column waiters format a50 tru
SQL>  column chain_signature format a100 wra
SQL>  column blocker_chain format a100 wra
SQL>  
SQL>  SELECT *
  2   FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
  3   'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||
  4   ' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
  5   'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
  6   'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
  7   'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
  8   '<none>',blocker_chain_id) blocker_chain
  9   FROM v$wait_chains wc,
 10   v$instance i
 11   WHERE wc.instance = i.instance_number (+)
 12   AND ( num_waiters > 0
 13   OR ( blocker_osid IS NOT NULL
 14   AND in_wait_secs > 10 ) )
 15   ORDER BY chain_id,
 16   num_waiters DESC)
 17   WHERE ROWNUM < 101;

Current Process: 16930                             SID orcl             INST #: 1
Blocking Process: <none> from Instance             Number of waiters: 2
Wait Event: SQL*Net message from client            P1: 1650815232   P2: 1            P3: 0
Seconds in Wait: 140                               Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

Current Process: 17005                             SID orcl             INST #: 1
Blocking Process: 16930 from Instance 1            Number of waiters: 1
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 524290       P3: 8984
Seconds in Wait: 119                               Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

Current Process: 17072                             SID orcl             INST #: 1
Blocking Process: 17005 from Instance 1            Number of waiters: 0
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 458768       P3: 8720
Seconds in Wait: 74                                Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

SQL> set pages 1000
SQL> set lines 120
SQL> set heading off
SQL> column w_proc format a50 tru
SQL> column instance format a20 tru
SQL> column inst format a28 tru
SQL> column wait_event format a50 tru
SQL> column p1 format a16 tru
SQL> column p2 format a16 tru
SQL> column p3 format a15 tru
SQL> column Seconds format a50 tru
SQL> column sincelw format a50 tru
SQL> column blocker_proc format a50 tru
SQL> column fblocker_proc format a50 tru
SQL> column waiters format a50 tru
SQL> column chain_signature format a100 wra
SQL> column blocker_chain format a100 wra
SQL> SELECT *
  2  FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
  3   'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)||
  4   ' from Instance '||blocker_instance BLOCKER_PROC,
  5   'Number of waiters: '||num_waiters waiters,
  6   'Final Blocking Process: '||decode(p.spid,null,'<none>',
  7   p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC,
  8   'Program: '||p.program image,
  9   'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
 10   'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
 11   'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
 12   '<none>',blocker_chain_id) blocker_chain
 13  FROM v$wait_chains wc,
 14   gv$session s,
  15  gv$session bs,
 16   gv$instance i,
 17   gv$process p
 18  WHERE wc.instance = i.instance_number (+)
 19   AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
 20   and wc.sess_serial# = s.serial# (+))
 21   AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
 22   AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
 23   AND ( num_waiters > 0
 24   OR ( blocker_osid IS NOT NULL
 25   AND in_wait_secs > 10 ) )
 26  ORDER BY chain_id,
 27   num_waiters DESC)
 28  WHERE ROWNUM < 101;

Current Process: 16930                             SID orcl             INST #: 1
Blocking Process: <none> from Instance             Number of waiters: 2
Final Blocking Process: <none> from Instance       Program:
Wait Event: SQL*Net message from client            P1: 1650815232   P2: 1            P3: 0
Seconds in Wait: 177                               Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

Current Process: 17005                             SID orcl             INST #: 1
Blocking Process: 16930 from Instance 1            Number of waiters: 1
Final Blocking Process: 16930 from Instance 1      Program: oracle@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3)
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 524290       P3: 8984
Seconds in Wait: 155                               Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

Current Process: 17072                             SID orcl             INST #: 1
Blocking Process: 17005 from Instance 1            Number of waiters: 0
Final Blocking Process: 16930 from Instance 1      Program: oracle@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3)
Wait Event: enq: TX - row lock contention          P1: 1415053318   P2: 458768       P3: 8720
Seconds in Wait: 110                               Seconds Since Last Wait:
Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock c
ontention'
Blocking Wait Chain: <none>

SQL> col SES for a15
SQL> set linesize 150
SQL> WITH BLOCKED AS
  2   (SELECT *
  3    FROM (SELECT INSTANCE,
  4                   SID,
  5                   SESS_SERIAL#,
  6                   BLOCKER_INSTANCE,
  7                   BLOCKER_SID,
  8                   BLOCKER_SESS_SERIAL#,
  9                   LEVEL LV,
 10                   NUM_WAITERS,
 11                   BLOCKER_CHAIN_ID
 12              FROM V$WAIT_CHAINS
 13            CONNECT BY PRIOR SID = BLOCKER_SID
 14                   AND PRIOR SESS_SERIAL# = BLOCKER_SESS_SERIAL#
 15                   AND PRIOR INSTANCE = BLOCKER_INSTANCE
 16             START WITH BLOCKER_IS_VALID = 'FALSE')
 17     WHERE NUM_WAITERS > 0
 18        OR BLOCKER_SID IS NOT NULL)
 19  SELECT INSTANCE,
 20         LPAD(' ', 2 * (LV - 1)) || B.SID SES,
 21         B.SESS_SERIAL#,
 22         B.BLOCKER_INSTANCE,
 23         B.BLOCKER_SID,
 24         B.BLOCKER_SESS_SERIAL#
 25    FROM BLOCKED B ;

  INSTANCE SES             SESS_SERIAL# BLOCKER_INSTANCE BLOCKER_SID BLOCKER_SESS_SERIAL#
---------- --------------- ------------ ---------------- ----------- --------------------
         1 42                      1819
         1   32                   52659                1          42                 1819
         1     39                 39865                1          32                52659

能够快速的定位到阻塞会话的源头,以及阻塞的级联关系