Refreshing the CAMIS Database

• 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.
  
  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

Possible actions

• 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.

On approval of the request

• 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

Day before the refresh is to occur:
Check SM-GS approvals completed for CAMIS DB refresh

Day of Refresh:

Inform clients and AVRO Arrows / Medevac teams of refresh taking place

Once Refresh is completed on acceptance

• Run 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.

• 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: