Oracle 死锁分析过程详解.doc

Oracle 死锁分析过程详解Oracle 死锁分析关于死锁一般3种处理方式1、事前预测2、资源分级3、事后检测释放我知道的ORACLE MYSQL都是采用第三种在行锁级别上的话。这里分析一个ORACLE死锁，首先一个死锁肯定会生成一个TRACE文件，这里会记录很多信息如：Deadlock graph:-Blocker(s)- -Waiter(s)-Resource Name process session holds waits process session holds waitsTX-0058000f-0000b473 649 1204 X 651 1252 XTX-0019001c-0004e0b0 651 1252 X 649 1204 X这里给出了进程和会话idRows waited on:Session 1204: obj - rowid = 0003D942 - AAA9lCAAEAADgaNAAI(dictionary objn - 252226, file - 4, block - 919181, slot - 8)Session 1252: obj - rowid = 0003D942 - AAA9lCAAEAADgaNAAa(dictionary objn - 252226, file - 4, block - 919181, slot - 26)这里给出导致死锁的行同时给出了最后触发死锁会话 1252的语句- Information for the OTHER waiting sessions -Session 1252:sid: 1252 ser: 35883 audsid: 7170593 user: 235/FEECORESVflags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-flags2: (0x40009) -/-/INCpid: 651 O/S info: user: oracle, term: UNKNOWN, ospid: 13035image: oracleoratest11client details:O/S info: user: sky, term: unknown, ospid: 1234machine: autobots program: JDBC Thin Clientapplication name: JDBC Thin Client, hash value=2546894660current SQL:UPDATE *- End of information for the OTHER waiting sessions -Information for THIS session:- Current SQL Statement for this session (sql_id=3vh5sc7pgtrjy) -UPDATE *那么到这里我们大概能够分析出A:1204拿到AAA9lCAAEAADgaNAAa 行锁 B:1252 拿到 AAA9lCAAEAADgaNAAI 行锁C:1204需要AAA9lCAAEAADgaNAAI 则等待 D:1252 需要 AAA9lCAAEAADgaNAAa 则触发死锁1204回滚那么随后trace给出1204 C这一步等待时间和事物信息SO: 0xee1fcd10, type: 4, owner: 0xf031e750, flag: INIT/-/-/0x00 if: 0x3 c: 0x3proc=0xf031e750, name=session, file=ksu.h LINE:12624, pg=0(session) sid: 1204 ser: 2443 trans: 0xe9221180, creator: 0xf031e750flags: (0x100045) USR/- flags_idl: (0x1 ) BSY/-/-/-/-/-flags2: (0x40009) -/-/INCDID: , short-term DID:txn branch: (nil)oct: 6, prv: 0, sql: 0xf25d2278, psql: 0xc4346788, user: 235/FEECORESVksuxds FALSE at location: 0service name: SYS$USERSCurrent Wait Stack:0: waiting for enq: TX - row lock contentionname|mode=0x54580006, usn select * from v$version; BANNER-Oracle Database 10g Enterprise Edition Release .0 - 64biPL/SQL Release .0 - ProductionCORE .0 ProductionTNS for IBM/AIX RISC System/6000: Version .0 - ProductioNLSRTL Version .0 - Production挂载参数(mount命令查看) /top/data4/nfs /back1 nfs3 Aug 29 13:40 cio,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600尝试创建redoSQL alter database add logfile group 13 (/back/newxff/redo13.log) size 2048m; alter database add logfile group 13 (/back1/newxff/redo13.log) size 2048m*ERROR at line 1:ORA-00301: error in adding log file /back1/newxff/redo13.log - file cannot becreatedORA-27054: NFS file system where the file is created or resides is not mountedwith correct optionsAdditional information: 6根据mos文档ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS (文档 ID 387700.1)SQL Alter system set events 10298 trace name context forever,level 32; System altered. Mon Sep 5 10:10:18 2016Thread 1 advanced to log sequence 109 (LGWR switch) Current log# 1 seq# 109 mem# 0: +DATA/xff/onlinelog/group_1.257.921671023Mon Sep 5 10:12:19 2016OS Pid: 160710 executed alter system set events 10298 trace name context forever,level 32创建redo成功SQL alter database add logfile group 13 (/back1/newxff/redo13.log) size 2048m; System altered. Mon Sep 5 10:18:13 2016alter database add logfile group 13 (/back1/newxff/redo13.log) size 2048mMon Sep 5 10:18:43 2016Completed: alter database add logfile group 13 (/back1/newxff/redo13.log) size 2048m数据库crashMon Sep 5 10:19:06 2016Errors in file /opt/oracle/admin/xff/bdump/xff1_lgwr_246566.trc:ORA-00313: open failed for members of log group 13 of thread 1ORA-00312: online log 13 thread 1: /back1/newxff/redo13.logORA-27054: NFS file system where the file is created or resides is not mounted with correct optionsAdditional information: 6Mon Sep 5 10:19:06 2016Errors in file /opt/oracle/admin/xff/bdump/xff1_lgwr_246566.trc:ORA-00313: open failed for members of log group 13 of thread 1ORA-00312: online log 13 thread 1: /back1/newxff/redo13.logORA-27054: NFS file system where the file is created or resides is not mounted with correct optionsAdditional information: 6Mon Sep 5 10:19:06 2016LGWR: terminating instance due to error 313Mon Sep 5 10:19:06 2016System state dump is made for local instanceSystem State dumped to trace file /opt/oracle/admin/xff/bdump/xff1_diag_299654.trc通过报错很明显可以看出来数据库挂掉的原因和当时不能创建redo的原因一样，都是由于ORA-27054导致数据库挂了，但是为什么创建redo成功,但是使用redo失败呢？这里需要注意使用的命令是events,而这个命令是对当前会话和后续新建的会话生效，也就是说他不会对数据库已经存在的后台进程生效，那也就可以理解了，我创建redo是在执行events的当前命令行窗口处理的，因此可以创建成功；但是lgwr进程是数据库一启动就存在的进程，现在设置的events对他没有影响，因此当lgwr 去使用redo的时候无法正常使用因此就导致数据库crash掉。如果希望event对已经存在的进程生效，可以考虑使用oradebug对进程进行设置event(这个案例主要要设置多个后台进程不光lgwr访问redo)，或者设置event=的方式，然后重启数据库让其生效。在一些特殊的情况下(比如ORA-00600 15267,ORA-00600 KKDLCOB-OBJN-EXISTS,Ora-600 15260)，考虑需要把dba_objects中的object_id往前推进，这里通过试验的方法实现该功能数据库版本信息SQL select * from v$version; BANNER-Oracle Database 10g Enterprise Edition Release .0 - ProdPL/SQL Release .0 - ProductionCORE .0 ProductionTNS for Linux: Version .0 - ProductionNLSRTL Version .0 - Production分析obj和dataobjSQL select max(obj#),max(dataobj#) from obj$; MAX(OBJ#) MAX(DATAOBJ#)- - 51887 51907 SQL select name from obj$ where obj#=51887; NAME-T_DUL SQL select name from obj$ where dataobj#=51907; NAME-_NEXT_OBJECT SQL select object_id,data_object_id from dba_objects where object_name=_NEXT_OBJECT; no rows selected为什么dba_objects中无_NEXT_OBJECT因为dba_objects视图中跳过了_NEXT_OBJECT这条记录_next_object测试创建新表后obj和dataobj的变化SQL create table t_xff as select * from dual; Table created. SQL select max(obj#),max(dataobj#) from obj$; MAX(OBJ#) MAX(DATAOBJ#)- - 51898 51907 SQL select name from obj$ where obj#=51898; NAME-T_XFF SQL select max(object_id),max(data_object_id) from dba_objects where object_name=T_XFF; MAX(OBJECT_ID) MAX(DATA_OBJECT_ID)- - 51898 51898通过测试可以确定,obj发生增加,但是dataobj不一定增加(因为dataobj本身比obj大，如果出现objdataobj那属于异常情况）测试数据库重启obj和dataobj是否会跳号-正常重启数据库SQL SHUTDOWN IMMEDIATE;Database closed.Database dismounted.ORACLE instance shut down.SQL STARTUPORACLE instance started. Total System Global Area 260046848 bytesFixed Size 1266920 bytesVariable Size 83888920 bytesDatabase Buffers 171966464 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened.SQL select max(obj#),max(dataobj#) from obj$; MAX(OBJ#) MAX(DATAOBJ#)- - 51898 51907 -强制重启数据库SQL shutdown abortORACLE instance shut down.SQL startupORACLE instance started. Total System Global Area 260046848 bytesFixed Size 1266920 bytesVariable Size 83888920 bytesDatabase Buffers 171966464 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened.SQL select max(obj#),max(dataobj#) from obj$; MAX(OBJ#) MAX(DATAOBJ#)- - 51898 51907通过这个证明obj和dataobj没有因为数据库重启而发生改变实现obj跳号SQL shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL startup restrictORACLE instance started. Total System Global Area 260046848 bytesFixed Size 1266920 bytesVariable Size 83888920 bytesDatabase Buffers 171966464 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened.SQL update obj$ set dataobj#=1000000 where name=_NEXT_OBJECT; 1 row updated. SQL