infa日志获取

来源：互联网发布：淘宝安全证书下载编辑：程序博客网时间：2024/06/16 14:48

CREATE OR REPLACE VIEW REP_SESS_LOG AS
SELECT
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_SUBJECT.SUBJ_ID SUBJECT_ID,
OPB_TASK_INST_RUN.TASK_NAME SESSION_NAME,
OPB_TASK_INST_RUN.TASK_ID SESSION_ID,
OPB_TASK_INST_RUN.INSTANCE_NAME SESSION_INSTANCE_NAME,
TARG_SUCCESS_ROWS SUCCESSFUL_ROWS,
TARG_FAILED_ROWS FAILED_ROWS,
SRC_SUCCESS_ROWS SUCCESSFUL_SOURCE_ROWS,
SRC_FAILED_ROWS FAILED_SOURCE_ROWS,
FIRST_ERROR_CODE,
FIRST_ERROR_MSG,
OPB_TASK_INST_RUN.RUN_ERR_CODE LAST_ERROR_CODE,
OPB_TASK_INST_RUN.RUN_ERR_MSG LAST_ERROR,
OPB_TASK_INST_RUN.RUN_STATUS_CODE,
OPB_TASK_INST_RUN.START_TIME ACTUAL_START,
OPB_TASK_INST_RUN.END_TIME SESSION_TIMESTAMP,
OPB_SESS_TASK_LOG.LOG_FILE SESSION_LOG_FILE,
ATTRDIR.ATTR_VALUE || ATTRFILE.ATTR_VALUE BAD_FILE_LOCATION,
OPB_TASK_INST_RUN.VERSION_NUMBER TASK_VERSION_NUMBER,
OPB_WFLOW_RUN.VERSION_NUMBER WORKFLOW_VERSION_NUMBER,
OPB_WFLOW_RUN.WORKFLOW_NAME WORKFLOW_NAME,
OPB_SESS_TASK_LOG.MAPPING_NAME MAPPING_NAME,
OPB_SESS_TASK_LOG.TOTAL_TRANS_ERRS TOTAL_ERR,
OPB_SESS_TASK_LOG.WORKFLOW_ID,
OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID,
OPB_SESS_TASK_LOG.WORKLET_RUN_ID,
OPB_SESS_TASK_LOG.INSTANCE_ID
FROM
OPB_TASK_INST_RUN, OPB_SESS_TASK_LOG, OPB_WFLOW_RUN, OPB_SUBJECT,
OPB_COMPONENT, OPB_CFG_ATTR ATTRFILE, OPB_CFG_ATTR ATTRDIR
WHERE
OPB_TASK_INST_RUN.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID AND
OPB_TASK_INST_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID AND
OPB_TASK_INST_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID AND
OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID AND
OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID AND
OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID AND
OPB_WFLOW_RUN.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID AND
OPB_COMPONENT.TASK_ID = OPB_TASK_INST_RUN.TASK_ID AND
OPB_COMPONENT.REF_OBJ_TYPE = 1 AND
OPB_COMPONENT.TASK_INST_ID = (SELECT MAX(C.TASK_INST_ID)
FROM OPB_COMPONENT C
WHERE C.TASK_ID = OPB_COMPONENT.TASK_ID AND
C.REF_OBJ_TYPE = 1 AND
(C.TASK_INST_ID = OPB_SESS_TASK_LOG.INSTANCE_ID OR
C.TASK_INST_ID = 0)) AND
ATTRDIR.CONFIG_ID = OPB_COMPONENT.REF_OBJ_ID AND
ATTRDIR.CONFIG_TYPE = 72 AND
ATTRDIR.ATTR_ID = 221 AND
ATTRDIR.VERSION_NUMBER = OPB_COMPONENT.VERSION_NUMBER AND
ATTRDIR.SESSION_ID = (SELECT MAX(A1.SESSION_ID)
FROM OPB_CFG_ATTR A1
WHERE A1.CONFIG_ID = ATTRDIR.CONFIG_ID AND
A1.CONFIG_TYPE = 72 AND
A1.ATTR_ID = 221 AND
A1.VERSION_NUMBER = ATTRDIR.VERSION_NUMBER AND
(A1.SESSION_ID = OPB_TASK_INST_RUN.TASK_ID OR A1.SESSION_ID = 0)) AND
ATTRDIR.SESSION_INST_ID = (SELECT MAX(A2.SESSION_INST_ID)
FROM OPB_CFG_ATTR A2
WHERE A2.CONFIG_ID = ATTRDIR.CONFIG_ID AND
A2.CONFIG_TYPE = 72 AND
A2.ATTR_ID = 221 AND
A2.VERSION_NUMBER = ATTRDIR.VERSION_NUMBER AND
A2.SESSION_ID = ATTRDIR.SESSION_ID AND
(A2.SESSION_INST_ID = OPB_SESS_TASK_LOG.INSTANCE_ID OR A2.SESSION_INST_ID = 0)) AND
ATTRFILE.CONFIG_ID = OPB_COMPONENT.REF_OBJ_ID AND
ATTRFILE.CONFIG_TYPE = 72 AND
ATTRFILE.ATTR_ID = 222 AND
ATTRFILE.VERSION_NUMBER = OPB_COMPONENT.VERSION_NUMBER AND
ATTRFILE.SESSION_ID = (SELECT MAX(A1.SESSION_ID)
FROM OPB_CFG_ATTR A1
WHERE A1.CONFIG_ID = ATTRFILE.CONFIG_ID AND
A1.CONFIG_TYPE = 72 AND
A1.ATTR_ID = 222 AND
A1.VERSION_NUMBER = ATTRFILE.VERSION_NUMBER AND
(A1.SESSION_ID = OPB_TASK_INST_RUN.TASK_ID OR A1.SESSION_ID = 0)) AND
ATTRFILE.SESSION_INST_ID = (SELECT MAX(A2.SESSION_INST_ID)
FROM OPB_CFG_ATTR A2
WHERE A2.CONFIG_ID = ATTRFILE.CONFIG_ID AND
A2.CONFIG_TYPE = 72 AND
A2.ATTR_ID = 222 AND
A2.VERSION_NUMBER = ATTRFILE.VERSION_NUMBER AND
A2.SESSION_ID = ATTRFILE.SESSION_ID AND
(A2.SESSION_INST_ID = OPB_SESS_TASK_LOG.INSTANCE_ID OR

A2.SESSION_INST_ID = 0));

