应用报错“Io 异常: Connection reset”异常案例

前几日,一套核心业务数据库待投产环境进行投产前测试时应用出现大量“Io 异常: Connection reset”报错,应用无法连接数据库。要来数据库相关信息后,登陆数据库检查状态发现数据库正处于大量’Library Cache Lock与Library Cache Mutex :X等待中,如下图ASH所示:

20191227-1

20191228-5

20191228-6

大量ddl_lock type为79,已经知晓了这种现象的根因,随手做了一个数据库systemstate dump进行验证:

oradebug setmypid
oradebug dump systemstate 266;
oradebug tracefile_name;

探寻 dump Trace文件,我们看到会话正在等待Library Cache Lock,做system dump 的意义在于,要找到持有Library Cache Handle Lock的会话为什么会持有锁,为什么不及时释放锁从而导致后续活动会话get不到Lock资源从而等待,

20191228-1

dump trace文件中已经明确显示了该handle address,继续搜寻后发现证实了我的猜测,

20191228-2我们看到,该Hanlde Lock既有RequestMode=X,同时又存在LockMode=X ,不兼容从而发生的阻塞等待是正常现象,但是发现了该Library Cache Lock 的Namespace=ACCOUNT_STATUS(79),证实了我的猜测,该阻塞等待是因为数据库账户密码错误导致触发Oracle 延迟登录特性,从而产生大量Library Cache Lock。但是为何Io 异常: Connection reset呢?猜测是因为数据库活动会话大量积压,应用服务器连接数被撑满。

既然找到并且证实了根因,针对此问题下一步该如何操作?针对数据库账户密码错误,我们可以执行:

alter system set events '1017 trace name errorstack forever,level 3';

该errorstack会生成trace文件,在trace文件中确认,waitint for library cahce lock中的machine,也就是密码错误触发延迟登录长时间持有Library Cache Lock的应用服务器。

设置完成后,只要利用错误密码登录数据库抛出ORA-01017,则会在数据库Alert中产生一个Trace文件.

20181228-3

20191228-4

随后将报错应用服务器发送至应用方,检查配置的数据库账户密码是否正确。

最后, 为消除之后隐患,在数据库中执行

ALTER SYSTEM SET EVENTS '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' :

从而使得,今后会话在因密码登录失败后,直接返回报错,不会长时间持有LIBRARY CACHE LOCK 。

Oracle 12c中分页查询增强-TOP N SQL

继之前文章中提到的分页查询SQL优化思路提到的分页框架:

SELECT /*+ gather_plan_statistics */ *
  FROM (
   SELECT *
      FROM (
    SELECT rownum AS rn, r.owner, r.object_id
   FROM (
    SELECT /*+ index(t t_idx01) --分页语句 */ owner, object_id
   FROM t
  ORDER BY object_id) r
 )
 WHERE rownum <= 10)
WHERE rn >= 1;

我们前文中提到过,需要在排序列中创建索引,借助索引有序特性来避免排序并且利用正确的分页框架实现COUNT STOPKEY特性,在Oracle 12c版本之后,Oracle提供了新的分页TOP N功能,来实现上述分页需求并且极大的减少了代码复杂程度。

参考Oracle 官方文档中的的描述:

20191208

我们可以利用FETCH ROWS ONLY来实现文章分页TOP-N需求代码:

20191208-2

继续阅读

alter table drop column(default value not null )导致ORA-12899

近日,同事的测试库中出现一则奇怪的现象邀请我共同探讨,一张数据表在删除一个含有DEFAULT值的非空列时报错,Error Code为ORA-12899,报错详情如下:

20191215-1

12899, 00000, "value too large for column %s (actual: %s, maximum: %s)"
// *Cause: An attempt was made to insert or update a column with a value
// which is too wide for the width of the destination column.
// The name of the column is given, along with the actual width
// of the value, and the maximum allowed width of the column.
// Note that widths are reported in characters if character length
// semantics are in effect for the column, otherwise widths are
// reported in bytes.
// *Action: Examine the SQL statement for correctness. Check source
// and destination column data types.
// Either make the destination column wider, or use a subset
// of the source column (i.e. use substring).

专业的同事,在同样觉得蹊跷的之后,再一次执行此语句并且抓取了相关10046 Trace文件,分析Trace文件后,发现有一条递归update语句导致此错误,导致drop column失败,进入死循环。

2019115-2

继续阅读

针对SCHEMA Rename需求

近日,某测试数据库用户存在更换schema名称的需求,同事准备将数据进行导入导出的同时进行remap schema的工作,其实,从ora11g(11.2.0.2)版本开始,Oracle提供在不动数据的情况下,将schema rename的功能,但是代价是需要将数据库启动restrict模式下,实验如下:

20191208-111

rename之前需要将_enable_rename_user隐含参数改为TRUE(默认为FALSE),此参数意义为enable RENAME-clause using ALTER USER statement。

将数据库启动到restrict模式后,可以利用alter user rename命令进行更改schema name,本案例中为schema独占测试数据库,若生产库将数据库中一般存在多个schema共享一套数据库,启动到 restrict模式会造成应用连接无法连接数据库的情况发生,因本案例为测试系统,生产系统慎用此方式。

20191208-222

Oracle 12c column add default value功能增强

前几日晚,某核心应用负责人准备进行上线操作前打来一个确认电话,咨询了一个Oracle 11g 表添加default值的问题,询问是否有何影响,听完他的描述后,简单跟他描述完原理后,建议他取消此上线操作,否则会造成核心应用严重的性能问题,但是此问题再Oracle 12c新特性中已得到很大改进,对性能的影响已经降到最低。

11g情况下,可以看到,增加一个带有default值的并且没有添加非空约束的列,需要分钟级别(与表数据量有直接关系),完成时间会随着数据量的增加而耗时变长。

20191208-11

会话2,我们在执行alter table时会造成读写阻塞,等待library cache lock

SID=916会话被阻塞

20191208-12

SID=346会话被阻塞:

20191208-14

所以在此期间内产生的应用事务,查询均会被阻塞,如果TABLE量级比较大,会造成更严重的性能问题,因为11g在添加DEFAULT值且没有非空约束时,在更新数据字典的同时仍要更新表中全部已有记录值,我们从segment大小与10046 event trace文件中可以得到论证。

继续阅读

Oracle12C TABLE ACCESS BY INDEX ROWID BATCHED引发的疑问

今天,公司同事问了我一个问题,Oracle 12c在执行计划中出现了TABLE ACCESS BY INDEX ROWID BATCHED“批量回表”特性,是否等同于我们消除了Nested Loop时被驱动表扫描多少次就需要回表多少次的现状?

答案是否定的,我们先看一下如下测试实验:

我们在12c环境下执行如下SQL:

select /*+ gather_plan_statistics use_hash(a,t2) */
a.owner,a.object_name,a.object_id from t a,t2 where a.owner=t2.username
and a.owner='SYSTEM'

因12c中TABLE ACCESS BY INDEX ROWID BATCHED特性受隐含参数控制(12C 默认为TRUE),我们分别将该参数改为FLASE,与TRUE:

(1)关闭TABLE ACCESS BY INDEX ROWID BATCHED特性,同ora11g版本:

2019203-2

继续阅读

应用端频繁报出java.sql.SQLException: pingDatabase failed, status=-1案例

近日,某航司B2C网站应用人员发来求助排查邮件,告知JBOSS连接数据库经常报出java.sql.SQLException: pingDatabase failed, status=-1的错误,并且经过确认,数据库正处于正常状态下,应用人员提供了相对具体报错时间点,希望尽快帮忙排查一下数据库端有无异常。

20191201-2

登录数据库查看alert与监听日志后发现,数据中确实偶发出现如下链接报错,

20191201-1

继续阅读

RAC数据库日志大量IPC Send timeout报错案例

上周,公司一套核心业务系统数据库出现大量IPC Send timeout的报错信息并伴随出现大量diag进程的dump Trace文件;同时,监控部门同事反馈监控agent已无法连入此库,下图为数据库alert日志截图,抛出的Trace文件均是用作监控需求,查询GV$视图的SQL。

201911127-1

当RAC数据库中出现类似信息时,我们优先考虑RAC数据库节点间出现私网通信不佳,私网丢包,包重组的故障原因,同时与监控查询GV$需要节点间通讯特性比较吻合,我们接下来看一下节点1,2,3的进群日志:

继续阅读