Tag: Capacity Management

ASM Storage Reclamation Utility (ASRU) for HP 3PAR Thin Provisioning

 

ASM Storage Reclamation Utility (ASRU) reclaims storage from an ASM disk group that was previously allocated but is no longer in use. In example after decommissioning a database. This Perl script writes blocks of Zeros where space is currently unallocated; the Zeros blocks are interpreted by the 3PAR Storage Server, as physical space to reclaim.

The execution of the ASRU script consists in three sequential phases:

  1. Compaction the disks are logically resized keeping 25% of free space for future needs and without affecting the physical size of the disks. This operation triggers the ASM disk group rebalance which compact the data at the beginning of the disks.
  2. Deallocation this phase writes Zeros blocks above the current data High Water Mark, those blocks of Zeros are interpreted by the storage as space available for reclaiming.
  3. Expansion here the utility resize the logical disks to the original size, because data remains untouched no ASM rebalance operation is required.

 

How to use ASRU

ASM Disk Groups

 

ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 4194304 3071904 1220008 511984 354012 0 N DATA/
MOUNTED NORMAL N 512 4096 4194304 7167776 3631252 511984 1559634 0 N FRA/
MOUNTED HIGH N 512 4096 1048576 41886 40621 20448 6405 0 Y OCRVOTING/
ASMCMD>

——————————————————————
Invoke the ASRU utility wirh the Grid Infrastructure owner
——————————————————————

[grid@xxxxxxxx space_reclaim]$ bash ASRU DATA
Checking the system ...done
Calculating the sizes of the disks ...done
Writing the data to a file ...done
Resizing the disks...done
Calculating the sizes of the disks ...done

/u01/GRID/11.2.0.4/perl/bin/perl -I /u01/GRID/11.2.0.4/perl/lib/5.10.0 /cloudfs/space_reclaim/zerofill 7 /dev/mapper/asm500GB_360002ac0000000000000000c0000964bp1 385789 511984 /dev/mapper/asm500GB_360002ac000000000000000150000964cp1 385841 511984 /dev/mapper/asm500GB_360002ac000000000000000160000964cp1 385813 511984 /dev/mapper/asm500GB_360002ac000000000000000110000964bp1 385869 511984 /dev/mapper/asm500GB_360002ac000000000000000120000964bp1 385789 511984 /dev/mapper/asm500GB_360002ac000000000000000140000964cp1 385789 511984
126171+0 records in
126171+0 records out
132299882496 bytes (132 GB) copied, 519.831 s, 255 MB/s
126195+0 records in
126195+0 records out
132325048320 bytes (132 GB) copied, 519.927 s, 255 MB/s
126195+0 records in
126195+0 records out
132325048320 bytes (132 GB) copied, 520.045 s, 254 MB/s
126143+0 records in
126143+0 records out
132270522368 bytes (132 GB) copied, 520.064 s, 254 MB/s
126115+0 records in
126115+0 records out
132241162240 bytes (132 GB) copied, 520.076 s, 254 MB/s
126195+0 records in
126195+0 records out
132325048320 bytes (132 GB) copied, 520.174 s, 254 MB/s

Calculating the sizes of the disks ...done
Resizing the disks...done
Calculating the sizes of the disks ...done
Dropping the file ...done

 

The second phase of the script called Deallocation uses dd to reset to zero the blocks beyond the HWM. One dd process per ASM Disk is started:

[grid@xxxxxxxx space_reclaim]$ top
top - 10:13:02 up 44 days, 16:16, 4 users, load average: 16.63, 16.45, 13.75
Tasks: 732 total, 6 running, 726 sleeping, 0 stopped, 0 zombie
Cpu(s): 2.8%us, 13.8%sy, 0.0%ni, 37.1%id, 43.9%wa, 0.0%hi, 2.4%si, 0.0%st
Mem: 131998748k total, 131419200k used, 579548k free, 42266420k buffers
Swap: 16777212k total, 0k used, 16777212k free, 3394532k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
 101 root 20 0 0 0 0 R 39.4 0.0 8:38.60 kswapd0
20332 grid 20 0 103m 1564 572 R 19.5 0.0 1:46.35 dd
20333 grid 20 0 103m 1568 572 D 18.2 0.0 1:44.93 dd
20325 grid 20 0 103m 1568 572 D 17.2 0.0 1:44.53 dd
20324 grid 20 0 103m 1568 572 R 15.6 0.0 1:20.63 dd
20328 grid 20 0 103m 1564 572 R 15.2 0.0 1:21.55 dd
20331 grid 20 0 103m 1568 572 D 14.6 0.0 1:21.42 dd
26113 oracle 20 0 60.2g 32m 26m S 14.6 0.0 0:00.75 oracle
20335 root 20 0 0 0 0 D 14.2 0.0 1:18.94 flush-252:24
20322 grid 20 0 103m 1568 572 D 13.9 0.0 1:21.51 dd
20342 root 20 0 0 0 0 D 13.2 0.0 1:16.61 flush-252:25
20338 root 20 0 0 0 0 R 12.9 0.0 1:17.42 flush-252:30
20336 root 20 0 0 0 0 D 10.9 0.0 1:00.66 flush-252:55
20339 root 20 0 0 0 0 D 10.9 0.0 0:57.79 flush-252:50
20340 root 20 0 0 0 0 D 10.3 0.0 0:58.42 flush-252:54
20337 root 20 0 0 0 0 D 9.6 0.0 0:58.24 flush-252:60
24409 root RT 0 889m 96m 57m S 5.3 0.1 2570:35 osysmond.bin
24861 root 0 -20 0 0 0 S 1.7 0.0 41:31.95 kworker/1:1H
21086 root 0 -20 0 0 0 S 1.3 0.0 36:24.40 kworker/7

[grid@xxxxxxxxxx~]$ ps -ef|grep 20332
grid 20332 20326 17 10:02 pts/0 00:01:16 /bin/dd if=/dev/zero of=/dev/mapper/asm500GB_360002ac000000000000000110000964cp1 seek=315461 bs=1024k count=196523

[grid@xxxxxxxxxx ~]$ ps -ef|grep 20325
grid 20325 20319 17 10:02 pts/0 00:01:35 /bin/dd if=/dev/zero of=/dev/mapper/asm500GB_360002ac0000000000000000d0000964cp1 seek=315309 bs=1024k count=196675

 

——————————————————————
ASM I/O Statistics  during the disk group rebalance
——————————————————————

ASMCMD> lsop
Group_Name Dsk_Num State Power EST_WORK EST_RATE EST_TIME
DATA REBAL WAIT 7
ASMCMD>
ASMCMD> iostat -et 5
Group_Name Dsk_Name Reads Writes Read_Err Write_Err Read_Time Write_Time
DATA S1_DATA01_FG1 23030185984 2082245521408 0 0 629.202365 561627.214525
DATA S1_DATA02_FG1 9678848 2002875955200 0 0 141.271598 556226.65866
DATA S1_DATA03_FG1 101520732160 2016216610304 0 0 3024.887841 561404.578818
DATA S2_DATA01_FG1 819643435008 2062069520896 0 0 50319.400536 563116.826573
DATA S2_DATA02_FG1 1126678040576 2045156313600 0 0 56108.943316 555738.806255
DATA S2_DATA03_FG1 947842624000 1994103517696 0 0 51845.856561 545466.151177
FRA S1_FRA01_FG1 9695232 305258886144 0 0 251.129038 5234.922326
FRA S1_FRA02_FG1 9691136 324037302272 0 0 234.499119 5478.064898
FRA S1_FRA03_FG1 9674752 287679095808 0 0 237.140794 4322.92991
FRA S1_FRA04_FG1 9678848 279486220800 0 0 563.687636 3845.515979
FRA S1_FRA05_FG1 9687040 287006669312 0 0 236.97403 4162.291019
FRA S1_FRA06_FG1 9695232 305493610496 0 0 260.062194 4776.712435
FRA S1_FRA07_FG1 9691648 286196798976 0 0 236.804526 14257.967546
FRA S2_FRA01_FG1 28695552 282395977216 0 0 565.469092 3874.206606
FRA S2_FRA02_FG1 63110656 290152312832 0 0 622.124042 14264.906378
FRA S2_FRA03_FG1 10750508032 318696439808 0 0 214.440821 5200.272304
FRA S2_FRA04_FG1 102140928 311658688512 0 0 624.488925 5098.68159
FRA S2_FRA05_FG1 55187456 298768577536 0 0 587.286013 4398.231978
FRA S2_FRA06_FG1 33064960 289082719232 0 0 21.587277 4597.368455
FRA S2_FRA07_FG1 28070912 284403925504 0 0 568.334218 4320.709945
OCRVOTING S1_OCRVOTING01_FG1 9666560 4096 0 0 292.504971 .000388
OCRVOTING S1_OCRVOTING02_FG2 9674752 0 0 0 14.6555 0
OCRVOTING S2_OCRVOTING01_FG1 10866688 4096 0 0 99.140306 .000388
OCRVOTING S2_OCRVOTING02_FG2 9695232 4096 0 0 110.684821 .000388
OCRVOTING S3_OCRVOTING01_FG1 9666560 0 0 0 73.171492 0


