诊断案例:从实例挂起到归档失败和内存管理的蝴蝶效应

杨廷琨(yangtingkun)

云和恩墨 CTO

高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主

编辑手记:在很多数据库的故障案例中,一个简单的疏忽可能导致问题被层层放大,最终导致故障,这就是蝴蝶效应的传播原理。这里分享的小案例自顶向下的追溯可以显见:实例挂起->归档失败->实例错误->参数配置。根本的原因往往很简单,DBA的严谨尤其重要。

客户的11.2.0.3 RAC数据库出现了归档失败的情况,导致单个实例出现HANG死的状况。

检查错误信息发现:

Tue Jul 02 16:49:13 2013 ARC1: Error 19504 Creating archive log file TO '+DATA02' ARCH: Archival stopped, error occurred. Will continue retrying ORACLE Instance orcl1 - Archival Error ORA-16038: log 14 SEQUENCE# 68244 cannot be archived ORA-19504: failed TO CREATE file "" ORA-00312: online log 14 thread 1: '+DATA02/orcl/onlinelog/group_14.264.792274883' ORA-00312: online log 14 thread 1: '+DATA02/orcl/onlinelog/group_14.265.792274889' Archiver process freed FROM errors. No longer stopped Tue Jul 02 16:50:37 2013 ARC0: LGWR IS actively archiving destination LOG_ARCHIVE_DEST_3 ARCH: Archival stopped, error occurred. Will continue retrying ORACLE Instance orcl1 - Archival Error ORA-16014: log 14 SEQUENCE# 68244 NOT archived, no available destinations ORA-00312: online log 14 thread 1: '+DATA02/orcl/onlinelog/group_14.264.792274883' ORA-00312: online log 14 thread 1: '+DATA02/orcl/onlinelog/group_14.265.792274889' ARC0: Archive log rejected (thread 1 SEQUENCE 68240) at host 'orclsh' FAL[server, ARC0]: FAL archive failed, see trace file. ARCH: FAL archive failed. Archiver continuing ORACLE Instance orcl1 - Archival Error. Archiver continuing.

由于归档失败发生在ASM磁盘上,首先检查ASM磁盘空间以及DB_RECOVERY_FILE_DEST_SIZE,ASM磁盘空间是足够的,而且由于只有一个节点出现出现了无法归档的问题,也可以排除是空间不足造成的。

确认两个节点的DB_RECOVERY_FILE_DEST_SIZE参数设置都是0,基本上可以判断问题和当前节点的ASM实例状态不正常有关。

接下来检查ASM实例的错误信息:

Tue Jul 02 16:41:43 2013 Dumping diagnostic DATA IN directory=[cdmp_20130702164115], requested BY (instance=2, osid=2032294 (LMD0)), summary=[incident=165521]. Tue Jul 02 16:49:13 2013 Dumping diagnostic DATA IN directory=[cdmp_20130702164845], requested BY (instance=2, osid=2032294 (LMD0)), summary=[incident=165522].

当前节点ASM实例出现了的这个信息,说明报错发生在实例2上,检查实例2的ASM告警日志文件:

Tue Jul 02 18:29:55 2013 Errors IN file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_2032294.trc (incident=186256): ORA-04031: unable TO allocate 3768 bytes OF shared memory ("shared pool","unknown object","sga heap(1,0)","ges enqueues") USE ADRCI OR Support Workbench TO package the incident. See Note 411.1 at My Oracle Support FOR error AND packaging details. Insufficient shared pool TO allocate a GES object (ospid 2032294) Tue Jul 02 18:29:55 2013 Sweep [inc][186256]: completed Tue Jul 02 18:36:49 2013 Errors IN file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_2032294.trc (incident=186257): ORA-04031: unable TO allocate 3768 bytes OF shared memory ("shared pool","unknown object","sga heap(1,0)","ges enqueues") USE ADRCI OR Support Workbench TO package the incident. See Note 411.1 at My Oracle Support FOR error AND packaging details. Insufficient shared pool TO allocate a GES object (ospid 2032294)

果然实例2上的ASM出现了大量ORA-4031错误,这说明ASM实例的Shared Pool内存可能配置不足。检查ASM启动的参数配置:

