TM锁的一个小例及发现的问题

最新推荐文章于 2023-12-21 14:38:07 发布

coh31298

最新推荐文章于 2023-12-21 14:38:07 发布

阅读量251

点赞数

文章标签：数据库

通过tm锁和等待事件发现了一个大问题~

[@more@]

TM锁：用来确保修改表的内容时，表的结构不会受到改变，好比说你在update一个表时，就会得到一个TM锁，
这会防止其它用户在该表上执行drop,alter等命令。

在CTAS时发现有tm锁，这是正常的，在创建表的过程中不允许用户进行update，因此会有tm锁。
一般产生锁的同时会有等待事件的产生，所以当发现产生死锁，其次就要查看时候有显著等待事件产生，
再根据具体的事件进行解决。

下面是一个小例，检查到tm锁，直觉是CTAS表没有进行，于是查询等待事件，发现了一个大问题，归档无法完成，
到归档路径下一看100%了。删除部分归档释放空间，问题解决，CTAS得以顺利进行。

下面是记录的操作过程:

SQL> select sid,event,wait_time,state from v$session_wait where sid=71;

SID EVENT WAIT_TIME STATE
---------- -------------------------------------------------- -------------------
71 log file switch (archiving needed) 0 WAITING

SQL> col member for a55
SQL> set linesize 120
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------ ---------- ---------- ---------- ------- --- -------- ------------- ---------
1 1 32315 267386880 3 NO CURRENT 6380183736 29-DEC-06
2 1 32314 267386880 3 YES ACTIVE 6380164426 29-DEC-06
3 1 32313 267386880 3 YES INACTIVE 6380147070 29-DEC-06
4 2 11611 267386880 3 NO INACTIVE 6380174025 29-DEC-06
5 2 11612 267386880 3 NO CURRENT 6380183720 29-DEC-06
6 2 11610 267386880 3 NO INACTIVE 6380167295 29-DEC-06

6 rows selected.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------
1 ONLINE /dev/rredo11
1 ONLINE /dev/rredo12
1 ONLINE /dev/rredo13
2 ONLINE /dev/rredo14
2 ONLINE /dev/rredo15
2 ONLINE /dev/rredo16
3 ONLINE /dev/rredo17
3 ONLINE /dev/rredo18
3 ONLINE /dev/rredo19
4 ONLINE /dev/rredo21
4 ONLINE /dev/rredo22

GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------
4 ONLINE /dev/rredo23
5 ONLINE /dev/rredo24
5 ONLINE /dev/rredo25
5 ONLINE /dev/rredo26
6 ONLINE /dev/rredo27
6 ONLINE /dev/rredo28
6 ONLINE /dev/rredo29

18 rows selected.

SQL> show parameter log_archive_dest_1

NAME TYPE VALUE
---------------------------------- ------------------
log_archive_dest_1 string location=/cashlog
log_archive_dest_10 string

SQL> col oracle_username for a15
SQL> select object_id,session_id,s.serial#,oracle_username,os_user_name,lo.process,locked_mode,l.type from
2 v$locked_object lo,v$session s,v$lock l
3 where lo.session_id=s.sid
4 and lo.locked_mode=l.lmode
5 and l.sid=s.sid;

OBJECT_ID SESSION_ID SERIAL# ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE TY
---------- ---------- ---------- --------------- ------------------------------ ------------ ----------- --
18 71 23845 CASH oracle 2899976 3 TM

SQL> /

SQL> select sid,event,wait_time,state from v$session_wait where sid=71;

SID EVENT WAIT_TIME STATE
---------- ---------------------------------------------------------------- ---------- -------------------
71 log buffer space 0 WAITING

SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 1024000

SQL> select issys_modifiable from v$parameter where name='log_buffer';

ISSYS_MOD
---------
FALSE

SQL> select sid,event,wait_time,state from v$session_wait where sid=71;

SID EVENT WAIT_TIME STATE
---------- ---------------------------------------------------------------- ---------- -------------------
71 log buffer space 16 WAITED KNOWN TIME

state解释
0 - WAITING (the session is currently waiting)
-2 - WAITED UNKNOWN TIME (duration of last wait is unknown)
-1 - WAITED SHORT TIME (last wait <1/100th of a second)
>0 - WAITED KNOWN TIME (WAIT_TIME = duration of last wait)

WAIT_TIME
A nonzero value is the session's last wait time. A zero value means the session is currently waiting.

SQL> col event for a40
SQL> set linesize 120
SQL> select sid,event,wait_time,seconds_in_wait,state from v$session_wait where sid=71;

SID EVENT WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------------------------------------- ---------- --------------- -------------------
71 log buffer space 6 9 WAITED KNOWN TIME

