Job process--------------------------------------------------------------@job.sqlJOB Q PROCESS FAILED OR NOT AND TIME WHEN IT STARTED====================================================set head offset verify offset echo offset pages 1500set linesize 132break on row skip 1prompt Job queue process details run by this process prompt ==============================================promptselect /*+ CHOOSE*/'Server Process Id...................................: ' || p.spid,'Session Id..........................................: ' || b.sid,'Login user..........................................: ' || log_user,'Default schema used to parse the job................: ' || schema_user,'Job Defination......................................: ' || what ,'Job code............................................: ' || job,'Date that this job started executing................: ' || to_char(THIS_DATE,'DD-MON- YYYY:HH24-MI-SS') this_date,'Date on which this job last successfully executed...: ' || to_char(LAST_DATE,'DD-MON-YYYY:HH24-MI-SS') last_date,'Date that this job will next be executed............: ' || to_char(NEXT_DATE,'DD-MON-YYYY:HH24-MI-SS') next_date,'Interval............................................: ' || interval,'Number of times this job has failed ................: ' || failures,'Broken..............................................: ' || brokenfrom dba_jobs a,(select sid,id2 from v$lock a where type='JQ') b,v$process p,v$session swhere b.id2=a.job andb.sid=s.sid andp.addr=s.paddr andp.spid='&SERVER_PROCESS';EUL Open/Locked---------------set head onselect username,account_status from dba_users where username like 'EUL5%';Quary to display all failed jobs=================================set head on col owner for a15 col NEXT_RUN_DATE for a30select OWNER,JOB_NAME,STATE,NEXT_RUN_DATE,RUN_COUNT,FAILURE_COUNT from dba_scheduler_jobs;GSS ( check this)================set head oncol OWNER for a15col LOG_DATE for a30col JOB_NAME for a20select OWNER,LOG_DATE,JOB_NAME,STATUS,ADDITIONAL_INFO from dba_scheduler_job_log where job_name like '&JOB_NAME'order by LOG_DATE desc;GATHER_SYS_STATS_JOB2nd try----------------------------col JOB_NAME for a10col SCHEDULE_NAME for a10select 'JOB_NAME...........................:'|| JOB_NAME, 'SCHEDULE_NAME......................:'||SCHEDULE_NAME, 'STATE..............................:'||STATE, 'FAILURE_COUNT......................:'||FAILURE_COUNT from DBA_SCHEDULER_JOBS where JOB_NAME like '&JOB%'; (enter value for job : action (common query)) 3rd try---------------------------With slave number here the slave number is 3 (J003)======================================SQL> set head off set verify off set echo off set pages 1500 set linesize 132 select 'PROGRAMNAME...............:'||PROGRAM_ACTION, 'MAX_RUN_DURATION..........:'||MAX_RUN_DURATION, 'NEXT_RUN_DATE.............:'||NEXT_RUN_DATE, 'LAST_START_DATE...........:'||LAST_START_DATE, 'FAILURE_COUNT.............:'||FAILURE_COUNT from SCHEDULER$_JOB where RUNNING_SLAVE=&SLAVENUMBER; to check for slaves With slave number here the slave number is 3 (J003)------------------------------------ set head off set verify off set echo off set pages 1500 set linesize 132 select 'PROGRAMNAME...............:'||PROGRAM_ACTION, 'MAX_RUN_DURATION..........:'||MAX_RUN_DURATION, 'NEXT_RUN_DATE.............:'||NEXT_RUN_DATE, 'LAST_START_DATE...........:'||LAST_START_DATE, 'FAILURE_COUNT.............:'||FAILURE_COUNT from SCHEDULER$_JOB where RUNNING_SLAVE=&SLAVENUMBER;EUL Open/Locked----------------------------------------set head onselect username,account_status from dba_users where username like 'EUL5%';Chking the details with program name==========================col JOB_NAME for a10col SCHEDULE_NAME for a10select 'JOB_NAME...........................:'|| JOB_NAME, 'SCHEDULE_NAME......................:'||SCHEDULE_NAME, 'STATE..............................:'||STATE, 'FAILURE_COUNT......................:'||FAILURE_COUNT from DBA_SCHEDULER_JOBS where JOB_NAME like '&JOB%';Enter value for job: AUTO_SPACE_ADVISORselect count(*) from all_jobs;select count(*) from all_jobs;=============================================================set head oncol OWNER for a15col LOG_DATE for a30col JOB_NAME for a20select OWNER,LOG_DATE,JOB_NAME,STATUS,ADDITIONAL_INFO from dba_scheduler_job_log where job_name like '&JOB_NAME'order by LOG_DATE desc;SELECT job,schema_user, broken, failures, what, next_date FROM dba_jobs WHERE broken='Y';FROM GLOBAL PORTAL===================col object_name format a25col object_type format a25col interval format a30SELECT job,schema_user, broken, failures, what, sysdate, next_date FROM all_jobs WHERE broken='Y' OR next_date < sysdate - 5/(60*24);SELECT job,schema_user, broken, failures, what, next_date FROM dba_jobs WHERE broken='N';JOB SCHEMA_USER B FAILUREScol job format a45col SCHEMA_USER format a12col broken format a2col failures format 99IF JOB Process Failed======================Check belowSQL> select instance_name,logins from v$instance;INSTANCE_NAME LOGINS---------------- ----------PCGEO2 ALLOWEDSQL> show parameter jobNAME TYPE VALUE------------------------------------ ----------- ------------------------------job_queue_processes integer 10SQL> select * from dba_jobs_running; SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE---------- ----- ---------- -------------------- -------- -------------------- -------- ---------- 1925 1089 3 15-OCT-2011 16:00:03 16:00:03 16-OCT-2011 16:35:15 16:35:15 0Check alert logfile if that job code failed=============================================Alertlog======Sun Oct 16 19:58:14 2011Completed checkpoint up to RBA [0x5563.2.10], SCN: 6024194784971Sun Oct 16 20:08:33 2011Incremental checkpoint up to RBA [0x5563.11050.0], current log tail at RBA [0x5563.12890.0]Sun Oct 16 20:30:04 2011Errors in file /pcgeo2/log/PCGEO2_ausodcgeo05/bdump/pcgeo2_j001_13200.trc:ORA-12012: error on auto execute of job 1089ORA-00001: unique constraint (SIEBEL.CX_ERP_ASET_CRM_P1) violatedORA-06512: at "SIEBEL.SPI_ERP_SIEBEL_ACTIVOS_CMB3", line 287ORA-06512: at "SIEBEL.SPI_INTERFAZ_SIEBEL_ORACLE", line 55ORA-06512: at "SIEBEL.SPI_ERP_MAINPROPERTY_SBL", line 18ORA-06512: at line 1Checking tracefile--------------------------[AMD64] orpcgeo2@ausodcgeo05 > head -100 /pcgeo2/log/PCGEO2_ausodcgeo05/bdump/pcgeo2_j001_13200.trc/pcgeo2/log/PCGEO2_ausodcgeo05/bdump/pcgeo2_j001_13200.trcOracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining optionsORACLE_HOME = /pcgeo2/oracle/product/102System name: LinuxNode name: ausodcgeo05Release: 2.6.9-78.0.25.0.1.ELsmpVersion: #1 SMP Wed Jul 15 15:12:55 EDT 2009Machine: x86_64Instance name: PCGEO2Redo thread mounted by this instance: 1Oracle process number: 120Unix process pid: 13200, image: oracle@ausodcgeo05 (J001)*** SERVICE NAME:(SYS$USERS) 2011-10-16 20:30:04.100*** SESSION ID:(1925.3471) 2011-10-16 20:30:04.100*** 2011-10-16 20:30:04.100ORA-12012: error on auto execute of job 1089ORA-00001: unique constraint (SIEBEL.CX_ERP_ASET_CRM_P1) violatedORA-06512: at "SIEBEL.SPI_ERP_SIEBEL_ACTIVOS_CMB3", line 287ORA-06512: at "SIEBEL.SPI_INTERFAZ_SIEBEL_ORACLE", line 55ORA-06512: at "SIEBEL.SPI_ERP_MAINPROPERTY_SBL", line 18ORA-06512: at line 1Process 13200 is consuming high cpu resources.It is associated with job queue process which is failed.Checking with Siebel team for further analysis.Regards,Lokesh