Sat Aug 25 20:06:55 2012 NOTE: No asm libraries found IN the system ERROR: -5(Duplicate disk DATA_DG01:ASM_DISK1) ERROR: -5(Duplicate disk DATA_DG01:ASM_DISK2) MEMORY_TARGET defaulting TO 411041792. * instance_number obtained FROM CSS = 2, checking FOR the existence OF node 0... * node 0 does NOT exist. instance_number = 2 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Private Interface 'en1' configured FROM GPnP FOR USE AS a private interconnect. [name='en1', TYPE=1, ip=169.254.78.6, mac=00-1a-64-bb-50-7d, net=169.254.0.0/16, mask=255.255.0.0, USE=haip:cluster_interconnect/62] Public Interface 'en0' configured FROM GPnP FOR USE AS a public interface. [name='en0', TYPE=1, ip=10.1.16.35, mac=00-1a-64-bb-50-7c, net=10.1.16.32/27, mask=255.255.255.224, USE=public/1] Picked latch-free SCN scheme 3 USING LOG_ARCHIVE_DEST_1 parameter DEFAULT VALUE AS /u01/app/11.2.0.3/grid/dbs/arch Autotune OF undo retention IS turned ON. LICENSE_MAX_USERS = 0 SYS auditing IS disabled NOTE: Volume support enabled Starting up: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the REAL Application Clusters AND Automatic Storage Management options. ORACLE_HOME = /u01/app/11.2.0.3/grid System name: AIX Node name: orcldb2 Release: 1 Version: 6 Machine: 00C94E064C00 USING parameter settings IN server-side pfile /u01/app/11.2.0.3/grid/dbs/init+ASM2.ora System parameters WITH non-DEFAULT VALUES: large_pool_size = 12M instance_type = "asm" remote_login_passwordfile= "EXCLUSIVE" asm_diskstring = "/dev/ocr_*" asm_diskstring = "/dev/voting_*" asm_diskstring = "/dev/asm_*" asm_diskgroups = "DATA" asm_diskgroups = "DATA_DG01" asm_diskgroups = "SPFILE_DG" asm_power_limit = 1 diagnostic_dest = "/u01/app/grid" Cluster communication IS configured TO USE the following interface(s) FOR this instance 169.254.78.6 cluster interconnect IPC version:Oracle UDP/IP (generic) IPC Vendor 1 proto 2

当前ASM实例使用默认的MEMORY_TARGET配置,分配大小大约是400M,根据Oracle的MOS文章:ASM & Shared Pool (ORA-4031) [ID 437924.1],在11.2.0.3中,Oracle增加了ASM实例所允许的默认进程数PROCESSES,但是默认的MEMORY_TARGET参数没有增加。

根据Oracle的建议,11.2.0.3的MEMORY_TARGET至少应该设置到1536M,而MEMORY_MAX_TARGET设置为4096M

SQL> ALTER system SET memory_max_target=4096m scope=spfile; SQL> ALTER system SET memory_target=1536m scope=spfile;

对于当前的情况,如果短时间内无法重启DB和ASM实例,可以在问题节点配置一个第二本地归档路径,设置目标路径为本地磁盘,从而避免归档无法完成而导致的实例HANG死。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-05-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏分布式系统和大数据处理

Hive中分区和分桶的概念和操作

在使用传统的RDBMS数据库(关系数据库),例如MySql时,对于一些大表,我们通常会进行分表操作,以提升查询效率。在Hive中也提供了类似的概念和操作,本文将...

1071
来自专栏杨建荣的学习笔记

物化视图刷新的问题及分析(61天)

最近现场需要搭建一套全新的环境,对于数据字典的管理采用了物化视图,因为数据量不大,采用了全量刷新的方式。因为有好几套环境,有几套环境是通过db link和主节点...

2927
来自专栏杨建荣的学习笔记

undo retention的思考(一)

最近有个网友咨询我一个问题,是关于undo_retention的,对于这个参数没有过多关注,只是知道需要设置undo_retention搭配使用undotabl...

3155
来自专栏杨建荣的学习笔记

只言片语分析datapump的工作原理(r2第18天)

datapump是从oracle 10g推出的新的数据导入导出工具,可以说是exp/imp的加强版,主要的亮点在于服务端,结合了direct+parallel,...

2043
来自专栏乐沙弥的世界

ORA-02409:超时:分布式事务处理等待锁定ORA-02063

ORA-02409:超时:分布式事务处理等待锁定ORA-02063 一、错误现象与环境     前端应用程序运行时出现下面的错误提示: 事件添加失败:O...

822
来自专栏杨建荣的学习笔记

关于exp/imp的总结学习(r4笔记第26天)

关于exp/imp,是很常用的数据导出导入工具,在10g开始推出的数据泵datapump相当于是exp/imp的补充和升级版本。在后续章节再做一个总结。 exp...

2907
来自专栏乐沙弥的世界

Oracle 控制文件(CONTROLFILE)

为二进制文件,初始化大小由CREATE DATABASE指定,可以使用RMAN备份

882
来自专栏轮子工厂

深入理解MySQL---数据库知识最全整理,这些你都知道了吗?

对于后端开发人员来说,经常会和数据打交道,今天总结下数据库相关的知识。包括MySQL,JDBC基础,JDBC进阶,MongoDB,性能优化等知识点。

803
来自专栏乐沙弥的世界

Oracle Net Services - Tracing and Logging at a Glance

    Oracle Net trace 用于跟踪或调试oracle连接故障,连接异常断开或者连接超时等情形,通过产生详细的跟踪信息来进行分析和诊断Oracle...

523
来自专栏乐沙弥的世界

关于undo表空间配置错误的ORA-30012

      undo表空间是Oracle体系结构的重要组成部分,为什么我们可以回滚,就是因为有它。数据库任意数据的修改都会在undo表空间里生成前镜像,一是可以...

441

扫码关注云+社区