--------------------------------------

CREATE OR REPLACE VIEW ETL_VIEW
(workflow_run_id, subj_name, workflow_name, session_name, start_time, end_time, run_err_msg, run_status)
AS
SELECT a.WORKFLOW_RUN_ID, e.subj_name,d.WORKFLOW_NAME,a.INSTANCE_NAME, a.
START_TIME, a.END_TIME, case when a.RUN_STATUS_CODE=1 THEN '成功导入'||rtrim
(to_char(b.TARG_SUCCESS_ROWS)) || '条,失败了' || rtrim(to_char(b. TARG_FAILED_ROWS
)) || '条' ELSE '错误' END RUN_ERR_MSG, CASE a. RUN_STATUS_CODE WHEN 1 THEN
'ok' WHEN 2 THEN 'Disabled' WHEN 3 THEN 'fail' WHEN 4 THEN 'Stopped' WHEN 5
THEN 'Aborted' WHEN 6 THEN 'Running' WHEN 7 THEN 'Suspending' WHEN 8 THEN
'Suspended' WHEN 9 THEN 'Stopping' WHEN 10 THEN 'Aborting' WHEN 11 THEN
'Waiting' WHEN 12 THEN 'Scheduled' WHEN 13 THEN 'Unscheduled' WHEN 14 THEN
'Unknown' WHEN 15 THEN 'Terminated' END RUN_STATUS FROM opb_task_inst_run a
, opb_sess_task_log b ,opb_wflow_run d,opb_subject e where d.WORKFLOW_NAME
like 'wf_%' and a.instance_name like 's_%' and e.subj_id =d.subject_id and a
.WORKFLOW_ID=b. WORKFLOW_ID and a.WORKFLOW_RUN_ID=b.WORKFLOW_RUN_ID and d.
WORKFLOW_ID=b. WORKFLOW_ID and d.WORKFLOW_RUN_ID=b.WORKFLOW_RUN_ID and a.
INSTANCE_ID=b. INSTANCE_ID and a.
workflow_run_id in ( select distinct workflow_run_id from opb_task_inst_run
);

