How To Configure DBR For Oracle Databases Oracle Database Resource Manager (DBRM)—often referred to as Database Resource Manager (DRM) or colloquially associated with Database Backup and Recovery (DBR) strategies—is a critical tool for managing system resources. It allows database administrators to allocate CPU, parallel execution servers, and I/O resources across different sessions and workloads.
Without proper configuration, a single runaway query can consume all available server capacity, slowing down critical business applications. This guide provides a step-by-step approach to configuring resource management for Oracle Databases. 1. Understand the Core Components
Before writing configuration code, you must understand the three pillars of Oracle Resource Manager:
Resource Consumer Groups: Containers that group sessions together based on their processing needs (e.g., OLTP_USERS, BATCH_JOBS, REPORTING).
Resource Plan Directives: The actual rules that assign resources (like CPU percentages or execution time limits) to the consumer groups.
Resource Plans: The top-level container that groups all the directives together. Only one resource plan can be active on a database instance at any given time. 2. Step-by-Step Configuration Using DBMS_RESOURCE_MANAGER
To configure resource management, you must use the DBMS_RESOURCE_MANAGER PL/SQL package. This process requires a “Pending Area,” which acts as a staging environment for your changes before they go live. Step 1: Create a Pending Area Initialize the staging area to start your configuration. EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); Use code with caution. Step 2: Create Resource Consumer Groups Define the categories for your database workloads.
BEGIN DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP ( consumer_group => ‘HIGH_PRIORITY_OLTP’, comment => ‘Group for critical online transactions’ ); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP ( consumer_group => ‘LOW_PRIORITY_BATCH’, comment => ‘Group for nightly batch jobs and reporting’ ); END; / Use code with caution. Step 3: Create the Resource Plan
Create the main plan container that will hold your resource allocation rules.
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN(plan => ‘PROD_DB_PLAN’, comment => ‘Main production resource plan’); Use code with caution. Step 4: Create Resource Plan Directives
Assign specific resource limits to your groups. In this example, we allocate CPU using a leveling method. If HIGH_PRIORITY_OLTP does not use its 80%, the batch group can utilize it.
BEGIN – Allocation for High Priority Group DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ( plan => ‘PROD_DB_PLAN’, group_or_subplan=> ‘HIGH_PRIORITY_OLTP’, comment => ‘80% CPU priority’, mgmt_p1 => 80 ); – Allocation for Low Priority Group DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ( plan => ‘PROD_DB_PLAN’, group_or_subplan=> ‘LOW_PRIORITY_BATCH’, comment => ‘20% CPU priority’, mgmt_p1 => 20 ); – Mandatory allocation for the default and recovery groups DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE ( plan => ‘PROD_DB_PLAN’, group_or_subplan=> ‘OTHER_GROUPS’, comment => ‘Catch-all for other sessions’, mgmt_p2 => 100 ); END; / Use code with caution. Step 5: Validate and Submit the Configuration
Check the pending area for consistency errors. If everything looks good, commit the changes to the data dictionary.
BEGIN DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); END; / Use code with caution. 3. Map Database Users to Consumer Groups
Once the groups are created, you must assign database users or sessions to them. You can map users statically or grant them the ability to switch groups dynamically.
– Grant permission to a user to switch to a group EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(‘reporting_user’, ‘LOW_PRIORITY_BATCH’, FALSE); – Set the default consumer group for a specific user EXEC DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(‘reporting_user’, ‘LOW_PRIORITY_BATCH’); Use code with caution. 4. Enable the Resource Plan
Creating the plan does not automatically turn it on. You must update your initialization parameters to activate it. You can do this dynamically without restarting the database.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘PROD_DB_PLAN’ SCOPE=BOTH; Use code with caution.
To disable resource management at any time, clear the parameter: ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = “ SCOPE=BOTH; Use code with caution. 5. Monitor and Verify the Configuration
After activation, monitor the performance and verify that resources are being distributed as expected. Use Oracle’s dynamic performance views (V\(</code>) to audit the setup. Check the currently active plan: <code>SELECT name, is_top_plan FROM v\)rsrc_plan; Use code with caution. Monitor CPU throttling and consumer group statistics:
SELECT sequence#, name, consumers, cpu_wait_time, cpu_waits FROM v$rsrc_consumer_group; Use code with caution.
By following this structural deployment, you protect your Oracle Database from unexpected resource exhaustion, ensuring steady performance for high-priority business operations.
To help refine this implementation for your specific environment, please share: Your Oracle Database version (e.g., 19c, 21c, 23c)
The primary bottleneck you are trying to solve (CPU, I/O, or parallel execution limits)
Whether you are configuring this on a Multitenant (CDB/PDB) architecture or a non-CDB database