Oracle Database 18c : Now available on the Oracle Cloud and Oracle Engineered Systems

Oracle Database 18c is the first version of the product to follow a yearly release pattern. From here onwards the Oracle Database will be released every year along with quarterly updates.

Oracle Database 18c is the next iteration of Oracle Database 12c Release 2.

Oracle Autonomous Database Cloud offers total automation based on machine learning and eliminates human labor, human error, and manual tuning.

Oracle’s 18c main aim is  “self-driving” database. New features which prefer No human labor means half cost, no human error means 100x  reliable. Here No Human Labor represents automation in Installation, Patching, Upgrades, and Tuning of Oracle Database which uses the Robot Technique with the help of Machine Learning.

Oracle Database technology automates management to deliver unprecedented availability, performance, and security—at a significantly lower cost.

Read more here:

 

 

Oracle Clinical 5.2 and Thesaurus Management System 5.2.1 released on Sept. 4th 2017

Today (Monday Sept. 4th, 2017) Oracle released the latest version of Oracle Clinical 5.2, Oracle Clinical Remote Data Capture 5.2 and Thesaurus Management System 5.2.1.

  • OC/RDC 5.2
  • TMS 5.2.1 (TMS 5.2 with patch for supporting WHODrug B3)

OC 5.2 and TMS 5.2 is primarily a technology stack upgrade from version 5.1 including some RDC bug-fixes, and hence no new functionality as such. However, TMS 5.2 comes with a patch (5.2.1) to support the new WHODrug B3 format, which was released on March 1st, 2017, and will replace the WHODrug B2 Enhanced Format. The dictionary vendor UMC will support WHODrug B2 until end of 2018.

The Technology Stack is upgraded on below tiers:

Database

  • Oracle Database 12c Release 1 (12.1.0.2) Enterprise Edition

Middletier

Oracle Fusion Middleware 12c which includes below on the middletier:

  • Oracle Forms and Reports 12.2.1.2
  • Oracle Application Development Framework (ADF) 12.2.1.2
  • Oracle WebLogic Server 12.2.1.2

Web Client

  • Microsoft Internet Explorer 11
  • Microsoft Edge 25.x (EdgeHTML 13), on Microsoft Windows 10 only
  • Apple Safari, on the supported iOS and OS X versions and for RDC only

Download

The new OC 5.2 and TMS 5.2.1 software is available for download on Oracle eDelivery

Documentation (release notes and user guides)

 

 

 

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

OC 5.2 / TMS 5.2 version targeted for 2nd half 2017

Oracle are currently working on an OC 5.2 / TMS 5.2 version that are targeted for 2nd half of 2017. The 5.2 version comes with a technology update on the middle-tier now using Oracle WebLogic server 12c, and support for Microsoft Edge Web browser for Windows 10 on web-clients.

For existing customers on OC/TMS versions 4.6.6, 5.0.1 or 5.1, this is an opportunity to plan for a final and most likely last upgrade of OC/TMS from Oracle.

From release date of OC/TMS 5.2 you can expect 5 years of support.

Future Outlook
Oracle are encouraging OC/TMS/RDC customers to switch to its Health Sciences solutions based on Oracle Life Sciences Hub (LSH), Data Management Workbench (DMW) and Inform (EDC cloud solution).

Oracle Health Sciences – Inform

Link: Oracle Health Sciences R&D Cloud Solutions

Oracle Health Sciences