系统环境:WIN2000 Advanced Server, oracle9i, 3G内存C/S模式财务系统会话数达到400时,再有客户端登录时提示12500错误无法登录,已登录的用户使用没有问题参数设置如下:
aq_tm_processes = 1
background_dump_dest = D:\oracle\admin\ORADB\bdump
bitmap_merge_area_size = 524288
compatible = 9.2.0.0.0
control_files = ('D:\oracle\oradata\ORADB\CONTROL01.CTL', 'D:\oracle\oradata\ORADB\CONTROL02.CTL', 'D:\oracle\oradata\ORADB\CONTROL03.CTL')
core_dump_dest = D:\oracle\admin\ORADB\cdump
create_bitmap_area_size = 524288
db_block_size = 8192
db_cache_size = 528482304
db_domain = ''
db_file_multiblock_read_count = 16
db_name = ORADB
dispatchers = '(PROTOCOL=TCP) (SERVICE=ORADBXDB)'
dml_locks = 1860
enqueue_resources = 2080
fast_start_mttr_target = 300
hash_join_enabled = TRUE
instance_name = ORADB
java_pool_size = 33554432
job_queue_processes = 10
large_pool_size = 25165824
log_archive_dest_1 = 'LOCATION=D:\oracle\ora92\RDBMS'
max_rollback_segments = 93
open_cursors = 300
pga_aggregate_target = 367001600
processes = 500
query_rewrite_enabled = FALSE
remote_login_passwordfile = EXCLUSIVE
resource_limit = TRUE
sessions = 800
sga_max_size = 1074864892
shared_pool_reserved_size = 41943040
shared_pool_size = 419430400
sort_area_size = 65536
star_transformation_enabled = FALSE
timed_statistics = TRUE
undo_management = AUTO
undo_retention = 10800
undo_tablespace = UNDOTBS1
user_dump_dest = D:\oracle\admin\ORADB\udump请大家帮忙看下怎样调整设置能够增加可用的会话数
aq_tm_processes = 1
background_dump_dest = D:\oracle\admin\ORADB\bdump
bitmap_merge_area_size = 524288
compatible = 9.2.0.0.0
control_files = ('D:\oracle\oradata\ORADB\CONTROL01.CTL', 'D:\oracle\oradata\ORADB\CONTROL02.CTL', 'D:\oracle\oradata\ORADB\CONTROL03.CTL')
core_dump_dest = D:\oracle\admin\ORADB\cdump
create_bitmap_area_size = 524288
db_block_size = 8192
db_cache_size = 528482304
db_domain = ''
db_file_multiblock_read_count = 16
db_name = ORADB
dispatchers = '(PROTOCOL=TCP) (SERVICE=ORADBXDB)'
dml_locks = 1860
enqueue_resources = 2080
fast_start_mttr_target = 300
hash_join_enabled = TRUE
instance_name = ORADB
java_pool_size = 33554432
job_queue_processes = 10
large_pool_size = 25165824
log_archive_dest_1 = 'LOCATION=D:\oracle\ora92\RDBMS'
max_rollback_segments = 93
open_cursors = 300
pga_aggregate_target = 367001600
processes = 500
query_rewrite_enabled = FALSE
remote_login_passwordfile = EXCLUSIVE
resource_limit = TRUE
sessions = 800
sga_max_size = 1074864892
shared_pool_reserved_size = 41943040
shared_pool_size = 419430400
sort_area_size = 65536
star_transformation_enabled = FALSE
timed_statistics = TRUE
undo_management = AUTO
undo_retention = 10800
undo_tablespace = UNDOTBS1
user_dump_dest = D:\oracle\admin\ORADB\udump请大家帮忙看下怎样调整设置能够增加可用的会话数
Finding which resource is depleted may be difficult.
1. The TNS-12500 can be a result of the 'processes' parameter in the init<sid>.ora file being too low.
2. If the init.ora 'processes' parameter seems ok then you may need to increase the Unix kernel parameters for the maximum number of processes or users (for example, nproc or maxuprc). Check the manuals for the Unix operating system for more information on these parameters. .
3. Check that you have adequate swap space.
4. Disable OTRACE. OTRACE is a tracing feature that can cause many problems. OTRACE is enabled by default. To disable OTRACE: a. Stop the Oracle database. b. Go to the $ORACLE_HOME/otrace/admin directory. c. Delete all files with a '.dat' extension. d. Restart the Oracle database.
5. Use the Multi-Threaded Server (MTS) option. MTS will fix the problem since the listener does not spawn dedicated server processes in this environment. MTS also cuts down on the amount of resources being utilized by having connections threaded through dispatchers and by having shared server processes read dispatcher requests from queues.
To invoke MTS:
a. Stop the database
b. Modify the init<sid>.ora file to include these parameters: mts_dispatchers="<protocol>,<number of dispatchers to create>"
mts_max_dispatchers=<max number of dispatchers>
mts_servers=<number of shared servers to start> mts_max_servers=<max number of shared servers>
mts_service=<SID name>
mts_listener_address=<address of the TNS listener>
An example of a working MTS configuration would be: mts_dispatchers="tcp,3" mts_max_dispatchers=10 mts_servers=2 mts_max_servers=4
mts_service=ORCL
mts_listener_address= "(address=(protocol=tcp)(host=mars1)(port=1521))"
Please note for Oracle 8i the MTS configuration would be:
mts_dispatchers = "(protocol=tcp)(dis=2)"
mts_max_dispatchers = 4
mts_servers = 4
mts_max_servers = 6
local_listener = "(ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=mars1)(PORT=1521)))"
c. Restart the database. For more information on the Multi-Threaded Server option, please see NOTE 16656.1.
文档 ID: 注释:224403.1 类型: BULLETIN
上次修订日期: 24-OCT-2008 状态: PUBLISHED
PURPOSE
-------The purpose of this article is to explain why the 12500 error is raised
and mainly how the tuning of resource in the platform is very important
to solve this issue.
This a short and quick checklist to understand and solve this error.
An ORA/TNS-12540 could be related to 12500 error.
SCOPE & APPLICATION
-------------------Microsoft platforms ( windows machines)
Understanding and Diagnostics for 12500 error on Windows Platform
-----------------------------------------------------------------
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceededThese errors are related to physical usage of Oracle and OS resources :
Memory, Swap, Open files, Open sockets ...ectThe client receive this message error from the listenerThe following actions may help to solve the problem when the error appear
after a longtime running:
- Increase memory and swap in the system
- Configure MTS and DCD
- Tune SGA / shared_pool_size parameter
Reduce the SGA size to a reasonable figure which allows the user
process to have enough memory to run.
- Increase processes parameter in the init.ora file
- Restart Listener/DB
- Check for any memory leak
- Tune microsoft TCP parameters in :
HKEY_LOCAL_MACHINE\System\CurrectControlSet\services\Tcpip\Parameters
TcpTimedWaitDelay, MaxUserPortData and TcpMaxDataRetransmissions
- Try to set the /3GB switch in the BOOT.INI file to enable the 4GB feature.
Allowing a process to address 3GB and reserving 1GB for the kernel.
- Install the latest Windows Service Pack If the error is persistent :
- Check the user who start the listener privileges.
- Check Oracle_Home and Oracle_SID
- The Oracle Service may be corrupt : Recreate The Oracle service by oradim command
RELATED DOCUMENTS
-----------------
Note 46001.1 Oracle Database and the Windows NT memory architecture
Note 171636.1 TNS-12500 TNS-12540 TNS-12560 TNS 510 Windows Error 8: Exec Format Error
Note 108180.1 Intermitent TNS-12540 Errors When Trying to Connect to oracle
Note 2064864.102 Troubleshooting TNS-12500 On Microsoft Windows NT
Note 151972.1 Dead Connection Detection (DCD)
create 'pfile' from 'spfile'修改表态参数文件,,processes = ...然后shutdown immediate
create 'spfile' from 'pfile'startup open spfile=' '