ARCH wait on ATTACH等待事件引起的切换归档hang住

最新推荐文章于 2022-08-24 16:12:13 发布

lk_db

最新推荐文章于 2022-08-24 16:12:13 发布

阅读量2.2k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/lk_db/article/details/51660902

版权

Oracle 专栏收录该内容

120 篇文章 5 订阅

订阅专栏

现象：
alter system switch logfile; 会hang住

解决步骤：
SQL> select event,count(*) from v$session group by event;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client 2
pmon timer 1
rdbms ipc message 13
smon timer 1
ARCH wait on ATTACH 3
enq: WL - contention 1
Streams AQ: qmn slave idle wait 1
SQL*Net message to client 1
Space Manager: slave idle wait 1
VKTM Logical Idle Wait 1
LNS wait on ATTACH 2
EVENT COUNT(*)
---------------------------------------------------------------- ----------
Streams AQ: qmn coordinator idle wait 1
Streams AQ: waiting for time management or cleanup tasks 1
DIAG idle wait 2
14 rows selected.

SQL> select group#, thread#, sequence#, archived, status from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 1 NO INACTIVE
2 1 14 NO CURRENT
3 1 13 YES INACTIVE
SQL> select name, thread#, sequence# from v$archived_log where sequence# between 13 and 14;
NAME
--------------------------------------------------------------------------------
THREAD# SEQUENCE#
---------- ----------
/u01/app/oracle/archivelog/1_13_914429606.dbf
1 13

SQL> select event,count(*) from v$session group by event;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client 3
pmon timer 1
rdbms ipc message 14
smon timer 1
ARCH wait on ATTACH 3
SGA: allocation forcing component growth 1
Streams AQ: qmn slave idle wait 1
SQL*Net message to client 1
Space Manager: slave idle wait 1
VKTM Logical Idle Wait 1
Streams AQ: qmn coordinator idle wait 1
EVENT COUNT(*)
---------------------------------------------------------------- ----------
Streams AQ: waiting for time management or cleanup tasks 1
DIAG idle wait 2
13 rows selected.

SQL> select sid, program from v$session where sid in (select sid from v$lock where sid > 8);
SID PROGRAM
---------- ------------------------------------------------
10 oracle@db (DBW0)
11 oracle@db (LGWR)
12 oracle@db (CKPT)
13 oracle@db (SMON)
15 oracle@db (MMON)
18 rman@db (TNS V1-V3)
19 rman@db (TNS V1-V3)
24 oracle@db (ARC0)
27 oracle@db (ARC2)
33 oracle@db (ARC3)
39 sqlplus@db (TNS V1-V3)
11 rows selected.

SQL> select event from v$session_wait where sid = 24;
EVENT
----------------------------------------------------------------
ARCH wait on ATTACH
SQL> select event from v$session_wait where sid = 27;
EVENT
----------------------------------------------------------------
ARCH wait on ATTACH
SQL> select event from v$session_wait where sid = 33;
EVENT
----------------------------------------------------------------
ARCH wait on ATTACH
SQL> select spid from v$process where addr = (select paddr from v$session where sid=24);
SPID
------------------------
3834
SQL> select spid from v$process where addr = (select paddr from v$session where sid=27);
SPID
------------------------
3836
SQL> select spid from v$process where addr = (select paddr from v$session where sid=33);
SPID
------------------------
3838

SQL> !kill -9 3834
SQL> !kill -9 3836
SQL> !kill -9 3838
SQL> select group#, thread#, sequence#, archived, status from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 15 NO CURRENT
2 1 14 YES INACTIVE
3 1 13 YES INACTIVE

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>