Set Up Resource Capping

Resource capping of Oracle DMW and Oracle LSH jobs is required to ensure that you have enough resources available for the UI activity especially when jobs demand a high CPU time. If you do not set up the resource capping, the UI operations may suffer due to the CPU queue length and resource crunch. The system routes the data processing jobs to a database service specifically defined for that purpose. A resource plan which limits the CPU usage of these jobs to 75% is applied on the sessions that connect to this service.

1. Connect to PDB as SYS user.
2. Create a database service. Perform one of the following:
   • For a non-RAC standalone database, run the following command. This service will be used for executing the data processing jobs (for example, XFM , Oracle InForm and so on).

     begin 
     dbms_service.create_service('service_name','service_name');
     end;
     /
     begin
     dbms_service.start_service('service_name');
     end;
     /

     For example:

     SQL> begin
     dbms_service.create_service('dmwforjobsd31ctst','dmwforjobsd31ctst');
     end;
     /
     PL/SQL procedure successfully completed.
     
     SQL> commit;
     Commit complete.
     
     SQL> begin
     dbms_service.start_service('dmwforjobsd31ctst');
     end;
     /
     PL/SQL procedure successfully completed.
     
     SQL> commit;
     Commit complete.

   • For a RAC database, run the following command:

     srvctl add service -d CDB NAME -s service_name -pdb PDB NAME -preferred CDB Instance1,CDB Instance2 
     srvctl start service -db CDB NAME -service service_name

     For example:

     srvctl add service -d dmw3dvc -s service_name -pdb dmw3dv -preferred dmw3dvc1,dmw3dvc2
     srvctl start service -db dmw3dvc -service service_name

3. Log in to the LSH application server.
4. Source the RUN file system environment file from the Oracle LSH application server by using the following command:
   source /<BASE_LOCATION>/EBSapps.env RUN
5. Navigate to $CDR_TOP/patch/115/sql.
6. Copy dmwRMSetup.sql to the database server stage location.
7. Log in to the database server and source the PDB environment file.
8. Connect to PDB as SYS user and execute the dmwRMSetup.sql script:

   $ sqlplus sys/<password>@<PDB name> as sysdba
   
   SQL> @dmwRMSetup.sql

   The script prompts for the database service created for the data processing jobs.
9. Enter the service_name you have created.
   The script prompts for the Oracle DMW PDB name.
10. Enter the Oracle DMW PDB name.
11. Type Y, and press Enter.
    The script creates a resource limit plan for the data processing jobs.