数据库服务器:win2003 sp2系统,物理内存4G
系统数据库服务器如上配置,oracle10数据库,ERP系统运行一直很正常。最近做了一个外挂的asp应用,就是从oracle数据库读取一些数据信息在页面展示,使用还是比较频繁的,自动加了这个asp应用后,asp应用经常提示ORA-12158的错误,也导致ERP系统无法正常运行,每次都是重启oracle服务。
按网上关于12158的处理尝试了,将process和session都调大了,但是还是不行,到底是什么问题啊。正常时的一些参数SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 1000
SQL> show parameter sessionsNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 1105
shared_server_sessions integer
SQL> select count(*) from v$process;
COUNT(*)
----------
234
SQL> select value from v$parameter where name='processes';
VALUE
--------------------------------------------------------------------------------1000
SQL> select count(*) from v$session;
COUNT(*)
----------
230
SQL> select value from v$parameter where name='sessions';
VALUE
--------------------------------------------------------------------------------1105
SQL> select name,busy,status,accept,idle from v$dispatcher;
NAME BUSY STATUS ACC IDLE
---- ---------- ---------------- --- ----------
D000 0 WAIT YES 70841SQL> select name,(busy/(busy+idle))*100 "busy rate%" from v$dispatcher;
NAME busy rate%
---- ----------
D000 0SQL> show parameters dispatchers;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=orclXD
B)
max_dispatchers integerSQL> show sga
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 159386500 bytes
Database Buffers 444596224 bytes
Redo Buffers 7135232 bytesSQL> show parameter sga_max
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
sga_max_size big integer 584M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
pga_aggregate_target big integer 194MSQL> select pool,sum(bytes) from v$sgastat group by pool;
POOL SUM(BYTES)
------------ ----------
452981884
java pool 4194304
shared pool 144365768
large pool 4194304SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TRUE
db_block_size integer 8192
系统数据库服务器如上配置,oracle10数据库,ERP系统运行一直很正常。最近做了一个外挂的asp应用,就是从oracle数据库读取一些数据信息在页面展示,使用还是比较频繁的,自动加了这个asp应用后,asp应用经常提示ORA-12158的错误,也导致ERP系统无法正常运行,每次都是重启oracle服务。
按网上关于12158的处理尝试了,将process和session都调大了,但是还是不行,到底是什么问题啊。正常时的一些参数SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 1000
SQL> show parameter sessionsNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 1105
shared_server_sessions integer
SQL> select count(*) from v$process;
COUNT(*)
----------
234
SQL> select value from v$parameter where name='processes';
VALUE
--------------------------------------------------------------------------------1000
SQL> select count(*) from v$session;
COUNT(*)
----------
230
SQL> select value from v$parameter where name='sessions';
VALUE
--------------------------------------------------------------------------------1105
SQL> select name,busy,status,accept,idle from v$dispatcher;
NAME BUSY STATUS ACC IDLE
---- ---------- ---------------- --- ----------
D000 0 WAIT YES 70841SQL> select name,(busy/(busy+idle))*100 "busy rate%" from v$dispatcher;
NAME busy rate%
---- ----------
D000 0SQL> show parameters dispatchers;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=orclXD
B)
max_dispatchers integerSQL> show sga
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 159386500 bytes
Database Buffers 444596224 bytes
Redo Buffers 7135232 bytesSQL> show parameter sga_max
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
sga_max_size big integer 584M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
pga_aggregate_target big integer 194MSQL> select pool,sum(bytes) from v$sgastat group by pool;
POOL SUM(BYTES)
------------ ----------
452981884
java pool 4194304
shared pool 144365768
large pool 4194304SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TRUE
db_block_size integer 8192
解决方案 »
- 查询与统计的数目不一样
- oracle 中 tablespace ,segment,extent,block 关系是什么样的
- 不显示删除回复显示所有回复显示星级回复显示得分回复 client/appServer/database架构,两客户端更新同一条数据同步问题。。。
- 请大家指教rownum的具体用法
- 将一个表中的部分列值拷到另一个表的对应列中,这个sql命令该如何写?
- SQL语句
- 求书?
- 大家看一下,新手高手勿喷
- 求oracle的安装包,电脑是64位,window10系统,跪谢!
- 关于oracle11g空表无法导出的问题
- Oracle游标的性能优化
- ORACLE11使用IMPDP导入出错,系统无法找到指定文件
检查下内存,很可能内存吃紧。如果是32位的系统,只有3G左右内存可用,1G分给系统,剩下2G,扣掉SGA,还要留一部分给其他应用,以及数据库连接。每个连接也要占一定内存
ORA-12537 / TNS-12537: connection closed
ORA-3135 / TNS-3135 : connection lost contact
ORA-3113 / TNS-3113 : end-of-file on communication channel
ORA-3106 / TNS-3106 : fatal two-task communication protocol error
ORA -3136 / TNS-3136 : WARNING inbound connection timed out
ORA-12535 / TNS-12535: TNS:operation timed out
ORA-12170 / TNS-12170: Connect timeout occurred
ORA-12637 / TNS-12637: Text: Packet receive failedCHANGESmemory consumption and/or high loadCAUSEIn general ORA-12518 occurs if there is a resource problem , may be on the listener side or on the Database server.It indicates a condition where the process is not able to make any more memory allocations either due to non-availability of resource or the process has reached the 32 bit (VM Size) limitSOLUTION1- Timer issues causing abnormal threads :
Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an approprate value for the application.
From Oracle base version 10.2.0.1 onwards the default value of INBOUND_CONNECT_TIMEOUT_<listenername> is 60 seconds. For previous releases it is zero by default.a) Please add following parameter in listener.ora
INBOUND_CONNECT_TIMEOUT_<listener_name> = 0For example, if the listener name is listenerTest, then:
INBOUND_CONNECT_TIMEOUT_listenerTest = 0ANDb) Add the following parameter to the sqlnet.ora file on the Database server
side:
SQLNET.INBOUND_CONNECT_TIMEOUT = 0The listener and database will need to be restarted after these changes.These parameters are set on the SERVER side:
listener.ora: INBOUND_CONNECT_TIMEOUT_listenername
sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUTNote: This timeout restriction was introduced to combat Denial of Service (DoS) attack whereby malicious clients attempt to flood database servers with connect requests that consumes resources.Fore more info/setting on inbound connection time out, check the following note :
Note 465043.1 - Troubleshooting ORA - 3136 WARNING Inbound Connection Timed Out2 - Lack of available memory :
Increase memory and swap in the system3 - Less of processes in the database,:
Increase processes parameter if using dedicated connexion (init.ora / spfile file)
In unix, adjust resource limits to increase the number of max processes allowed. 4 - Set /3GB option :
Switch in the BOOT.INI file to enable the 4GB feature.
This allows a process to address 3GB and reserving 1GB for the kernel. 5- Tune SGA / shared_pool_size :
Reduce the SGA size to a reasonable figure which allows the user process to have enough memory to run.
Decrease the size of the database SGA by reducing the PGA_AGGREGATE_TARGET or SGA_MAX_SIZE or both.
The amount of memory for a session depends upon PGA_AGGREGATE_TARGET.
If PGA_AGGREGATE_TARGET is set to a very small value ,Oracle session may exceed this value.6 - Set or Tune MTS :
Configure MTS or increase the number of DISPATCHERS (if Shared Server is being used).7- If the listener crash with a core dump and the ora-12518 appears in the log, then you are probably hitting Bug 6139856 due to heavy load or out of memory.For more detail and solutions, see :
Note 549932.1 - TNS Listener Crash with Core dump
java pool 4194304
shared pool 144365768
large pool 4194304这几个参数设置的合理吗?