11g r2 RAC 报ORA-609错误

ccboceb7

最近项目在做压力测试，有时候应用程序报数据库连接失败，然后手动tnsping也非常慢，有时得10多秒，有时6，7秒，这时检alert*.log 和sqlnet.log日志文件 都有错误出现，当连接正常时，这两日志文件中错误不再出现。
这种情况 在压力情况下偶有出现，也有点怀疑应用程序连接太频繁，但MOS上的类似现象有说跟DNS配置有关，现在还没有思路，望解决一下。
db info: RAC 双节点，
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE        11.2.0.3.0        Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

出现错误时的alert*.log 错误信息：

Fri Dec 07 22:28:36 2012 opiodr aborting process unknown ospid (42140792) as a result of ORA-609 Fri Dec 07 22:28:36 2012 opiodr aborting process unknown ospid (59573728) as a result of ORA-609 Fri Dec 07 22:28:36 2012 opiodr aborting process unknown ospid (983818) as a result of ORA-609 Fri Dec 07 22:28:36 2012 opiodr aborting process unknown ospid (63112042) as a result of ORA-609 Fri Dec 07 22:28:36 2012 opiodr aborting process unknown ospid (41223176) as a result of ORA-609 Fri Dec 07 22:28:36 2012 opiodr aborting process unknown ospid (48956442) as a result of ORA-609 Fri Dec 07 22:28:36 2012 opiodr aborting process unknown ospid (57410504) as a result of ORA-609 Fri Dec 07 22:28:36 2012 opiodr aborting process unknown ospid (40043852) as a result of ORA-609 Fri Dec 07 22:28:36 2012 opiodr aborting process unknown ospid (58066376) as a result of ORA-609 Fri Dec 07 22:28:36 2012 opiodr aborting process unknown ospid (12255452) as a result of ORA-609 Fri Dec 07 22:28:36 2012 Fri Dec 07 22:28:36 2012

出错时的sqlnet.log文件中的内容：

*********************************************************************** Fatal NI connect error 12514, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.142.243)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=shzw1)(CID=(PROGRAM=oracle)(HOST=p750703a)(USER=tanwei))))   VERSION INFORMATION:         TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production         TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production         Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production   Time: 08-DEC-2012 00:52:13   Tracing not turned on.   Tns error struct:     ns main err code: 12564     TNS-12564: TNS:connection refused     ns secondary err code: 0     nt main err code: 0     nt secondary err code: 0     nt OS err code: 0 *********************************************************************** Fatal NI connect error 12547, connecting to: (LOCAL=NO)   VERSION INFORMATION:         TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production         TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production         Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production   Time: 08-DEC-2012 14:03:35   Tracing not turned on.   Tns error struct:     ns main err code: 12547     TNS-12547: TNS:lost contact     ns secondary err code: 12560     nt main err code: 0     nt secondary err code: 0     nt OS err code: 0

参数配置：

*._PX_use_large_pool=true
*._enable_NUMA_optimization=false
*._gby_hash_aggregation_enabled=false
*._gc_policy_time=0
*._optim_peek_user_binds=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/oracle/app/oracle/admin/shzw/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='/oradata04/ctl/control02.ctl','/oradata03/ctl/control01.ctl'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_cache_size=32G
*.db_file_multiblock_read_count=128
*.db_files=8192
*.db_name='shzw'
*.db_securefile='PERMITTED'
*.diagnostic_dest='/oracle/app/oracle'
*.fast_start_mttr_target=300
shzw1.instance_number=1
shzw2.instance_number=2
*.job_queue_processes=64
*.large_pool_size=512M
*.lock_sga=FALSE
shzw1.log_archive_dest_1='LOCATION=/shzwarch1/archivelog'
shzw2.log_archive_dest_1='LOCATION=/shzwarch2/archivelog'
*.log_archive_format='arch_%t_%s_%r.arc'
*.max_dump_file_size='UNLIMITED'
*.open_cursors=1000
*.open_links=8
*.parallel_adaptive_multi_user=TRUE
*.parallel_execution_message_size=16384
*.parallel_force_local=true
*.parallel_max_servers=512
*.pga_aggregate_target=16G
*.processes=15000
*.recyclebin='off'
*.remote_login_passwordfile='none'
*.resource_limit=true
*.session_cached_cursors=20
*.sessions=16505
*.sga_max_size=96G
*.sga_target=0
*.shared_pool_reserved_size=208M
*.shared_pool_size=8G
shzw1.thread=1
shzw2.thread=2
*.undo_retention=10800
shzw1.undo_tablespace='undotbs1'
shzw2.undo_tablespace='undotbs2'

sqlnet.ora内容：

# sqlnet.ora.p750703a Network Configuration File: /oracle/11.2.0/grid/network/admin/sqlnet.ora.p750703a
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /oracle/app/oracle
DIAG_ADR_ENABLED = OFF
#SQLNET.INBOUND_CONNECT_TIMEOUT=0

Liu Maclean(刘相兵

上传完整的alert.log 和 问题发生时段的 AWR

ccboceb7

刘大， 日志文件 和 awr报告我上传到原贴子中的附件中 了，

Liu Maclean(刘相兵

        Snap Id        Snap Time        Sessions        Cursors/Session
Begin Snap:        1880        07-Dec-12 14:00:26        2437         18.8
End Snap:        1882        07-Dec-12 16:00:06        2750         12.2
Elapsed:                  119.66 (mins)                  
DB Time:                  5,159.71 (mins)                  



Elapsed Time (s)        Executions        Elapsed Time per Exec (s)        %Total        %CPU        %IO         SQL Id        SQL Module        SQL Text
14,196.22        31,038,377        0.00        4.59        3.73        0.00        atrnd612k84mz         JefOrm@QZ-ZGAPP06        begin dbms_utility.get_tz_tran...



最耗时的SQL语句是 atrnd612k84mz        begin dbms_utility.get_tz_transitions(:1, :2); end;

The JDBC driver executes dbms_utility.get_tz_transitions() to retrieve data necessary for timezone conversion. Normally this happens only once per session, because the driver caches the results. If the call to get_tz_transitions() fails, then there will be no results to cache, and the driver will contine to call this method, in the worst case as part of every JDBC API call.

The more common reason for get_tz_transitions() to fail is that the JVM timezone is unknown to the database. In this case, however, the consequences are potentially more severe, because the database server will attempt to parse the method on each call. Naturally this will fail, but the database server does not rememer that it failed, and will attempt to perform a hard parse operation on every call. If client load is substantial, the problem can be enough to make the database server inoperable.


这个语句 用来查询TZ信息，但是如此多的 调用次数 和性能损耗 不是很正常。

ccboceb7

嗯 ，关于这个语句（begin dbms_utility.get_tz_transitions(:1, :2); end;）
正在查找原因，那关于ora-609的错误 估计也jdbc这边的问题，实在不好解决啊

qcbz

数据库压力过大，也会导致 609的错误