Hi
We are tying to implement the resource manager for manage the multiple workloads and when we try to switch resource consumer groups,
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('ETL_USER','MOB_ETL_LOW_PERF_CG');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-29393: user ETL_USER does not exist or is not logged on
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 4403
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 4429
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 737
ORA-06512: at line 2
the user exist in the database.
GRANTEE GRANTED_GROUP GRA INI
------------------------------ ------------------------------ --- ---
ETL_USER ETL_HIGH_PERF NO YES
ETL_USER ETL_LOW_PERF NO NO
Regards,
Jayanath
Steps to recreate
- CDB
DECLARE
l_plan VARCHAR2(30) := 'TESTDB01_CDB_PROFILE';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.create_cdb_plan(
plan => l_plan,
comment => 'The CDB resource plan using profiles');
DBMS_RESOURCE_MANAGER.create_cdb_profile_directive(
plan => l_plan,
profile => 'MOB_STD_PROF',
shares => 10,
utilization_limit => 100,
parallel_server_limit => 100);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'TESTDB01_CDB_PROFILE';
ALTER SESSION SET CONTAINER=TESTDB01_CTESTDB;
ALTER SYSTEM SET DB_PERFORMANCE_PROFILE=MOB_STD_PROF SCOPE=SPFILE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN;
SHOW PARAMETER DB_PERFORMANCE_PROFILE
-- PDB
---------------------------------------------------------------------------
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area();
DBMS_RESOURCE_MANAGER.create_pending_area();
dbms_resource_manager.create_consumer_group(
consumer_group => 'MOB_DATA_CG',
comment => 'MOB interactive application workload');
dbms_resource_manager.create_consumer_group(
consumer_group => 'MOB_ETL_HI_PERF_CG',
comment => 'MOB High Intensity ETL workload');
dbms_resource_manager.create_consumer_group(
consumer_group => 'MOB_ETL_LOW_PERF_CG',
comment => 'MOB Low Intensity ETL workload');
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
END;
/
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
attribute => dbms_resource_manager.oracle_user,
value => 'MOB_USER',
consumer_group => 'MOB_DATA_CG');
dbms_resource_manager.set_consumer_group_mapping(
attribute => dbms_resource_manager.oracle_user,
value => 'MOB_MIC_SRV',
consumer_group => 'MOB_DATA_CG');
dbms_resource_manager.set_consumer_group_mapping(
attribute => dbms_resource_manager.oracle_user,
value => 'APCH_USER',
consumer_group => 'MOB_DATA_CG');
dbms_resource_manager.set_consumer_group_mapping(
attribute => dbms_resource_manager.oracle_user,
value => 'ETL_USER',
consumer_group => 'MOB_ETL_HI_PERF_CG');
dbms_resource_manager.validate_pending_area();
END;
/
begin
dbms_resource_manager_privs.grant_system_privilege(grantee_name => 'MOB_USER',admin_option => false);
dbms_resource_manager_privs.grant_system_privilege(grantee_name => 'MOB_MIC_SRV',admin_option => false);
dbms_resource_manager_privs.grant_system_privilege(grantee_name => 'APCH_USER',admin_option => false);
dbms_resource_manager_privs.grant_system_privilege(grantee_name => 'MOB_RPT',admin_option => false);
dbms_resource_manager_privs.grant_system_privilege(grantee_name => 'ETL_USER',admin_option => false);
end;
/
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(
GRANTEE_NAME => 'MOB_USER',
CONSUMER_GROUP => 'MOB_DATA_CG',
GRANT_OPTION => FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(
GRANTEE_NAME => 'MOB_MIC_SRV',
CONSUMER_GROUP => 'MOB_DATA_CG',
GRANT_OPTION => FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(
GRANTEE_NAME => 'APCH_USER',
CONSUMER_GROUP => 'MOB_DATA_CG',
GRANT_OPTION => FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(
GRANTEE_NAME => 'MOB_RPT',
CONSUMER_GROUP => 'MOB_DATA_CG',
GRANT_OPTION => FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(
GRANTEE_NAME => 'ETL_USER',
CONSUMER_GROUP => 'MOB_ETL_HI_PERF_CG',
GRANT_OPTION => FALSE);
dbms_resource_manager_privs.grant_switch_consumer_group(
GRANTEE_NAME => 'ETL_USER',
CONSUMER_GROUP => 'MOB_ETL_LOW_PERF_CG',
GRANT_OPTION => FALSE);
/*May not be necessary */
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('MOB_USER','MOB_DATA_CG');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('MOB_MIC_SRV','MOB_DATA_CG');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('APCH_USER','MOB_DATA_CG');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('ETL_USER','MOB_ETL_HI_PERF_CG');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('MOB_RPT','MOB_DATA_CG');
END;
/
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping_pri(
explicit => 1,
client_program => 2,
oracle_user => 3,
service_module_action => 4,
service_module => 5,
module_name_action => 6,
module_name => 7,
service_name => 8,
client_os_user => 9,
client_machine => 10);
dbms_resource_manager.submit_pending_area();
END;
/
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan(
PLAN => 'WORKLOAD_RP',
comment => 'Resource plan for normal business hours');
dbms_resource_manager.create_plan_directive(
plan => 'WORKLOAD_RP',
group_or_subplan => 'SYS_GROUP',
comment => 'Default for SYS/SYSTEM groups',
mgmt_p1 => 100);
dbms_resource_manager.create_plan_directive(
plan => 'WORKLOAD_RP',
group_or_subplan => 'MOB_DATA_CG',
comment => 'High priority MOB Connections',
mgmt_p2 => 75,
parallel_degree_limit_p1 => 0);
dbms_resource_manager.create_plan_directive(
plan => 'WORKLOAD_RP',
group_or_subplan => 'MOB_ETL_HI_PERF_CG',
comment => 'For High intensity ETL work',
mgmt_p2 => 20,
parallel_degree_limit_p1 => 8);
dbms_resource_manager.create_plan_directive(
plan => 'WORKLOAD_RP',
group_or_subplan => 'MOB_ETL_LOW_PERF_CG',
comment => 'For Low intensity ETL work',
mgmt_p2 => 4,
parallel_degree_limit_p1 => 4);
dbms_resource_manager.create_plan_directive(
plan => 'WORKLOAD_RP',
group_or_subplan => 'OTHER_GROUPS',
comment => 'Unspecified workloads',
mgmt_p3 =>100);
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
END;
/
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'WORKLOAD_RP';
BEGIN
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('ETL_USER','MOB_ETL_LOW_PERF_CG');
END;
/
BEGIN
*
ERROR at line 1:
ORA-29393: user ETL_USER does not exist or is not logged on
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 4403
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 4429
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 737
ORA-06512: at line 2