Search This Blog

Tuesday, January 2, 2007

ORA-12520

Oracle Version : 10.1.0.2.0
OS : Windows XP sp2
--------------------------------------------------
SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 50


SQL> conn scott/tiger@test
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server



Clause : Process parameter value is LOW. we have to increase.
on Listner.log file
04-MAR-2007 10:23:38 * service_update * test * 0
04-MAR-2007 10:23:39 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)(CID=(PROGRAM=C:\oracle\product\10.1.0\Db_1\bin\sqlplus.exe)(HOST=0A49914)(USER=m.taj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.64)(PORT=1199)) * establish * test * 12520
TNS-12520: TNS:listener could not find available handler for requested type of server


Lsnrctl services
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:29 refused:0 state:blocked
LOCAL SERVER

Suggestion:

1.You should wait for some minutes and try to connect.
( but it is temporary solution)
2.You should increase PROCESSES parameter values.
( increase PROCESSES parameter and SHUTDOWN + STARTUP DATABASE)








Oracle Version : 10.1.0.2.0
OS : Windows XP sp2
------------------------------------
Database configure in Shared Server Mode.
--------------------------------------------
SQL> show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------dispatchers string (PROTOCOL=TCP)
max_dispatchers integer

SQL> show parameter shared_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------max_shared_servers integer
shared_servers integer 1
------------------------------------------------------------------------------------------------
after change shared server = 0 then i got error "ora-12520".
SQL> alter system set shared_servers = 0;
System altered.

SQL> conn scott/tiger@sh
ERROR:ORA-12520: TNS:listener could not find available handler for requested type ofserver
C:\>lsnrctl services
Service "sh" has 1 instance(s).
Instance "sh", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:3 refused:0 current:2 max:1002 state:blocked
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))

-------------------------------------------------------------------
For above error you have to check configure two parameters.
1.Shared_servers
2.Dispatchers

SQL> alter system set shared_servers = 1;
System altered.
SQL> alter system set dispatchers = "(PROTOCOL=TCP)" ;
System altered.

Service "sh" has 1 instance(s).
Instance "sh", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:4 refused:0 current:2 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))
The command completed successfully

1 comment:

mmxlyg said...
This comment has been removed by a blog administrator.