升级Oracle导致procedure执行报错原因排查

背景：

​ Oracle 9i升级到11g 之后，procedure执行报错。

问题回顾：

1) procedure 在原来的9i的环境中为如下写法：

PKG_FILE := UTL_FILE.FOPEN('UTL_FILE_PATH', PKG_FILE_NAME, 'W');
PKG_FILE := UTL_FILE.FOPEN('UTL_FILE_PATH', PKG_FILE_NAME, 'R');

在9i环境下可以正常执行，但是升级到11g之后，执行抛出如下报错：

SQL> execute TSP_xxxx;
begin TSP_xxxx; end;
ORA-20300: ORA-20300: ORA-06502: PL/SQL: 數字或值錯誤: character string buffer too small
ORA-06512: 在 "xxx.TSP_xxxx", line 378
ORA-06512: 在 line 1

怀疑是字节太长，导致报错，官方文档中关于UTL_FILE描述如下：

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#ARPLS70900

The `FOPEN` `max_linesize` parameter must be a number in the range 1 and 32767. 
If unspecified, Oracle supplies a default value of 1024. The `GET_LINE` `len` parameter must be a number in the range 1 and 32767.
If unspecified, Oracle supplies the default value of `max_linesize`.
If `max_linesize` and `len` are defined to be different values, then the lesser value takes precedence.

2）所以怀疑是拼接的字符串超过默认的1024字节而引起的， 显式设置成最大的32767试试，修改如下：

PKG_FILE := UTL_FILE.FOPEN('UTL_FILE_PATH', PKG_FILE_NAME, 'W', 32767);
PKG_FILE := UTL_FILE.FOPEN('UTL_FILE_PATH', PKG_FILE_NAME, 'R', 32767);

结论：经过测试，显式设置max_linesize 最大值32767后，procedure可以正常执行。 

3）怀疑字符集导致字节过长
虽然上面显式设置max_linesize 最大的32767，可以解决问题。但是我们检查其他11g环境，没有设置max_linesize的也能正常执行procedure，怀疑是不是客户端字符集导致字节长度不一样呢？我们数据库的字符集设置都为BIG5， 对比发现其他系统环境PMON查到的NLS_LANG都是BIG5，而升级之后的系统PMON查到的NLS_LANG是AL32UTF8，和数据库字符集不一样。

4）根据文档Doc ID 1077034.1，修改PMON进程字符集

-- 1.修改s_crsconfig_xxxx_env.txt文件
[grid@xxxx install]$ cd /u01/app/11.2.0/grid/crs/install
[grid@xxxx install]$ cat s_crsconfig_xxxx_env.txt
### This file can be used to modify the NLS_LANG environment variable, which determines the charset to be used for messages.
### For example, a new charset can be configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8 
### Do not modify this file except to change NLS_LANG, or under the direction of Oracle Support Services

TZ=Asia/Shanghai
# 修改NLS_LANG
#NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NLS_LANG=TRADITIONAL CHINESE_TAIWAN.ZHT16BIG5
TNS_ADMIN=
ORACLE_BASE=

注意：NLS_LANG=TRADITIONAL CHINESE_TAIWAN.ZHT16BIG5 ，NLS_LANG不能加单引号或者双引号括起来，否则启动CRS时候，ohasd.log 会抛出如下错误，无法启动：

ORA-12705: Cannot access NLS data files or invalid environment specified

-- 2.重启实例&集群
[grid@xxxx install]$ srvctl stop instance -d ORCL-i ORCL1
[root@xxxx bin]$ ./crsctl stop crs
[grid@xxxx bin]$ ./crsctl start crs
[grid@xxxx install]$ srvctl start instance -d ORCL-i ORCL1

--验证：
strings /proc/<pmon process ID>/environ | grep NLS

结论：经过测试，不需要设置max_linesize 最大值32767，procedure也能正常执行了。 

所以，升级11g之后，导致procedure执行失败的根本原因是因为PMON进程对应的NLS_LANG和数据库字符集不一致。

 
注意：RAC 环境，2个节点都要修改。