Month: March 2017

Scheduled BVS Jobs

SQL for listing Scheduled BVS jobs in Oracle Clinical.

BVS = Batch Validation Session.

select 
 batch_job_id,
 module_name,
 m.description, 
 user_name,
 study, 
 execution_status,
 to_char(submission_ts,'DAY') submission_day,
 to_char(submission_ts,'YYYY-MM-DD HH24:MI:SS') submission_ts,
 to_char(entered_ts,'YYYY-MM-DD HH24:MI:SS') entered_ts,
 to_char(started_ts,'YYYY-MM-DD HH24:MI:SS') started_cet, 
 to_char(completion_ts,'YYYY-MM-DD HH24:MI:SS') completed_cet,
 round((completion_ts-started_ts)*24,2) dura_hours,
 round((completion_ts-started_ts)*1440,0) dura_min,
 round((completion_ts-started_ts)*86400,0) dura_sec, 
 output_file_name,
 log_file_name,
 failure_text,
 bj.schedule_server,
 bj.schedule_string
from batch_jobs bj, modules m
where bj.module_name = m.name
 and module_name = 'RXCBVBVS' 
 and bj.execution_status = 'SCHEDULED' 
order by 8 desc;

Also, see table BATCH_DM_RUNS for info on BVS runs including success flag, modified patient count and data currency flag (CURRENT =Y for production data).

select * from batch_dm_runs
order by creation_ts desc;

Failed Login Attempts – DBA_AUDIT_SESSION

How to list logon/logoff sessions in the Oracle (Clinical) database including failed logins?

Example for Oracle 11g database (OC 4.6.x)

Logon to SQL*Plus or TOAD as DBA user in the Oracle database with access to DBA system views and tables.

SQL query DBA Audit Session

/* Logon/Logoffs using referencing lookup function for returning error message for return (error) code */

select 
 username,
 terminal, 
 action_name,
 to_char(timestamp,'YYYYMMDD HH24:MI:SS') timestamp,
 to_char(logoff_time,'YYYYMMDD HH24:MI:SS') logoff_time,
 returncode,
 get_ora_error_message(-returncode) error_msg
from dba_audit_session
where 1=1 -- timestamp > sysdate-7
 AND username like '%' -- = 'OPS$<init>' -- OC users are prefixed with OPS$
 and returncode <> 0 -- <> 0 to list failed logins
 --and logoff_time is not null
order by 5 desc;

Function used to return error message from error code (return code)

create or replace function get_ora_error_message(p_ora_number in number) return varchar2 is 
v_msg varchar2(255); 
begin 
 v_msg := sqlerrm(p_ora_number); 
 return v_msg; 
end; 
/

Return codes and messages (commonly seen)
ORA-01017: invalid username/password; logon denied
ORA-01045: user lacks CREATE SESSION privilege; logon denied
ORA-03136: inbound connection timed out
ORA-28000: the account is locked
ORA-28001: the password has expired
ORA-28003: password verification for the specified password failed
ORA-28007: the password cannot be reused
ORA-28043: invalid bind credentials for DB-OID connection