How To Truncate CDR Temp BLOB Tables (Doc ID 2631858.1)

Last updated on JULY 13, 2023

Applies to:

Oracle Life Sciences Data Management Workbench - Version 2.3.0 and later
Oracle Life Sciences Data Hub - Version 2.2 and later
Information in this document applies to any platform.

Goal

This document is listing the steps required for truncating the CDR temp BLOB tables.

Truncating the temp BLOB’s as indicated in this document leads to issues while re-loading the file (as explained under Known Issues section) and so, ensure you have copies of Data files in case there is a need to reload them in the future.

Known Issues:

DMW:

When attempting to reload data from a file in DMW ( Study Configuration > Clinical Data Model > (Select a File based input model) > Detected Files) OR re-submit the Loadset Job from LSH after truncating the BLOB tables, the following errors occurs:

"ORA-20001: get BLOB from CDR_TEMP_CLOBS failed: ORA-01403: no data found::15:"

Workaround:

Download the file from File watcher archive location and reload the file manually.

LSH:

When attempting to resubmit a File-based text Loadset through LSH with the same file after truncating the BLOB tables, the job fails with the same error:

"ORA-20001: get BLOB from CDR_TEMP_CLOBS failed: ORA-01403: no data found::15:"

Workaround:

Upload the file again manually to reload the data.

Before attempting to truncate these tables, LSH/DMW should always be stopped:

To Shutdown LSH/DMW:

1. Stop Job Queue/Message Queue and Confirm queues are not running
2. Stop Middle Tiers (DMW first then LSH)
3. Confirm there are no jobs in EXECUTING status in the system
4. Stop OWB services (CRITICAL: Make sure you wait 10 minutes before starting up OWB services)
5. Stop DP Servers

6. Truncate the temp BLOB tables.

To start up LSH/DMW, you practically apply the reverse order:

7. Start OWB Services
8. Start DP Servers
9. Start Middle Tiers (LSH first then DMW)
10. Start Job Queue/Message Queue and confirm queues are up and running