首页 » ORACLE 9i-23ai » Troubleshooting RMAN backup datafile slow , ASM wait ‘buffer busy’ and ‘GCS lock esc’

Troubleshooting RMAN backup datafile slow , ASM wait ‘buffer busy’ and ‘GCS lock esc’

最近在做一批Oracle database datafile 从1个ASM diskgroup 迁移到另1个ASM diskgroup时,单个datafile一直无法完成或挂起或多出几倍的时间,正常时1个datafile 30g在150s, 而突然间变的600s都没有完成, 数据库版本oracle 11.2.0.4 。 问题时尝试查询v$asm_diskgroup[_stats]或asmcmd lsdg一样很慢或hang。 登录ASM实例查询会话在等待“buffer busy”和”GCS lock esc” 这里简单的记录一下。

问题现象

[oracle@weejar1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 14 08:28:25 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ANBOB1 (DBID=2886773813)

RMAN> backup as copy datafile 1170 format '+SSDDG';

Starting backup at 14-NOV-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3793 instance=ANBOB11 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=01170 name=+HDDDG/ANBOB1/datafile/anbob_02.24283.1148419163
^C
user interrupt received
Finished backup at 14-NOV-23
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03099: job cancelled at user request

SQL> select * from v$asm_diskgroup;

-- slow

select * from v$asm_diskgroup_stat;
-- hang too

Note:
在数据库实例的操作和查询挂起,该操作也可能是在同时在两个RMAN会话同时备份不同的文件提速,在ASM中可能会存在并发的创建文件操作,后中断另一RMAN会话。

数据库实例查询活动会话

SQL> @ase

                                                                                   last_call
USERNAME          SID EVENT                MACHINE    MODULE               STATUS         et       SEQ# SQL_ID          WAI_SECINW ROW_WAIT_OBJ# SQLTEXT                        BS          CH# OSUSER          HEX
---------- ---------- -------------------- ---------- -------------------- ------- --------- ---------- --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ----------
SYS              3793 KSV master wait      weejar1     backup full datafile ACTIVE       1480        250                 0:874                 -1                                :           oracle

NoTE:
数据库实例等待“KSV master wait” event ,事件’KSV master wait’表示RDBMS端的进程正在等待ASM端的进程的回复。有时还会有event ‘ASM Metadata File Operation’, 这里我们没有使用DBFS(有可能进程hang “D”状态进程), 另外在11.2.0.3前存在一个 Bug 11800170 , 当cell_offload_processing=true时及时不是EXADATA,也会驱动Smart-scan。 或11.2.0.4前还有Bug 13253549, 12c前bug 17768168 . 接下来看一下ASM 层的等待。

ASM实例活动会话

[grid@weejar1 ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 09:03:48 2023
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> @ase

                                                                                   last_call
USERNAME          SID EVENT                MACHINE    MODULE               STATUS         et       SEQ# SQL_ID          WAI_SECINW ROW_WAIT_OBJ# SQLTEXT                        BS          CH# OSUSER          HEX
---------- ---------- -------------------- ---------- -------------------- ------- --------- ---------- --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ----------
SYS              1892 wait for unread mess weejar1     oracle               ACTIVE          3       8848                 0:0                   -1                                :             0 oracle
SYS              1770 wait for unread mess weejar1     oracle               ACTIVE          3      52606                 0:0                   -1                                :             0 oracle
SYS              2868 buffer busy          weejar1     perl                 ACTIVE          7         45 acnhubu2pzw1k   0:1                   -1 /* ASMCMD */ select * from v$a :             0 grid      1059174
SYS              2197 buffer busy          weejar1     perl                 ACTIVE         21        108 b2vy0x47p53h3   0:1                   -1 /* ASMCMD */ SELECT * FROM v$a 1:1404        0 grid      143882c
SYS              2746 buffer busy          weejar1     python               ACTIVE         52        253 7hvpcv1hc3pxh   0:1                   -1                  SELECT a.name 1:1404        0 grid      100713e
SYS              2441 GCS lock esc         weejar1     oracle               ACTIVE       2241      39066                 0:1                   -1                                :             0 oracle
SYS              2502 buffer busy          weejar1     oracle               ACTIVE       2439      18851                 0:1                   -1                                1:1404        0 oracle
SYS              1404 buffer busy          weejar1     oracle               ACTIVE       2858       1152                 0:1                   -1                                :             0 oracle
SYS              1953 buffer busy          weejar1     oracle               ACTIVE       3885      60218                 0:1                   -1                                1:1404        0 oracle
SYS              2075 buffer busy          weejar1     oracle               ACTIVE       3985       9284                 0:1                   -1                                1:1404        0 oracle

10 rows selected.

SQL> @ase

                                                                                   last_call
USERNAME          SID EVENT                MACHINE    MODULE               STATUS         et       SEQ# SQL_ID          WAI_SECINW ROW_WAIT_OBJ# SQLTEXT                        BS          CH# OSUSER          HEX
---------- ---------- -------------------- ---------- -------------------- ------- --------- ---------- --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ----------
SYS              1892 wait for unread mess weejar1     oracle               ACTIVE          1       8876                 0:0                   -1                                :             0 oracle
SYS              1770 wait for unread mess weejar1     oracle               ACTIVE          1      52637                 0:0                   -1                                :             0 oracle
SYS              2868 buffer busy          weejar1     perl                 ACTIVE         15         75 acnhubu2pzw1k   0:1                   -1 /* ASMCMD */ select * from v$a 1:1404        0 grid      1059174
SYS              2197 buffer busy          weejar1     perl                 ACTIVE         29        140 b2vy0x47p53h3   0:1                   -1 /* ASMCMD */ SELECT * FROM v$a 1:1404        0 grid      143882c
SYS              2746 buffer busy          weejar1     python               ACTIVE         60        289 7hvpcv1hc3pxh   0:1                   -1                  SELECT a.name 1:1404        0 grid      100713e
SYS              2441 GCS lock esc         weejar1     oracle               ACTIVE       2249      39186                 0:1                   -1                                :             0 oracle
SYS              2502 buffer busy          weejar1     oracle               ACTIVE       2447      18903                 0:1                   -1                                1:1404        0 oracle
SYS              1404 buffer busy          weejar1     oracle               ACTIVE       2866       1290                 0:1                   -1                                :             0 oracle
SYS              1953 buffer busy          weejar1     oracle               ACTIVE       3893      60330                 0:1                   -1                                1:1404        0 oracle
SYS              2075 buffer busy          weejar1     oracle               ACTIVE       3993       9326                 0:1                   -1                                1:1404        0 oracle

10 rows selected

note:
在ASM实例中,我们可以看到阻塞事件,如“GCS lock cvt S/GCS lock esc/GCS lock open S/buffer busy”。这表明锁定在GCS层导致整个ASM操作的减速。但另一个节点并无ASM 操作。

进程调用

SQL> r
  1* select pid,spid from v$process where addr in(select paddr from v$session where sid=1953)

       PID SPID
---------- ------------------------
        32 223944

SQL> oradebug setorapid 32
Oracle pid: 32, Unix process pid: 223944, image: oracle@weejar1 (TNS V1-V3)
SQL> oradebug short_call;
ORA-00070: command short_call is not valid
SQL> oradebug short_stack;
ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163<-
kslwait()+141<-kfclAttachBuffer()+1934<-kfclEscalate()+2319<-kfcGet0()+11164<-kfcGet1Priv()+1526<-kfcGetPriv()+900<-kfdusGet()+362<-kffGetUsdFromSrd()+153<-kffIxAddXmap()+3213<-kffFdAddImap()+1839<-kffFdAddMap()+479<-kffFileResize()+6627<-kffFileCreate()+5544<-kffCreate()+868<-kfnsFileCreate()+1500<-kfnDispatch()+694<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245 
SQL> oradebug short_stack;
ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163<
-kfcWaitForBufferPriv()+1199<-kfcGetPin()+3938<-kfcGet0()+7000<-kfcGet1Priv()+1526<-kfcGetPriv()+900<-kfdusGet()+362<-kffGetUsdFromSrd()+153<-kffIxAddXmap()+3213<-kffFdAddImap()+1839<-kffFdAddMap()+479<-kffFileResize()+6627<-kffFileCreate()+5544<-kffCreate()+868<-kfnsFileCreate()+1500<-kfnDispatch()+694<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245 
SQL> oradebug short_stack;
ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163
<-kfcWaitForBufferPriv()+1199<-kfcGetPin()+3938<-kfcGet0()+7000<-kfcGet1Priv()+1526<-kfcGetPriv()+900<-kfdusGet()+362<-kffGetUsdFromSrd()+153<-kffIxAddXmap()+3213<-kffFdAddImap()+1839<-kffFdAddMap()+479<-kffFileResize()+6627<-kffFileCreate()+5544<-kffCreate()+868<-kfnsFileCreate()+1500<-kfnDispatch()+694<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245 
SQL> oradebug short_stack;
ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163
<-kfcWaitForBufferPriv()+1199<-kfcGetPin()+3938<-kfcGet0()+7000<-kfcGet1Priv()+1526<-kfcGetPriv()+900<-kfdusGet()+362<-kffGetUsdFromSrd()+153<-kffIxAddXmap()+3213<-kffFdAddImap()+1839<-kffFdAddMap()+479<-kffFileResize()+6627<-kffFileCreate()+5544<-kffCreate()+868<-kfnsFileCreate()+1500<-kfnDispatch()+694<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245 
SQL> oradebug short_stack;
ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163
<-kfcWaitForBufferPriv()+1199<-kfcGetPin()+3938<-kfcGet0()+7000<-kfcGet1Priv()+1526<-kfcGetPriv()+900<-kfdusGet()+362<-kffGetUsdFromSrd()+153<-kffIxAddXmap()+3213<-kffFdAddImap()+1839<-kffFdAddMap()+479<-kffFileResize()+6627<-kffFileCreate()+5544<-kffCreate()+868<-kfnsFileCreate()+1500<-kfnDispatch()+694<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245 
SQL> 

NOTE:

kfcWaitForBufferPriv()+1199<-kfcGetPin()+3938<-kfcGet0()+7000<-kfcGet1Priv()+1526<-kfcGetPriv()+900<-kfdusGet()+362<-kffGetUsdFromSrd 主要是kernel automatic storage management cache 层的调用。

定位方法ASM hanganaylze

connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 4
--Wait 1 min to give time to identify process state changes.
oradebug -g all hanganalyze 4
oradebug tracefile_name
oradebug close_trace
exit

HANGANALYZE TRACE FILE

*** 2023-11-14 09:25:43.315
===============================================================================
HANG ANALYSIS:
  instances (db_name.oracle_sid): +asm.+asm1, +asm.+asm3, +asm.+asm2, +asm.+asm4
  oradebug_node_dump_level: 4
  analysis initiated by oradebug
  os thread scheduling delay history: (sampling every 1.000000 secs)
    0.000000 secs at [ 09:25:42 ]
      NOTE: scheduling delay has not been sampled for 0.504839 secs    0.000000 secs from [ 09:25:38 - 09:25:43 ], 5 sec avg
    0.000000 secs from [ 09:24:43 - 09:25:43 ], 1 min avg
    0.000000 secs from [ 09:20:43 - 09:25:43 ], 5 min avg
  vktm time drift history
===============================================================================
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'GCS lock esc'<='buffer busy'
Chain 1 Signature Hash: 0x118af497
[b] Chain 2 Signature: 'GCS lock cvt S'<='buffer busy'
Chain 2 Signature Hash: 0x33376d50
[c] Chain 3 Signature: 'GCS lock open S'<='buffer busy'
Chain 3 Signature Hash: 0x55a838ff

===============================================================================
Non-intersecting chains:

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (+asm.+asm1)
os id: 335393
process id: 23, oracle@weejar1 (TNS V1-V3)
session id: 1404
session serial #: 26357
}
is waiting for 'buffer busy' with wait info:
{
p1: 'group#'=0x2
p2: 'obj#'=0x8
p3: 'block#'=0x0
time in wait: 0.166308 sec
timeout after: 1.833692 sec
wait id: 2429030
blocking: 0 sessions
current sql:
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163<-kfcWaitForBufferPriv()+1199<-kfcGetPin()+3938<-kfcGet0()+7000<-kfcGet1Priv()+1526<-kfcGetPriv()+900<-kfdusGet()+362<-kffGetUsdFromSrd()+153<-kffIxAddXmap()+3213<-kffFdAddImap()+1839<-kffFdAddMap()+479<-kffFileResize()+6627<-kffFileCreate()+5544<-kffCreate()+868<-kfnsFileCreate()+1500<-kfnDispatch()+694<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917 wait history: * time between current wait and wait #1: 0.000000 sec 1. event: 'ASM file metadata operation' time waited: 0.000006 sec (last interval) time waited: 66 min 17 sec (total) wait id: 2391893 p1: 'msgop'=0xb p2: 'locn'=0x1 * time between wait #1 and #2: 0.000000 sec 2. event: 'GCS lock esc X' time waited: 0.002127 sec wait id: 2429029 p1: 'group'=0x2 p2: 'obj#'=0x8 p3: 'block#'=0x0 * time between wait #2 and #3: 0.000000 sec 3. event: 'ASM file metadata operation' time waited: 0.000002 sec (last interval) time waited: 66 min 17 sec (total) wait id: 2391893 p1: 'msgop'=0xb p2: 'locn'=0x1 } and is blocked by => Oracle session identified by:
{
instance: 1 (+asm.+asm1)
os id: 342179
process id: 41, oracle@weejar1 (TNS V1-V3)
session id: 2502
session serial #: 21651
}
which is waiting for 'GCS lock esc' with wait info:
{
p1: 'group'=0x2
p2: 'obj#'=0x8
p3: 'block#'=0x0
time in wait: 0.168497 sec
timeout after: 0.831503 sec
wait id: 13860
blocking: 12 sessions
current sql:
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163<-kfcWaitForBufferPriv()+1199<-kfcGetPin()+3938<-kfcGet0()+7000<-kfcGet1Priv()+1526<-kfcGetPriv()+900<-kfdusGet()+362<-kffGetUsdFromSrd()+153<-kffIxDelXmap()+520<-kffFdDelImap()+783<-kffFdDelMap()+380<-kffFileDestroy()+3918<-kffDelete()+1922<-kfnsFileDelete()+1011<-kfnDispatch()+694<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o(
wait history:
* time between current wait and wait #1: 0.000000 sec
1. event: 'ASM file metadata operation'
time waited: 0.000012 sec (last interval)
time waited: 59 min 18 sec (total)
wait id: 595 p1: 'msgop'=0x10
p2: 'locn'=0x1
* time between wait #1 and #2: 0.000000 sec
2. event: 'buffer busy'
time waited: 0.028099 sec
wait id: 13859 p1: 'group#'=0x2
p2: 'obj#'=0x8
p3: 'block#'=0x0
* time between wait #2 and #3: 0.000000 sec
3. event: 'ASM file metadata operation'
time waited: 0.000221 sec (last interval)
time waited: 59 min 18 sec (total)
wait id: 595 p1: 'msgop'=0x10
p2: 'locn'=0x1
}

Chain 1 Signature: 'GCS lock esc'<='buffer busy'
Chain 1 Signature Hash: 0x118af497
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
Chain 2:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 3 (+asm.+asm3)
os id: 432454
process id: 35, oracle@zbdb03 (TNS V1-V3)
session id: 2136
session serial #: 63835
}
is waiting for 'buffer busy' with wait info:
{
p1: 'group#'=0x2
p2: 'obj#'=0x8
p3: 'block#'=0x0
time in wait: 0.155237 sec
heur. time in wait: 1.206931 sec
timeout after: 1.844763 sec
wait id: 300
blocking: 0 sessions
current sql: /* ASMCMD */ select * from v$asm_diskgroup_stat
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163<-kfcWaitForBufferPriv()+1199<-kfcGet0()+2223<-kfcGet1Priv()+1526<-kfcGetPriv()+900<-kfdusFetchPriv()+743<-kfgComputeGrpProp()+1445<-kfgGrpEntryCb()+677<-kfgTableCb()+1057<-kfgGrpTableCbInternal()+4227<-kfgGrpTableCb1()+56<-qerfxFetch()+3141<-opifch2()+2766<-kpoal8()+8432<-opiodr()+917<-ttcpip()+1255<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opim wait history: * time between current wait and wait #1: 0.000000 sec 1. event: 'buffer busy' time waited: 0.000095 sec wait id: 299 p1: 'group#'=0x2 p2: 'obj#'=0x8 p3: 'block#'=0x0 * time between wait #1 and #2: 0.000001 sec 2. event: 'buffer busy' time waited: 0.000671 sec wait id: 298 p1: 'group#'=0x2 p2: 'obj#'=0x8 p3: 'block#'=0x0 * time between wait #2 and #3: 0.000074 sec 3. event: 'buffer busy' time waited: 0.032701 sec wait id: 297 p1: 'group#'=0x2 p2: 'obj#'=0x8 p3: 'block#'=0x0 } and is blocked by => Oracle session identified by:
{
instance: 3 (+asm.+asm3)
os id: 107597
process id: 22, oracle@zbdb03 (TNS V1-V3)
session id: 1343
session serial #: 1
}
which is waiting for 'GCS lock cvt S' with wait info:
{
p1: 'group'=0x2
p2: 'obj#'=0x8
p3: 'block#'=0x0
time in wait: 0.155054 sec
timeout after: 1.094946 sec
wait id: 32034480
blocking: 9 sessions
current sql: select 'DATA' dum1, name DISK_NAME,
free_mb FREE_MB,
(free_mb/total_mb * 100) FREE_MB_PCT,
reads DISK_IO_READS,
writes DISK_IO_WRITES,
bytes_read DISK_READS,
bytes_written DISK_WRITES,
(total_mb - free_mb) USED_MB,
((total_mb - free_mb)/total_mb * 100) USED_MB_PCT
from v$asm_disk_stat
w
short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+178<-ksliwat()+2066<-kslwaitctx()+163<-kfcWaitForBufferPriv()+1199<-kfcGet0()+2223<-kfcGet1Priv()+1526<-kfcGetPriv()+900<-kfdusFetchPriv()+743<-kfdDskEntryCb()+826<-kfdTableCb()+243<-kfgTableCb()+1057<-kfdDskTableCbInternal()+233<-kfdDskTableCb1()+56<-qerfxFetch()+3141<-rwsfcd()+103<-qersoProcessULS()+223<-qersoFetch()+6131<-qerjoFetch()+954<-opifch2()+2766<-kpoal8()+8432<-opiodr()+917<-ttcpip()+1255<-opitsk()+
wait history:
* time between current wait and wait #1: 0.000763 sec
1. event: 'buffer busy'
time waited: 0.000454 sec
wait id: 32034479 p1: 'group#'=0x2
p2: 'obj#'=0x8
p3: 'block#'=0x0
* time between wait #1 and #2: 0.000059 sec
2. event: 'buffer busy'
time waited: 0.032470 sec
wait id: 32034478 p1: 'group#'=0x2
p2: 'obj#'=0x8
p3: 'block#'=0x0
* time between wait #2 and #3: 0.000546 sec
3. event: 'buffer busy'
time waited: 1.017051 sec
wait id: 32034477 p1: 'group#'=0x2
p2: 'obj#'=0x8
p3: 'block#'=0x0
}

Chain 2 Signature: 'GCS lock cvt S'<='buffer busy'
Chain 2 Signature Hash: 0x33376d50
-------------------------------------------------------------------------------

Note:
主要等待链条是GCS lock esc/ GCS lock cvt S /GCS lock open S <=’buffer busy’.  buffer busy是根。

KILL ASM堵塞进程

[grid@weejar1 ~]$ ps -ef|grep 223944
grid      76371  68279  0 09:17 pts/16   00:00:00 grep --color=auto 223944
grid     223944      1  0 Nov13 ?        00:01:30 oracle+ASM1_o003_ANBOB11 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

SQL> select 'kill -9 '||spid from v$process where addr in(select paddr from v$session where event='buffer busy' and type='USER');

'KILL-9'||SPID
--------------------------------
kill -9 238011
kill -9 245109
kill -9 251295
kill -9 257805
kill -9 262101
kill -9 258914
kill -9 335393
kill -9 253634
kill -9 250481

9 rows selected.

因为是非致命进程,可以尝试kill 处理后,恢复正常。

RMAN 其它一些定位方法如debug和10046 trace, call stack

$ rman target / debug trace=/tmp/rman_debug
RMAN> sql "alter session set tracefile_identifier=''rman_10046''";
RMAN> sql "alter session set events ''10046 trace name context forever,level 12''";

-- or --
RMAN>connect target sys/oracle123@rptpri2
RMAN> debug on ;

-- or --
SELECT s.sid, p.spid, s.client_info, s.module, s.program
 FROM v$process p, v$session s
 WHERE p.addr = s.paddr
 AND s.program like '%rman%' ;

SQL> oradebug setospid 
SQL> oradebug setmypid
SQL> oradebug short_stack


Others

有一案例RMAN Backup waiting on “ASM file metadata operation”是因为ASM 兼容性低于GI版本,修改后恢复性能。在理想的情况下,ASM兼容性必须设置为当前的GRID Infrastructure版本,而RDBMS兼容性可以设置为较低的兼容数据库。

ALTER DISKGROUP DATADG1 SET ATTRIBUTE 'compatible.asm' = '11.2.0.4.0';
打赏

,

对不起,这篇文章暂时关闭评论。