1.CJQ进程不存在
2.模拟问题处理
3.问题总结
一、问题现象
CJQ0进程不存在
[root@adg1 ~]# ps -ef|
grep cjq
root 4741 4675 0 11:
36 pts/
6 00:
00:
00 grep cjq
[oracle@adg1 ~]$ sqlplus /
as sysdba
SQL*Plus: Release
11.2.
0.4.
0 Production on Sun Jul
28 11:
35:
04 2019
SQL>
show parameter job_que
NAME 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;
begin
dbms_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_Alert
Sun Jul 28 11:
42:
21 2019
Starting background process CJQ0
Sun Jul 28 11:
42:
21 2019
CJQ0 started with pid=
31, OS id=
4851
[root@adg1 ~]# ps -ef|
grep cjq
oracle 4851 1 0 11:
42 ?
00:
00:
00 ora_cjq0_tt
root 4855 4675 0 11:
42 pts/
6 00:
00:
00 grep cjq
SQL> alter session
set nls_date_format=
‘yyyy-mm-dd hh24:mi:ss‘;
SQL>
select *
from tt ;
C_DATE
-------------------
2019-
07-
28 13:
58:
59
2019-
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;
0
SQL>
select *
from tt ;
C_DATE
-------------------
2019-
07-
28 13:
58:
59
2019-
07-
28 13:
59:
39
SQL>
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 selected
SQL> 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 cjq
oracle 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_Alert
Sun Jul 28 11:
36:
56 2019
ALTER SYSTEM SET job_queue_processes=
0 SCOPE=
BOTH;
Sun Jul 28 11:
37:
26 2019
ALTER SYSTEM SET job_queue_processes=
200 SCOPE=
BOTH;
[root@adg1 ~]# ps -ef|
grep cjq
root 4817 4675 0 11:
37 pts/
6 00:
00:
00 grep cjq
通过重置job_queue_process参数并未达到启动cjq进程的目的
2.3 查询sga 内存区域状态，是否为1
SQL>
oradebug setmypid
Statement processed.
SQL>
oradebug dumpvar sga kkjsre
sword kkjsre_ [060040500,
060040504) =
00000001
2.4 修改参数,
event
IF -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.2
SQL> 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权限则无法登陆db
SQL>
select instance_name,logins
from v$instance;
INSTANCE_NAME LOGINS
---------------- ----------
tt RESTRICTED
SQL>
alter system disable restricted session;
System altered.
SQL>
select instance_name,logins
from v$instance;
INSTANCE_NAME LOGINS
---------------- ----------
tt ALLOWED
SQL>
select *
from tt ;
C_DATE
-------------------
2019-
07-
28 13:
58:
59
2019-
07-
28 13:
59:
39
2019-
07-
28 14:
09:
52
2019-
07-
28 14:
10:
52
2019-
07-
28 14:
11:
52
2019-
07-
28 14:
12:
52
6 rows selected.
SQL>
truncate table tt;
Table truncated.
Alert 日志,恢复正常情况后，job自动调用执行，自动后台启动cjq进程服务。
Sun Jul 28 22:
28:
24 2019
Restarting dead background process CJQ0
Sun Jul 28 22:
28:
24 2019
CJQ0 started with pid=
20, OS id=
11444
SQL> alter system
set job_queue_processes=
0;
Sun Jul 28 22:
34:
11 2019
Stopping background process CJQ0
Sun Jul 28 22:
34:
11 2019
ALTER SYSTEM SET job_queue_processes=
0 SCOPE=
BOTH;
SQL> alter system
set job_queue_processes=
200;
[oracle@adg1 ~]$ ps -ef|
grep cjq
oracle 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);
oracle_job进程相关学习测试
标签：进程 session product 没有 ica proc 查询日志 存在 影响
