CAMIS is a protected B medical application.
When a database refresh is performed precautions need to be taken to ensure that users connecting to acceptance cannot view unauthorized data. MERS themselves will not be deleted. Links to MER documents will be broken but the rest of the view will be viewable.
The TWOFA database should be refreshed at the same time when CAMIS db is refreshed because they have to be in sync.
Concerns that were identified were:Privacy/Security: medical information (especially documents)
Portal trial setup would need to be redone
CAMIS/eMER accounts - 2FA links would be broken
...
Refresh from prod but delete assessments past a certain date (2000) and all documents
There is an old sanitize script that we can possibly use as a starting point: \\tc4s0a\Groups\aara\aarad\dba\camis\scripts
MERs won't be deleted - if they click on the button to view the eMER file it will break but the rest of the MER info is still there
Backup document info (blobs and others) for portal trial and then update all of it once refresh is done
All the data in the account tables in CAMIS need to be backed up and then put back in once the refresh is done
...
see also Draft: CAMIS refresh - impacts / considerations re 2FA data
PROPOSED PLAN (associated subtask placeholders are created).
Database refresh of CAMIS Acc (only) from Production
Before refresh
Reach out to Daniel Baptiste to get permission for the refresh of CAMIS Acceptance only.
...
Create an SM-GS change request ticket: it needs to take place asap to minimize the amount of CAMMS data that would be lost.
The DBAs should
Check for and recompile all invalid objects
Verify that the db links still point to Acc (note: based on feedback from corporate DBAs these are not typically refreshed).
Backup accounts/permissions and settings, as applicable (eMer and CAMIS) Identify and Backup CAMIS tables containing eMER and CAMIS account information, permissions for Acceptance
Review / update sanitize script: Review "sanitize" script
...
Once Refresh is completed on acceptance
Restore backup of acceptance accounts, settings and permissions for eMER and CAMISRun the latest sanitize_script.sql . It is backed up in \\tc4s0a\Groups\aara\aarad\dba\camis\scripts
Verify that records identified for deletion / sanitization are removed/updated.
Check for invalid objects that need to be recompiled.
Verify that the db links still point to Acc (note: based on feedback from corporate DBAs these are not typically refreshed).
Restore backup of acceptance accounts, settings and permissions for eMER and CAMIS
Database compare: acc and prod.
Testing, eMER and Acc by Avro Arrows and Medevac.
Testing by Client.
Issues encountered during the refresh of CAMIS production to acceptance:
The amount of time it took: started on Tuesday May 23, 2023.
Not yet completed as of the morning of June 2, 2023
It is not currently possible to clone the CAMIS database from Production to Acceptance:
the database is > 200gb and the current acceptance server for CAMIS won’t support that.
DBA has revised the CAMIS refresh script to make it easier and faster.
Space available on the acceptance server when exporting CAMIS from Prod and Importing it to acceptance was an issue
“It ran out of space when running into the evening, more space has been added and is running again, I'll message you when it's done”
When the refresh fails the process has to be restarted from the beginning.
Unknown to me, the DBAs had existing instructions to exclude the tables
UA018_DOCUMENT
WA005_PROCESS_LOG
CAMIS crashed when clicking the “English” or “French” buttons when those tables were not present.
There were Invalid packages that pointing to UA018_DOCUMENT and WA005_PROCESS_LOG
”Hi, so everything that refers to WA005_process_log and UA018_document is invalid. Everything I checked refers to a view, a package, a package body, a materialized view that refers to those. The synonyms are all invalid because they refer to one of those that are invalid. I can check how big those are and maybe bring those in if the import or export can. Otherwise we would have to bring the entire database as a clone and then drop the WA005 tables that hare a backup of wa005_process_log”
DBA imported UA018_DOCUMENT and WA005_PROCESS_LOG and emptied them to remove the data as I had been advised they would have to be empty.
I provided the scripts, from prod, to recreate as empty tables.
DBA was prevented from adding the foreign key constraints when UA018_DOCUMENT was empty.
“I'm currently trying to add the foreign key constraints but it won't let me. The error shows that it cannot because there are values in UA018_document DOCUMENT_NBR that are not in the table compare to the main table. Because it's empty, we cannot add the foreign key constraints.”
DBA recreated UA018_DOCUMENT and WA005_PROCESS_LOG without the BLOB/CLOB columns.
has added a step to alter the table to contain the BLOB and CLOB columns “empty”.
The FILE_CONTENT_TXT file in UA018_DOCUMENT is needed: when an eMER is submitted to CAMIS, PDF and HTML emer files are automatically attached .
The existing “sanitize” script was updated in May 2023 to empty the
FILE_CONTENT_TXT field for the records imported from production.
Backups of tables in Production:
could we request the DBA team to drop the following backup tables in production: