Database Resource Manager on 11gR2

Creating a plan that kill session if this session exceeds the CPU limit specified:

begin

dbms_resource_manager.create_plan_directive(plan => 'HIGH',
switch_estimate => TRUE,
group_or_subplan => 'oltp_pl',
mgmt_p1 => 85,
switch_group => 'kill_session',
switch_time => 50);
end;


When the session exceeds 50 seconds of CPU usage time, the session is killed.

Calibrating I/O on 11g

You can use PL/SQL to calibrate I/O as you can use Database Control. A simple example of how to use this tool using PL/SQL is below:

declare

v_max_mbps binary_integer;
v_actual_latency binary_integer;
v_max_iops binary_integer;
begin
dbms_resource_manager.calibrate_io(
num_physical_disks => 10,
max_latency => 15,
max_iops => v_max_iops,
max_mbps => v_max_mbps,
actual_latency => v_actual_latency);
end;
/


where:
num_disks:     number of disks on your storage (approximately)
max_latency:  maximum value for a tolerable latency in miliseconds
max_iops:      maximu number of random db-block size read request
actual_latency: average latency


--Now query the view v$io_calibration_status to check the status of job
--Finally check dba_rsrc_io_calibrate to check the results

Installing Oracle 11gR2 on RH5 UP3

When I was installing Oracle Database 11gR2 on RH5 UP3 I didn't found all elf*.rpm files. As it is a database that I'm using for training purpose, I have just ignored these files and everything os working fine until now :).

!!!DON'T DO THAT IF YOU ARE INSTALLING FOR A PRODUCTION ENVIRONMENT!!!

Creating Single AWR Baseline Template

We cam now , in 11gR2 schedule  baseline template to run in a future time just using the package dbms_workload like bellow:


begin

dbms_workload_repository.create_baseline_template (
start_time => to_date('2009/10/28 22:00:00','yyyy/mm/dd hh24:mi:ss'),
end_time => to_date('2009/10/29 08:00:00','yyyy/mm/dd hh24:mi:ss'),
baseline_name => 'baseline1',
template_name => 'base_temp_1',
expiration => 30);
end;
/

Then, you can check this object using view wrm$_baseline_template;

In this example the AWR will create a baseline captured between 2009/10/28 22:00 and 2009/10/29 08:00.

Installing Win XP Professional - Missing xml files

If you find problems when installing your win XP Professional because of missing .xml files, try to format your partition and install again. It works for me!

Ensure that you have backed up everything before that :)

LOB Segments and enq: HW Contention

If you have LOB segments and are experiencing system wait event 'enq: HW Contention' then you should use this workaround:

1-Check for HW contention:
select
*
from
gv$session_wait
where
event like '%contention%';

2-Get the block and file id
select
dbms_utility.data_block_address_file(id2) file#,
dbms_utility.data_block_address_block(id2) block#
from
gv$lock
where
type='HW'
and
inst_id= (&use the instance number where you found the HW contention);

3-Find the object
select
owner,
segment_type,
segment_name
from
dba_extents
where
file_id=&USE_THE_FILE#
and
&USE_BLOCK_NUMBER between block_id
and
block_id + blocks - 1;

4-Check the extent size and add one extent:
alter table owner.table_name
modify lob (lob_name) (allocate extent (size 131072k));


5-Your HW Contention have gone. I hope so:)

Pending Transactions

Use the following queries to get pending transactions and after commit or rollback, purge then if it's necessary:

--Identifying transactions
select
'commit force '''local_tran_id'''; '
from
dba_2pc_pending;

--Purging
select
'exec dbms_transaction.purge_lost_db_entry('''local_tran_id'''); '
from
dba_2pc_pending
where
state='forced commit';