Solved

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

Posted on 2008-06-15
3
2,384 Views
Last Modified: 2013-12-20
Hay Experts,
         I am working on a distributed environment. This is a web application where the UI is designed using Cold Fusion 5.0. ColdFusion interacts with a local oracle 9i database. All major tables are in a remote db which is in  
Oracle 10g. We are using private dblink to connect from 9i to 10g.
        This works fine, but sometimes this gives us ORA-12500: TNS:listener failed to start a dedicated server process. We are not able to figure out the reason.
        Initially we thought may be the fetching is starting a lot of process. SO started monitoring, but the count is very less.

       NOTE: To fetch data ColdFusion calls a stored procedure in 9i, which in turn calls the views present in 10g.  
The process is initiated by ColdFusion.

Details:
Local database: oracle 9.2.0.6
Remote database: oracle 10.2
 Coldfusion version:5.0
 Thanks In Advance.
Ragards,
Plaban Patra
0
Comment
Question by:Plaban_Patra
  • 2
3 Comments
 
LVL 23

Expert Comment

by:David
ID: 21789685
The most common cause of a ORA-12500 is a busy system whereby PGA RAM is exhausted.  The solutions are:

Reduce sort_area_size (pga_aggregate_target) and hash_area_size to make each default PGA size smaller.

Add additional server RAM
0
 
LVL 23

Expert Comment

by:David
ID: 21797329
May I be of further assistance?
0
 
LVL 1

Accepted Solution

by:
Plaban_Patra earned 0 total points
ID: 21812431
Hi DVZ,
We have found the solution after a hard day. This is because some of the tables in remote db being defined with 'parallel (degree default instances default)', which forces parallel query. By executing the queries, what we see is sessions generated by parallel query in this database. We removed the parallel option, and now the system is running fine.
Thanks for your assistance.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question