Cjq background process issue

From: Sweetser, Joe <JSweetser_at_icat.com>
Date: Thu, 13 Dec 2007 10:15:00 -0700
Message-ID: <E323160E08E560459CD05A883546C3CE0B134B91@earthquake.ICAT.COM>

Oracle 9.2.0.6
RH AS 2.1

I have a strange situation where the CJQ process is not running but there is an entry in v$process that seems to indicate that the database thinks the process is there. This was discovered because a materialized view was not being automatically refreshed.

To wit:

SQL> connect / as sysdba;
Connected.
SQL> show parameter job_queue_process

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
job_queue_processes                  integer     1

SQL> !ps -ef | grep ora_

(don't see it here)

oracle   28815     1 11 Jul17 ?        17-01:25:04 ora_pmon_tiros
oracle   28817     1  0 Jul17 ?        00:36:43 ora_dbw0_tiros
oracle   28819     1  0 Jul17 ?        00:38:24 ora_dbw1_tiros
oracle   28821     1  0 Jul17 ?        01:56:39 ora_lgwr_tiros
oracle   28823     1  0 Jul17 ?        00:22:31 ora_ckpt_tiros
oracle   28825     1  0 Jul17 ?        00:13:06 ora_smon_tiros
oracle   28827     1  0 Jul17 ?        00:00:00 ora_reco_tiros
oracle   28831     1  0 Jul17 ?        00:00:09 ora_qmn0_tiros
oracle   28833     1  0 Jul17 ?        00:42:13 ora_arc0_tiros
oracle   28835     1  0 Jul17 ?        00:42:08 ora_arc1_tiros
oracle   28838     1  0 Jul17 ?        00:00:00 ora_p000_tiros
oracle   28840     1  0 Jul17 ?        00:00:00 ora_p001_tiros

oracle 5795 5526 0 10:04 pts/2 00:00:00 /bin/ksh -c ps -ef | grep ora_
oracle 5797 5795 0 10:04 pts/2 00:00:00 grep ora_

SQL> select b.spid
  2 from v$bgprocess a, v$process b
  3 where a.name = 'CJQ0' and
  4 a.paddr = b.addr;

(however, the database returns a spid for it)

SPID

---

9710

SQL> !ps -ef | grep 9710

(but that process doesn't exist at the Linux level)

oracle 5958 5526 0 10:06 pts/2 00:00:00 /bin/ksh -c ps -ef | grep 9710
oracle 5960 5958 0 10:06 pts/2 00:00:00 grep 9710

SQL> Is there a way to correct this without restarting the database? Is it safe to delete the entry out
of v$process? I would think not but I don't know how else to try and convince the database that the CJQ0 process is really not there. I have tried setting job_queue_processes=0, waiting 5 minutes, and resetting it to 1 per Metalink 228059.1.

Thanks,
-joe  

Confidentiality Note: This message contains information that may be confidential and/or privileged. If you are not the intended recipient, you should not use, copy, disclose, distribute or take any action based on this message. If you have received this message in error, please advise the sender immediately by reply email and delete this message. Although ICAT Managers, LLC scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. Thank you.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 13 2007 - 11:15:00 CST