Metric extensions for user defined metrics OEM 12c

We can create custom alerts in oem 12c

In this example i will show how to create an alerts if there are any user locks in the DB.

On oem 12c home page

go to

Enterprise > Monitoring > Metric extensions

Create a new metric



Target type: Choose what target this alert is for

In my case DB instance

Give a name

Adapter choose sql as i will be using a query for result

Leave the collection schedule as-is



In this page just give the sql query and hit next


Give a column name by clicking add column and select the o/p in my case o/p is a number

select critical threshold and warning

I chose >0 which means i get alerted immediately if there is a lock

I classified it as fault

Hit next

I used default monitoring credentials

tested with a test instance. Test was successful. Hit next





Click finish

Go to metric extensions page again. You will see the metric you just created

select that and click actions and select save as deployable draft


Once that is done. select the metric and click actions again and choose deploy to targets.
Add the target you want here to get alerted for and click Add

Once thats done. You are ready to go. Goto target page. Click Oracle database> Monitoring> All metrics

Here you will see the locks metric that you just created


Select it and you will see the current real time data.

Once you verify it is there. You need to configure your email alerts in incident rules as below.




Once thats done, Create a test table and update it from two different sessions without commit. You should see your alert email as below :)

Changing AWR snapshot retention

To check what the current retention is

 select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract( minute from snap_interval ) snapshot_interval,
extract( day from retention) *24*60+extract( hour from retention) *60+extract( minute from retention ) retention_interval,
topnsql
from dba_hist_wr_control;


SNAPSHOT_INTERVAL RETENTION_INTERVAL TOPNSQL  
----------------- ------------------ ----------
               60              20160 DEFAULT  



Current retention is snapshot every 60 minutes and retention days=20160 minutes

which means 14 days (24*14*60)


If we want to change retention to 10 days

which means (24*10*60) = 14400

 execute dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 14400);

enq: TX - allocate ITL entry fix

This is the fix for enq: TX - allocate ITL entry

First find the table your are having issues with

Check AWR report to verify you have ITL waits on the table

On the AWR it should look like this

Segments by ITL Waits

  * % of Capture shows % of ITL waits for each top segment compared
  * with total ITL waits for all segments captured by the Snapshot

Owner Tablespace Name Object Name Subobject Name Obj. Type       ITL  Waits % of Capture
PIN   HMA_ODS_DATA      CURR_ACX_PROFILE                  TABLE           3               84.30


Now we have 2 options

Increase INITRANS parameter.

Defualt value for tables is 1
Default value for index is 2

I found an article online which suggested to increase INITRANS to 100. I am not sure what happens if i just increase it to 3 or 4 depending on waits i see on AWR report. I will reupdate this article if i try again in future.

So i decided to change INITRANS to 100

If we change INITRANS using the below statement, only the newly modified values have this in effect.

alter table <table name> INITRANS 100;

To apply it for the whole table we need to use move

alter table <table_name> move;


After this rebuild the indexes as the above operation will invalidate them

alter index <index_name> rebuild INITRANS 100;

For me doing the above fixed the issue.

*************************************************************************

However if it did not fix the issue you can try adjusting PCTFREE

If the issue is not resolved by increasing INITRANS then try increasing PCTFREE. Increasing PCTFREE holds more space back and so spreads the same number of rows over more blocks. This means that there are more ITL slots available overall

alter table <table name>  PCTFREE 40;

alter table <table_name> move;

After this rebuild the indexes as the above operation will invalidate them

alter index <index_name> rebuild PCTFREE 40;

*************************************************************************

We can change both at the same time as well

alter table <table name>  PCTFREE 40 INITRANS 100;
alter table <table name> move;
alter index <index_name> rebuild PCTFREE 40 INITRANS 100;

Tar, gzip and Untar in solaris

cd /opt/app/oracle/product/11.2.0
tar cEvf database.tar /opt/app/oracle/product/11.2.0/11.2.0.4

cd /opt/app/crs/11.2.0
tar cEvf grid.tar /opt/app/crs/11.2.0/11.2.0.4


To untar it

tar -xvf database.tar
tar -xvf grid.tar



To gzip the tar file
gzip database.tar
gzip grid.tar

To unzip and untar
tar xvfz database.tar.gz
tar xvfz grid.tar.gz


Also try if above doesnt work

gtar xzvf somefile.tar.gz
if that doesn't work:
gunzip -c somefile.tar.gz |tar xvf -