重庆思庄Oracle、Redhat认证学习论坛
标题: ORACLE11G 奇怪的全闪存存储RMAN恢复 IO为零 [打印本页]
作者: 郑全 时间: 2021-5-19 20:01
标题: ORACLE11G 奇怪的全闪存存储RMAN恢复 IO为零
现象：
从单机恢复到11.2.0.4 RAC，超过2通道后，IO为零，
等待事件为：
1* select event from v$session where wait_class<>'Idle'
EVENT
----------------------------------------------------------------
SQL*Net message to client
ASM file metadata operation
ASM file metadata operation
ASM file metadata operation
ASM file metadata operation
ASM file metadata operation
ASM file metadata operation
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.00 0.00 9.00 0.00 210.00 46.67 0.01 0.72 0.00 0.72 0.06 0.05
dm-0 0.00 0.00 0.00 0.50 0.00 8.00 32.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 2.00 1.50 1.00 2.50 2.00 0.00 0.43 0.50 0.33 0.43 0.15
sdc 0.00 0.00 2.00 1.50 1.00 2.50 2.00 0.00 0.57 0.50 0.67 0.57 0.20
sdd 0.00 0.00 2.00 1.50 1.00 2.50 2.00 0.00 0.43 0.50 0.33 0.43 0.15
sde 0.00 0.00 2.00 1.50 1.00 2.50 2.00 0.00 0.43 0.25 0.67 0.43 0.15
sdf 0.00 0.00 2.00 1.50 1.00 2.50 2.00 0.00 0.71 0.50 1.00 0.71 0.25
sdg 0.00 0.00 1.50 7.00 6.00 56.00 14.59 0.01 0.76 0.67 0.79 0.29 0.25
sdh 0.00 0.00 1.50 9.50 6.00 506.00 93.09 0.01 0.77 0.00 0.89 0.36 0.40
sdi 0.00 0.00 1.50 6.50 6.00 26.00 8.00 0.01 0.94 0.67 1.00 0.25 0.20
sdj 0.00 0.00 0.00 0.50 0.00 2.00 8.00 0.00 1.00 0.00 1.00 1.00 0.05
sdk 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdl 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-8 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-9 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-10 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-12 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdm 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdn 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdo 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdp 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdq 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdr 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sds 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdt 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdu 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdv 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdw 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-13 0.00 0.00 0.00 8.50 0.00 202.00 47.53 0.01 0.76 0.00 0.76 0.06 0.05
dm-14 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
分析：
找到一个官方的一个BUG说明 1308282.1
High 'ksv master wait' And 'ASM File Metadata Operation' Waits In Non-Exadata 11g
Symptoms
High waits for 'ksv master wait' while doing an ASM file metadata operation were reported when a data migration utility was running.
This wait was also seen for a drop of a tablespace.
The AWR showed the top events were CPU (> 100%), with 'ASM file metadata operation' (7%).
Cause
Event 'KSV master wait' indicates the process on the RDBMS side is waiting for a reply from a process on the ASM side.
In 11g, the parameter cell_offload_processing is set to TRUE.
Although that is a parameter is not applicable for non-Exadata databases, it caused ASM to try to deliver smart-scan results.
The issue was reported in Bug 11800170 - ASM IN KSV WAIT AFTER APPLICATION OF 11.2.0.2 GRID PSU.
After applying the workaround for this issue (see Solution below), a drop of a tablespace that used to take 13 minutes took 4 seconds.
Solution
The following solutions are available for non-Exadata databases:
For the quickest solution, use the workaround. The workaround does not negatively impact non-Exadata databases.
This parameter is to be set on the database instance.
alter system set cell_offload_processing = false;
Upgrade to 12.1, when available. OR
Apply the 11.2.0.3 patch set OR
Apply one-off Patch 11800170, if available for your RDBMS and Grid Homes
Note: At the time this note was written (March 2011), neither 12.1 nor 11.2.0.3 were available.
解决：
按该解决办法：
alter system set cell_offload_processing=false;
之后，
SQL> select event from v$session where wait_class<>'Idle';
EVENT
----------------------------------------------------------------
SQL*Net message to client
RMAN backup & recovery I/O
RMAN backup & recovery I/O
RMAN backup & recovery I/O
RMAN backup & recovery I/O
RMAN backup & recovery I/O
RMAN backup & recovery I/O
io也起来了。
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.50 0.00 9.50 0.00 120.00 25.26 0.01 1.00 0.00 1.00 0.11 0.10
dm-0 0.00 0.00 0.00 0.50 0.00 8.00 32.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 2.00 1.50 1.00 2.50 2.00 0.00 0.43 0.25 0.67 0.43 0.15
sdc 0.00 0.00 2.00 1.50 1.00 2.50 2.00 0.01 2.29 0.50 4.67 2.29 0.80
sdd 0.00 0.00 2.00 1.50 1.00 2.50 2.00 0.00 0.57 0.25 1.00 0.57 0.20
sde 0.00 0.00 2.00 1.50 1.00 2.50 2.00 0.00 1.14 1.25 1.00 1.14 0.40
sdf 0.00 0.00 2.00 1.50 1.00 2.50 2.00 0.00 0.43 0.25 0.67 0.43 0.15
sdg 0.00 11.50 1.50 1561.00 6.00 200196.00 256.26 4.23 2.65 0.67 2.65 0.41 64.50
sdh 0.00 9.00 1.50 1606.50 6.00 202548.00 251.93 3.90 2.41 0.33 2.41 0.38 61.15
sdi 0.00 9.50 1.50 1606.50 6.00 201202.00 250.26 4.16 2.53 0.00 2.53 0.40 64.40
sdj 0.00 0.00 120.00 0.50 15360.00 2.00 254.97 0.40 3.19 3.20 0.00 0.48 5.75
sdk 0.00 0.00 115.50 0.00 14784.00 0.00 256.00 0.37 3.20 3.20 0.00 0.48 5.50
sdl 0.00 0.00 120.00 0.00 15360.00 0.00 256.00 0.40 3.35 3.35 0.00 0.47 5.70
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-8 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-9 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-10 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-11 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-12 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdm 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdn 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdo 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdp 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdq 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdr 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sds 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdt 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdu 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdv 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdw 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-13 0.00 0.00 0.00 9.50 0.00 112.00 23.58 0.01 1.05 0.00 1.05 0.11 0.10
dm-14 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |