Post Secure File Migration Task

Tasks that are required after the secure file migration completes.

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. Ensure that no active jobs are running.

   Note:

   DO NOT stop the database.
   1. Stop the listener and database services.
   2. Disable all the enabled DBMS SCHEDULER JOBS.
   3. Make sure that no scheduler job is in the RUNNING state.
   4. Disable the Logon Trigger.
   5. Make sure that no application-related sessions are there in gv$session.
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;

4. Count the number of pending jobs to be synchronized for migration. Execute the following command:

   SELECT
       COUNT(*) AS cnt_pending_sync_jobs
   FROM
       cdr_job_log jl
   WHERE
       NOT EXISTS (
           SELECT
               NULL
           FROM
               cdr_job_log_secfile sfjl
           WHERE
               sfjl.job_id = jl.job_id
       );

   Note:

   If the number of pending jobs is more than 10000, contact Health Sciences Support.

5. Once all 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 cdrsecfilecutoff.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. If case of an error, contact Health Sciences Support.
   It will take some time for the script to execute.
6. After the script cdrsecfilecutoff.sql executes, make sure no DBMS SCHEDULER JOBS are running related to secure file migration.
7. Make sure the new CDR_JOB_LOG table's LOB column LOG_MESSAGE is of type SECUREFILE.
   To do so, execute the following SQL command. The output of the SECUREFILE column should be YES corresponding to the CDR_JOB_LOG table.

   select OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       SEGMENT_NAME,
       LOGGING,
       SECUREFILE
       from dba_lobs
       where table_name like 'CDR_JOB_LOG%';
   

8. If there is any 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;

   1. Start the UTLRP and wait for its completion.
   2. Enable the Logon Trigger.
   3. Start the listener/database services.
   4. Enable all the DBMS SCHEDULER JOBS which were disabled at step 1.b.
9. After all validations are successful, start all the application tier services and job queue.