数据库打patch建议设置nls_language为英文
惜分飞发表于 2023-12-03 11:08:31
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：数据库打patch建议设置nls_language为英文
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
昨晚给客户11.2.0.4打比较新的psu和ojvm patch(主要为了修复安全扫描漏洞)
结果今天早上客户那边反馈应用有报ORA-29548错
该错误比较明显应该是和java有关系,在本次变更中最大可能就是ojvm有关系,查看ojvm的postinstall.sql执行日志,发现如下问题
34 -- Check the validity of JAVAVM and let the registry be updated accordingly. 35 36 initjvmaux.validate_javavm; 37 38 -- Add a row in registry$history to indicate this script was run. 39 40 EXECUTE IMMEDIATE 'insert into registry$history 41 (action_time, action, namespace, version, id, comments) 42 values(SYSTIMESTAMP, ''jvmpsu.sql'', ''SERVER'', 43 ''11.2.0.4.221018OJVMPSU'', 0, ''RAN jvmpsu.sql'')'; 44 45 END IF; 46 47 EXECUTE IMMEDIATE 'alter system set java_jit_enabled = ' || :jitstate; 48 49 END; 50 / BEGIN * 第 1 行出现错误: ORA-01843: 无效的月份 ORA-06512: 在 line 8
查询组件有效性
SQL> select comp_name,version,status from dba_registry order by 1; COMP_NAME -------------------------------------------------------------------------------- VERSION STATUS ------------------------------ ---------------------- JServer JAVA Virtual Machine 11.2.0.4.0 INVALID OLAP Analytic Workspace 11.2.0.4.0 VALID OLAP Catalog 11.2.0.4.0 VALID COMP_NAME -------------------------------------------------------------------------------- VERSION STATUS ------------------------------ ---------------------- OWB 11.2.0.1.0 VALID Oracle Application Express 3.2.1.00.10 VALID Oracle Database Catalog Views 11.2.0.4.0 VALID COMP_NAME -------------------------------------------------------------------------------- VERSION STATUS ------------------------------ ---------------------- Oracle Database Java Packages 11.2.0.4.0 VALID Oracle Database Packages and Types 11.2.0.4.0 VALID Oracle Enterprise Manager 11.2.0.4.0 VALID COMP_NAME -------------------------------------------------------------------------------- VERSION STATUS ------------------------------ ---------------------- Oracle Expression Filter 11.2.0.4.0 VALID Oracle Multimedia 11.2.0.4.0 VALID Oracle OLAP API 11.2.0.4.0 VALID COMP_NAME -------------------------------------------------------------------------------- VERSION STATUS ------------------------------ ---------------------- Oracle Rules Manager 11.2.0.4.0 VALID Oracle Text 11.2.0.4.0 VALID Oracle Workspace Manager 11.2.0.4.0 VALID COMP_NAME -------------------------------------------------------------------------------- VERSION STATUS ------------------------------ ---------------------- Oracle XDK 11.2.0.4.0 VALID Oracle XML Database 11.2.0.4.0 VALID Spatial 11.2.0.4.0 VALID 已选择18行。
确认JServer JAVA Virtual Machine组件无效,很可能是ojvm的postinstall.sql执行失败有关系,而引起这个失败的原因是由于“ORA-01843: 无效的月份”,根据经验,引起这个问题的可能是由于win汉语环境中日期/时间的显示格式导致
SQL> select sysdate from dual; SYSDATE -------------- 03-12月-23
设置为英文格式显示
SQL> alter session set nls_language='American'; Session altered. SQL> select sysdate from dual; SYSDATE ------------ 03-DEC-23
重新执行postdeinstall和postinstall脚本之后,组件状态恢复正常,软件功能也恢复正常
SQL> select comp_name,version,status from dba_registry order by 1; JServer JAVA Virtual Machine 11.2.0.4.0 VALID OLAP Analytic Workspace 11.2.0.4.0 VALID OLAP Catalog 11.2.0.4.0 VALID OWB 11.2.0.1.0 VALID Oracle Application Express 3.2.1.00.10 VALID Oracle Database Catalog Views 11.2.0.4.0 VALID Oracle Database Java Packages 11.2.0.4.0 VALID Oracle Database Packages and Types 11.2.0.4.0 VALID Oracle Enterprise Manager 11.2.0.4.0 VALID Oracle Expression Filter 11.2.0.4.0 VALID Oracle Multimedia 11.2.0.4.0 VALID Oracle OLAP API 11.2.0.4.0 VALID Oracle Rules Manager 11.2.0.4.0 VALID Oracle Text 11.2.0.4.0 VALID Oracle Workspace Manager 11.2.0.4.0 VALID Oracle XDK 11.2.0.4.0 VALID Oracle XML Database 11.2.0.4.0 VALID Spatial 11.2.0.4.0 VALID 18 rows selected.
自此提醒由于oracle 环境语言默认显示的问题导致某些patch不能正常打成功,建议在执行数据库patch或者升级之时,把数据库语言环境调整为英文nls_language=’American’,以避免本次出现的不必要的麻烦(以前由于大部分客户没有是使用jvm这个功能组件因此没有暴露该问题)