Group_Name Dsk_Name Reads Writes Read_Err Write_Err Read_Time Write_Time
DATA S1_DATA01_FG1 1329561.60 51507.20 0.00 0.00 0.13 0.01
DATA S1_DATA02_FG1 773324.80 417792.00 0.00 0.00 0.14 0.03
DATA S1_DATA03_FG1 1255014.40 11468.80 0.00 0.00 0.18 0.00
DATA S2_DATA01_FG1 0.00 5734.40 0.00 0.00 0.00 0.00
DATA S2_DATA02_FG1 32768.00 30208.00 0.00 0.00 0.00 0.02
DATA S2_DATA03_FG1 0.00 416972.80 0.00 0.00 0.00 0.01
FRA S1_FRA01_FG1 0.00 6553.60 0.00 0.00 0.00 0.00
FRA S1_FRA02_FG1 3276.80 10649.60 0.00 0.00 0.00 0.00
FRA S1_FRA03_FG1 0.00 0.00 0.00 0.00 0.00 0.00
FRA S1_FRA04_FG1 0.00 3276.80 0.00 0.00 0.00 0.00
FRA S1_FRA05_FG1 0.00 0.00 0.00 0.00 0.00 0.00
FRA S1_FRA06_FG1 0.00 3276.80 0.00 0.00 0.00 0.00
FRA S1_FRA07_FG1 0.00 4812.80 0.00 0.00 0.00 0.00
FRA S2_FRA01_FG1 0.00 819.20 0.00 0.00 0.00 0.00
FRA S2_FRA02_FG1 0.00 3276.80 0.00 0.00 0.00 0.00
FRA S2_FRA03_FG1 0.00 6553.60 0.00 0.00 0.00 0.00
FRA S2_FRA04_FG1 0.00 6553.60 0.00 0.00 0.00 0.00
FRA S2_FRA05_FG1 0.00 3276.80 0.00 0.00 0.00 0.00
FRA S2_FRA06_FG1 0.00 4812.80 0.00 0.00 0.00 0.00
FRA S2_FRA07_FG1 0.00 3276.80 0.00 0.00 0.00 0.00
OCRVOTING S1_OCRVOTING01_FG1 0.00 819.20 0.00 0.00 0.00 0.60
OCRVOTING S1_OCRVOTING02_FG2 0.00 819.20 0.00 0.00 0.00 0.60
OCRVOTING S2_OCRVOTING01_FG1 0.00 819.20 0.00 0.00 0.00 0.60
OCRVOTING S2_OCRVOTING02_FG2 0.00 819.20 0.00 0.00 0.00 0.60
OCRVOTING S3_OCRVOTING01_FG1 0.00 819.20 0.00 0.00 0.00 0.0


