a collection of scripts to ease the day-to-day work of an Oracle DBA
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 
 

135 lines
5.6 KiB

/* =============================================================================
Example Setup for Oracle Resource Manager
-----------------------------------------------------------------------------
You will need to adjust that to reflect the database you wish to deploy this
to. This example uses 6 Groups:
* (SYS_GROUP)
- already defined by Oracle
- consists of SYS, SYSTEM
* ERM_ADMIN_GROUP
- application administrators
* ERM_APPLICATION_GROUP
- (interactive) application(s)
- we want to ensure fast responses for this one
* ERM_BATCH_GROUP
- (scheduled) batch processes
- we usually don't mind about a little delay here if the above need resources
* ERM_EXTERNALS_GROUP
- applications/users accessing our database via DBLinks
- we are not "obligated" to guarantee them "performance"
* (OTHER_GROUPS)
- already defined by Oracle
- consists of all users not explicitly defined otherwise
In case anyone wonders: the prefix ERM stands for Extend Resource Manager.
You can of course use anything (else) you like.
Resources are assigned "top-to-bottom" - so the "lower ones" get the "left
overs" from the "higher ones".
========================================================================== */
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA(); -- just in case
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); -- We need this so we can create/modify/adjust/test and then submit
/* if we need/want to define our own groups, we can do so: */
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'ERM_ADMIN_GROUP',
COMMENT => 'Application Administrators'
);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'ERM_APPLICATION_GROUP',
COMMENT => 'Applications and Services (interactive, responsiveness needed)'
);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'ERM_BATCH_GROUP',
COMMENT => 'Batch Services (scheduled jobs which are OK to be delayed a little, if needed)'
);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'ERM_EXTERNALS_GROUP',
COMMENT => 'External Systems accessing us via DBLink'
);
/* Now we define the members of our groups */
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( -- app admin *DB* user
ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
VALUE => 'APPADM',
CONSUMER_GROUP => 'ERM_ADMIN_GROUP'
);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( -- Application and Services, by their *OS* users
ATTRIBUTE => DBMS_RESOURCE_MANAGER.CLIENT_OS_USER,
VALUE => 'APP_USER',
CONSUMER_GROUP => 'ERM_APPLICATION_GROUP'
);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
ATTRIBUTE => DBMS_RESOURCE_MANAGER.CLIENT_OS_USER,
VALUE => 'MServiceBatch%',
CONSUMER_GROUP => 'ERM_APPLICATION_GROUP'
);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( -- batch user, by *CLIENT PROGRAM* (see V$SESSION.PROGRAM)
ATTRIBUTE => DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM,
VALUE => 'APPBATCHPROG%',
CONSUMER_GROUP => 'ERM_BATCH_GROUP'
);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( -- external users via DBLink, again by *DB* user
ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
VALUE => 'APPDWH',
CONSUMER_GROUP => 'ERM_EXTERNALS_GROUP'
);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
VALUE => 'EXTAPP',
CONSUMER_GROUP => 'ERM_EXTERNALS_GROUP'
);
/* Finally, create our plans (or alternatively, use CREATE_SIMPLE_PLAN below) */
dbms_resource_manager.create_plan( plan => 'ERM_PLAN', comment => 'ERM MasterPlan', max_iops => NULL, max_mbps => NULL );
dbms_resource_manager.create_plan_directive( -- Prio 1: SYS_GROUP (gets ALL resources if it needs them)
plan => 'ERM_PLAN',
group_or_subplan => 'SYS_GROUP',
comment => 'Sys group',
mgmt_p1 => 100
);
dbms_resource_manager.create_plan_directive( -- Prio 2: Admins, Applications and Services (share what SYS_GROUP leaves them, 20:80 here)
plan => 'ERM_PLAN',
group_or_subplan => 'ERM_ADMIN_GROUP',
comment => 'App Administrators',
mgmt_p2 => 20
);
dbms_resource_manager.create_plan_directive(
plan => 'ERM_PLAN',
group_or_subplan => 'ERM_APPLICATION_GROUP',
comment => 'Applications and Services',
mgmt_p2 => 80
);
dbms_resource_manager.create_plan_directive( -- Prio 3: Batch Processes and Externals (share what's left by the above, 50:50 here)
plan => 'ERM_PLAN',
group_or_subplan => 'ERM_BATCH_GROUP',
comment => 'Scheduled batch jobs',
mgmt_p3 => 50
);
dbms_resource_manager.create_plan_directive(
plan => 'ERM_PLAN',
group_or_subplan => 'ERM_EXTERNALS_GROUP',
comment => 'Everybody else',
mgmt_p3 => 50
);
dbms_resource_manager.create_plan_directive( -- Prio 4: Everybody else (can use all that is left)
plan => 'ERM_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => 'Everybody else',
max_idle_time => 86400,
max_idle_blocker_time => 1800,
mgmt_p4 => 100
);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); -- Verify if everything is OK
--DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA(); -- If Validate failed, reset the above
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); -- If Validate succeded, submit the definition
END;
/
-- If everything went well, we can activate our new plan:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'ERM_PLAN'; -- SCOPE=BOTH ?
-- To switch off resource manager again, simply "activate no plan":
-- ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';