新浦京娱乐手机平台:守得云开见月明,ASM磁盘

2019-04-27 16:03栏目:新能源

原标题:守得云开见月明:一次ASM存储高可用故障解决过程分析

今天他们要测试IBM的那个SVC存储同步的东西,然后需要把服务器上的盘都踢出去后再加进来,不过这样的话磁盘名称就会变了。因此需要把ASM中的磁盘名称都换了,其实过程也很简单:

背景:

新浦京娱乐手机平台 1

1、修改asm实例的asm磁盘默认查找路径参数asm_diskstring,使用如下命令:

阅读新闻

作者 | 姜劲松,云和恩墨专家支持部Oracle技术专家,Oracle OCP,MySQL OCP,RHCE等认证专家。长期服务移动运营商行业客户,精通 oracle 性能优化,故障诊断,特殊恢复领域。23年IT从业经验、资深数据库及系统软硬件集成专家。

alter system set asm_diskstring='/dev/rhdisk*';

11G ASM磁盘组不能自动MOUNT处理

百万级用户规模营销账务系统研发及实施运维经验,主持过11省千万级电力营销业务系统运维主管工作;设计实施过10多个阿里云平台新能源SAAS系统。历任开发工程师、项目经理、技术经理、项目总监、运维主管、云平台架构师等职位。

2、关闭整个Cluster,等待他们踢盘加盘后再修改如下属性,我的是RAC环境所以一下操作要在所有节点上执行

[日期:2016-01-12]

前言

修改磁盘用户及属组:

来源:Linux社区

Oracle ASM 全称为Automated Storage Management,即自动存储管理,它是自 Oracle10g 这个版本 Oracle 推出的新功能。这是 Oracle 提供的一个卷管理器,用于替代操作操作系统所提供的 LVM,它不仅支持单实例配置,也支持RAC这样的多实例配置。

[rac11g2@root]# chown grid:asmadmin /dev/rhdisk[2-4]

作者:Linux

给 Oracle 数据库管理员带来极大的方便,ASM 可以自动管理磁盘组,并提供数据冗余和优化。 ASM提供了丰富的管理和容灾手段,通过适当的配置,可以实现高效的数据库层面的存储容灾功能。

修改磁盘属性为660:

[字体:大 中 小]

本案例通过某客户项目现场1次ASM存储容灾无法实现预期目标的问题分析解决过程,和大家共同探讨对于非预期问题的解决之道。

[rac11g2@root]# chmod 660 /dev/rhdisk[2-4]

今天启动了一下ASM,发现只挂载了一个SYS_DG磁盘组(用于存放OCR和VOTING DISK信息),另外两个磁盘组DATA_DG,DG_FRA都没有mount。

01问题简述

修改磁盘共享属性:

环境:OS=RHEL 6 DB=Oracle11GR2

背景说明:

[rac11g2@root]# lsattr -El hdisk2|grep reserve_policy
reserve_policy  no_reserve                                          Reserve Policy                          True

[grid@myrac1 ~]$ crs_stat -t

1、Oracle12.2RAC ASM Normal Redendancy 模式,数据库存储采用双存储冗余架构,规避单存储故障导致服务中断及数据丢失;

2、 ASM DiskGroup 设计2个 Failgroup(FG),1个FG磁盘全部存储在1#存储;1个FG全部磁盘存储在2#存储中;

style="font-size: 16px;">3、期望任意存储故障或断电,数据库实例不受影响,数据不丢失,故障存储上线后数据自动同步。

[rac11g2@root]# chdev -l hdisk2 -a reserve_policy=no_reserve
[rac11g2@root]# chdev -l hdisk3 -a reserve_policy=no_reserve
[rac11g2@root]# chdev -l hdisk4 -a reserve_policy=no_reserve

Name Type Target State Host

在实际高可用测试中,拔掉1个存储,发现如下现象:

3、现在就可以启动Cluster了


style="font-size: 16px;">1.CRS集群不受影响,ocr/votedisk自动Failover;

2.DB Controlfile/Redolog发生I/O错误,导致LWGR/CKPT等核心进程长时间阻塞后,Oracle主动重启DB实例(1个或2个实例)后,数据库恢复正常;

style="font-size: 16px;">3.数据库数据正常,故障存储Online后自动同步正常。

[rac11g1@root]# crsctl start cluster -all

ora.DATA_DG.dg ora....up.type OFFLINE OFFLINE

02测试过程

注:我曾经因为忘了修改磁盘属性为660,结果导致Database起不来,在Alert日志中出现了ORA-00600的错误,吓我一跳,不过从日志中比较容易看出来是权限的问题,调整磁盘属性后再重启就可以了:

ora.DG_FRA.dg ora....up.type OFFLINE OFFLINE

1) 第一类测试

Sweep [inc][393409]新浦京娱乐手机平台,: completed
Sweep [inc2][393409]: completed
NOTE: Loaded library: System 
ORA-15025: could not open disk "/dev/rhdisk4"
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 11
SUCCESS: diskgroup DATA was mounted
Errors in file /soft/Oracle/diag/rdbms/nint/nint1/trace/nint1_ckpt_19136654.trc  (incident=409793):
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /soft/oracle/diag/rdbms/nint/nint1/incident/incdir_409793/nint1_ckpt_19136654_i409793.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
NOTE: dependency between database nint and diskgroup resource ora.DATA.dg is established
ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 19136654 
Dumping diagnostic data in directory=[cdmp_20120302172201], requested by (instance=1, osid=19136654 (CKPT)), summary=[incident=409793].
Fri Mar 02 17:22:01 2012
PMON (ospid: 14156014): terminating the instance due to error 469
System state dump requested by (instance=1, osid=14156014 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /soft/oracle/diag/rdbms/nint/nint1/trace/nint1_diag_21168306.trc
Fri Mar 02 17:22:02 2012
ORA-1092 : opitsk aborting process
Fri Mar 02 17:22:02 2012
License high water mark = 1
Instance terminated by PMON, pid = 14156014
USER (ospid: 15335672): terminating the instance
Instance terminated by USER, pid = 15335672

ora....ER.lsnr ora....er.type ONLINE ONLINE myrac1

1、存储完成拔线:16:56:05

2、实例16:57:37-16:57:39 挂掉

【摘录】

ora.SYS_DG.dg ora....up.type ONLINE ONLINE myrac1

ASM日志:

First, you can try to check the OS drive ownership , permission and reserve_policy attribute on all nodes. Then restart the ASM instance.
  1)Make sure that the hdisk# is owned by the OS user who installed the ASM Oracle Home ... and that the disk is mounted correctly (with the correct owner) 
  2)Make sure that the permissions are set correctly at the disk level ... 660 is normal ... but if there are problems use 777 as a test 
  ls -l /dev/rhdisk3 output:
  For 10gR2/11gR1 like:  crw-rw----  oracle:oinstall /dev/rhdisk3 
  For 11gR2 like:        crw-rw----  grid:asmadmin /dev/rhdisk3

