Configure the Oracle Database Resource Manager

Contents

  1. 1 Summary
  2. 2 References
  3. 3 Scenario Description
  4. 4 Scenario Done Through SQL*Plus
    1. 4.1 Connect to Root Container
    2. 4.2 Drop Resource Plan
    3. 4.3 Create a Pending Area
    4. 4.4 Create a CDB Resource Plan
    5. 4.5 Create the CDB Resource Plan Pirectives
    6. 4.6 All Other PDBs
    7. 4.7 Validate the Pending Area
    8. 4.8 Submit the Pending Area
  5. 5 Scenario Done Through OEM
    1. 5.1 Enter Resource Manager
    2. 5.2 Connect as SYSDBA
    3. 5.3 Go to CDB Resource Plan Page
    4. 5.4 Create CDB Plan
    5. 5.5 Fill in CDB Plan Description
    6. 5.6 Select PDBs with Non-Default Settings
    7. 5.7 Fill in Settings for Selected PDBs
    8. 5.8 CDB Plan Created
    9. 5.9 Review CDB Plan

Summary

Using Oracle Database Resource Manager (ORM) in a container database (CDB) builds upon ORM in a non-CDB. ORM in a CDB is split across two (2) levels: CDB and Pluggable Database (PDB). However, ORM in a PDB is more restrictive than in a non-CDB (max 8 consumer groups and no sub-plans).

References

Scenario Description

I propose to convert a sample resource plan into resource plans used with a CDB. The example chosen is “44.3.1 Creating a CDB Resource Plan: A Scenario”.

This is done in both SQL*Plus and through OEM.

Scenario Done Through SQL*Plus

Connect to Root Container

This scenario requires that you connect to the root container.

Drop Resource Plan

I ran the following PL/SQL code to drop any existing CDB plan:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN('jar_cdb_plan');
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

The expected output is:

PL/SQL procedure successfully completed.

Create a Pending Area

Create a pending area using the CREATE_PENDING_AREA procedure as follows in order to build the necessary resource plan:

exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

The expected output is:

PL/SQL procedure successfully completed.

Create a CDB Resource Plan

Create a CDB resource plan, named newcdb_plan, using the CREATE_CDB_PLAN procedure as follows:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
    plan    => 'jar_cdb_plan',
    comment => 'CDB resource plan for JAR');
END;
/

The expected output is:

PL/SQL procedure successfully completed.

Create the CDB Resource Plan Pirectives

Create the CDB resource plan directives for the PDBs using the CREATE_CDB_PLAN_DIRECTIVE procedure. Each directive specifies how resources are allocated to a specific PDB.

The PLUM PDB will be allocated three (3) shares and be allowed to use all available resources:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan                  => 'jar_cdb_plan', 
    pluggable_database    => 'PLUM', 
    shares                => 3, 
    utilization_limit     => 100,
    parallel_server_limit => 100);
END;
/

The expected output is:

PL/SQL procedure successfully completed.

The VEGEMITER PDB will be allocated three (3) shares and be allowed to use all available resources:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan                  => 'jar_cdb_plan', 
    pluggable_database    => 'VEGEMITER', 
    shares                => 3, 
    utilization_limit     => 100,
    parallel_server_limit => 100);
END;
/

The expected output is:

PL/SQL procedure successfully completed.

The VEGEMITE PDB will be allocated one (1) share and be allowed to use up to 70% of available resources:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan                  => 'jar_cdb_plan', 
    pluggable_database    => 'VEGEMITE', 
    shares                => 1, 
    utilization_limit     => 70,
    parallel_server_limit => 70);
END;
/

The expected output is:

PL/SQL procedure successfully completed.

All Other PDBs

All other PDBs in this CDB use the default PDB directive. No changes are needed.

Validate the Pending Area

Validate the pending area using the VALIDATE_PENDING_AREA procedure as follows:

exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

The expected output is:

PL/SQL procedure successfully completed.

Submit the Pending Area

Submit the pending area using the SUBMIT_PENDING_AREA procedure to create the new resource plan:

exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

The expected output is:

PL/SQL procedure successfully completed.

Scenario Done Through OEM

The procedure can be found in “45.3 Creating a PDB Resource Plan with Cloud Control”. This CDB resource plan will be called CDB_OEM_PLAN.

Enter Resource Manager

From the database home page, select the Resource Manager from the Administration drop-down menu as follows:

Select Resource Manager entry from Administration drop-down menu on database home page

Connect as SYSDBA

Enter the login credentials for the SYSDBA user as follows:

Enter login credentials for SYSDBA user

Click the Login button to continue.

Go to CDB Resource Plan Page

On the Resource Manager page, click the following link to go to the CDB Resource Plans page:

Create CDB Plan

On the CDB Plan page, click the Create button as follows:

Fill in CDB Plan Description

Fill in the main details for the new CDB plan as follows:

Click on the Add/Remove button to continue.

Select PDBs with Non-Default Settings

Select the following PDBs to apply non-default settings:

  • PLUM
  • VEGEMITE
  • VEGEMITER

Click OK to continue.

Fill in Settings for Selected PDBs

Fill in the settings for the PDBs with non-default settings as follows:

Click OK to continue.

CDB Plan Created

The following message should be displayed:

Click on the new CDB plan (CDB_OEM_PLAN) to see the details of the plan.

Review CDB Plan

The following screen shows that the details of the new plan (CDB_OEM_PLAN):

Details of new plan