This section contains the contents of the Resource Manager Plan script that must be run after the upgrade, if Oracle Fusion Applications is running on an Oracle Virtual Machine (VM) environment. This script can be used to create FUSION Resource plan in Oracle 11g as well as in Oracle 12c for both PDB and Non-CDB.
To apply the FUSIONAPPS_PLAN (Resource Manager Plan) post upgrade, run the following SQL script as the privileged database user (SYSTEM/SYS):
SET LINES 200 PAGES 300
WHENEVER SQLERROR EXIT
DECLARE
comp varchar2(20);
pdbs varchar2(20);
USRROW NUMBER(2):= 0;
BEGIN
select substr(value,1,2) into comp from v$parameter where name='compatible';
SELECT COUNT(1) INTO USRROW FROM user_role_privs WHERE GRANTED_ROLE='DBA';
IF USRROW=0 THEN
RAISE_APPLICATION_ERROR (-20100, 'Insufficient privileges. You need DBA privileges to run this script.');
END IF;
IF comp>11 then
select sys_context('userenv','con_name') into pdbs from dual;
IF pdbs='CDB$ROOT' then
RAISE_APPLICATION_ERROR (-20111, 'Please run this script connnecting as FUSION PDB, You are connected to CDB.');
END iF;
END IF;
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
--Removing FUSIONAPPS_PLAN from scheduler windows before deleting
begin
for REC in (select WINDOW_NAME from DBA_SCHEDULER_WINDOWS where RESOURCE_PLAN = 'FUSIONAPPS_PLAN' ) LOOP
DBMS_SCHEDULER.SET_ATTRIBUTE (rec.window_name,'resource_plan','');
end LOOP;
end;
begin
DBMS_RESOURCE_MANAGER.SWITCH_PLAN( PLAN_NAME => '');
DBMS_RESOURCE_MANAGER.DELETE_PLAN('FUSIONAPPS_PLAN');
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
dbms_resource_manager.delete_consumer_group(CONSUMER_GROUP => 'FUSIONAPPS_ONLINE_GROUP');
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
dbms_resource_manager.delete_consumer_group(CONSUMER_GROUP => 'FUSIONAPPS_BATCH_GROUP');
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
dbms_resource_manager.delete_consumer_group(CONSUMER_GROUP => 'FUSIONAPPS_DIAG_GROUP');
EXCEPTION WHEN OTHERS THEN NULL;
END;
dbms_resource_manager.create_consumer_group(CONSUMER_GROUP => 'FUSIONAPPS_ONLINE_GROUP', COMMENT => 'Consumer Group for online users');
dbms_resource_manager.create_consumer_group(CONSUMER_GROUP =>'FUSIONAPPS_BATCH_GROUP', COMMENT => 'Consumer Group for batch');
dbms_resource_manager.create_consumer_group(CONSUMER_GROUP =>'FUSIONAPPS_DIAG_GROUP', COMMENT => 'Consumer Group for FUSION_RO and FUSION_ERO');
dbms_resource_manager.create_plan(PLAN => 'FUSIONAPPS_PLAN', COMMENT => 'Fus Applications Resource Plan');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan => 'FUSIONAPPS_PLAN',
GROUP_OR_SUBPLAN => 'FUSIONAPPS_ONLINE_GROUP',
comment => 'Online users at level 1',
MGMT_P1 => 35,
PARALLEL_DEGREE_LIMIT_P1 => 0,
SWITCH_TIME => 1200,
SWITCH_IO_MEGABYTES => 10000,
SWITCH_GROUP => 'CANCEL_SQL',
switch_for_call => TRUE );
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan => 'FUSIONAPPS_PLAN',
GROUP_OR_SUBPLAN => 'FUSIONAPPS_BATCH_GROUP',
comment => 'Batch users at level 1',
MGMT_P1 => 25);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan => 'FUSIONAPPS_PLAN',
GROUP_OR_SUBPLAN => 'SYS_GROUP',
comment => 'System administrator group at level 1',
MGMT_P1 => 10);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan => 'FUSIONAPPS_PLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
comment => 'Other users at level 1',
MGMT_P1 => 5);
--Add directives for maintenance and diag process during maintenance windows
--Note: These allocations are only active during maintenance windows.
IF pdbs<>'CDB$ROOT' and comp='12' then
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan => 'FUSIONAPPS_PLAN',
GROUP_OR_SUBPLAN => 'ORA$AUTOTASK',
comment => 'Maintenance Tasks',
MGMT_P1 => 20);
ELSE IF comp<>'12' then
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan => 'FUSIONAPPS_PLAN',
GROUP_OR_SUBPLAN => 'ORA$AUTOTASK_SUB_PLAN',
comment => 'Maintenance Tasks',
MGMT_P1 => 15);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan => 'FUSIONAPPS_PLAN',
GROUP_OR_SUBPLAN => 'ORA$DIAGNOSTICS',
comment => 'Background Diag Processes',
mgmt_P1 => 5);
END IF;
END IF;
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan => 'FUSIONAPPS_PLAN',
GROUP_OR_SUBPLAN => 'FUSIONAPPS_DIAG_GROUP',
comment => 'Diagnostic users at level 1',
mgmt_P1 => 5,
PARALLEL_DEGREE_LIMIT_P1 => 0,
SWITCH_TIME => 120,
SWITCH_IO_MEGABYTES => 10000,
SWITCH_GROUP => 'CANCEL_SQL',
MAX_EST_EXEC_TIME => 100,
switch_for_call => TRUE );
FOR rec IN (SELECT username FROM dba_users WHERE (username LIKE '%FUSION%' or username ='FMW_RUNTIME')) LOOP
IF (rec.username = 'FUSION_RO' OR rec.username = 'FUSION_ERO') THEN
dbms_resource_manager.set_consumer_group_mapping(attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER, value => rec.username, CONSUMER_GROUP => 'FUSIONAPPS_DIAG_GROUP');
ELSIF (rec.username <> 'FUSION_READ_ONLY') THEN
dbms_resource_manager.set_consumer_group_mapping(attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER, value => rec.username, CONSUMER_GROUP => 'FUSIONAPPS_BATCH_GROUP');
END IF;
end LOOP;
begin
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,value => 'SEARCHSYS', CONSUMER_GROUP => 'FUSIONAPPS_BATCH_GROUP');
--Specifically for RMAN: any session running a backup/copy operation with RMAN is automatically switched to SYS_GROUP when the operation begins.
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( attribute => DBMS_RESOURCE_MANAGER.ORACLE_FUNCTION, value => 'BACKUP', CONSUMER_GROUP => 'SYS_GROUP');
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( attribute => DBMS_RESOURCE_MANAGER.ORACLE_FUNCTION, value => 'COPY', consumer_group => 'SYS_GROUP');
EXCEPTION WHEN OTHERS THEN NULL;
end;
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
dbms_resource_manager_privs.grant_switch_consumer_group('PUBLIC','FUSIONAPPS_ONLINE_GROUP',FALSE);
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('PUBLIC','FUSIONAPPS_BATCH_GROUP',false);
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('PUBLIC','FUSIONAPPS_DIAG_GROUP',false);
--Grant DBA role privilege to switch to SYS_GROUP. Assumption is that any ADMIN task (including RMAN) will be performed by user with DBA Privileges.
---SYS/SYSTEM already map to SYS_GROUP by default.
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('DBA','SYS_GROUP',FALSE);
--Associate FUSIONAPPS_PLAN to all defined maintenance windows
begin
for REC in (SELECT WINDOW_NAME FROM DBA_SCHEDULER_WINDOWS WHERE resource_plan IS NULL OR RESOURCE_PLAN = '' ) LOOP
DBMS_SCHEDULER.SET_ATTRIBUTE (rec.window_name,'resource_plan','FUSIONAPPS_PLAN');
end LOOP;
end;
begin
--Sets the current resource manager plan
DBMS_RESOURCE_MANAGER.SWITCH_PLAN( PLAN_NAME => 'FUSIONAPPS_PLAN');
end;
END;
/
--- Setting newly created FUSION resource plan.
alter system set resource_manager_plan='FUSIONAPPS_PLAN' scope=both;