Hi All,
I'm trying to activate Resource manager on oracle 11gR2 (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)...But my plan remains not ACTIVE. I don't understand why?
select plan, group_or_subplan, mgmt_p1, mgmt_P2,mgmt_p3,active_sess_pool_p1,queueing_p1,parallel_degree_limit_p1,max_est_exec_time, status
from dba_rsrc_plan_directives
where plan like 'NRSP%'
order by 1,2,3,4,5,6;
PLAN GROUP_OR_SUBPLAN MGMT_P1 MGMT_P2 MGMT_P3 ACTIVE_SESS_POOL_P1 QUEUEING_P1 PARALLEL_DEGREE_LIMIT_P1 MAX_EST_EXEC_TIME STATUS
NRSP-APP_PLAN NRSP_APP_GROUP 0 95 0
NRSP-APP_PLAN NRSP_READ_GROUP 0 5 0 50 1800 1 3600
NRSP-APP_PLAN OTHER_GROUPS 0 0 100
NRSP-APP_PLAN SYS_GROUP 100 0 0
select * from dba_rsrc_consumer_group_privs
where grantee='NRSP_GUEST'
GRANTEE GRANTED_GROUP GRANT_OPTION INITIAL_GROUP
NRSP_GUEST NRSP_READ_GROUP NO NO
select plan, cpu_method, sub_plan, comments, status
from dba_rsrc_plans
where plan like 'NRSP%'
order by 1
PLAN CPU_METHOD SUB_PLAN COMMENTS STATUS
NRSP-APP_PLAN EMPHASIS NO Resource plan/method for NRSP Databases
select username, initial_rsrc_consumer_group
from dba_users
where username like 'NRSP_GUEST';
USERNAME INITIAL_RSRC_CONSUMER_GROUP
NRSP_GUEST DEFAULT_CONSUMER_GROUP
below the spool file generated during the deployment of the plan NRSP-APP_PLAN
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 25 17:09:17 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> spool nrsp-app_plan.log
SQL> @@nrsp-app_plan.sql
Clear Pending Area
PL/SQL procedure successfully completed.
Create pending area for plan, consumer group and directives
PL/SQL procedure successfully completed.
1. Create Resource Plan
PL/SQL procedure successfully completed.
2. Create Resource Consumer Group
PL/SQL procedure successfully completed.
3. Create Resource Plan Directives
PL/SQL procedure successfully completed.
Validate Pending Area For Plan
PL/SQL procedure successfully completed.
Submit Pending Area For Plan
PL/SQL procedure successfully completed.
Create pending area for privileges, roles and assign users
PL/SQL procedure successfully completed.
4. Grant Switch Privilege For Resource Consumer Groups To Users Or Roles
PL/SQL procedure successfully completed.
5. Assign users to resource consumer groups
PL/SQL procedure successfully completed.
Validate Pending Area For Plan
PL/SQL procedure successfully completed.
Submit Pending Area For Plan
PL/SQL procedure successfully completed.
System altered.
SQL> spool off
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Here the SQL script used :
SET SERVEROUTPUT ON SIZE 1000000
-- Create pending area for plan, consumer group and directives
BEGIN
dbms_output.put_line('Clear Pending Area');
dbms_resource_manager.clear_pending_area();
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
BEGIN
dbms_output.put_line('Create pending area for plan, consumer group and directives');
dbms_resource_manager.create_pending_area();
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
-- 1. Create resource plan
BEGIN
dbms_output.put_line('1. Create Resource Plan');
dbms_resource_manager.create_plan
(plan => 'NRSP-APP_PLAN'
,comment => 'Resource plan/method for NRSP Databases');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
-- 2. Create resource consumer group
BEGIN
dbms_output.put_line('2. Create Resource Consumer Group');
dbms_resource_manager.create_consumer_group
(consumer_group => 'NRSP_APP_GROUP'
,comment => 'Resource consumer group/method for NRSP applicative users sessions');
dbms_resource_manager.create_consumer_group
(consumer_group => 'NRSP_READ_GROUP'
,comment => 'Resource consumer group/method for non-applicative users sessions who execute Ad-Hoc Queries');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
-- 3. Create resource plan directives
BEGIN
dbms_output.put_line('3. Create Resource Plan Directives');
dbms_resource_manager.create_plan_directive
(plan => 'NRSP-APP_PLAN'
,group_or_subplan => 'SYS_GROUP'
,comment => 'SYS Group - Mandatory Group'
,mgmt_p1 => 100 );
dbms_resource_manager.create_plan_directive
(plan => 'NRSP-APP_PLAN'
,group_or_subplan => 'NRSP_APP_GROUP'
,comment => 'Applicative NRSP Users'
,mgmt_P2 => 95);
dbms_resource_manager.create_plan_directive
(plan => 'NRSP-APP_PLAN'
,group_or_subplan => 'NRSP_READ_GROUP'
,comment => 'ADHOC users sessions'
,active_sess_pool_p1 => 50
,queueing_p1 => 1800 -- In seconds
,parallel_degree_limit_p1 => 1
,max_est_exec_time => 3600 -- In seconds
,mgmt_P2 => 5);
dbms_resource_manager.create_plan_directive
(plan => 'NRSP-APP_PLAN'
,group_or_subplan => 'OTHER_GROUPS'
,comment => 'OTHER_GROUP - Mandatory Group'
,mgmt_p3 => 100);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
-- Validate and Submit Pending area for plan
BEGIN
dbms_output.put_line('Validate Pending Area For Plan');
dbms_resource_manager.validate_pending_area();
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
BEGIN
dbms_output.put_line('Submit Pending Area For Plan');
dbms_resource_manager.submit_pending_area();
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
-- We have to grant privileges in order to assign consumer groups to users
-- Create pending area for privileges, roles and assign users
BEGIN
dbms_output.put_line('Create pending area for privileges, roles and assign users');
dbms_resource_manager.create_pending_area();
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
-- 4. Grant switch privilege for resource consumer groups to users or roles
BEGIN
dbms_output.put_line('4. Grant Switch Privilege For Resource Consumer Groups To Users Or Roles');
dbms_resource_manager_privs.grant_switch_consumer_group
(grantee_name => 'NRSP_GUEST'
,consumer_group => 'NRSP_READ_GROUP'
,grant_option => FALSE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
-- 5. Assign users to resource consumer groups
BEGIN
dbms_output.put_line('5. Assign users to resource consumer groups');
dbms_resource_manager.switch_consumer_group_for_user
(user => 'NRSP_GUEST'
,consumer_group => 'NRSP_READ_GROUP');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
-- Validate and Submit Pending area for plan
BEGIN
dbms_output.put_line('Validate Pending Area For Plan');
dbms_resource_manager.validate_pending_area();
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
BEGIN
dbms_output.put_line('Submit Pending Area For Plan');
dbms_resource_manager.submit_pending_area();
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SUBSTR(SQLERRM,1,200));
END;
/
-- 6. Specify the plan to be used
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'NRSP-APP_PLAN' SCOPE=BOTH
/
Thanks in advance for your support (helping and suggestions)