Oracle Resource Manager

Resource Manager

Oracle Database Resource Manager enables you to limit database resources. Oracle Database Resource Manager lets you specify how much of the resources you want to assign. It provides granular control of database resources allocated to users, applications, and services. It can be used to guarantee certain session a minimum and maximum amount of CPU. Limiting parallelism, active session pools to control maximum number of sessions within the group. The most interesting features is manage runaway sessions and switch groups. Manage runaway sessions help us with terminating user session whenever a session or call consumes more than specified resources. Any session in a particular group can be switched to another group if the session consumes more than specified resource.

Elements of Oracle Database Resource Manager

1. Resource consumer group

2. Resource plan

3. Resource plan directive

Resource consumer group is collection of user based on their need. SYS_GROUP and OTHER_GROUPS are two predefined groups. User SYS and SYSTEM belongs to SYS_GROUP and all other users within a plan that do not belongs to any group falls in OTHER_GROUPS.

Resource plan is the directives stating how the resource should be shared among the consumer groups.

The set of Reource plan directive determines how resources will be distributed among the users with in a Resource Plan.

Create dummy table for testing.

Copying data from same table to the table.

 

 

Create Service for single instance database.

 

Start service.

 Check if service is listed on listener or not

Create pending area. If any already existing pending area.

Create Resource Manager Plan.

Create Plan Directives

Mapping services SRV_DEV to Resource Consumer Group.

Validate pending area.

Submit pending area.

Create tnsnames.

Connecting user using tnsnames.

Check current user is logged in using the services.

Execute cpu intensive query as user DILLI.

 Show oracle resource related parameters.

 Noticed CPU load has increased and PID 2519 has consumed 99.9% of CPU.

Set resource manager plan to DAY_PLAN.

Noticed CPU usage has decreased to 3.7% load.

Revert back the resource manager plan.

Noticed CPU% has increased to 99.9% for PID 2519.

Install Oracle 11g Grid Infrastructure With Oracle Virtualbox

Create a VM on Oracle VM VirtualBox Manager.

Type the Name, Type and Version and Click on Continue.

Select appropriate size of RAM. I have selected 4096 MB and click on continue.

I am using my existing virtual hard disk so I have selected "Use an existing virtual hard disk file" option and Virtual hard disk file. Those who do not have existing virtual hard disk, Please select "Create a virtual hard disk now" option and Click on Create.

You new VM is ready. Now select VM and click on Setting to change additional settings.

Disable audio.

Click on Network and select host only adapter option. Click on ok to continue.

Click on Start to start VM.

VM has been started. Shutdown the VM for additional steps.

Make Sure you have powered off the Virtual machine.

Add ASM disk to the VM. Make sure these drives are visible in the OS.
Click on VM -> Click on Setting -> Storage.

Click on + sign right to the Controller: SATA 

Click on "Choose existing disk"

Select proper vdi file and Click on Open.

Repeat it for all the asm disks.

Finally all the disks have been added. Now Click on OK.

Start the VM and start creating partitions on the newly added disks.

Verify list of disks has been available with the fdisk command

fdisk -l | grep Disk | grep bytes

Start Creating partition.

[root@OEL1 ~]# fdisk -l /dev/sdb

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xdb78f0d7

   Device Boot      Start         End      Blocks   Id  System

[root@OEL1 ~]# fdisk /dev/sdb

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-261, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-261, default 261):
Using default value 261

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@OEL1 ~]# fdisk -l /dev/sdb

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xdb78f0d7

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         261     2096451   83  Linux

We can copy the partition setting with sfdisk command
sfdisk -d /dev/sdb | sfdisk /dev/sdo

Download following rpm files: oracleasm-support-2.1.8-1.el6.x86_64.rpm, oracleasmlib-2.0.4-1.el6.x86_64.rpm and execute yum install kmod-oracleasm to install oracleasm kernel model.

Configure ASM Kernel Module

/etc/init.d/oracleasm configure

Type following values 

Default user to own the driver interface: oracle
Default group to own the driver interface: oinstall
Start Oracle ASM library driver on boot (y/n): y
Scan for Oracle ASM disks on boot (y/n): y

In the case the disk was already used and required cleanup execute following command to clean up the disk header to make sure the disk has not been already marked.

dd if=/dev/zero of=/dev/sdb1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdc1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdd1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sde1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdf1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdg1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdh1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdi1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdj1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdk1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdl1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdm1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdn1 bs=1024 count=1024
dd if=/dev/zero of=/dev/sdo1 bs=1024 count=1024

Stamp Disk partition as the ASM Disk

/etc/init.d/oracleasm createdisk ASMDISK01 /dev/sdb1
/etc/init.d/oracleasm createdisk ASMDISK02 /dev/sdc1
/etc/init.d/oracleasm createdisk ASMDISK03 /dev/sdd1
/etc/init.d/oracleasm createdisk ASMDISK04 /dev/sde1
/etc/init.d/oracleasm createdisk ASMDISK05 /dev/sdf1
/etc/init.d/oracleasm createdisk ASMDISK06 /dev/sdg1
/etc/init.d/oracleasm createdisk ASMDISK07 /dev/sdh1
/etc/init.d/oracleasm createdisk ASMDISK08 /dev/sdi1
/etc/init.d/oracleasm createdisk ASMDISK09 /dev/sdj1
/etc/init.d/oracleasm createdisk ASMDISK10 /dev/sdk1
/etc/init.d/oracleasm createdisk ASMDISK11 /dev/sdl1
/etc/init.d/oracleasm createdisk ASMDISK12 /dev/sdm1
/etc/init.d/oracleasm createdisk ASMDISK13 /dev/sdn1
/etc/init.d/oracleasm createdisk ASMDISK14 /dev/sdo1

View the list of the disk with following command

/etc/init.d/oracleasm listdisks

Start grid installation.
Make sure you have exported environment variable ORACLE_HOME as the grid_home
change directory to grid software  and execute runInstaller

Landing page after you execute ./runInstaller

Select Skip Software Updates and Click on Next.

Select Configure Oracle Grid Infrastructure for Standalone Server and Click on Next.

Add required Languages and click on Next.

Select Appropriate Disk Group Name, Redundancy, AU Size and candidate disks. In our case we have selected 6 disks from ASMDISK01-ASMDISK06 Click on Next to continue.

Select Appropriate Password that meets the complexity Click on Next to continue.

Select dba for all group settings Click on Next to continue.

Warning Message will be poped up simply click on Yes to continue.

Select appropriate Oracle Base and Grid Software location Click on Next to continue.

Select Inventory location and Click on Next to continue.

Since we have low swap space it will warn for it.  Just select Ignore All and Click on Next to continue.

Warning message will be pop up simply click on Yes to continue.

Click on save response file.

Select proper location and the file name. Click on save.

Click on Install to begin installation.

Installation on progress ##########.

Once Execute configuration script window pop up Open terminal and execute listed script as user root. Once execution is completed Click on OK.

ASM configuration is on progress.

Installation completion window will be appeared. Click on Close to end the installation.

List the process that is related to Oracle High Availability Services and ASM background Process.

1. Install Oracle 11g Grid Infrastructure With Oracle Virtualbox
2. ASM Disk Creation with asmca
3. Install Oracle 11g R2 Software
4. Create Oracle database with ASM Disk Group