无人连接却删除不了oracle用户（报ORA-01940错误？）

qmf

无人连接却删除不了oracle用户（报ORA-01940错误？）  

ORACLE数据库大量生成trace文件排查过程
现场彩铃业务环境，创建有USDP测试用数据库用户ring，割接前需要drop掉该用户并重新创建用户，创建应用数据库环境。SYS登陆数据库sqlplus后执行SQL>drop user ring cascade;
遇到如下错误：

反复尝试各种方法drop该用户，每次都遇到相同的报错，提示该用户正在使用。提单到oracle原厂，其专家提供以下方法尝试drop该用户：
SQL>alter user ring account lock;
再次执行SQL>drop user ring cascade， 发现可以成功的把ring用户drop掉了。
取数据库AWR报告，检查发现EVENT中top 1 是row cache wait；研发怀疑存在某个应用进程在不断的尝试链接数据库，现场检查了业务相关的所有网元中与数据库相关的配置部分，并没有发现错误的账号/密码配置内容。

研发提供方案在数据库部署触发器，检测是是哪个网元在不断的尝试连接数据库。如下：
1.     登录sys执行如下脚本，过几分钟看一下表logonaudittable并保存数据

2.     在logonaudittable有数据后执行如下命令删除触发器及新建的表
drop TRIGGER tr_log_errors;
drop table  TABLE logonaudittable;
从logonaudittable表中发现反复尝试连接数据库的客户端信息如下;
EVENT
SID
SERIAL#
TIMESTAMP
USERNAME
OSUSERID
MACHINENAME
IP
LOGERR
1006
36385
5/21/2012 1:38:11 AM
　
aipcti
FC1A
192.168.108.133
LOGERR
1007
12729
5/21/2012 1:38:11 AM
　
aipcti
FC1A
192.168.108.133
LOGERR
1007
12734
5/21/2012 1:38:11 AM
　
aipcti
FC1A
192.168.108.133
LOGERR
1006
36495
5/21/2012 1:38:12 AM
　
aipcti
FC1A
192.168.108.133
LOGERR
1006
36499
5/21/2012 1:38:12 AM
　
aipcti
FC1A
192.168.108.133
LOGERR
1006
36503
5/21/2012 1:38:13 AM
　
aipcti
FC1A
192.168.108.133
192.168.108.133 是现网CTI主机的fabric平面地址，业务应用上考虑CTI到数据库的链接是由Aplogic来发起的。所以首先需要检查aplogic的配置。登陆WEB配置台检查，确认aplogic连接数据库对应的用户名,密码是完全正确的。需要继续排查是哪个进程在反复发起到数据库的连接。
root用户登陆CTI主机（192.168.108.133）检查主机与外部网元建链情况：
</home/aipcti/icddir/config>netstat -anp | grep 192.168.108.133
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp        0      0 192.168.108.133:427     0.0.0.0:*               LISTEN      -                  
tcp        0      0 192.168.108.133:8849    0.0.0.0:*               LISTEN      4079/netcheck      
tcp        0      0 192.168.108.133:8888    0.0.0.0:*               LISTEN      3854/icdcomm      
tcp        1      0 192.168.108.133:33066   192.168.108.155:8080    CLOSE_WAIT  4071/ui            

                      -                  
</home/aipcti/icddir/config>
我们注意到蓝色字体部分显示，有大量的异常链接请求从133主机发送到135主机（数据库服务器ip），并且这些连接始终处于“TIME_WAIT”状态，表明链接无法成功建立。观察其他链接（如下），我们可以确认该连接是由aplogic进程发起，在133主机与135主机间建立了连接。
tcp        0      0 192.168.108.133:59347   192.168.108.135:1521    ESTABLISHED 4063/aplogic   
然而对于蓝色字体部分的链接，netstat –anp 命令却无法看到是哪个进程发起的请求。问题的
定位一时陷入了僵局。

排除了CTI主机业务应用的问题，考虑该主机上是否有其他程序在跑，对比CTI主机1和CTI主机2，发现只有CTI主机1上存在这样的问题。比较两者的配置发现CTI主机1上除了业务程序外还安装有WAS应用程序。并且该应用程序处于运行状态。由此怀疑问题出在WAS上。使用维护助手登陆WAS配置管理页面，检查配置发现WAS确实配置有老的数据库账号(ring).修改数据库账号并重启WAS进程后，执行netstat –anp |grep 192.168.108.133发现没有了异常的链接，相关情况如下：
</home/aipcti/icddir/bin>netstat -an |grep 192.168.108.133
tcp        0      0 192.168.108.133:427     0.0.0.0:*               LISTEN     
tcp        0      0 192.168.108.133:8849    0.0.0.0:*               LISTEN     
                 

导致ring用户一直无法drop掉的根本原因在于WAS依然使用ring账户链接数据库。检查数据库/opt/oracle/diag/rdbms/instancename/SID/trace/ 下已经不再大量产生trace文件，制作ARW报告检查确认EVENT中top 1 已经不再是row cache wait了。如下; 至此可以确认问题解决。
Event
Waits
Time(s)
Avg wait (ms)
% DB time
Wait Class
db file sequential read
105,302
816
8
50.29
User I/O
DB CPU

687

42.38

log file sync
54,104
49
1
3.01
Commit
SQL*Net message to client
1,174,626
1
0
0.08
Network
db file scattered read
226
1
4
0.05
User I/O

不过这里还有个疑问，为何通过netstat –anp 命令查看主机与外部网元链接情况时无法检测到是WAS发起这些链接的呢？


总结：
在数据库部署触发器，检查是哪个客户端在反复尝试建立链接是解决这个问题的关键。之前有尝试过多次探寻到底是哪个网元/进程还在连接数据库，一直没有找到很好的方法，曾经重启数据库，主机，业务各个组件都无法解决。
附件： ORACLE数据库大量生成trace文件问题排查.docx ( 106.56 KB ,11 downs )

fengerapple

Thank you very much for sharing!The good man!The good life of peace!