OS: AIX 6
DB: RAC 2节点
10205
症状:
普通的索引创建
CREATE INDEX justin.aaa_index ON justin.aaa_table(X_PERSON_ID);
通过os authentication进入数据库能够执行成功,但是通过listener连接的session却总是报告ora-4030;
Listener是由oracle用户启动的;
justin_$ ps -ef | grep tns
oracle 35127444 1 0 Jul 10 - 0:11 /u001/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_1_justin –inherit
更为离奇的是,通过listener进入数据库,倘若把workarea_size_policy 设置为manual,也能创建成功;
SQL> alter session set workarea_size_policy=manual;
Session altered.
SQL> CREATE INDEX justin.aaa_index ON justin.aaa_table(X_PERSON_ID);
Index created.
SQL> drop index justin.aaa_index;
Index dropped.
SQL> alter session set workarea_size_policy=auto;
Session altered.
SQL> CREATE INDEX justin.aaa_index ON justin.aaa_table(X_PERSON_ID);
CREATE INDEX justin.aaa_index ON justin.aaa_table(X_PERSON_ID)
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort
subheap,sort key)
/etc/security/limits.conf内容如下
default:
fsize = -1
core = 2097151
cpu = -1
data = 262144
rss = 65536
stack = 65536
nofiles = 2000
root:
rss = -1
daemon:
bin:
sys:
adm:
uucp:
nobody:
esaadmin:
stack = 393216
stack_hard = 393216
oracle:
stack_hard = -1
data = -1
stack = -1
core = -1
rss = -1
ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) unlimited
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) 2000
threads(per process) unlimited
processes(per user) unlimited
刚开始reload过listener,但是错误依旧;
尝试重启listener,远程登录后发现index可以正常创建;
http://www.itpub.net/thread-1635002-1-1.html
http://t.askmaclean.com/thread-1420-1-2.html
在t.askmaclean.com和itpub发帖,发现有不少网友遇到类似问题,有可能由listener bug引起;
开SR得到回复,可能由stack或者rss(memory)设置过低造成的,修改/etc/security/limits.conf后需要重启listener重新读取配置参数,可采用以下措施进一步验证该问题
$ pidof tnslsnr
24539 24537 23956
$ grep memory /proc/24539/limits
Max locked memory unlimited unlimited bytes
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-735930/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-735930/