python program to check oracle alert log file for error in past 48 hours

# python program to check oracle alert log file for error in past 48 hours

#!/usr/bin/env python

import io
import datetime
import traceback

DayList=['Sun','Mon','Tue','Wed','Thu','Fri','Sat']
KeyWordList=['ORA-','Error','alter']
# KeyWordList=['ORA-','Error','Starting ORACLE instance','Shutting down instance']
OutputList=[]

# customize hours to look for 
SkipOldEventHours=48

# customize name of alert.log
AlertLogFile=r'alert_cisdbc.log'

SkipOldEventDateTimeDelta=datetime.timedelta(hours=SkipOldEventHours)
EventDate=datetime.datetime(1, 1, 1, 0, 0)

try:
    with io.open(AlertLogFile,mode='r') as f:
        for line in f:
           if len(line)>3 and line[0:3] in DayList:
                # OutputList.append(line)              
                EventDate=datetime.datetime.strptime(line.rstrip('\n'), '%a %b %d %H:%M:%S %Y')
                if EventDate < datetime.datetime.now()-SkipOldEventDateTimeDelta :
                    continue        
           elif len(line)>3:
                if EventDate < datetime.datetime.now()-SkipOldEventDateTimeDelta :
                    continue              
                for w in KeyWordList:
                    if w in line:
                        OutputList.append([EventDate,line.rstrip('\n')])
           else:
                continue
except:
    print(traceback.format_exc())

for o in OutputList:
    # use o[0].strftime('%a %b %d %H:%M:%S %Y') to get original Oracle style Format  
    print('[%s] %s' % (o[0],o[1]))

# the end

List TOP 10 by size directories

# List TOP 10 by size directories
du -m --max-depth 1 | sort -rn | head -11

Get max value in partition

Set serverout on size 1000000
declare
    cursor tab_part_cur IS
      select table_name,partition_name from user_tab_partitions where table_name='MESSAGE_LOG' order by 1,2;
    v_date date;
    v_pass integer := 0;
begin
    dbms_output.put_line('These partitions can be dropped.');
    for tab_part_rec in tab_part_cur
    loop
        exit when tab_part_cur%notfound;
        v_date := GET_MAX_LOG_TS(tab_part_rec.table_name,tab_part_rec.partition_name);
        if v_date <= (sysdate - 10) then
           dbms_output.put_line(tab_part_rec.table_name||' '||tab_part_rec.partition_name|| ' '||v_date );
        end if;
    end loop;
end;
/




CREATE  or replace FUNCTION GET_MAX_LOG_TS (
    p_TableName     IN VARCHAR2,
    p_PatitionName  IN VARCHAR2
) RETURN DATE
IS
   v_DateVal Date;
   v_string varchar2(400);
BEGIN      
       v_string := 'SELECT max(log_ts)  v_DateVal FROM MESSAGE_LOG PARTITION('||p_PatitionName||')';

dbms_output.put_line(v_string);
dbms_output.put_line(chr(10));
execute immediate v_string into into v_DateVal;;

    RETURN v_DateVal;
END GET_max_log_ts;
/

list max value in partition

function

CREATE OR REPLACE FUNCTION GET_HIGH_VALUE_AS_DATE (
    p_TableName     IN VARCHAR2,
    p_PatitionName  IN VARCHAR2
) RETURN DATE
IS
   v_LongVal LONG;
BEGIN
    SELECT HIGH_VALUE INTO v_LongVal
      FROM USER_TAB_PARTITIONS
     WHERE TABLE_NAME = p_TableName
       AND PARTITION_NAME = p_PatitionName;

    RETURN TO_DATE(substr(v_LongVal, 11, 19), 'YYYY-MM-DD HH24:MI:SS');
END GET_HIGH_VALUE_AS_DATE;
/

calls function
Set serverout on size 1000000
declare
    cursor tab_part_cur IS
      select table_name,partition_name from user_tab_partitions where table_name='BOMR_MESSAGE_LOG' order by 1,2;
    v_date date;
    v_pass integer := 0;
begin
    dbms_output.put_line('These partitions can be dropped.');
    for tab_part_rec in tab_part_cur
    loop
        exit when tab_part_cur%notfound;
        v_date := GET_HIGH_VALUE_AS_DATE(tab_part_rec.table_name,tab_part_rec.partition_name);
        if v_date <= (sysdate - 10) then
           dbms_output.put_line(tab_part_rec.table_name||' '||tab_part_rec.partition_name|| ' '||v_date );
        end if;
    end loop;
end;
/

rebuild unusable indexes

Set serverout on size 1000000
declare
    cursor idx_cur IS
      select owner, index_name  from dba_indexes where status = 'UNUSABLE';
    v_string varchar2(400);
   
begin
    dbms_output.put_line('These indexes are in unusable.');
    for idx_rec in idx_cur
    loop
        exit when idx_cur%notfound;
           dbms_output.put_line(idx_rec.owner||' '||idx_rec.index_name);
           v_string := 'alter index '|| idx_rec.owner ||'.'|| idx_rec.index_name||' rebuild;';
           dbms_output.put_line(v_string);
           dbms_output.put_line(chr(10));
           execute immediate v_string ;
    end loop;
end;
/