Problem statement:We want to
automate the process of adding datafile when tablespace alert is crossing
critical threshold.To do this we will utilize corrective action feature of
OEM.Please follow below step by step process to automate this process.
From Enterprise>Monitoring>Corrective
Actions menu we can see below screen.
In General tab select event
type as Metric Alert and target type as Database Instance.
Provide Name and Description
as per your choice
Attach below PL/SQL code under parameters tab
SET SERVEROUTPUT ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
decalre
v_tablespace varchar2(100);
begin
v_tablespace := '%key_value%' ;
v_stmt := 'ALTER TABLESPACE ' || v_tablespace
|| ' ADD DATAFILE SIZE 10g AUTOEXTEND ON MAXSIZE UNLIMITED';
EXECUTE IMMEDIATE v_stmt;
dbms_output.put_line('Successfully added
datafile to: ' || v_tablespace );
END;
/
Publish the corrective actions under library so that it can be utilized
while attaching with rule set.
Now we need create incident rule so that corrective actions can be attached
with that rule.
To do that goto menu
Setup>Incident>Incident Rules
Provide Rule name and add specific database targets or Group,if you have
already created.
Here I have chosen specific database instance.
Define rule as shown below in screenshot
While searching specific Metric we can search with the string
Tablespace Space Used% like below and check/uncheck Undo/Temp
as
per your environment support policy.
Choose severity as Critical,which means rule will be applied only
for critical alert
Now under add action select corrective action as Add datafile and
optionally add your email.
Finish the rule set steps as per below screenshot
Now rule is ready for action when tablespace full alert reaches critical threshold.
Lets consider a table ci_cc_log whose size is 5 GB.I will create a tablespace of 2 GB maxsize and I will create to create backup of ci_cc_log into that tablespace.
SQL> select sum(bytes)/(1024*1024*1024) from dba_segments
where segment_name='CI_CC_LOG';
SUM(BYTES)/(1024*1024*1024)
---------------------------
5.29199219
SQL> create tablespace
smaltbl datafile '+DATA' size 1G autoextend on maxsize 2G;
Tablespace created.
Now try to create backup table like below
SQL> create table
ci_cc_log_bkp tablespace smaltbl as select * from cisadm.ci_cc_log where 1=0;
Table created.
Load data into table using below statement
SQL> insert /*+ append- */
into ci_cc_log_bkp (select * from cisadm.ci_cc_log);
insert /*+ append- */ into
ci_cc_log_bkp (select * from cisadm.ci_cc_log)
*
ERROR at line 1:
ORA-01653: unable to extend table
SYS.CI_CC_LOG_BKP by 8192 in tablespace
SMALTBL
Now focus on below snippet from alert log file.Error
appeared in log at 22:30 and add datafile command was executed by corrective
action at 22:30 and finished at 22:37
2024-07-18T22:29:16.809981-07:00
ORA-1653: unable to extend table SYS.CI_CC_LOG_BKP by 8192
in tablespace SMALTBL
2024-07-18T22:30:40.319901-07:00
ALTER TABLESPACE SMALTBL ADD DATAFILE '+DATA' SIZE 10G AUTOEXTEND
ON MAXSIZE 32767M
2024-07-18T22:30:46.238200-07:00
Completed: ALTER TABLESPACE SMALTBL ADD DATAFILE '+DATA'
SIZE 10G AUTOEXTEND ON MAXSIZE 32767M
2024-07-18T22:37:17.547144-07:00
This conclude our corrective action is working fine.If your
alert is configured you will receive a clear alert for Tablespace metric alert.