Group_Name Dsk_Name Reads Writes Read_Err Write_Err Read_Time Write_Time
DATA S1_DATA01_FG1 77004.80 248217.60 0.00 0.00 0.01 0.01
DATA S1_DATA02_FG1 6553.60 819.20 0.00 0.00 0.01 0.60
DATA S1_DATA03_FG1 83558.40 11468.80 0.00 0.00 0.01 0.00
DATA S2_DATA01_FG1 0.00 235110.40 0.00 0.00 0.00 0.01
DATA S2_DATA02_FG1 36044.80 17203.20 0.00 0.00 0.00 0.60
DATA S2_DATA03_FG1 0.00 8192.00 0.00 0.00 0.00 0.00
FRA S1_FRA01_FG1 0.00 6553.60 0.00 0.00 0.00 0.00
FRA S1_FRA02_FG1 3276.80 11468.80 0.00 0.00 0.00 0.01
FRA S1_FRA03_FG1 0.00 233472.00 0.00 0.00 0.00 0.01
FRA S1_FRA04_FG1 0.00 0.00 0.00 0.00 0.00 0.00
FRA S1_FRA05_FG1 0.00 0.00 0.00 0.00 0.00 0.00
FRA S1_FRA06_FG1 0.00 6553.60 0.00 0.00 0.00 0.00
FRA S1_FRA07_FG1 0.00 0.00 0.00 0.00 0.00 0.00
FRA S2_FRA01_FG1 0.00 1638.40 0.00 0.00 0.00 0.01
FRA S2_FRA02_FG1 0.00 0.00 0.00 0.00 0.00 0.00
FRA S2_FRA03_FG1 0.00 9830.40 0.00 0.00 0.00 0.00
FRA S2_FRA04_FG1 0.00 6553.60 0.00 0.00 0.00 0.00
FRA S2_FRA05_FG1 0.00 6553.60 0.00 0.00 0.00 0.00
FRA S2_FRA06_FG1 0.00 0.00 0.00 0.00 0.00 0.00
FRA S2_FRA07_FG1 0.00 233472.00 0.00 0.00 0.00 0.01
OCRVOTING S1_OCRVOTING01_FG1 0.00 1638.40 0.00 0.00 0.00 1.20
OCRVOTING S1_OCRVOTING02_FG2 0.00 1638.40 0.00 0.00 0.00 1.20
OCRVOTING S2_OCRVOTING01_FG1 0.00 1638.40 0.00 0.00 0.00 1.20
OCRVOTING S2_OCRVOTING02_FG2 0.00 1638.40 0.00 0.00 0.00 1.20
OCRVOTING S3_OCRVOTING01_FG1 0.00 1638.40 0.00 0.00 0.00 0.01

——————————————————————
ASM Alert Log produced during the execution of the ASRU utility
——————————————————————

