Download the pdf version: Auto Fix TBS alert
Introduction:
With database systems growing, database administrators increasingly have to deal with routine tasks. Small tasks may consume time, resources and increase the cost. Oracle 11g version provides solutions allowing to automatically fix some of those routine tasks.
Oracle 11g provides tools like Data Recovery Advisor DRA (check my article about this tool here: http://www.oracle-class.com/?p=1801) helping the DBA to take corrective actions. In this article, I am going to show you how to automate the fix of a tablespace alert.
Auto fix tablespace alert:
The manageability monitor process (MMON) wakes up every minute to check, capture and calculate metric values. MMON compares the actual values with the threshold value and raises and alert in case the threshold is exceeded. All alerts are then published to the queue ALERT_QUE.
To get the information about the alert, every agent must be subscribed to the queue. Every alert category has its own reason_id and every agent accessing the queue must know the reason_id to get information about the alert. For example, a tablespace alert has the reason id 9, an ORA 1555 has a reason id of 10.
You can get the reason id by executing the following PL/SQL block:
DECLARE
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message ALERT_TYPE;
message_handle RAW(16);
BEGIN
dequeue_options.consumer_name := 'ALERTAGENT';
dequeue_options.wait := DBMS_AQ.NO_WAIT;
dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;
DBMS_AQ.DEQUEUE(
queue_name => 'ALERT_QUE',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
DBMS_OUTPUT.PUT_LINE('Alert message dequeued:');
DBMS_OUTPUT.PUT_LINE(' Timestamp: ' || message.timestamp_originating);
DBMS_OUTPUT.PUT_LINE(' Organization Id: ' || message.organization_id);
DBMS_OUTPUT.PUT_LINE(' Component Id: ' || message.component_id);
DBMS_OUTPUT.PUT_LINE(' Message Type: ' || message.message_type);
DBMS_OUTPUT.PUT_LINE(' Message Group: ' || message.message_group);
DBMS_OUTPUT.PUT_LINE(' Message Level: ' || message.message_level);
DBMS_OUTPUT.PUT_LINE(' Host Id: ' || message.host_id);
DBMS_OUTPUT.PUT_LINE(' Host Network Addr: ' || message.host_nw_addr);
DBMS_OUTPUT.PUT_LINE(' Reason: ' ||
DBMS_SERVER_ALERT.EXPAND_MESSAGE(userenv('LANGUAGE'),message.message_id,
message.reason_argument_1,message.reason_argument_2,message.reason_argument_3,
message.reason_argument_4,message.reason_argument_5));
DBMS_OUTPUT.PUT_LINE(' Sequence Id: ' || message.sequence_id);
DBMS_OUTPUT.PUT_LINE(' Reason Id: ' || message.reason_id);
DBMS_OUTPUT.PUT_LINE(' Object Name: ' || message.object_name);
DBMS_OUTPUT.PUT_LINE(' Object Type: ' || message.object_type);
DBMS_OUTPUT.PUT_LINE(' Instance Name: ' || message.instance_name);
DBMS_OUTPUT.PUT_LINE(' Suggested action: ' ||
DBMS_SERVER_ALERT.EXPAND_MESSAGE(userenv('LANGUAGE'),
message.suggested_action_msg_id,
message.action_argument_1,message.action_argument_2,
message.action_argument_3,message.action_argument_4, message.action_argument_5));
DBMS_OUTPUT.PUT_LINE(' Advisor Name: ' || message.advisor_name);
DBMS_OUTPUT.PUT_LINE(' Scope: ' || message.scope);
END;
42 /
Alert message dequeued:
Timestamp: 14-AUG-11 05.32.44.337310 PM +02:00
Organization Id: oracle.com
Component Id: SMG
Message Type: Warning
Message Group: Space
Message Level: 5
Host Id: wissem.localdomain
Host Network Addr: ::1
Reason: Tablespace [MY_TBS] is [53 percent ] full
Sequence Id: 1426
Reason Id: 9
Object Name: MY_TBS
Object Type: TABLESPACE
Instance Name: ORAWISS
Suggested action: Add space to the tablespace
Advisor Name:
Scope: Database
PL/SQL procedure successfully completed.
Auto fix a given tablespace alert:
In this section, we will see how to fix the tablespace alert for a given tablespace.
Step by Step:
First create a tablespace:
[oracle@wissem dbs]$ rlsqlplus SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 15 12:00:40 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> drop tablespace TBS_TST_QUE including contents and datafiles; drop tablespace TBS_TST_QUE including contents and datafiles * ERROR at line 1: ORA-00959: tablespace 'TBS_TST_QUE' does not exist SQL> create tablespace TBS_TST_QUE datafile '/home/oracle/app/oracle/oradata/ORAWI/tbs_tst_que.dbf' size 1M; Tablespace created.
For this tablespace TBS_TST_QUE, we will define the alert threashold. We define the warning alert value from 80% and the critical value from 97% of tablespace usage.
SQL> exec dbms_server_alert.set_threshold(9000,dbms_server_alert.operator_ge,'80',dbms_server_alert.operator_ge,'97',1,1,null,dbms_server_alert.object_type_tablespace,'TBS_TST_QUE'); PL/SQL procedure successfully completed.
We create the agent who is going to dequeue the ALERT_QUE queue. To catch the tablespace error, the agent must subscribe to the queue following the rule with reason id 9 and the tablespace name as the object name .
SQL> exec dbms_aqadm.drop_aq_agent(agent_name=> 'AGENT_TBS_TST_QUE');
BEGIN dbms_aqadm.drop_aq_agent(agent_name=> 'AGENT_TBS_TST_QUE'); END;
*
ERROR at line 1:
ORA-24088: AQ Agent AGENT_TBS_TST_QUE does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 10476
ORA-06512: at "SYS.DBMS_AQADM", line 1311
ORA-06512: at line 1
SQL> exec dbms_aqadm.create_aq_agent(agent_name=> 'AGENT_TBS_TST_QUE');
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.REMOVE_SUBSCRIBER( queue_name => 'ALERT_QUE', subscriber => AQ$_AGENT('AGENT_TBS_TST_QUE','',0));
BEGIN DBMS_AQADM.REMOVE_SUBSCRIBER( queue_name => 'ALERT_QUE', subscriber => AQ$_AGENT('AGENT_TBS_TST_QUE','',0)); END;
*
ERROR at line 1:
ORA-24035: AQ agent AGENT_TBS_TST_QUE is not a subscriber for queue
SYS.ALERT_QUE
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7479
ORA-06512: at "SYS.DBMS_AQADM", line 441
ORA-06512: at line 1
SQL> exec dbms_aqadm.add_subscriber(queue_name=>'ALERT_QUE', subscriber => AQ$_AGENT('AGENT_TBS_TST_QUE','',0), rule => 'tab.user_data.reason_id IN (137,9) and tab.user_data.object_name =''TBS_TST_QUE''');
PL/SQL procedure successfully completed.
Give the appropriate privileges to the user SYSTEM for example. The SYSTEM user will be associated with subscribing agent and can access to any queued message.
SQL> exec dbms_aqadm.enable_db_access(agent_name =>'AGENT_TBS_TST_QUE', db_username=>'SYSTEM'); PL/SQL procedure successfully completed. SQL> exec dbms_aqadm.enable_db_access(agent_name =>'AGENT_TBS_TST_QUE', db_username=>'SYS'); PL/SQL procedure successfully completed. SQL> exec dbms_aqadm.grant_queue_privilege(privilege =>'DEQUEUE',queue_name => 'ALERT_QUE',grantee=>'SYSTEM',grant_option=>false); PL/SQL procedure successfully completed.
Create a log table.
SQL> create table log_my_tbs (P_DATE DATE, TEXT VARCHAR2(4000)); Table created.
Create a callback procedure; we define the automatic repair of the alert in this procedure.
SQL> create or replace procedure callback_tbs_tst_que(
3
context raw, reginfo sys.aq$_reg_info, descr sys.aq$_descriptor,
5
payload raw, payloadl number)
7
AS
9
dequeue_options DBMS_AQ.dequeue_options_t;
11
message_properties DBMS_AQ.message_properties_t;
13
message_handle RAW(16);
15
message ALERT_TYPE;
17
V_CURRENT_SIZE NUMBER;
V_NEXT_SIZE NUMBER;
ecode NUMBER;
emesg VARCHAR2(200);
22
BEGIN
24
25
insert into log_my_tbs values (sysdate, 'begin procedure call, success');
27
commit;
29
30
dequeue_options.consumer_name := 'AGENT_TBS_TST_QUE' ;
32
-- Dequeue the message
34
DBMS_AQ.DEQUEUE(queue_name => 'SYS.ALERT_QUE',
36
dequeue_options => dequeue_options,
38
message_properties => message_properties,
40
payload => message,
42
msgid => message_handle);
44
commit;
46
48
select NVL(sum(bytes)/1024/1024,0) INTO V_CURRENT_SIZE
from dba_data_files
where tablespace_name = 'TBS_TST_QUE'
;
insert into log_my_tbs values (sysdate, 'after calculate current size, success');
commit;
56 57
58
---add 5 MB
V_NEXT_SIZE:= V_CURRENT_SIZE + 5;
61
62
insert into log_my_tbs values (sysdate, 'after get new size, success');
64
commit;
66 67
EXECUTE IMMEDIATE 'ALTER DATABASE DATAFILE ''/home/oracle/app/oracle/oradata/ORAWI/tbs_tst_que.dbf'' RESIZE '||V_NEXT_SIZE||'M';
69
insert into log_my_tbs values (sysdate, 'after add datafile, success');
71
commit;
EXCEPTION
WHEN OTHERS THEN
ecode := SQLCODE;
emesg := SQLERRM;
dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
insert into log_my_tbs values (sysdate, 'error:::'||TO_CHAR(ecode) || '-' || emesg);
commit;
83
END;
85
86 /
Procedure created.
Now, we have to register the callback procedure to the subscriber AGENT_TBS_TST_QUE.
DECLARE
2
reginfo1 sys.aq$_reg_info;
4
reginfolist sys.aq$_reg_info_list;
6
BEGIN
8
reginfo1 := sys.aq$_reg_info('SYS.ALERT_QUE:AGENT_TBS_TST_QUE',
10
DBMS_AQ.NAMESPACE_AQ, 'plsql://SYS.CALLBACK_TBS_TST_QUE',
HEXTORAW('FF'));
14
-- Create the registration info list
16
reginfolist := sys.aq$_reg_info_list(reginfo1);
18
sys.dbms_aq.register(reginfolist, 1);
20 21 END;
22 /
PL/SQL procedure successfully completed.
To test the procedure, I am going to create a test table and fill it with data.
SQL> create table test_tbs (ID NUMBER) TABLESPACE TBS_TST_QUE; Table created. BEGIN for i in 1 .. 1000000 loop insert into test_tbs values (i); commit; end loop; 6 END; 7 / BEGIN * ERROR at line 1: ORA-01653: unable to extend table SYS.TEST_TBS by 8 in tablespace TBS_TST_QUE ORA-06512: at line 3 SQL> select tablespace_name, used_percent from dba_tablespace_usage_metrics where used_percent > 90; TABLESPACE_NAME USED_PERCENT ------------------------------ ------------ TBS_TST_QUE 100 SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; Session altered. set linesize 200 set pagesize 200 col P_DATE format a20 col TEXT format a40 SQL> SQL> SQL> SQL> select * from log_my_tbs; P_DATE TEXT -------------------- ---------------------------------------- 15.08.2011 12:06:26 begin procedure call, success 15.08.2011 12:06:26 after calculate current size, success 15.08.2011 12:06:26 after get new size, success 15.08.2011 12:06:27 after add datafile, success SQL> select tablespace_name, used_percent from dba_tablespace_usage_metrics where used_percent > 90; no rows selected SQL> SQL> select FILE_NAME, round(sum(bytes)/1024/1024 ,2) from dba_data_files where TABLESPACE_NAME = 'TBS_TST_QUE' group by FILE_NAME; FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ROUND(SUM(BYTES)/1024/1024,2) ----------------------------- /home/oracle/app/oracle/oradata/ORAWI/tbs_tst_que.dbf 6 SQL>
As you have see above, an ORA-01653 was raised. An automatic fix has been performed and the datafile size has been increased by 5 MB. Let ‘s have a look into the alert log file content;
SQL> !adrci ADRCI: Release 11.2.0.1.0 - Production on Mon Aug 15 12:11:30 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ADR base = "/home/oracle/app/oracle" adrci> show alert Choose the alert log from the following homes to view: 1: diag/rdbms/orawiss/ORAWISS 2: diag/rdbms/orawi/ORAWI 3: diag/tnslsnr/meka/listener 4: diag/tnslsnr/wissem/istener_orawiss 5: diag/tnslsnr/wissem/listener 6: diag/tnslsnr/wissem/listener_orawiss Q: to quit Please select option: 2 Output the results to file: /tmp/alert_30726_1404_ORAWI_1.ado ORA-1653: unable to extend table SYS.TEST_TBS by 8 in tablespace TBS_TST_QUE 2011-08-15 12:05:36.803000 +02:00 Thread 1 advanced to log sequence 8 (LGWR switch) Current log# 2 seq# 8 mem# 0: /home/oracle/app/oracle/oradata/ORAWI/onlinelog/o1_mf_2_74ks86z3_.log Current log# 2 seq# 8 mem# 1: /home/oracle/app/oracle/flash_recovery_area/ORAWI/onlinelog/o1_mf_2_74ks88dz_.log 2011-08-15 12:05:37.923000 +02:00 Archived Log entry 3 added for thread 1 sequence 7 ID 0x3a1d4760 dest 1: 2011-08-15 12:06:24.943000 +02:00 Starting background process EMNC EMNC started with pid=25, OS id=30546 2011-08-15 12:06:26.786000 +02:00 ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWI/tbs_tst_que.dbf' RESIZE 6M Completed: ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWI/tbs_tst_que.dbf' RESIZE 6M
We raise again the error;
SQL> BEGIN for i in 1 .. 1000000 loop insert into test_tbs values (i); commit; end loop; 6 END; 7 / BEGIN * ERROR at line 1: ORA-01653: unable to extend table SYS.TEST_TBS by 128 in tablespace TBS_TST_QUE ORA-06512: at line 3 SQL> select * from log_my_tbs; P_DATE TEXT -------------------- ---------------------------------------- 15.08.2011 12:06:26 begin procedure call, success 15.08.2011 12:06:26 after calculate current size, success 15.08.2011 12:06:26 after get new size, success 15.08.2011 12:06:27 after add datafile, success 15.08.2011 12:16:25 begin procedure call, success 15.08.2011 12:16:25 after calculate current size, success 15.08.2011 12:16:25 after get new size, success 15.08.2011 12:16:25 after add datafile, success 8 rows selected. SQL> SQL> select FILE_NAME, round(sum(bytes)/1024/1024 ,2) from dba_data_files where TABLESPACE_NAME = 'TBS_TST_QUE' group by FILE_NAME; FILE_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ROUND(SUM(BYTES)/1024/1024,2) ----------------------------- /home/oracle/app/oracle/oradata/ORAWI/tbs_tst_que.dbf 11 SQL> Alert log file content: 2011-08-15 12:14:55.360000 +02:00 ORA-1653: unable to extend table SYS.TEST_TBS by 128 in tablespace TBS_TST_QUE 2011-08-15 12:16:25.350000 +02:00 ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWI/tbs_tst_que.dbf' RESIZE 11M Completed: ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWI/tbs_tst_que.dbf' RESIZE 11M
Auto fix all tablespace alerts:
I have tested defining for every tablespace a dedicated callback procedure as well as a consumer ( or subscriber). I started doing it for 2 different tablespaces. The issue is by doing that, I was not able to fix an alert on the second tablespace and I have received ORA-25242: cannot change
subscriber name from AGENT_TBS_TST_QUE t
o AGENT_TBS_TST_QUE2 without FIRST_MESSA
GE option.
The easier solution I have found is to create a single callback procedure for all tablespaces.
Let’s do the test on two different tablespaces.
[oracle@wissem dbs]$ rlsqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 15 15:28:51 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create tablespace TBS_TST_QUE2 datafile '/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que2.dbf' size 1M;
Tablespace created.
SQL> create tablespace TBS_TST_QUE datafile '/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf' size 1M;
Tablespace created.
SQL> exec dbms_server_alert.set_threshold(9000,dbms_server_alert.operator_ge,'80',dbms_server_alert.operator_ge,'97',1,1,null,dbms_server_alert.object_type_tablespace,'TBS_TST_QUE2');
PL/SQL procedure successfully completed.
SQL> exec dbms_server_alert.set_threshold(9000,dbms_server_alert.operator_ge,'80',dbms_server_alert.operator_ge,'97',1,1,null,dbms_server_alert.object_type_tablespace,'TBS_TST_QUE');
PL/SQL procedure successfully completed.
SQL> exec dbms_aqadm.drop_aq_agent(agent_name=> 'AGENT_TBS_TST');
BEGIN dbms_aqadm.drop_aq_agent(agent_name=> 'AGENT_TBS_TST'); END;
*
ERROR at line 1:
ORA-24088: AQ Agent AGENT_TBS_TST does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 10476
ORA-06512: at "SYS.DBMS_AQADM", line 1311
ORA-06512: at line 1
SQL> exec dbms_aqadm.create_aq_agent(agent_name=> 'AGENT_TBS_TST');
PL/SQL procedure successfully completed.
SQL> exec dbms_aqadm.add_subscriber(queue_name=>'ALERT_QUE', subscriber => AQ$_AGENT('AGENT_TBS_TST','',0), rule => 'tab.user_data.reason_id IN (137,9)');
PL/SQL procedure successfully completed.
SQL> exec dbms_aqadm.enable_db_access(agent_name =>'AGENT_TBS_TST', db_username=>'SYSTEM');
PL/SQL procedure successfully completed.
SQL> exec dbms_aqadm.enable_db_access(agent_name =>'AGENT_TBS_TST', db_username=>'SYS');
PL/SQL procedure successfully completed.
SQL> exec dbms_aqadm.grant_queue_privilege(privilege =>'DEQUEUE',queue_name => 'ALERT_QUE',grantee=>'SYSTEM',grant_option=>false);
PL/SQL procedure successfully completed.
SQL> create table log_my_tbs_gen (P_DATE DATE, TEXT VARCHAR2(4000));
Table created.
create or replace procedure callback_tbs_tst(
context raw, reginfo sys.aq$_reg_info, descr sys.aq$_descriptor,
payload raw, payloadl number)
AS
dequeue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_handle RAW(16);
message ALERT_TYPE;
V_CURRENT_SIZE NUMBER;
V_NEXT_SIZE NUMBER;
ecode NUMBER;
emesg VARCHAR2(200);
14
CURSOR CUR_TBS IS
select NVL(sum(bytes)/1024/1024,0) AS CURR_SIZE,
''''||FILE_NAME||'''' as FILE_NAME
from dba_data_files
where tablespace_name IN ( select tablespace_name from dba_tablespace_usage_metrics where used_percent >= 80)
GROUP BY FILE_NAME
;
22
BEGIN
insert into log_my_tbs_gen values (sysdate, 'begin procedure call, success');
commit;
dequeue_options.consumer_name := 'AGENT_TBS_TST' ;
-- Dequeue the message
DBMS_AQ.DEQUEUE(queue_name => 'SYS.ALERT_QUE',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
commit;
37
38
insert into log_my_tbs_gen values (sysdate, 'after calculate current size, success');
41
commit;
43
FOR C IN CUR_TBS LOOP
45
V_NEXT_SIZE := 0;
47
V_CURRENT_SIZE := C.CURR_SIZE;
---add 5 MB
V_NEXT_SIZE:= V_CURRENT_SIZE + 5;
51
insert into log_my_tbs_gen values (sysdate, 'after get new size, success'||C.FILE_NAME);
53
commit;
55
EXECUTE IMMEDIATE 'ALTER DATABASE DATAFILE '||C.FILE_NAME||' RESIZE '||V_NEXT_SIZE||'M';
57
insert into log_my_tbs_gen values (sysdate, 'after add datafile, success'||C.FILE_NAME);
commit;
61
END LOOP;
63
EXCEPTION
WHEN OTHERS THEN
ecode := SQLCODE;
emesg := SQLERRM;
dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
insert into log_my_tbs_gen values (sysdate, 'error:::'||TO_CHAR(ecode) || '-' || emesg);
commit;
72
73 END;
74 /
Procedure created.
DECLARE
reginfo1 sys.aq$_reg_info;
reginfolist sys.aq$_reg_info_list;
BEGIN
reginfo1 := sys.aq$_reg_info('SYS.ALERT_QUE:AGENT_TBS_TST',
DBMS_AQ.NAMESPACE_AQ, 'plsql://SYS.CALLBACK_TBS_TST',
HEXTORAW('FF'));
-- Create the registration info list
reginfolist := sys.aq$_reg_info_list(reginfo1);
sys.dbms_aq.register(reginfolist, 1);
12
13 END;
14
15 /
PL/SQL procedure successfully completed.
One you understand the idea, you can optimize the code above by reducing the number of commits.
Now, let’s test having two tablespace alerts at the same time and we will see what will happen.
SQL> create table test_tbs_2 (ID NUMBER) TABLESPACE TBS_TST_QUE2; Table created. SQL> create table test_tbs (ID NUMBER) TABLESPACE TBS_TST_QUE; Table created. BEGIN for i in 1 .. 1000000 loop insert into test_tbs values (i); commit; end loop; 6 END; 7 / BEGIN * ERROR at line 1: ORA-01653: unable to extend table SYS.TEST_TBS by 8 in tablespace TBS_TST_QUE ORA-06512: at line 3 SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss'; Session altered. set linesize 200 set pagesize 200 col P_DATE format a20 SQL> col TEXT format a40 SQL> select * from log_my_tbs_gen; P_DATE TEXT -------------------- ---------------------------------------- 15.08.2011 15:35:08 begin procedure call, success 15.08.2011 15:35:08 after calculate current size, success 15.08.2011 15:35:08 after get new size, success'/home/oracle /app/oracle/oradata/ORAWISSE/tbs_tst_que .dbf' 15.08.2011 15:35:08 after add datafile, success'/home/oracle /app/oracle/oradata/ORAWISSE/tbs_tst_que .dbf' SQL> SQL> alert log file content: 2011-08-15 15:32:48.988000 +02:00 ORA-1653: unable to extend table SYS.TEST_TBS by 8 in tablespace TBS_TST_QUE 2011-08-15 15:35:06.459000 +02:00 Starting background process EMNC EMNC started with pid=33, OS id=17268 2011-08-15 15:35:08.362000 +02:00 ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf' RESIZE 6M Completed: ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf' RESIZE 6M
SQL> BEGIN
for i in 1 .. 1000000 loop
insert into test_tbs_2 values (i);
commit;
end loop;
END;
7 /
BEGIN
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TEST_TBS_2 by 8 in tablespace TBS_TST_QUE2
ORA-06512: at line 3
SQL> BEGIN
for i in 1 .. 1000000 loop
insert into test_tbs values (i);
commit;
end loop;
END;
7
8 /
BEGIN
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TEST_TBS by 128 in tablespace TBS_TST_QUE
ORA-06512: at line 3
SQL>
2011-08-15 15:40:43.370000 +02:00
ORA-1653: unable to extend table SYS.TEST_TBS_2 by 8 in tablespace TBS_TST_QUE2
2011-08-15 15:41:38.587000 +02:00
Archived Log entry 6 added for thread 1 sequence 10 ID 0xec497858 dest 1:
2011-08-15 15:41:40.228000 +02:00
ORA-1653: unable to extend table SYS.TEST_TBS by 128 in tablespace TBS_TST_QUE
select NVL(sum(bytes)/1024/1024,0) AS CURR_SIZE,
''''||FILE_NAME||'''' as FILE_NAME
from dba_data_files
where tablespace_name IN ( select tablespace_name from dba_tablespace_usage_metrics where used_percent >= 80)
5 GROUP BY FILE_NAME;
no rows selected
SQL> select object_name, reason from dba_outstanding_alerts;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
REASON
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TBS_TST_QUE2
Tablespace [TBS_TST_QUE2] is [84 percent] full
TBS_TST_QUE
Tablespace [TBS_TST_QUE] is [96 percent] full
SQL> select * from log_my_tbs_gen;
P_DATE TEXT
-------------------- ----------------------------------------
...
...
...
15.08.2011 18:17:48 begin procedure call, success
15.08.2011 18:17:48 after calculate current size, success
15.08.2011 18:17:49 after get new size, success'/home/oracle
/app/oracle/oradata/ORAWISSE/tbs_tst_que
.dbf'
15.08.2011 18:17:49 after add datafile, success'/home/oracle
/app/oracle/oradata/ORAWISSE/tbs_tst_que
.dbf'
15.08.2011 18:17:49 after get new size, success'/home/oracle
/app/oracle/oradata/ORAWISSE/tbs_tst_que
2.dbf'
15.08.2011 18:17:50 after add datafile, success'/home/oracle
/app/oracle/oradata/ORAWISSE/tbs_tst_que
2.dbf'
15.08.2011 18:17:50 begin procedure call, success
15.08.2011 18:17:50 after calculate current size, success
15.08.2011 18:17:50 after get new size, success'/home/oracle
/app/oracle/oradata/ORAWISSE/tbs_tst_que
.dbf'
15.08.2011 18:17:50 after add datafile, success'/home/oracle
/app/oracle/oradata/ORAWISSE/tbs_tst_que
.dbf'
SQL> select object_name, reason from dba_outstanding_alerts;
no rows selected
SQL>
Alert log file:
2011-08-15 18:17:49.062000 +02:00
ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf' RESIZE 32M
Completed: ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf' RESIZE 32M
ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que2.dbf' RESIZE 11M
2011-08-15 18:17:50.151000 +02:00
Completed: ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que2.dbf' RESIZE 11M
ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf' RESIZE 37M
Completed: ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ORAWISSE/tbs_tst_que.dbf' RESIZE 37M
I know, a commit inside a loop is not good. But, I am using that code just to get the alert.
As you have seen above, both alerts got cleared automatically and there is no more entry in the dba_outstanding_alerts table.
Conclusion:
The same idea can be applied on other category of alerts like for example; recovery area size alert, undo tablespace alert and others. The common procedure starts by creating an agent, subscribe it to the queue, dequeue the ALERT_QUE and register a callback procedure with the subscribed agent.
The callback procedure contains all the actions required to fix the alert.
Another solution to fix the alerts would be the creation of an action script (PL/SQL, Shell, …) and call it from the Enterprise Manager Console. In this case, make sure you have given the appropiate privileges to the action script.
Blog's RSS Feed
Loading ...
Just the scripts I posted above
Wissem,
It’s a good article detailing the mechanism for
fixing an Oracle alert.
The Tablespace usage alert’s exemple is interesting,
but may be resolved by enabling AUTOEXTEND feature.
The code in the callback procedure can implement any home based features.