Oracle Monitoring System Error Log Process

Oracle Monitoring System Error Log Process

-- Create a temporary table to store system error information
Create table superflow (cust_id number (10), cust_name varchar2 (100), d varchar (50), error_info varchar2 (500), client_ip varchar2 (50), protocol varchar2 (100 ), t number (20), sj date) tablespace db_data;

-- Drop table superflow

-- Create a data write path
Create directory db_dir as '/home/Oracle ';

-- Authorize the directory to the user
Grant read, write on directory db_dir to db;

Monitor System error logs
Process completion tasks include
Insert extracted error logs to superflow
The error log is written to the/home/oracle/SQL .txt file of the operating system.
Create or replace procedure pro_superflow

Pro_name VARCHAR2 (50 );
Err_info VARCHAR2 (1000 );
V_ SQL VARCHAR2 (1000 );
Sj VARCHAR2 (20 );
Jl number (10 );
-- Cursor record_t_sms_customer_errlog is select c. cust_id "Customer ID", cu. cust_name "customer name", c. d "status", c. error_info "error message", c. client_ip "customer IP", c. protocol "protocol", c. t "record COUNT" from (select CUST_ID, case ACTION when '1' log on to 'else' downlink 'end d, ERROR_INFO, CLIENT_IP, PROTOCOL, COUNT (ERRLOG_ID) t from t_sms_customer_errlog where create_time> sysdate-interval '1' hour and err_code = 8 group by cust_id, action, ERROR_INFO, client_ip, protocol) c, t_sms_customer cu where c. cust_id = cu. cust_id and t> 5;

Cursor record_t_sms_customer_errlog is select c. cust_id "Customer ID", cu. cust_name "customer name", c. d "status", c. error_info "error message", c. client_ip "customer IP", c. protocol "protocol", c. t "record COUNT" from (select CUST_ID, case ACTION when '1' log on to 'else' downlink 'end d, ERROR_INFO, CLIENT_IP, PROTOCOL, COUNT (ERRLOG_ID) t from t_sms_customer_errlog where err_code = 8 group by cust_id, action, ERROR_INFO, client_ip, protocol) c, t_sms_customer cu where c. cust_id = cu. cust_id;

-- All_record t_sms_customer_errlog % rowtype;
Type table_type is record (cust_id_1 number (10), cust_name_1 varchar2 (100), d varchar2 (20), error_info_1 varchar2 (1000), client_ip_1 varchar2 (50 ), protocol_1 varchar2 (1000), t_1 number (10 ));

All_record table_type;


Vsfile UTL_FILE.file_type;
Begin
Select count (1) into jl from superflow;
If jl> 10000 THEN

Delete from superflow where sj <sysdate-interval '7' day;

End if;
Vsfile: =
UTL_FILE.fopen ('db _ dir', ---> Use fopen to open a file and define the file path, file name, read/write mode, and the maximum length of each line of characters. The default value is 1024.
'SQL .txt ',
'W ');
For all_record in record_t_sms_customer_errlog
Loop

Insert into superflow values (all_record. customer id, all_record. customer name, all_record. status, all_record. error message, all_record. customer ip address, all_record. protocol, all_record. number of records, sysdate );
Commit;
UTL_FILE.put_line (vsfile, all_record. Customer id );
UTL_FILE.put_line (vsfile, all_record. customer name );
UTL_FILE.put_line (vsfile, all_record. status );
UTL_FILE.put_line (vsfile, all_record. error message );
UTL_FILE.put_line (vsfile, all_record. Client ip );
-- UTL_FILE.put_line (vsfile, all_record. Protocol)
UTL_FILE.put_line (vsfile, all_record. Number of records );
UTL_FILE.put_line (vsfile, sysdate );
-- Write the input content to a file
End loop;
UTL_FILE.fflush (vsfile );
UTL_FILE.fclose (vsfile );
Exception
When others then
-- Sg_log_err ('Manage _ partition ', sqlerrm );
Pro_name: = 'Manage _ partition ';
Err_info: = sqlerrm;
Select sysdate into sj from dual;
V_ SQL: = 'insert into err_log values ('| ''' PRO _ SUPERFLOW ''' |', ''' | err_info | ''', ''' | sj | ''')';
Execute immediate v_ SQL;
Commit;

Dbms_output.put_line (sqlcode | sqlerrm );
End pro_superflow;

Send the error log by email
Echo 'monitoring log' | mail-S' system log' [email protected]