Mon Apr 04 09:11:39 2016
SQL> ALTER DISKGROUP DATA RESIZE DISK S2_DATA03_FG1 SIZE 385840M DISK S1_DATA01_FG1 SIZE 385788M DISK S2_DATA02_FG1 SIZE 385812M DISK S1_DATA02_FG1 SIZE 385868M DISK S2_DATA01_FG1 SIZE 385788M DISK S1_DATA03_FG1 SIZE 385788M REBALANCE WAIT/* ASRU */
NOTE: GroupBlock outside rolling migration privileged region
NOTE: requesting all-instance membership refresh for group=1
Mon Apr 04 09:12:11 2016
NOTE: membership refresh pending for group 1/0x48695261 (DATA)
Mon Apr 04 09:12:12 2016
GMON querying group 1 at 10 for pid 18, osid 25195
SUCCESS: refreshed membership for 1/0x48695261 (DATA)
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
NOTE: starting rebalance of group 1/0x48695261 (DATA) at power 7
Starting background process ARB0
Mon Apr 04 09:12:15 2016
ARB0 started with pid=41, OS id=46711
NOTE: assigning ARB0 to group 1/0x48695261 (DATA) with 7 parallel I/Os
cellip.ora not found.
Mon Apr 04 09:13:38 2016
NOTE: stopping process ARB0
SUCCESS: rebalance completed for group 1/0x48695261 (DATA)
Mon Apr 04 09:13:39 2016
NOTE: GroupBlock outside rolling migration privileged region
NOTE: requesting all-instance membership refresh for group=1
Mon Apr 04 09:13:42 2016
GMON updating for reconfiguration, group 1 at 11 for pid 41, osid 47334
NOTE: group 1 PST updated.
SUCCESS: disk S1_DATA01_FG1 resized to 96447 AUs
SUCCESS: disk S1_DATA02_FG1 resized to 96467 AUs
SUCCESS: disk S2_DATA01_FG1 resized to 96447 AUs
SUCCESS: disk S2_DATA02_FG1 resized to 96453 AUs
SUCCESS: disk S2_DATA03_FG1 resized to 96460 AUs
SUCCESS: disk S1_DATA03_FG1 resized to 96447 AUs
NOTE: resizing header on grp 1 disk S1_DATA01_FG1
NOTE: resizing header on grp 1 disk S1_DATA02_FG1
NOTE: resizing header on grp 1 disk S2_DATA01_FG1
NOTE: resizing header on grp 1 disk S2_DATA02_FG1
NOTE: resizing header on grp 1 disk S2_DATA03_FG1
NOTE: resizing header on grp 1 disk S1_DATA03_FG1
NOTE: membership refresh pending for group 1/0x48695261 (DATA)
GMON querying group 1 at 12 for pid 18, osid 25195
SUCCESS: refreshed membership for 1/0x48695261 (DATA)
Mon Apr 04 09:13:48 2016
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
Mon Apr 04 09:13:49 2016
SUCCESS: ALTER DISKGROUP DATA RESIZE DISK S2_DATA03_FG1 SIZE 385840M DISK S1_DATA01_FG1 SIZE 385788M DISK S2_DATA02_FG1 SIZE 385812M DISK S1_DATA02_FG1 SIZE 385868M DISK S2_DATA01_FG1 SIZE 385788M DISK S1_DATA03_FG1 SIZE 385788M REBALANCE WAIT/* ASRU */
Mon Apr 04 09:22:42 2016
SQL> ALTER DISKGROUP DATA RESIZE DISK S2_DATA03_FG1 SIZE 511984M DISK S1_DATA01_FG1 SIZE 511984M DISK S2_DATA02_FG1 SIZE 511984M DISK S1_DATA02_FG1 SIZE 511984M DISK S2_DATA01_FG1 SIZE 511984M DISK S1_DATA03_FG1 SIZE 511984M REBALANCE WAIT/* ASRU */
NOTE: GroupBlock outside rolling migration privileged region
NOTE: requesting all-instance membership refresh for group=1
NOTE: requesting all-instance disk validation for group=1
Mon Apr 04 09:22:46 2016
NOTE: disk validation pending for group 1/0x48695261 (DATA)
SUCCESS: validated disks for 1/0x48695261 (DATA)
Mon Apr 04 09:23:24 2016
NOTE: increased size in header on grp 1 disk S1_DATA01_FG1
NOTE: increased size in header on grp 1 disk S1_DATA02_FG1
NOTE: increased size in header on grp 1 disk S2_DATA01_FG1
NOTE: increased size in header on grp 1 disk S2_DATA02_FG1
NOTE: increased size in header on grp 1 disk S2_DATA03_FG1
NOTE: increased size in header on grp 1 disk S1_DATA03_FG1
Mon Apr 04 09:23:24 2016
NOTE: membership refresh pending for group 1/0x48695261 (DATA)
Mon Apr 04 09:23:26 2016
GMON querying group 1 at 13 for pid 18, osid 25195
SUCCESS: refreshed membership for 1/0x48695261 (DATA)
NOTE: starting rebalance of group 1/0x48695261 (DATA) at power 7
Starting background process ARB0
Mon Apr 04 09:23:26 2016
ARB0 started with pid=38, OS id=53105
NOTE: assigning ARB0 to group 1/0x48695261 (DATA) with 7 parallel I/Os
cellip.ora not found.
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
Mon Apr 04 09:23:37 2016
NOTE: stopping process ARB0
SUCCESS: rebalance completed for group 1/0x48695261 (DATA)
Mon Apr 04 09:23:38 2016
NOTE: GroupBlock outside rolling migration privileged region
NOTE: requesting all-instance membership refresh for group=1
NOTE: membership refresh pending for group 1/0x48695261 (DATA)
Mon Apr 04 09:23:44 2016
GMON querying group 1 at 14 for pid 18, osid 25195
SUCCESS: refreshed membership for 1/0x48695261 (DATA)
Mon Apr 04 09:23:47 2016
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
Mon Apr 04 09:23:48 2016
SUCCESS: ALTER DISKGROUP DATA RESIZE DISK S2_DATA03_FG1 SIZE 511984M DISK S1_DATA01_FG1 SIZE 511984M DISK S2_DATA02_FG1 SIZE 511984M DISK S1_DATA02_FG1 SIZE 511984M DISK S2_DATA01_FG1 SIZE 511984M DISK S1_DATA03_FG1 SIZE 511984M REBALANCE WAIT/* ASRU */
Mon Apr 04 09:23:50 2016
SQL> /* ASRU */alter diskgroup DATA drop file '+DATA/tpfile'
SUCCESS: /* ASRU */alter diskgroup DATA drop file '+DATA/tpfile'

