经典故障分析 -用好UTL_FILE包其实并不是太容易

作者介绍

崔华 网名 dbsnake

Oracle ACE Director,ACOUG 核心专家

UTL_FILE包可以用来读写操作系统上的文本文件,UTL_FILE提供了在客户端(FORM等等)和服务器端的文件访问功能。

因此,如何用好UTL_FILE包就成了至关重要的点

要想用好UTL_FILE,就需要注意以下几点:

  1. UTL_FILE.GET_LINE和UTL_FILE.PUT_LINE在读取行记录和写入行记录时并不做任何的字符集转换,所以你要小心可能由此产生的乱码;
  2. UTL_FILE包在一个session里最多只能支持并发打开50个文件句柄;
  3. 调用UTL_FILE.FOPEN打开文件句柄,一旦使用完毕后别忘了调用UTL_FILE.FCLOSE关闭相应的文件句柄;

用好UTL_FILE包其实并不是太容易,不信吗?

我们来看两个跟UTL_FILE包有关的故障分析过程。

故障一:

某环境最近用PL/SQL向远程服务器端写文件时遇到中文乱码情况,该程序主要是提取数据库中插入数据语句,通过UTL_FILE.PUT_LINE 在服务器端写入文本文件,但只要数据中含有中文,那输出文件里的insert语句中中文就是乱码。

故障分析过程:

首先要明白一点,UTL_FILE.GET_LINE和UTL_FILE.PUT_LINE在读取行记录和写入行记录时并不做任何的字符集转换,所以如果源数据库的字符集是AL32UTF8,则当我们使用默认是中文字符集ZHS16GBK的Windows查看从上述源数据库调用UTL_FILE.PUT_LINE写入的含中文的行记录时,就必然会是乱码。

我们来看一个实例:

现在源数据库的字符集是AL32UTF8:

创建一个测试表T1,并插入一条含中文的行记录:

将上述行记录写入/ftptemp下的test1.txt中:

从如下结果里可以看到,test1.txt中的中文“崔华”确实对应的是乱码:

oracle:/ftptemp>cat test1.txt TEST1宕斿崕

解决办法:

用CONVERT函数手工转一下字符集就可以了

从如下结果里可以看到,现在中文“崔华”已经能正常显示了:

oracle:/ftptemp>cat test2.txt TEST1崔华

故障二:

最近一段时间,国航生产库在写接口文件的时候,不定期出现ORA-29283错误。

具体表现为:

1、某些模块的写接口文件的程序不定期报错ORA-29283,整个写接口文件的作业失败,但第二天再次尝试执行同样的程序则成功,不再报错;

2、出问题的地方全部集中在Oracle内部的包“SYS.UTL_FILE”代码的第488行;

故障分析过程:

如下是国航生产库syslog里记录的所有跟上述错误相关的具体信息:

经我仔细核查,所有常规的导致ORA-29283错误的原因均被我一一排除,而且现在最关键的问题是所有相关经验均无法解释为什么在出错后第二天再执行同样的代码就可以成功执行不再报错了?

现在,我们在测试环境重现了这个问题并且给出了解决方法:

首先我们要了解utl_file在一个session里最多只能支持并发打开50个文件句柄,如果超过了50,则Oracle会报错,我们来看看如下测试结果:

在上述存储过程P_TEST_UTLFILE,我们尝试并发打开51个文件句柄,现在我们执行一下这个存储过程:

从结果里我们可以看到,我们已经一模一样的重现了国航生产的ORA-29283错误,并且出问题的地方就是在Oracle内部的包“SYS.UTL_FILE”代码的第488行。

看起来国航生产的情况就是因为文件句柄的并发open的数量超过了50,但是这里的超过50有两种情况:

  • 在某个时间段由于大量的并发,导致文件句柄的并发open的数量超过了50;
  • 由于在调用utl_file.fopen打开文件句柄的时候没有与之相匹配的调用utl_file.fclose,导致某个session的open文件句柄的数量在缓慢增加,当增加到50的时候,这个session如果再次调用utl_file.fopen,则也会报上述错误。