ora.asm ora.asm.type ONLINE ONLINE myrac1

2018-08-01T16:57:41.712885 08:00

NOTE: ASM client style="font-size: 16px;">node11:node1:node1-rac disconnected unexpectedly

  How to change the drive ownership and permission ?
  For 10gR2/11gR1:
    # chown -R oracle:oinstall /dev/rhdisk[3-10]
    # chmod -R 660 /dev/rhdisk[3-10]
  For 11gR2:
    # chown -R grid:asmadmin /dev/rhdisk[3-10]
    # chmod -R 660 /dev/rhdisk[3-10]

ora.cssd ora.cssd.type ONLINE ONLINE myrac1

DB:

  3)Make sure that the reserve_policy attribute of the needed hdisk# is no_reserve or no on all nodes.
    chdev -l hdisk# -a reserve_policy=no_reserve

ora.diskmon ora....on.type ONLINE ONLINE myrac1

2018-08-01T16:57:45.214182 08:00

Instance terminated by USER, pid = 10158

2018-08-01T16:57:36.704927 08:00

Errors in file /oracle/diag/rdbms/node1/node11/trace/node11_ckpt_10158.trc:

ORA-00206: error in writing (block 3, # blocks 1) of control file

ORA-00202: control file: ' DG_DATA_FAB/NODE1/CONTROLFILE/current.265.981318275'

ORA-15081: failed to submit an I/O operation to a disk

ORA-15081: failed to submit an I/O operation to a disk

ORA-15064: communication failure with ASM instance

2018-08-01T16:57:36.705340 08:00

Errors in file /oracle/diag/rdbms/node1/node11/trace/node11_ckpt_10158.trc:

ORA-00221: error on write to control file

ORA-00206: error in writing (block 3, # blocks 1) of control file

ORA-00202: control file: ' DG_DATA_FAB/NODE1/CONTROLFILE/current.265.981318275'

ORA-15081: failed to submit an I/O operation to a disk

ORA-15081: failed to submit an I/O operation to a disk

ORA-15064: communication failure with ASM instance

If it also fail by the first step, you may try to set the Oracle ASM parameter ASM_DISKSTRING to /dev/* or /dev/rhdisk*. The Step is:
1)Backup the ASM instance pfile(Parameter File) or spfile (Server Parameter File).
  Most in the $ORACLE_HOME/dbs. pfile name like is init ASM1.ora, you can use cp command to backup it .and vi the content. 
  You to create spfile to pfile for backup,if use spfile. 
2)set ASM_DISKSTRING parameter
  use pfile ENV:
    Add or Edit "ASM_DISKSTRING" line to *.ASM_DISKSTRING='/dev/rhdisk*' in pfile. Startup the ASM instance using the pfile.
  
  use spfile ENV:
    $ ORACLE_SID= ASM1;export ORACLE_SID
    
    $ sqlplus "/ as sysdba"
    or
    $ sqlplus "/ as sysasm"
    
    SQL> startup
    SQL> alter system set asm_diskstring='/dev/rhdisk*';
    SQL> select group_number,disk_number,path from v$asm_disk; 
        --You can get some disk info and the most disk's group_number  is not 0.

ora.hjj.db ora....se.type OFFLINE OFFLINE

Oracle CKPT 进程因为控制文件 IO 错误阻塞,导致主动重启 instance,每次测试都在超时70s之后开始Terminate instance。

If ASM_DISKSTRING is NOT SET ... then the following default is used

查看参数disk_groups发现没有值,按理来说应该是这三个磁盘组。

怀疑是ASM实例offline disk时间过慢,希望调高CKPT阻塞时间阀值解决问题,但是没有找到对应的参数。

    Default ASM_DISKSTRING per OS

SQL> show parameter disk

既然是controlfile存在此问题,是不是因为DATA磁盘比较多,导致offline检测时间长呢?

    Operating System Default            Search String
    =======================================
    Solaris (32/64 bit)                        /dev/rdsk/*
    Windows NT/XP                          \.orcldisk* 
    Linux (32/64 bit)                          /dev/raw/* 

NAME TYPE VALUE

尝试将controlfile转移到磁盘较少的REDO DG,仍然在controfile这里报错:

    LINUX (ASMLIB)                         ORCL:*
    LINUX (ASMLIB)                         /dev/oracleasm/disks/* ( as a workaround )


systemstatedump文件:

----- Beginning of Customized Incident Dump(s) -----

Process CKPT (ospid: 4693) is waiting for event 'control file sequential read'.

Process O009 (ospid: 5080) is the blocker of the wait chain.

===[ Wait Chain ]===

CKPT (ospid: 4693) waits for event 'control file sequential read'.

LGWR (ospid: 4691) waits for event 'KSV master wait'.

O009 (ospid: 5080) waits for event 'ASM file metadata operation'.

node1_lgwr_4691.trc

----- END DDE Actions Dump (total 0 csec) -----

ORA-15080: synchronous I/O operation failed to write block 1031 of disk 4 in disk group DG_REDO_MOD

ORA-27063: number of bytes read/written is incorrect

HPUX-ia64 Error: 11: Resource temporarily unavailable

Additional information: 4294967295

Additional information: 1024

NOTE: process _lgwr_node1 (4691) initiating offline of disk 4.4042263303 (DG_REDO_MOD_0004) with mask 0x7e in group 3 (DG_REDO_MO

D) with client assisting

    HPUX                                       /dev/rdsk/* 
    HP-UX(Tru 64)                            /dev/rdisk/*
    AIX                                            /dev/*


2) 第二类测试

新浦京娱乐手机平台 2

asm_diskgroups string

尝试对 controlfile 进行 multiplex:

asm_diskstring string

1、每个存储分配1个10GB LUN给服务器;

2、基于每个LUN创建1个DG,controlfile multiplex到这2个DG中。

进行修改

重新开始模拟1个存储故障测试,发现仍然会发生控制文件无法读写,重启实例!

[grid@myrac1 ~]$ sqlplus / as sysasm

在Oracle文档发现只能采用ASM FG来实现高可用,因为任何控制文件都需要在线,否则将直接导致实例中止!

style="font-size: 16px;">

Multiplex Control Files on Different Disks

Every Oracle Database should have at least two control files, each stored on a different physical disk. If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired, the damaged control file can be restored using the intact copy of the control file from the other disk and the instance can be restarted. In this case, no media recovery is required.

The behavior of multiplexed control files is this:

The database writes to all filenames listed for the initialization parameter CONTROL_FILES in the database initialization parameter file.

The database reads only the first file listed in the CONTROL_FILES parameter during database operation.

If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.

Note:

Oracle strongly recommends that your database has a minimum of two control files and that they are located on separate physical disks.

SQL> startup nomount

所以这种 multiplex 方法对 controlfile 的高可用无效!

ASM instance started

3) 第三类测试

Total System Global Area 284565504 bytes

将controlfile存储在一个RPT存储中,避免因为controlfile同步导致的阻塞。

Fixed Size 1336036 bytes

发现有时测试能够成功,但是有时会在**REDO LOG**读写时报错导致DB重启!

Variable Size 258063644 bytes

4) 第四类测试

ASM Cache 25165824 bytes

创建2个独立的DG,指向2个不同存储,REDO GROUP的2个member multiplex到2个DG中。

SQL> alter system set asm_diskgroups=sys_dg,data_dg,dg_fra scope=spfile;

测试failover成功,ASM实例会将故障DG dismount,数据库完全不受影响!

alter system set asm_diskgroups=sys_dg,data_dg,dg_fra scope=spfile

根据以上的测试过程,发现如下现象:

*

1、 ASM Failgroup对数据库文件处理完全没有问题,可以实现Failover

2、 ControlFile/RedoLogfile在Normal DG做offline时,异常长时间阻塞并主动重启DB实例,重启后运行正常,数据完整性不受影响!

ERROR at line 1:

反复多次测试,问题均随机出现,因此高度怀疑为Oracle BUG,在MOS上发现1个类似『 链接:Bug 23179662 - ASM B-slave Process Blocking Fatal background Process like LGWR producing ORA-29771 (文档 ID 23179662.8)』,但是MOS说明 20180417PSU 已经 fixed 此 BUG, Wordaround 行为就是重启实例。

ORA-32000: write to SPFILE requested but SPFILE is not modifiable

在连续1周无法解决问题的情况,采用了如下临时的解决方案:

SQL> !oerr ora 32000

style="font-size: 16px;">(1)controlfile迁移到第三个存储;

(2)Redo通过Oracle multiplex功能将2个member存储到不同存储。

32000, 00000, "write to SPFILE requested but SPFILE is not modifiable"

但是这样一来,控制文件又造成了单点故障风险,难道这个问题没有办法解决吗?

// *Cause: An ALTER SYSTEM command or an internal self-tuning mechanism

既然理论和实战存在差异,那肯定是有原因的,我开始了新的探索及分析,不放过任何一个可疑点:

// requested a write to the SPFILE but the SPFILE was not modifiable.

03再次梳理

// *Action: Perform an in-memory parameter update only.

重新将controlfile及redologs迁移到Normal Diskgroup中,测试中发现数据库实例存在正常、1节点重启、2节点重启等多种情况,故障现象不规律!

提示只能在memory中进行修改,尝试修改

我反复测试,细致梳理了关键事件的时间点信息,示例如下:

SQL> alter system set asm_diskgroups=sys_dg,data_dg,dg_fra scope=memory;

ALERT LOG :

System altered.


Filename=alert_p4moddb1.log

2018-08-16T14:56:00.272280 08:00

WARNING: Read Failed. group:2 disk:4 AU:1053 offset:2605056 size:16384

style="font-size: 16px;">path:/dev/rdisk/MES1_p4_moddb_redo02

incarnation:0xf7e12348 synchronous result:'I/O error'

subsys:System krq:0x9ffffffffd1c0608 bufp:0x9ffffffffd007000 osderr1:0x69c0 osderr2:0x0

IO elapsed time: 0 usec Time waited on I/O: 0 usec

WARNING: failed to read mirror side 1 of virtual extent 7 logical extent 0 of file 260 in group [2.3551108175] from disk MES1_REDO02 allocation unit 1053 reason error; if possible, will try another mirror side

NOTE: successfully read mirror side 2 of virtual extent 7 logical extent 1 of file 260 in group [2.3551108175] from disk RPT_REDO01 allocation unit 1052 -->检测到I/O error,但是能成功读取mirror数据

……

2018-08-16T14:56:13.489201 08:00. -->大量IO操作错误

Errors in file /oracle/diag/rdbms/p4moddb/p4moddb1/trace/p4moddb1_lgwr_13680.trc:

ORA-15080: synchronous I/O operation failed to write block 1383 of disk 4 in disk group DG_REDO_MOD

ORA-27063: number of bytes read/written is incorrect

HPUX-ia64 Error: 11: Resource temporarily unavailable

Additional information: 4294967295

Additional information: 1024

WARNING: failed to write mirror side 1 of virtual extent 0 logical extent 0 of file 257 in group 2 on disk 4 allocation unit 277

2018-08-16T14:56:31.050369 08:00

……

ERROR: cannot read disk header of disk MES1_REDO02 (4:4158726984)

2018-08-16T14:56:34.418045 08:00

NOTE: ospid 13682 initiating cluster wide offline of disk 5 in group 2

2018-08-16T14:56:34.418576 08:00

NOTE: process _rms0_p4moddb1 (13666) initiating offline of disk 4.4158726984 (MES1_REDO02) with mask 0x7e in group 2 (DG_REDO_MOD) with client assisting

2018-08-16T14:56:34.446660 08:00

……

NOTE: disk 4 (MES1_REDO02) in group 2 (DG_REDO_MOD) is offline for reads

2018-08-16T14:56:49.513068 08:00

CKPT (ospid: 13682) waits for event 'control file parallel write' for 78 secs.

Errors in file /oracle/diag/rdbms/p4moddb/p4moddb1/trace/p4moddb1_lmhb_13668.trc (incident=3529892):

ORA-29771: process MARK (OSID 13751) blocks CKPT (OSID 13682) for more than 70 seconds

Incident details in: /oracle/diag/rdbms/p4moddb/p4moddb1/incident/incdir_3529892/p4moddb1_lmhb_13668_i3529892.trc

2018-08-16T14:56:51.744467 08:00

MARK (ospid: 13751) is blocking CKPT (ospid: 13682) in a wait

LMHB (ospid: 13668) kills MARK (ospid: 13751). style="font-size: 16px;">-->LMHB检测到核心后台进程阻塞70s以上,并开始尝试kill

Please check LMHB trace file for more detail.

2018-08-16T14:56:54.755778 08:00

ERROR: failed to mark AU as stale [4294967279]

WARNING: failed to mark stale some of AUs corresponding to the mirrors of virtual extent 0 of file 260 in group 2

WARNING: group 2 file 260 vxn 0 block 3 write I/O failed

2018-08-16T14:56:54.756850 08:00

Errors in file /oracle/diag/rdbms/p4moddb/p4moddb1/trace/p4moddb1_ckpt_13682.trc:

ORA-00206: error in writing (block 3, # blocks 1) of control file

ORA-00202: control file: ' DG_REDO_MOD/p4moddb.ctl01'

ORA-15081: failed to submit an I/O operation to a disk

ORA-15081: style="font-size: 16px;">failed to submit an I/O operation to a disk

2018-08-16T14:56:54.757193 08:00

Errors in file /oracle/diag/rdbms/p4moddb/p4moddb1/trace/p4moddb1_ckpt_13682.trc:

ORA-00221: error on write to control file

ORA-00206: error in writing (block 3, # blocks 1) of control file

ORA-00202: control file: ' DG_REDO_MOD/p4moddb.ctl01'

ORA-15081: failed to submit an I/O operation to a disk

ORA-15081: failed to submit an I/O operation to a disk

Errors in file /oracle/diag/rdbms/p4moddb/p4moddb1/trace/p4moddb1_ckpt_13682.trc (incident=3529948):

ORA-221 [] [] [] [] [] [] [] [] [] [] [] []

Incident details in: /oracle/diag/rdbms/p4moddb/p4moddb1/incident/incdir_3529948/p4moddb1_ckpt_13682_i3529948.trc

2018-08-16T14:56:56.478631 08:00

USER (ospid: 13682): style="font-size: 16px;">terminating the instance due to error 221. --à因为控制文件IO错误导致实例被重启!

2018-08-16T14:56:56.502831 08:00

System state dump requested by (instance=1, osid=3891780 (CKPT)), summary=[abnormal instance termination].

System State dumped to trace file /oracle/diag/rdbms/p4moddb/p4moddb1/trace/p4moddb1_diag_13633_20180816145656.trc

是可以修改,但是没办法永久保存,要修改的是spfile中的disk_groups参数,让ASM实例每次启动都能加载所有的diskgroup。

alert_ ASM1.log :

那问题就是如何修改spfile中的asm_diskgroups参数

2018-08-16T14:56:04.957199 08:00 -->差不多和DB同时检测到IO错误

WARNING: Write Failed. group:1 disk:1 AU:1 offset:4190208 size:4096

style="font-size: 16px;">path:/dev/rdisk/MES1_p4_moddb_ocrvote

incarnation:0xf0996d00 asynchronous result:'I/O error'

subsys:System krq:0x9ffffffffd285710 bufp:0x9ffffffffd299000 osderr1:0x69b7 osderr2:0x0

IO elapsed time: 0 usec Time waited on I/O: 0 usec

……

2018-08-16T14:56:34.400892 08:00

WARNING: Disk 4 (MES1_REDO02) in group 2 mode 0x7f is now being offlined

……

ORA-27072: File I/O error

HPUX-ia64 Error: 11: Resource temporarily unavailable

Additional information: 4

Additional information: 8188

Additional information: 4294967295

GMON checking disk modes for group 2 at 2460 for pid 49, osid 13802

2018-08-16T14:56:35.056049 08:00

NOTE: checking PST for grp 2 done.

2018-08-16T14:56:35.056382 08:00

Errors in file /grid/crs_base/diag/asm/ asm/ ASM1/trace/ ASM1_gmon_18741.trc:

ORA-27072: File I/O error

HPUX-ia64 Error: 11: Resource temporarily unavailable

Additional information: 4

Additional information: 8188

Additional information: 4294967295

ORA-27072: File I/O error

HPUX-ia64 Error: 11: Resource temporarily unavailable

Additional information: 4

Additional information: 8188

Additional information: 4294967295

ORA-27072: File I/O error

HPUX-ia64 Error: 11: Resource temporarily unavailable

Additional information: 4

Additional information: 8188

Additional information: 4294967295

2018-08-16T14:56:35.056592 08:00

GMON checking disk modes for group 2 at 2461 for pid 19, osid 18728

NOTE: initiating PST update: grp 2 (DG_REDO_MOD), dsk = 4/0xf0996e2a, mask = 0x6a, op = clear mandatory

GMON checking disk modes for group 1 at 2462 for pid 57, osid 24043

2018-08-16T14:56:35.057277 08:00

NOTE: checking PST for grp 2 done.

NOTE: initiating PST update: grp 2 (DG_REDO_MOD), dsk = 4/0xf0996e2a, mask = 0x6a, op = clear mandatory

2018-08-16T14:56:35.059431 08:00

NOTE: checking PST for grp 1 done.

2018-08-16T14:56:35.059697 08:00

NOTE: sending set offline flag message (3413962691) to 1 disk(s) in group 1

2018-08-16T14:56:35.059796 08:00

GMON checking disk modes for group 4 at 2463 for pid 59, osid 24045

2018-08-16T14:56:36.205496 08:00

NOTE: Attempting voting file refresh on diskgroup DG_CRS_MOD

WARNING: Read Failed. group:1 disk:1 AU:0 offset:0 size:4096

style="font-size: 16px;">path:/dev/rdisk/MES1_p4_moddb_ocrvote

incarnation:0xf0996d00 asynchronous result:'I/O error'

subsys:System krq:0x9ffffffffd4a4068 bufp:0x9ffffffffcf91000 osderr1:0x534b4950 osderr2:0x0

IO elapsed time: 0 usec Time waited on I/O: 0 usec

NOTE: Refresh completed on diskgroup DG_CRS_MOD. Found 3 voting file(s).

NOTE: Voting file relocation is required in diskgroup DG_CRS_MOD

NOTE: Attempting voting file relocation on diskgroup DG_CRS_MOD

WARNING: Read Failed. group:1 disk:1 AU:0 offset:0 size:4096

style="font-size: 16px;">path:/dev/rdisk/MES1_p4_moddb_ocrvote

incarnation:0xf0996d00 asynchronous result:'I/O error'

subsys:System krq:0x9ffffffffd4a4068 bufp:0x9ffffffffcf84000 osderr1:0x534b4950 osderr2:0x0

IO elapsed time: 0 usec Time waited on I/O: 0 usec

NOTE: Successful voting file relocation on diskgroup DG_CRS_MOD

2018-08-16T14:56:52.061661 08:00

System state dump requested by (instance=1, osid=3906536), summary=[SYSTEMSTATE_GLOBAL: global system state dump request (kjdgdss_g)].

System State dumped to trace file /grid/crs_base/diag/asm/ asm/ ASM1/trace/ ASM1_diag_18708_20180816145652.trc

2018-08-16T14:56:55.250804 08:00

WARNING: Read Failed. group:2 disk:4 AU:0 offset:0 size:4096

2018-08-16T14:56:55.250889 08:00

WARNING: Read Failed. group:2 disk:4 AU:7 offset:245760 size:4096

2018-08-16T14:56:55.251279 08:00

path:/dev/rdisk/MES1_p4_moddb_redo02

2018-08-16T14:56:55.251590 08:00

incarnation:0xf0996e2a synchronous result:'I/O error'

subsys:System krq:0x9ffffffffd4b2f80 bufp:0x9ffffffffd407000 osderr1:0x69b7 osderr2:0x0

2018-08-16T14:56:55.251753 08:00

path:/dev/rdisk/MES1_p4_moddb_redo02

IO elapsed time: 0 usec Time waited on I/O: 0 usec

2018-08-16T14:56:55.252112 08:00

incarnation:0xf0996e2a synchronous result:'I/O error'

subsys:System krq:0x9ffffffffd225ec0 bufp:0x9ffffffffcc0f000 osderr1:0x69c0 osderr2:0x0

IO elapsed time: 0 usec Time waited on I/O: 0 usec

WARNING: cache failed reading from group=2(DG_REDO_MOD) fn=8 blk=60 count=1 from disk=4 (MES1_REDO02) mirror=0 kfkist=0x20 status=0x07 osderr=0x69c0 file=kfc.c line=12986

WARNING: cache succeeded reading from group=2(DG_REDO_MOD) fn=8 blk=60 count=1 from disk=3 (MES2_REDO01) mirror=1 kfkist=0x20 status=0x01 osderr=0x0 file=kfc.c line=13031

2018-08-16T14:56:55.274598 08:00

SUCCESS: extent 0 of file 8 group 2 - all online mirror sides found readable, no repair required ---> 所有online mirror side是完好的

通过create pfile 然后修改pfile,再通过pfile创建spfile,重启实例即可。

通过记录每次的存储断开时间点、日志报错时间点及ASM offline disks时间点,反复比较分析后,发现每次重启实例时的规律如下:

SQL> show parameter pfile

(1)关掉存储后,数据库在 style="font-size: 16px;">60s左右后开始报IO错误;

style="font-size: 16px;">(2)在OS查看全部多路径正常关闭,但是diskinfo查看磁盘会卡死,经测试确认是Oracle asm instance锁住的;

style="font-size: 16px;">(3)数据库在第一次IO报错后,每次需要 style="font-size: 16px;">100s左右完成disk offline及DG PST refresh,然后OS层面diskinfo能正常发现asm disk不可访问。如果关闭asm instance,OS层面 style="font-size: 16px;">30s报多路径失效, style="font-size: 16px;">60sdiskinfo报asm disk设备报不可访问!

style="font-size: 16px;">(3)db实例单开的情况下,会在关掉多路径 style="font-size: 16px;">130s左右完成disk offline正常运行;db实例2个节点全开, style="font-size: 16px;">70s左右1个数据库日志LMHB报CKPT/LGWR阻塞超时的错误并且时间持续增加,期间有controlfile parallel write阻塞及IO 错误,有时会强制重启1个或2个实例,有时候却能正常!

(4)如果手工offline disks,数据库能快速完成操作,不受任何影响!

NAME TYPE VALUE

存在疑问:


style="font-size: 16px;">1、OS在断开存储后60s就会返回多路径失效及磁盘无法访问,但是ASM实例后台为什么一直锁盘,具体是在做哪些工作?

style="font-size: 16px;">2、目前看问题都是在检测到IO错误100s后发生,能否控制ASM在收到OS 磁盘无法访问后,快速将asm disk offline,而不是反复报IO错误?

style="font-size: 16px;">3、如果手工offline存储对应的磁盘,ASM会迅速反应并更新DG状态,但是故障时日志却在不断发出offline请求,为什么无法快速完成呢?


结合上述分析得到的规律及测试数据,我做了多方尝试并深入思考:

spfile string SYS_DG/asm/asmparameterfile/registry.253.837910359

(1) 结合DB报出的70s超时警告,试图找到数据库的隐含参数调高后台进程HUNG住的检测阀值,多次尝试发现这个是硬限制,没有合适的参数控制;

(2) 结合上述现象,问题都出现在数据库检测到IO失败到offline disks的这段时间中,IO报错的时间点和多路径失效是吻合的,预期行为应该是数据库发现多路径失败,快速offline失败的磁盘并且使用Mirror side!但是实际的情况是数据库反复对故障设备进行IO尝试,最后导致实例异常重启。这个要么是数据库判断逻辑失误,要么是存储故障时的返回信息有误?

(3) 结合手工offline disks非常迅速,没有延迟时间, style="font-size: 16px;">因此重点怀疑存储端的响应异常问题?

SQL> select instance_name,host_name,version,status from v$instance;

04初露曙光

INSTANCE_NAME HOST_NAME VERSION STATUS

由于怀疑是存储端问题,我询问了HP工程师是否在存储多路径方面有相关的参数?HP工程师回复有多路径超时设置默认120s,并且无法调整。


无奈我自行搜索HP多路径说明文档,终于发现2个可疑的相关参数:


1、esd_secs

attribute determines the timeout of I/O operations to block devices. By default esdisk sets esd_secs to 30 seconds for all devices bound to it.

2、path_fail_secs:

Timeout in seconds before declaring a lunpath offline when the device no longer responds to I/O requests sent through this I/O path. The default value is 120 seconds.


简单来说,esd_secs是磁盘块设备的IO超时时间,path_fail_secs是LUN的IO超时时间,超过上述时间后多路径软件会把对应的设备或者路径置为offline。直接基于主机多路径设备的测试结果也证明了这种行为。

ASM myrac1.oracle.com 11.2.0.1.0 STARTED

由于ASM DG使用的LUN裸设备,所以我修改了path_fail_secs参数,从120s减少为30s,修改后的确发现数据库检查到IO错误的的确从60s左右缩短到了30s左右。但是发现在IO错误后,ASM instance并没有直接offline disks,而是在不断的Retry并报出IO错误,直到大约130s才能成功offline故障磁盘,故障现象仍然不规律出现,问题仍然没有彻底解决!

SQL> create pfile='/g01/app/grid/product/11.2.0/grid/dbs/asmpfile.ora' from spfile;

我陷于苦思冥想中……

File created.

05柳暗花明

[grid@myrac1 dbs]$ vi asmpfile.ora

反复的测试和分析都将问题的疑点指向了多路径,但是翻遍官方手册没有新的收获。那么是否还有其他的控制机制造成了这种非预期行为呢?

ASM.__oracle_base='/g01/app/grid'#ORACLE_BASE set from in memory value

无奈之下直接去主机man scsimgr翻阅对应参数,又发现1个可疑的参数:

ASM.asm_diskgroups='SYS_DG,DATA_DG,DG_FRA'#Manual Mount 不能在这里直接修改

3、transient_secs

Seconds to wait after a LUN has gone OFFLINE before failing I/Os,default 120s

*.asm_power_limit=1

transient_secs说明配置的在 LUN OFFLINE 后、failing I/O前的一段静默期,这个值默认是120s,如果把这个时间加上,ASM instance每次的offline操作时间就可以解释了,如果这个静默期多路径是不对IO请求响应的,将会直接导致ASM instance无法判断IO是否成功,导致了核心数据库文件(controlfile/redolog)的IO提交错误及核心后台进程的阻塞超时。

*.diagnostic_dest='/g01/app/grid'

通过减低主机服务器path_fail_secs、transient_secs:

*.instance_type='asm'

scsimgr save_attr -N /escsi/esdisk -a path_fail_secs=30 -a transient_secs=10

*.large_pool_size=12M

反复测试ASM实例在检测到IO错误了,会迅速offline所以故障磁盘,快速恢复了IO正常,证明了上述分析结论!

*.remote_login_passwordfile='EXCLUSIVE'

问题到此迎刃而解!

~

06总结

SQL> create spfile from pfile='/g01/app/grid/product/11.2.0/grid/dbs/asmpfile.ora';

问题虽然解决了,但是有些疑问需要反思和解答:

File created.

1、 OS在断开存储后30s就会返回多路径失效及磁盘无法访问,ASM后台为什么一直锁盘,具体是在做哪些工作?

style="font-size: 16px;">多路径进入静默等待期,IO锁定无反应,导致ASM实例反复Retry直到静默超时(120s)

2、 问题都是在检测到IO错误100s后发生,能否控制ASM实例在收到OS 磁盘无法访问后,快速将asm disk offline,而不是反复报IO错误?

style="font-size: 16px;">数据库层面无法控制,因为多路径没有返回失败信息

3、 为什么故障现象不稳定?

style="font-size: 16px;">这个推测很可能是故障时的IO行为不同导致:

1) 如果故障时没有核心的controlfile/redofile的写入,或者IO写入没有发生断裂,那么在多路径静默期超时后,系统是能够恢复正常的;

2) 否则由于静默期IO锁定,Oracle无法判断IO一致性,从而超时导致实例重启!

3) 至于为什么重启后就正常就比较好解释了,因为实例重启需要的时间是大于120s的,重启后静默期已经过去了,disks成功offline了,自然就没有问题了。

4) 修改默认多路径参数是否有其他不利影响?

style="font-size: 16px;">多路径的这些参数的默认值原因是用于容忍短时间存储IO抖动,避免系统异常的。但是在我们的项目场景中,由于Oracle数据库的IO完整性要求,导致了故障的产生。对于双存储系统来说,一旦存储IO故障时,快速Failover到另外1个存储才是最好的选择!

SQL> startup

本问题能够得以解决的主要原因:

ORA-01078: failure in processing system parameters

style="font-size: 16px;">1、坚定的决心:前期测试一度将注意力转移到Oracle BUG,在MOS没有收获后,通过变通方法解决,但是没有完全解决控制文件高可用的问题。基于为客户负责的原则,迫使我背水一战,开始细致分析规律,这才有了明确的问题定位方向;

2、 多方资源整合及信息沟通:在连续几天无法解决问题时,我一方面主动寻求了公司专家、Oracle SR的帮助,虽然没有最终解决本问题,但是排除了一些问题分析方向可能并借鉴了一些思路;一方面和客户负责人主动沟通,发现这个问题并不是个例,之前他们类似系统在AIX/HPUX/LINUX及Veritas文件系统的11g/12c版本RAC均未能解决此问题,这让我进一步意识到Oralce BUG的可能性较小,基础平台支撑的问题更可能是关键!

3、 “精诚所至金石为开”:技术探索的路上不能遇到困难绕道走!在没有HP工程师支持,Oracle SR也没有有效回复的情况下,通过细致深入的分析、不厌其烦的反复测试数十次,促成了问题的最终解决;

4、 在问题发生过程中,充分利用网络资源并分析整合,HP多路径使用文档让我开始有了解决问题的思路和信心,后续进而通过man帮助找到了解决问题的关键!

5、 通过案例梳理了RAC、ASM的整体工作流程和原理,这些基础知识的掌握才能在复杂的故障现象和日志中找到规律并快速寻找到解决方案。

ORA-62001: value for parameter cannot contain a comma

原创:姜劲松。

SQL> startup pfile='/g01/app/grid/product/11.2.0/grid/dbs/asmpfile.ora';

转载:意向文章下方留言。返回搜狐,查看更多

ORA-62001: value for parameter cannot contain a comma

责任编辑:

SQL> startup

ASM instance started

Total System Global Area 284565504 bytes

Fixed Size 1336036 bytes

Variable Size 258063644 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

发现磁盘组DATA_DG,DG_FRA还是没有Mount

[grid@myrac1 ~]$ crs_stat -t

Name Type Target State Host


ora.DATA_DG.dg ora....up.type OFFLINE OFFLINE

ora.DG_FRA.dg ora....up.type OFFLINE OFFLINE

ora....ER.lsnr ora....er.type ONLINE ONLINE myrac1

ora.SYS_DG.dg ora....up.type ONLINE ONLINE myrac1

ora.asm ora.asm.type ONLINE ONLINE myrac1

ora.cssd ora.cssd.type ONLINE ONLINE myrac1

ora.diskmon ora....on.type ONLINE ONLINE myrac1

ora.hjj.db ora....se.type OFFLINE OFFLINE

只能以这种方式进行asm_diskgroups参数的修改

SQL> alter system set asm_diskgroups=sys_dg,data_dg,dg_fra scope=spfile sid=' ASM';

System altered.

SQL> select status from v$instance;

STATUS


STARTED

SQL> shutdown immediate

ASM diskgroups dism九度快排系统

ASM instance shutdown

SQL> startup

ASM instance started

Total System Global Area 284565504 bytes

Fixed Size 1336036 bytes

Variable Size 258063644 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

SQL> !

[grid@myrac1 ~]$ crs_stat -t

Name Type Target State Host


ora.DATA_DG.dg ora....up.type ONLINE ONLINE myrac1

ora.DG_FRA.dg ora....up.type ONLINE ONLINE myrac1

ora....ER.lsnr ora....er.type ONLINE ONLINE myrac1

ora.SYS_DG.dg ora....up.type ONLINE ONLINE myrac1

ora.asm ora.asm.type ONLINE ONLINE myrac1

ora.cssd ora.cssd.type ONLINE ONLINE myrac1

ora.diskmon ora....on.type ONLINE ONLINE myrac1

ora.hjj.db ora....se.type OFFLINE OFFLINE

SQL> show parameter pfile

NAME TYPE VALUE



spfile string /g01/app/grid/product/11.2.0/grid/dbs/spfile ASM.ora

[grid@myrac1 dbs]$ mv spfile ASM.ora spfile ASM.ora.bak

[grid@myrac1 dbs]$ ls

20140219 ab_ ASM.dat asmpfile.ora hc_ ASM.dat init.ora orapw ASM peshm_ ASM_1 spfile ASM.ora.bak

SQL> shutdown immediate

ASM diskgroups dismounted

ASM instance shutdown

SQL> startup

ORA-00099: warning: no parameter file specified for ASM instance

ASM instance started

Total System Global Area 284565504 bytes

Fixed Size 1336036 bytes

Variable Size 258063644 bytes

ASM Cache 25165824 bytes

ORA-15110: no diskgroups mounted

发现使用的$CRS_HOME/dbs/spfile ASM.ora参数文件,而不是磁盘组SYS_DG上的参数文件。

可以让asm实例启动的时候读取磁盘组上的spfile,步骤如下:

SQL> show parameter spfile

NAME TYPE VALUE



spfile string /g01/app/grid/product/11.2.0/grid/dbs/spfile ASM.ora

SQL> startup

ASM instance started

Total System Global Area 284565504 bytes

Fixed Size 1336036 bytes

Variable Size 258063644 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

SQL> !crs_stat -t

Name Type Target State Host


ora.DATA_DG.dg ora....up.type OFFLINE OFFLINE

ora.DG_FRA.dg ora....up.type OFFLINE OFFLINE

ora....ER.lsnr ora....er.type ONLINE ONLINE myrac1

ora.SYS_DG.dg ora....up.type ONLINE ONLINE myrac1

ora.asm ora.asm.type ONLINE ONLINE myrac1

ora.cssd ora.cssd.type ONLINE ONLINE myrac1

ora.diskmon ora....on.type ONLINE ONLINE myrac1

ora.hjj.db ora....se.type OFFLINE OFFLINE

SQL> create spfile=' SYS_DG' from pfile='/g01/app/grid/product/11.2.0/grid/dbs/asmpfile.ora';

File created.

SQL> shutdown immediate

ASM diskgroups dismounted

ASM instance shutdown

SQL> startup

ASM instance started

Total System Global Area 284565504 bytes

Fixed Size 1336036 bytes

Variable Size 258063644 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

SQL> !crs_stat -t

Name Type Target State Host


ora.DATA_DG.dg ora....up.type OFFLINE OFFLINE

ora.DG_FRA.dg ora....up.type OFFLINE OFFLINE

ora....ER.lsnr ora....er.type ONLINE ONLINE myrac1

ora.SYS_DG.dg ora....up.type ONLINE ONLINE myrac1

ora.asm ora.asm.type ONLINE ONLINE myrac1

ora.cssd ora.cssd.type ONLINE ONLINE myrac1

ora.diskmon ora....on.type ONLINE ONLINE myrac1

ora.hjj.db ora....se.type OFFLINE OFFLINE

SQL> alter system set asm_diskgroups=sys_dg,data_dg,dg_fra scope=spfile sid=' ASM';

System altered.

文件如下:

ASMCMD> ls

DATA_DG/

DG_FRA/

SYS_DG/

ASMCMD> cd sys_dg

ASMCMD> ls

ASM/

ASMCMD> cd asm

ASMCMD> ls

ASMPARAMETERFILE/

ASMCMD> cd asmparameterfile

ASMCMD> ls

REGISTRY.253.840236163

ASMCMD> pwd

sys_dg/asm/asmparameterfile

SQL> show parameter spfile

NAME TYPE VALUE



spfile string SYS_DG/asm/asmparameterfile/r

egistry.253.840236163

SQL> shutdown immediate

ASM diskgroups dismounted

ASM instance shutdown

SQL> startup

ASM instance started

Total System Global Area 284565504 bytes

Fixed Size 1336036 bytes

Variable Size 258063644 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

SQL> show parameter spfile

NAME TYPE VALUE



spfile string SYS_DG/asm/asmparameterfile/registry.253.840236163

SQL> show parameter diskgroups

NAME TYPE VALUE



asm_diskgroups string SYS_DG, DATA_DG, DG_FRA

SQL> !crs_stat -t

Name Type Target State Host


ora.DATA_DG.dg ora....up.type ONLINE ONLINE myrac1

ora.DG_FRA.dg ora....up.type ONLINE ONLINE myrac1

ora....ER.lsnr ora....er.type ONLINE ONLINE myrac1

ora.SYS_DG.dg ora....up.type ONLINE ONLINE myrac1

ora.asm ora.asm.type ONLINE ONLINE myrac1

ora.cssd ora.cssd.type ONLINE ONLINE myrac1

ora.diskmon ora....on.type ONLINE ONLINE myrac1

ora.hjj.db ora....se.type OFFLINE OFFLINE

注意:asmpfile.ora中的 ASM.asm_diskgroups='SYS_DG' #Manual Mount,不能添加DATA_DG,DG_FRA进去,否则创建spfile报错。只能再通过alter system进行修改。

总结:在NOMOUNT阶段oracle根据参数文件来分配内存空间及启动后台进程,这个时候磁盘组都没有加载,但是oracle的spfile是直接从磁盘读取的,与SYS_DG是否MOUNT没关系。

更多Oracle相关信息见Oracle 专题页面

本文永久更新链接地址:

新浦京娱乐手机平台 3

Oracle Bug ORA-04043

ORA-01012: not logged on处理

相关资讯

ASM

定位数据在ASM中的位置 (今 20:49)

RAC 11G ASM磁盘损坏恢复 (10/15/2016 16:56:55)

Oracle Linux6.7下使用udev做ASM (07/04/2016 20:02:26)

ASM磁盘组扩容流程 (12/01/2016 08:19:16)

Oracle 11g RAC 启动时无法识别ASM (10/06/2016 15:36:51)

监控ASM磁盘组IO吞吐量 (04/10/2016 07:10:10)

本文评论

查看全部评论 (0)

表情: 新浦京娱乐手机平台 4

姓名:

匿名

字数

同意评论声明

评论声明

尊重网上道德,遵守中华人民共和国的各项有关法律法规

承担一切因您的行为而直接或间接导致的民事或刑事法律责任

本站管理人员有权保留或删除其管辖留言中的任意内容

本站有权在网站内转载或引用您的评论

参与本评论即表明您已经阅读并接受上述条款

最新资讯

定位数据在ASM中的位置

Oracle ASM spfile in a disk group

Oracle ASM ACFS disk group rebalance

Windows 10升级bug导致Bash的 Ctrl-C失效

学生黑客因销售间谍软件面临10年徒刑

Debian GNU/Linux 8.7 正式发布:超 85 项

彻底消失,Linux下用命令行彻底删除文件

Facebook计划将假新闻过滤工具引进德国

版权声明:本文由新浦京娱乐手机平台发布于新能源,转载请注明出处:新浦京娱乐手机平台:守得云开见月明,ASM磁盘