--------------------------

获取session状态以及失败，成功条数

create or replace view v_session_run_log as
select SUBJECT_AREA FOLDER,
workflow_name,
session_name,
replace(replace(replace(substr(session_name,instr(session_name,'_',1,2)+1),'_full',''),'_insert',''),'_update','') table_name,
SUCCESSFUL_SOURCE_ROWS source_success_rows,
failed_source_rows source_fail_rows,
successful_rows target_success_rows,
failed_source_rows target_failed_rows,
first_error_msg error_log,
actual_start start_time,
session_log_file session_log_path,
mapping_name,
decode(first_error_msg,'No errors encountered.','0','1') flag
from rep_sess_log;

---------------------------------------------------------------------

select "FOLDER","WORKFLOW_NAME","SESSION_NAME","TABLE_NAME","SOURCE_SUCCESS_ROWS","SOURCE_FAIL_ROWS","TARGET_SUCCESS_ROWS","TARGET_FAILED_ROWS","ERROR_LOG","START_TIME","SESSION_LOG_PATH","MAPPING_NAME","FLAG" from (select SUBJECT_AREA FOLDER,
workflow_name,
session_name,
replace(replace(replace(substr(session_name,instr(session_name,'_',1,2)+1),'_full',''),'_insert',''),'_update','') table_name,
SUCCESSFUL_SOURCE_ROWS source_success_rows,
failed_source_rows source_fail_rows,
successful_rows target_success_rows,
failed_source_rows target_failed_rows,
first_error_msg error_log,
actual_start start_time,
session_log_file session_log_path,
mapping_name,
decode(first_error_msg,'No errors encountered.','0','1') flag
from infa_rep.rep_sess_log@infa
where trunc(actual_start) in(trunc(sysdate),trunc(sysdate-1))
and actual_start in(
select
max(actual_start)
from rep_sess_log
where trunc(actual_start) in(trunc(sysdate),trunc(sysdate-1))
group by SUBJECT_AREA, workflow_name,session_name,session_log_file,mapping_name,to_char(trunc(actual_start),'yyymmdd')))
where flag<>'0'
and error_log not like 'FR_3085 ERROR: Row [1]: %'
and workflow_name<>'wf_wbsc';

-------------------------------

获取workflow运行的时间

SELECT folder_name,
workflow_name,
start_time,
end_time,
ROUND( (to_date(end_time,'YYYY-MM-DD HH24:MI') - to_date(start_time,'YYYY-MM-DD HH24:MI')) * 24 * 60 ) run_minutes
FROM ( SELECT s.subj_name AS folder_name,
DECODE (s.subj_name, 'BUFF', '1', 'DW', '2', 'DM', '3')
AS ORDERS,
w.workflow_name,
TO_CHAR (MIN (t.start_time), 'YYYY-MM-DD HH24:MI')
AS start_time,
TO_CHAR (MAX (t.end_time), 'YYYY-MM-DD HH24:MI') AS end_time
FROM opb_task_inst_run@infa t,
opb_wflow_run@infa w,
opb_subject@infa s
WHERE t.workflow_run_id = w.workflow_run_id
AND w.subject_id = s.subj_id
AND (t.start_time >=
TO_DATE (
TO_CHAR (SYSDATE - 1, 'yyyy-mm-dd') || '19:00:00',
'yyyy-MM-dd HH24:mi:ss'))
AND s.subj_name IN ('DM', 'DW', 'BUFF', 'MONITOR')
AND WORKFLOW_NAME NOT IN
('wf_email_xj', 'wf_email_p6', 'wf_email_xj_sc')
GROUP BY s.subj_name, w.workflow_name) t
ORDER BY t.orders, start_time;

------------------------------------------------

获取sql Query

select e.mapping_name,b.instance_name,a.attr_value from opb_widget_attr a,opb_widget_inst b,opb_object_type c,opb_attr d,opb_mapping e

where a.widget_id=b.widget_id
and b.widget_type=c.object_type_id
and object_type_name='Source Qualifier'
and a.widget_id=b.widget_id
and a.attr_id=d.attr_id;

---------------------------------------
select * from opb_widget_attr;
and c.object_type_id=d.object_type_id
and attr_name='Sql Query'
and b.mapping_id=e.mapping_id

