Migrate Secure File for DME_DISC_CSV_FILES

To perform the next set of tasks, wait for at least 12 hours after the secure file migration completes. A complete application downtime is required.

1. Stop all application tier services and job queue.

   Note:

   DO NOT stop the database.
2. If there is any cronjob related to Oracle LSH or Oracle DMW, suspend them. Disable any custom scheduler or DBMS jobs related to Oracle LSH or Oracle DMW.
3. Note the count of the invalid objects of APPS, APPLSYS, and CDR schema by executing the following command:

   select owner,
   			status,
   			count(1)
   			from dba_objects
   			where status <> 'VALID'
   			and owner in ('APPS','APPLSYS','CDR')
   			AND object_name <> 'CDR_SECUREFILE_MIGRATION'
   			AND object_name NOT LIKE 'CDR%SECFILE%'
   			AND object_name NOT LIKE 'CDR%SFM%'
   			group by owner,
   			status;

   Or, create a backup table with the list of all INVALID OBJECTS by executing the following command:
   CREATE TABLE <TABLE_NAME> AS SELECT * FROM DBA_OBJECTS WHERE STATUS <>'VALID';
4. Once all the application tier services are stopped, execute the cutover script by performing the following steps:
   1. Log in to the application tier.
   2. Source the environment file.
   3. Navigate to the $CDR_TOP/patch/115/sql directory.
   4. Log in to SQL*Plus as the APPS user.
   5. Execute the script cdrsecfilecsvcutoff.sql.
      A prompt to press enter to start the process appears.
   6. Press Enter.
      A prompt to enter the logfile pathname appears.
   7. Press Enter to select the default logfile pathname or enter a name of your choice.
   8. After the script execution is complete, check for any errors. In case of an error, contact Life Sciences Support.
   It will take some time for the script to execute.
5. After the script cdrsecfilecsvcutoff.sql executes, make sure no DBMS SCHEDULER JOBS are running related to secure file migration.
6. Make sure the new DME_DISC_CSV_FILES table's LOB column CONTENT is of type SECUREFILE.
   To do so, execute the following SQL command. The output of the SECUREFILE column should be YES corresponding to the DME_DISC_CSV_FILES table.

   select OWNER,
   			  TABLE_NAME,
   			  COLUMN_NAME,
   			  TABLESPACE_NAME,
   			  SECUREFILE
   		from dba_lobs
   		where table_name like 'DME_DISC_CSV_FILES%'
   		and column_name =CONTENT'
   		and OWNER='CDR';

7. If there are new INVALID objects in the APPS, APPLSYS, or CDR schema, compile those invalid objects.

   select owner,
   			status,
   			count(1)
   			from dba_objects
   			where status <> 'VALID'
   			and owner in ('APPS','APPLSYS','CDR')
   			AND object_name <> 'CDR_SECUREFILE_MIGRATION'
   			AND object_name NOT LIKE 'CDR%SECFILE%'
   			AND object_name NOT LIKE 'CDR%SFM%'
   			group by owner,
   			status;

8. After all validations are successful, start all the application tier services and job queue.