scheduler 异常卡死

am196

系统oracle linux 6.3
数据库版本11.2.0.3.0
JOB_STYLE   JOB_NAME                       SESSION_ID SLAVE_PROCESS_ID ELAPSED_TIME                            CPU_USED                                    LOG_ID
----------- ------------------------------ ---------- ---------------- --------------------------------------- --------------------------------------- ----------
REGULAR     YS_INVENTORY_DTSDATAFROM54DB         1974               68 +000 00:00:00.08                        +000 00:00:00.04                        52558.0000
REGULAR     JOB_REFRESHDATA                       756              106 +000 00:15:21.06                        +000 00:01:11.60                        52546.0000
REGULAR     YS_OTHER_IMPORTDATAFROM54DB                                +001 04:38:26.72                                                                50944.0000


下面为异常的job信息.
没有session_id,也没有SLAVE_PROCESS_ID,在不重启数据库的情况下.不知道该如何把job终止掉.
附件为2个节点的alert.log

不能用dbms_scheduler.stop_job,
alter system kill session 'sid,serial#' immediate;
kill -9 pid
等方法结束掉该进程.

Maclean Liu(刘相兵

qlplus / as sysdba
spool someinfo.txt
set pages 10000
set lines 1000
select * from dba_jobs;
select * from dba_jobs_running;

select * frm dba_scheduler_jobs;
select * from DBA_SCHEDULER_JOB_LOG;
select * from DBA_SCHEDULER_JOB_RUN_DETAILS;
select * from DBA_SCHEDULER_WINDOW_LOG;
select * from DBA_SCHEDULER_WINDOW_DETAILS;

exit

Maclean Liu(刘相兵

你可以观察一下 cjq 和j00x进程的trace是否有 可查之线索

am196

Maclean Liu(刘相兵 发表于 2013-4-20 18:01
你可以观察一下 cjq 和j00x进程的trace是否有 可查之线索

我查找了一下,没有找到线索,看到有被终止过的信息.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1
System name:    Linux
Node name:      chnap-itd65
Release:        2.6.39-200.24.1.el6uek.x86_64
Version:        #1 SMP Sat Jun 23 02:39:07 EDT 2012
Machine:        x86_64
Instance name: dcrac2
Redo thread mounted by this instance: 2
Oracle process number: 91
Unix process pid: 7585, image: oracle@chnap-itd65 (J001)


*** 2013-04-19 13:21:00.223
*** SESSION ID:(321.4769) 2013-04-19 13:21:00.223
*** CLIENT ID:() 2013-04-19 13:21:00.223
*** SERVICE NAME:(SYS$USERS) 2013-04-19 13:21:00.223
*** MODULE NAME:(DBMS_SCHEDULER) 2013-04-19 13:21:00.223
*** ACTION NAME:(YS_OTHER_IMPORTDATAFROM54DB) 2013-04-19 13:21:00.223

error 1013 in job queue process
ORA-01013: user requested cancel of current operation

am196

am196 发表于 2013-4-20 23:15
我查找了一下,没有找到线索,看到有被终止过的信息.
Oracle Database 11g Enterprise Edition Release 11. ...

能不能通过数据字典直接修改该job的状态.

Maclean Liu(刘相兵

execute dbms_scheduler.auto_purge;  

试试

-- The following procedure purges from the logs based on class and global
-- log_history
PROCEDURE auto_purge;