检查alert日志
ngn_cash:/oracle/app/oracle/product/9.2.0 $tail -f /oracle/app/oracle/admin/bill/bdump/alert_bill2.log
Fri Dec 29 17:21:52 2006
ARC1: Evaluating archive log 6 thread 2 sequence 11610
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 6 thread 2 sequence 11610
Fri Dec 29 17:21:52 2006
Errors in file /oracle/app/oracle/admin/bill/bdump/bill2_arc1_2482244.trc:
ORA-16014: log 6 sequence# 11610 not archived, no available destinations
ORA-00312: online log 6 thread 2: '/dev/rredo27'
ORA-00312: online log 6 thread 2: '/dev/rredo28'
ORA-00312: online log 6 thread 2: '/dev/rredo29'
Fri Dec 29 17:21:53 2006
ARC0: Evaluating archive log 6 thread 2 sequence 11610
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive log 6 thread 2 sequence 11610
Fri Dec 29 17:21:53 2006
Errors in file /oracle/app/oracle/admin/bill/bdump/bill2_arc0_2736230.trc:
ORA-16014: log 6 sequence# 11610 not archived, no available destinations
ORA-00312: online log 6 thread 2: '/dev/rredo27'
ORA-00312: online log 6 thread 2: '/dev/rredo28'
ORA-00312: online log 6 thread 2: '/dev/rredo29'
Fri Dec 29 17:21:54 2006
ARC1: Evaluating archive log 6 thread 2 sequence 11610
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 6 thread 2 sequence 11610
Fri Dec 29 17:21:54 2006
Errors in file /oracle/app/oracle/admin/bill/bdump/bill2_arc1_2482244.trc:
ORA-16014: log 6 sequence# 11610 not archived, no available destinations
ORA-00312: online log 6 thread 2: '/dev/rredo27'
ORA-00312: online log 6 thread 2: '/dev/rredo28'
ORA-00312: online log 6 thread 2: '/dev/rredo29'
Fri Dec 29 17:21:55 2006
ARC0: Evaluating archive log 6 thread 2 sequence 11610
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive log 6 thread 2 sequence 11610
Fri Dec 29 17:21:55 2006
Errors in file /oracle/app/oracle/admin/bill/bdump/bill2_arc0_2736230.trc:
ORA-16014: log 6 sequence# 11610 not archived, no available destinations
ORA-00312: online log 6 thread 2: '/dev/rredo27'
ORA-00312: online log 6 thread 2: '/dev/rredo28'
ORA-00312: online log 6 thread 2: '/dev/rredo29'

ngn_cash:/oracle/app/oracle/product/9.2.0 $df -k
Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
/dev/hd4 524288 474264 10% 3100 2% /
/dev/hd2 3080192 1354868 57% 33902 5% /usr
/dev/hd9var 524288 228280 57% 979 1% /var
/dev/hd3 524288 211672 60% 118 1% /tmp
/dev/hd1 4194304 655076 85% 16880 2% /home
/proc - - - - - /proc
/dev/hd10opt 65536 47736 28% 429 3% /opt
/dev/lv00 8454144 3630088 58% 31484 2% /oracle
/dev/cashlognewlv 104464384 0 100% 165153 99% /cashlog
ngn_cash:/oracle/app/oracle/product/9.2.0 $cd /cashlog

ngn_cash:/cashlog $rm bill_2_113*
ngn_cash:/cashlog $rm bill_2_114*
ngn_cash:/cashlog $rm bill_2_115*
ngn_cash:/cashlog $ls

ngn_cash:/cashlog $tail -f /oracle/app/oracle/admin/bill/bdump/alert_bill2.log
Fri Dec 29 17:24:12 2006
ARC1: Evaluating archive log 6 thread 2 sequence 11610
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 6 thread 2 sequence 11610
Fri Dec 29 17:24:12 2006
Errors in file /oracle/app/oracle/admin/bill/bdump/bill2_arc1_2482244.trc:
ORA-16014: log 6 sequence# 11610 not archived, no available destinations
ORA-00312: online log 6 thread 2: '/dev/rredo27'
ORA-00312: online log 6 thread 2: '/dev/rredo28'
ORA-00312: online log 6 thread 2: '/dev/rredo29'
Fri Dec 29 17:24:13 2006
ARC0: Evaluating archive log 6 thread 2 sequence 11610
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive log 6 thread 2 sequence 11610
Fri Dec 29 17:24:13 2006
Errors in file /oracle/app/oracle/admin/bill/bdump/bill2_arc0_2736230.trc:
ORA-16014: log 6 sequence# 11610 not archived, no available destinations
ORA-00312: online log 6 thread 2: '/dev/rredo27'
ORA-00312: online log 6 thread 2: '/dev/rredo28'
ORA-00312: online log 6 thread 2: '/dev/rredo29'
Fri Dec 29 17:24:14 2006
ARC1: Evaluating archive log 6 thread 2 sequence 11610
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 6 thread 2 sequence 11610

ngn_cash:/cashlog $tail -f /oracle/app/oracle/admin/bill/bdump/alert_bill2.log
Fri Dec 29 17:26:49 2006
ARC1: Completed archiving log 4 thread 2 sequence 11611
ARC1: Evaluating archive log 5 thread 2 sequence 11612
ARC1: Unable to archive log 5 thread 2 sequence 11612
Log actively being archived by another process
ARC1: Evaluating archive log 5 thread 2 sequence 11612
ARC1: Unable to archive log 5 thread 2 sequence 11612
Log actively being archived by another process
Fri Dec 29 17:27:28 2006
ARC0: Completed archiving log 5 thread 2 sequence 11612
Fri Dec 29 17:28:34 2006
Thread 2 advanced to log sequence 11614
Current log# 4 seq# 11614 mem# 0: /dev/rredo21
Current log# 4 seq# 11614 mem# 1: /dev/rredo22
Current log# 4 seq# 11614 mem# 2: /dev/rredo23
Fri Dec 29 17:28:34 2006
ARC0: Evaluating archive log 6 thread 2 sequence 11613
ARC0: Beginning to archive log 6 thread 2 sequence 11613
Creating archive destination LOG_ARCHIVE_DEST_1: '/cashlog/bill_2_11613.arc'
Fri Dec 29 17:28:51 2006
ARC1: Evaluating archive log 6 thread 2 sequence 11613
ARC1: Unable to archive log 6 thread 2 sequence 11613
Log actively being archived by another process
^C