My Oracle Support Banner

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

Last updated on MARCH 17, 2020

Applies to:

Oracle Health 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

 


 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
 Shut Down LSH/DMW
 1. Stop Job Queue/Message Queue and Confirm queues are not running
 2.  Stop Middle Tiers (DMW first then LSH)
 2b. Restrict scheduled run during downtime
 3. Confirm there are no jobs in EXECUTING status in the system
 4. Stop OWB services
 5. Stop DP Servers
 Trucate CDR Temp BLOB Tables
 6. Truncate temp BLOB tables
 Start Up LSH/DMW
 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
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.