Oracle cjq0进程测试测试流程：1.CJQ进程不存在2.模拟问题处理3.问题总结一、问题现象CJQ0进程不存在[root@adg1 ~]# ps -ef|grep cjqroot      4741  4675  0 11:36 pts/6    00:00:00 grep cjq[oracle@adg1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 28 11:35:04 2019SQL> show parameter job_queNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------job_queue_processes                  integer     100二、模拟问题处理1.JOB创建1)创建JOB测试https://www.cnblogs.com/lijiasnong/p/3382578.html--创建表--truncate table TT;SQL> GRANT EXECUTE ON DBMS_JOB TO yz;create table TT(C_DATE DATE); create or replace procedure PRO_TT is begin  insert into TT values(sysdate);  commit; end; /exec PRO_TT;SQL> select * from tt;declare   job number;      begindbms_job.submit(job,'PRO_TT;',sysdate,'sysdate+1/1440');end;/--begin  dbms_job.remove(32);  commit;end;/--SQL> select JOB,WHAT,BROKEN from user_jobs;       JOB WHAT                                                          B---------- ----------------------------------------------------------------- -        34 PRO_TT;                                                       N--dbms_job.broken(23,false,sysdate); 第一次执行job        begin dbms_job.run(34);end;/DB_AlertSun Jul 28 11:42:21 2019Starting background process CJQ0Sun Jul 28 11:42:21 2019CJQ0 started with pid=31, OS id=4851[root@adg1 ~]# ps -ef|grep cjqoracle    4851     1  0 11:42 ?        00:00:00 ora_cjq0_ttroot      4855  4675  0 11:42 pts/6    00:00:00 grep cjqSQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';SQL> select * from tt ;C_DATE-------------------2019-07-28 13:58:592019-07-28 13:59:39--上述操作是，创建一个JOB,并测试手工调用执行存储过程及手工执行JOB均有效，会自动插入一条测试数据。正常情况下,JOB是间隔1分钟，插入一条记录select job,       log_user,       to_char(last_date,'yyyy-mm-dd hh24:mi:ss') last_date,       to_char(next_date,'yyyy-mm-dd hh24:mi:ss') next_date,       interval,       what,       FAILURES  from user_jobs where job=34/       JOB LOG_USER        LAST_DATE           NEXT_DATE              INTERVAL             WHAT                   FAILURES---------- ---------- ------------------- ------------------- -------------------- -------------------- ----------        34 YZ           2019-07-28 13:59:39 2019-07-28 14:00:39      sysdate+1/1440       PRO_TT;                  0SQL> select * from tt ;C_DATE-------------------2019-07-28 13:58:592019-07-28 13:59:39SQL> select sysdate from dual;SYSDATE-------------------2019-07-28 13:25:34上述验证，发现JOB并未自动调用执行？？？2)JOB异常,问题排查2.1 查询日志或者进程信息db alert无异常，job无异常SQL> select * from dba_jobs_running;no rows selectedSQL> SELECT USERNAME,PNAME,PROGRAM,TRACEFILE from v$process where PNAME like '%CJQ%';USERNAME        PNAME PROGRAM                                          TRACEFILE--------------- ----- -------------------------------------------------------------------------------------------------oracle          CJQ0  oracle@adg1 (CJQ0)                               /u01/app/oracle/diag/rdbms/tt/tt/trace/tt_cjq0_4851.trc SQL> select * from v$bgprocess where name='CJQ0';PADDR              PSERIAL# NAME  DESCRIPTION                ERROR---------------- ---------- ----- ---------------------------- ------00000000F552A288          3 CJQ0  Job Queue Coordinator        0[oracle@adg1 ~]$ ps -ef|grep cjqoracle   10884  9146  0 21:37 pts/2    00:00:00 grep cjq本次环境发现cjq进程不存在！！！Jobs Not Executing Automatically (文档 ID 313102.1)    2.2 重置参数SQL> alter system set job_queue_processes=0;SQL> alter system set job_queue_processes=200;DB_AlertSun Jul 28 11:36:56 2019ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;Sun Jul 28 11:37:26 2019ALTER SYSTEM SET job_queue_processes=200 SCOPE=BOTH;[root@adg1 ~]# ps -ef|grep cjqroot      4817  4675  0 11:37 pts/6    00:00:00 grep cjq通过重置job_queue_process参数并未达到启动cjq进程的目的2.3 查询sga 内存区域状态，是否为1SQL> oradebug setmypidStatement processed.SQL> oradebug dumpvar sga kkjsresword kkjsre_ [060040500, 060040504) = 00000001       2.4 修改参数,eventIF -Jobs Do Not Run Automatically as CJQ is Not Getting Started when Jobs are to be Run (文档 ID 2081753.1)     Upgraded from 10.2.0.4 to 11.2.0.2SQL> alter system set events '10195 trace name context off';2.5 手工调用执行Job无异常begin dbms_job.run(34);end;/但是进程还是未启动2.6 终于发现问题测试环境JOB，无法自动调用，根据文档排查,测试环境处于受限模式http://blog.itpub.net/29487349/viewspace-1663945/受限模式,配置后，已连接session不影响，新的session无restricted session权限则无法登陆dbSQL> select instance_name,logins from v$instance;INSTANCE_NAME    LOGINS---------------- ----------tt               RESTRICTEDSQL> alter system disable restricted session;System altered.SQL> select instance_name,logins from v$instance;INSTANCE_NAME    LOGINS---------------- ----------tt               ALLOWEDSQL> select * from tt ;C_DATE-------------------2019-07-28 13:58:592019-07-28 13:59:392019-07-28 14:09:522019-07-28 14:10:522019-07-28 14:11:522019-07-28 14:12:526 rows selected.SQL> truncate table tt;Table truncated.Alert 日志,恢复正常情况后，job自动调用执行，自动后台启动cjq进程服务。Sun Jul 28 22:28:24 2019Restarting dead background process CJQ0Sun Jul 28 22:28:24 2019CJQ0 started with pid=20, OS id=11444 SQL> alter system set job_queue_processes=0;Sun Jul 28 22:34:11 2019Stopping background process CJQ0Sun Jul 28 22:34:11 2019ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;SQL> alter system set job_queue_processes=200;[oracle@adg1 ~]$ ps -ef|grep cjqoracle   11545  9146  0 22:34 pts/2    00:00:00 grep cjq总结：1.修改job_queue_processes,从>0修改为0，并且cjq0进程存在，则会kill cjq0进程，并且后续的job无法自动调用执行2.修改参数job_queue_processes >0,无法立即启动cjq0进程，而需要配合Job定时调用一个工作，会先启动cjq0进程(如果参数为0,则无法启动)3.手工执行调用存储过程，job id 并非能够立即启动cjq0进程，修改events '10195 trace name context off'只是解决， 存在CJQ进程或者10g升级至11g job无法自动执行问题处理;4.如果数据库连接状态为RESTRICTED 受限模式，如果没有相应权限，则JOB无法自动执行;5.如果cjq进程存在，但是job并不能自动调用执行，并且sga内存区域不为1 00000001，则需要重启或者根据mos Jobs do not execute automatically (Doc ID 309945.1)进行处理SQL> exec  dbms_ijob.set_enabled(true);