上述情况1经询问,不太可能在我们系统里出现,IPRA里应该是没有在某个时间点(特别是在daily作业的时候)出现文件句柄的并发open的数量超过50的情况。

现在我们重点来关注情况2。

我们依然在测试环境构造出情况2:

现在我们改写了上述测试存储过程P_TEST_UTLFILE,使得其只调用了utl_file.fopen,而没有调用utl_file.fclose。

我们同时开两个Session。

首先在Session 1里执行上述存储过程P_TEST_UTLFILE:

Session 1:

SQL> exec P_TEST_UTLFILE; PL/SQL procedure successfully completed

可以看到,在Session 1里,存储过程P_TEST_UTLFILE是可以成功执行的,但请注意,Session 1里已经累计open了50个文件句柄,也就是说如果我在session 1里再次执行P_TEST_UTLFILE,这时候Oracle一定会报错:

Session 1:

但如果这时候再新开一个Session 2,大家可以看到,Session 2里依然是可以成功执行上述存储过程P_TEST_UTLFILE的,这很正常,文件句柄的并发open的上限50是针对session而言的,并不针对整个数据库

Session 2:

SQL> exec P_TEST_UTLFILE; PL/SQL procedure successfully completed

好了,现在我们已经可以解释为什么国航生产某些模块的写接口文件的程序不定期报错ORA-29283,整个写接口文件的作业失败,但第二天再次尝试执行同样的程序则成功,不再报错?

因为国航生产采用了连接池的机制,连接数据库的Session是在连接池里一直存在的,当前台有连接请求的时候,就随机从连接池里返回一个空闲的连接,前台用完这个连接后就将其释放回连接池,所以这就导致了可能某些Session是一直存在的,从来就没有真正断开过。

我们现在假设这样一种情况:

我们的某段代码调用utl_file.fopen打开文件句柄后没有与之相匹配的调用utl_file.fclose,那么一旦连接池里的某个Session调用了这段代码,在这个Session里,这些打开的文件句柄就永远不会释放了,并且如果下次还是这个Session调用了同样的那段代码,则这个Session里的文件句柄数量是会缓慢增加的,当增加到50后,任何前台连接只要从连接池里选择了这个session并且这个前台连接执行了产生接口文件的操作,那么Oracle这里一定会报错ORA-29283: invalid file operation(这就是我在上述测试中用Session 1模拟的情况)。

但同时,只要前台连接并没有从连接池里选择到这个session,那么执行同样的产生接口文件的操作就不会报错了(这就是我在上述测试中用Session 2模拟的情况)。国航生产里发现错误后第二天再次执行同样代码的时候,很可能已经不是原来的那个Session了,所以会成功执行。

解决办法:

1、 临时的缓解方法是重启一下IPRA应用的server,这样,连接池里的已有session就都被清掉了;

2、 根本的解决方法是在每个可能调用到utl_file.fopen的代码的末尾(包括代码里每一个return之前)和exception处理中加入utl_file.fclose_all()以强制关闭所有可能的文件句柄,注意Oracle已经帮你封装好了utl_file.fclose_all,可以直接调用,无需判断是否还有打开的文件句柄,如下所示:

3、 另外一个简便的解决方法是在每个可能调用到utl_file.fopen的代码的最开始加入utl_file.fclose_all()以强制关闭所有可能的文件句柄,如下所示:

首先执行存储过程P_TEST_UTLFILE,并发打开50个文件句柄,并且不关闭:

此时只要我在上述session中执行任何一个需要调用到utl_file.fopen的存储过程oracle都会报错:

为了让上述存储过程P_TEST_UTLFILE_1能够成功执行,我们修改一下P_TEST_UTLFILE_1的代码,强制在其代码最开头加入utl_file.fclose_all()以强制关闭所有可能的文件句柄,可以看到,修改代码后P_TEST_UTLFILE_1已经可以成功执行,因为那50个打开的文件句柄已经被我们强制清除了:

但请注意,调用utl_file.fclose_all()以强制关闭所有可能的文件句柄可能是有副作用的,如下所示:

我先修改P_TEST_UTLFILE_1的代码,使其在代码的末尾调用utl_file.fclose_all():

接着我创建存储过程P_TEST_UTLFILE_2,P_TEST_UTLFILE_2模拟了一种极端的情况——就是在打开了一个文件句柄的情况下又同时调用了P_TEST_UTLFILE_1,这样随着P_TEST_UTLFILE_1的成功执行,P_TEST_UTLFILE_2中那个打开的文件句柄也会被关闭,等到P_TEST_UTLFILE_2的后续代码想往已经被关闭的那个文件句柄里写数据的时候,Oracle这时候就报错了:

所以如果IPRA里写接口文件有互相嵌套的情况,那么相关模块的负责人就要注意了,这种情况下就不应该用utl_file.fclose_all()了!

这时候我们应该怎么办呢?很简单,直接调用utl_file.fclose(文件句柄名)就可以了:

可以用如下SQL检查出IPRA国航生产所有的调用了utl_file.fopen的地方,请相关负责人去检查一下代码,必要的时候在每个可能调用到utl_file.fopen的代码的末尾和exception处理中加入utl_file.fclose_all()以强制关闭所有可能的文件句柄(另外就是如果你的代码里还有分支,没执行到末尾就return了,那么必要的时候在每个return之前加入utl_file.fclose_all()以强制关闭所有可能的文件句柄):

SQL> select * from dba_source where owner not in(‘ORACLE_OCM’,’SYS’,’OLAPSYS’,’MDSYS’) and lower(text) like ‘%utl_file.fopen%’ order by name; ……省略显示输出内容

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

原文发表时间:2017-09-20

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Python爬虫与算法进阶

Hi,这里是我的爬虫笔记

平时有个习惯,会把自己的笔记写在有道云里面,现在做个整理。会长期更新,因为我是BUG制造机。 解析 xpath提取所有节点文本 <div id="test3"...

3245
来自专栏开发与安全

linux系统编程之管道(三):命名管道FIFO和mkfifo函数

进程间通信必须通过内核提供的通道,而且必须有一种办法在进程中标识内核提供的某个通道,前面讲过的匿名管道是用打开的文件描述符来标识的。如果要互相通信的几个进程没有...

2036
来自专栏技术墨客

Hazelcast集群服务(3)——集群功能详解

    在前2篇博文中,介绍了 Hazelcast的基本原理 和 Hazelcast基本配置。后续的博文会逐一介绍Hazelcast的主要功能组件。本篇将详细说...

974
来自专栏开发与安全

linux系统编程之管道(一):匿名管道和pipe函数

一、进程间通信 每个进程各自有不同的用户地址空间,任何一个进程的全局变量在另一个进程中都看不到,所以进程之间要交换数据必须通过内核,在内核中开辟一块缓冲区,进程...

2070
来自专栏程序猿DD

Spring Boot中使用Actuator的/info端点输出Git版本信息

对于Spring Boot的Actuator模块相信大家已经不陌生了,尤其对于其中的/health、/metrics等强大端点已经不陌生(如您还不了解Actua...

2167
来自专栏闻道于事

Spring Boot yml格式配置

824
来自专栏文渊之博

Elasticsearch-深入理解索引原理

最近开始大面积使用ES,很多地方都是知其然不知其所以然,特地翻看了很多资料和大牛的文档,简单汇总一篇。内容多为摘抄,说是深入其实也是一点浅尝辄止的理解。希望...

3294
来自专栏散尽浮华

Mysql占用过高CPU时的优化手段

Mysql占用CPU过高的时候,该从哪些方面下手进行优化? 占用CPU过高,可以做如下考虑: 1)一般来讲,排除高并发的因素,还是要找到导致你CPU过高的哪几条...

35211
来自专栏狂码一生

C++安装、删除、启动服务

/* 名称:系统服务管理 语言:C++ 介绍:对Windows系统服务的状态获取,服务暂停,开启,停止操作代码 */ void CStartServiceDlg...

43610
来自专栏jeremy的技术点滴

redis研究

2608

扫描关注云+社区