经典故障分析 -用好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 条评论
登录 后参与评论

相关文章

来自专栏我的博客

Strace命令手册

strace简介 strace常用来跟踪进程执行时的系统调用和所接收的信号。 在Linux世界,进程不能直接访问硬件设备,当进程需要访问硬件设备(比如读取磁盘...

3528
来自专栏小樱的经验随笔

【批处理学习笔记】第二十六课:返回值

    有些命令在执行之后将会返回一定的错误值(errorlevel),可以通过errorlevel的值判断命令执行的状况。这点类似于C语言里面的exit(nu...

2696
来自专栏http://www.cnblogs.com

centos6.5新增加硬盘挂载并实现开机自动挂载

在内网主机新增一个2T硬盘,先关机断电再连接硬盘数据线和电源线! 查看当前磁盘设备信息: [root@tb ~]# fdisk -l WARNING: GPT...

38414
来自专栏运维小白

4.10/4.11/4.12 lvm讲

LVM讲解 lvm的优缺点 优势:很方便的扩容和缩容磁盘空间 局限性:,磁盘发生损坏,不易于恢复 ? lvm准备工作 fdisk /dev/sdb n 创建3...

1668
来自专栏技术专栏

Scala入门与进阶(二)- Scala入门

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

使用sql*plus编辑sql文本(r4笔记第53天)

工作中可能会经常实用工具来编辑sql 文本,实用sql*plus来编辑的机会比较少,但是这些也是硬功夫,一旦有需要手工编辑,其实发现也是很容易的。 关于编辑使用...

2744
来自专栏散尽浮华

Mysql慢查询操作梳理

Mysql慢查询解释 MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_quer...

1916
来自专栏csxiaoyao

mysql 命令完全总结

4097
来自专栏Laoqi's Linux运维专列

for 循环,while循环,break,continue,exit

3318
来自专栏炉边夜话

写程序应该注意的地方

1. 对于文本编辑框,一定要判断是否含有非法字符。对于数字型一定要判断是否含有非数字。对于字符型,一定要判断是否在允许的字符内。

1003

扫码关注云+社区