Once the ASRU utility has completed, the Storage Administrator should invoke the Space Compact from the 3Par console.

Oracle Resource Manager

 

#############################################################
##    How to implement Oracle Resource Manager granting    ##
##    CONSUMER GROUPS to Clusterware services              ##
#############################################################

--Create a service for OLTP sessions
srvctl add service -d dbrac10 -s DBRAC10_OLTP -r dbrac11,dbrac12,dbrac13
srvctl start service -d dbrac10 -s DBRAC10_OLTP

--Create a service for BATCH sessions
srvctl add service -d dbrac10 -s DBRAC10_BATCH -r dbrac11,dbrac12,dbrac13
srvctl start service -d dbrac10 -s DBRAC10_BATCH


###################################################################
## Resource Plan Design:
## MGMT_P1=75% SYS_GROUP,  MGMT_P2=80% OLTP, MGMT_P2=10% BATCH, MGMT_P2=5%
## ORA$AUTOTASK_SUB_PLAN,  MGMT_P2=5% ORA$DIAGNOSTICS, MGMT_P3=70% OTHER_GROUPS           
###################################################################       

## Resource Plan Implementation:
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'REAL_TIME_PLAN', COMMENT => 'Respurce Plan for OLTP database');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'OLTP',CATEGORY => 'INTERACTIVE', COMMENT => 'OLTP sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'BATCH', CATEGORY => 'BATCH', COMMENT => 'BATCH sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'OLTP', COMMENT => 'OLTP group', MGMT_P2 => 80);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'BATCH', COMMENT => 'BATCH group',
MGMT_P3 => 70, PARALLEL_DEGREE_LIMIT_P1 => 6, ACTIVE_SESS_POOL_P1 => 4, MAX_IDLE_TIME => 240);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'SYS_GROUP', COMMENT => 'SYS group', MGMT_P1 => 70);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'OTHER group', MGMT_P4 => 50);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN',GROUP_OR_SUBPLAN => 'ORA$AUTOTASK_SUB_PLAN', COMMENT => 'ORA$AUTOTASK_SUB_PLAN group', MGMT_P3 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'ORA$DIAGNOSTICS', COMMENT => 'ORA$DIAGNOSTICS group', MGMT_P3 => 10);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.SERVICE_NAME, 'DBRAC10_OLTP', 'OLTP');
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.SERVICE_NAME, 'DBRAC10_BATCH', 'BATCH');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

###################################################################


## Grant the Switch to the Users
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();    
dbms_resource_manager_privs.grant_switch_consumer_group ('PERF_TEST','OLTP',FALSE); 
dbms_resource_manager_privs.grant_switch_consumer_group ('PERF_TEST','BATCH',FALSE); 
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

###################################################################       
## Enable the Resource plan with the FORCE Option to avoid the Scheduler window to
## activate a different plan during the job execution.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:REAL_TIME_PLAN';
###################################################################       

## Example of Group and Plan Deletion:

BEGIN
DBMS_RESOURCE_MANAGER.DELETE_PLAN (PLAN => 'REAL_TIME_PLAN');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(CONSUMER_GROUP => 'OLTP');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(CONSUMER_GROUP => 'BATCH');
END;
/


###################################################################
## SQL Queries for monitoring Resource Manager  utilization:
###################################################################

## Check the service name used by each session
select inst_id, username, SERVICE_NAME, count(*) from gv$session 
where SERVICE_NAME <>'SYS$BACKGROUND'
group by inst_id, username, SERVICE_NAME order by  order by 2,3,1;


## List the Active Resource Consumer Groups:
select INST_ID, NAME, ACTIVE_SESSIONS, EXECUTION_WAITERS, REQUESTS, 
CPU_WAIT_TIME, CPU_WAITS, CONSUMED_CPU_TIME, YIELDS, QUEUE_LENGTH, 
ACTIVE_SESSION_LIMIT_HIT from gV$RSRC_CONSUMER_GROUP where name in 
('SYS_GROUP','BATCH','OLTP','OTHER_GROUPS') order by 2,1;