Failed to connect to database occasionally

Today I got a phone call from a customer complaining that they occasionally failed to connect to production database.

I logged into the database as sysdba and everything seems just fine until suddenly I got this error:

SQL> select * from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-01012: not logged on

Then I tried to log on again, things return back to normal


SQL> conn / as sysdba

SQL> select STATUS, ACTIVE_STATE, DATABASE_STATUS from v$instance;

STATUS       ACTIVE_ST DATABASE_STATUS
------------ --------- -----------------
OPEN         NORMAL    ACTIVE

Then I tried to log on again, things return back to normal.

In the alert.log it appeared the following error message for quite a few times:


Process J000 died, see its trace file
Thu Jan 20 10:56:29 CET 2011
kkjcre1p: unable to spawn jobq slave process

It seemed the limit of maximum processes or jobs in queue was reached.

SQL> show parameter processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     150

SQL> select * from v$resource_limit where resource_name = 'processes';

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes                                      147              149        150        150

It proves that the limit of concurrent running processes was reached which caused new connections were refused from time to time.

I restarted App. Server, Grid control, database and listeners and reset parameter “processes” to a higher value, until then the database connection problem was solved.

According to metalink notes
Note:416244.1 – ORA-00610 AND “unable to spawn jobq slave process ” IN THE ALERT LOG.
Note:344275.1 – Kkjcre1p: Unable To Spawn Jobq Slave Process, Error 1089

the “ORA-01012: not logged on” can be also result of lack of some resources like Swap, processes limit on OS level, etc. Record here for reference.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: