Appendix 2: EBS 12.2 Replication Online Patching

Summary

In order to provide a better integrated solution for EBS 12.2 on a replicated environment, the current replication script required extra steps to assist automating the creation of DB objects to support online patching for EBS 12.2.

Enhancements to Replication Script

Some extra permissions have been added to the script to support the online patching process. These are as follows:

• CREATE JOB grant – this permission allows the replication schema, created through the script, to create DBMS scheduled jobs. The job that will be created through the script will have the ability to run the online patching procedure at a set time every day.
• CREATE PROCEDURE grant – this permission allows the replication schema, created through the script, to create the procedure which will create a table and populate with a list of EBS 12.2 tables that have been patched/editioned.
• CREATE TABLE grant – this permission allows the replication schema, created through the script, to create a table via the procedure in order to contain the list of EBS 12.2 tables that have been patched/ editioned.
• The script will now create, only for EBS 12.2, a procedure named HUB_TABLE_SET_DEF_PK so that a DBA does not need to create it. The procedure will create a table, if one is not present, named HUB_TABLE_SET_DEF and maintain this table with the correct run set data identifier for each table that is editioned. The identifier will either be ORA$BASE, SET1 or SET2. The procedure will be created under the replication schema being created by the replication script and once created, the replication script will run the procedure. The procedure will only be created for 12.2 systems when the replication script is run on it. The DBA can also manually run the procedure once created.
• Supplemental logging for the new table HUB_TABLE_SET_DEF has been added to the script so Qlik can replicate the data with CDC. If the DBA selects auto_supplemental_log = N then the permission is specifically added but if the DBA selects auto_supplemental_log = Y then Qlik can add the permission. This is required so Qlik can add logs to the relevant tables in order to support CDC.
• The last enhancement for the replication script is the ability to add an Oracle job if the DBA requires it, in order to refresh the HUB_TABLE_SET_DEF with the latest run set data identifiers by running the HUB_TABLE_SET_DEF_PK created earlier in the script. The DBA can choose via a prompt whether to create the job or not. The name of the job is a maximum of 30 characters and job name will be made up of the prefix of HUB_TSD_ and then the replication schema name for example HUB_TSD_REP_SCHEMA_1. If the job already exists, it will be dropped and re-created. The job will be scheduled to run at midnight.
• The specific reason for creating the job is to ensure that if the DBA fails to run it as part of the Patching cutover then it will be automatically run.

Setup

After running through the script and entering the initial details like schema name and password for example, the script will run as normal granting the expected permissions for selecting data and then supplemental logging permissions, if required. Once this is complete, if running on 12.2 systems, the script will run through the online patching section for creating the procedure and job if required. The following screenshots will show the output from this process along with the job creation question and resulting output if the job is created:

Screenshot of output from a newly created replication schema, with no procedure or job present:

Screenshot of output from an existing replication schema, with procedure and job already present:

Screenshot of output from a newly created replication schema, with no job creation required:

All of the screenshots above were used with supplemental logging set to Y so that Qlik can add the permissions it requires