ora-12500错误

昨天，一个客户打电话过来，说数据库连接不了，报ora-12500错误：

1)查询官方文档对此错的描述：

TNS-12500 TNS:listener failed to start a dedicated server process

Cause: The process of starting up a dedicated server process failed. The executable could not be found or the environment may be set up incorrectly.

Action: Turn on tracing at the ADMIN level and re-execute the operation. Verify that the Oracle Server executable is present and has execute permissions enabled. Ensure that the Oracle environment is specified correctly in LISTENER.ORA. The Oracle Protocol Adapter that is being called may not be installed on the local hard drive. Check that the correct Protocol Adapter are successfully linked. If the error persists, contact Oracle Support Services.

用户的环境为windows 2003 server + oracle9208,查询相关参数：

18 processes 450
20 sessions 500
49 sga_max_size 1889086836
48 shared_pool_size 318767104
274 db_cache_size 1073741824
56 large_pool_size 8388608
58 java_pool_size 33554432
769 pga_aggregate_target 500000000

2)查看oracle的位数，发现是32bit的：

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

3)询问过客户，应该不是连接的session过大造成的，我估计是windows 32bit对oracle可使用的内存的限制(sga<1.7g,all <2g)造成的错误。

4)查看alert日志

发现没有任何记录，实例已经down掉了。

5)查看oracle.exe进程

发现只使用了56M内存，此时说明oracle确实已经down了

6)让客户重启操作系统，重启数据库

7)然后修改了一些sga,pga参数，目的是让sga<1.7g,sga + pga < 2g:

alter system set sga_max_size=1300M scope=spfile;

alter system set db_cache_size=700M scope=spfile;

alter system set shared_pool_size=250M scope=spfile;

alter system set pga_max_size=350M scope=spfile;

shutdown immediate

startup

目前数据库恢复正常，不过还有待观察是否还有down掉的情况。

另外一种方法，可以修改windows和oracle的某些设置来使得orace可以使用超过2g内存，但当时时间不允许，而且该库负载不重，且也不是生产库，所以就调小内存了事。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8861952/viewspace-1034698/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8861952/viewspace-1034698/