DBA Tips for Oracle Failure to Spawn!!

Author: Jeff Patrick

If you are a database administrator that have used Oracle release prior to 10g then you must have encountered a tricky situation where your database gets crashed and you can not log into database due to Oracle failure to spawn error (skgpspawn failed). In this article we will discuss some great tips that will help you handle such situation.

When Oracle failure to spawn error is encountered then you get error messages as follows in your alert log.

skgpspawn failed:category = 27143, depinfo = 24, op = pipe, loc = skgpspawn2
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc =skgpspawn3 Article Continues below...

Basically these error messages indicate Oracle failure to spawn a shared server. This error is encountered due to lack of resources on your machine required for the servers to startup. The skgpspawn errors gets translated as follows

You will find failed operating system calls pipe() and fork() in the alert log due to lack of system resources. As a result of lack of system resources the operating system calls pipe() and fork() fail as there is not enough memory and swap available on the system and hence the shared servers are unable to start. If you are running dispatcher (MTS/Shared server) and more shared servers need to be started then they are automatically started up to the maximum number of servers specified in the INIT.ORA file.

A Windows port-specific bug (2441734) causes the setting of SQLNET.EXPIRE_TIME parameter to waste stack memory. As a result of this wastage the total number of connections which can be achieved gets reduced and you get skgpspawn error message. The skgpspawn failed error is fixed in Oracle10g and included in Oracle 9.2.0.3 patchset release. If patching is failed then you should check the parameters even if you haven't made any kernel changes. Oracle MAXUPRC and SHMMAX are dynamic and when you apply a patch and your kernel gets rebuilt then if some system file does not have the values of these parameters then they will be dropped back to their default values. Another solution is to set the SQLNET EXPIRE_TIME parameter to zero or completely remove it in your SQLNET.ORA file.

As we discussed earlier that Oracle failure to spawn a new server is a Windows resource issue. Therefore you need to have enough resources such as at least 500K RAM. Along with it you need to monitor the CPU consumption. Another solution to this problem is to re-boot the server and then start your database and the listener.

Yet there are some other solutions such as to increase the swap or memory or to reduce the number of shared servers that can be started. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   What if your database lock gets blocked??
   SQL in Action I - Single Table Queries
   Exciting Oracle 11g features you should not miss to know!!
   Efficiently handle Tricky Data Guard Failures!!