------------------------------------------

错误日志

SELECT t.文件夹名称,
工作流名称,
-- t.映射名称,
t.session运行状态,
t.workflow运行状态,
session名称,
t.开始时间,
t.结束时间,
t.源成功条数,
t.源失败条数,
t.全部转换错误,
t.目标成功条数,
t.目标失败条数,
t.第一条错误信息

FROM (SELECT distinct osub.subj_name AS 文件夹名称,
ott.task_name AS 工作流名称,
logg.mapping_name AS 映射名称,
ot.task_name AS session名称,
ott.comments as 工作流描述,
logg.server_name AS 节点,
logg.start_time AS 开始时间,
logg.end_time AS 结束时间,
logg.targ_success_rows AS 目标成功条数,
logg.targ_failed_rows AS 目标失败条数,
logg.total_trans_errs AS 全部转换错误,
logg.first_error_msg AS 第一条错误信息,
logg.wflow_status,
logg.session_status,
logg.total_trans_errs,
logg.src_success_rows as 源成功条数,
logg.src_failed_rows as 源失败条数,
decode(logg.session_status,
1,
'Succeeded',
2,
'Disabled',
3,
'Failed',
4,
'Stopped',
5,
'Aborted',
6,
'Running',
15,
'Terminated') session运行状态,
decode(logg.wflow_status,
1,
'Succeeded',
2,
'Disabled',
3,
'Failed',
4,
'Stopped',
5,
'Aborted',
6,
'Running',
15,
'Terminated') workflow运行状态

FROM --infa.OPB_MAPPING om,
OPB_SESSION os,
OPB_TASK ot,
OPB_TASK_INST oti,
OPB_TASK ott,
OPB_SUBJECT osub,
rep_users ru,
(SELECT a.subject_id,
a.workflow_name,
a.workflow_id,
a.workflow_run_id,
d.mapping_name,
d.mapping_id,
c.instance_id,
a.server_name,
c.start_time,
c.end_time,
a.run_err_msg,
b.targ_success_rows,
b.targ_failed_rows,
b.total_trans_errs,
-- b.first_error_msg,
b.first_error_code,
b.src_success_rows,
b.src_failed_rows,
a.run_status_code as wflow_status,
c.run_status_code as session_status,
decode(b.first_error_msg,
null,
c.run_err_msg,
b.first_error_msg) first_error_msg
FROM opb_wflow_run a,
opb_sess_task_log b,
OPB_TASK_INST_RUN c,
(select opm.mapping_name, opm.mapping_id, se.session_id
from OPB_TASK_INST ti,
opb_session se,
opb_mapping opm
where ti.task_id = se.session_id
and ti.task_type = 68
and opm.mapping_id = se.mapping_id) d
WHERE a.workflow_run_id = c.workflow_run_id
and a.workflow_id = c.workflow_id
and c.task_id = d.session_id
and b.workflow_run_id(+) = c.workflow_run_id
and b.workflow_id(+) = c.workflow_id
and b.instance_id(+) = c.instance_id
AND to_char(c.start_time,'YYYY-MM-DD HH24:MI:SS') >= to_char(TRUNC(SYSDATE-1),'YYYY-MM-DD')||' 08:00:00') logg
where /*om.mapping_id = os.mapping_id
AND*/
ot.is_visible = 1
-- AND om.is_visible = 1
AND ot.task_type = 68
AND ot.version_number = os.version_number
AND ot.task_id = os.session_id
AND oti.version_number = ott.version_number
AND oti.task_id = os.session_id
AND ott.task_id = oti.workflow_id
AND ott.is_visible = 1
AND ott.task_type = 71
AND osub.subj_id = ott.subject_id
AND logg.workflow_id = ott.task_id
and logg.instance_id = oti.instance_id

-- and logg.mapping_name = om.mapping_name

and ( logg.first_error_msg != 'No errors encountered.' or logg.session_status!=1 or logg.total_trans_errs>0)
and decode(logg.first_error_msg,null,'unknown',logg.first_error_msg) not like 'DBG_21450 [UPDTRANS]: REJECT: %'

) t

ORDER BY t.开始时间, t.文件夹名称, t.工作流名称, t.映射名称