shell脚本实现logmnr分析

Kay

930

3页

9次

2019-08-21

5墨值下载

#!/bin/bash#!/bin/bash

# Created:2019-05-28

# Written by:Kay

# Description: analyze archivelog using logmnr fetch sql_redo!

# Please make happy in analyze log!

#ORACLE_SID=ora11g;

START_TIME=$1;

#START_TIME example '2019-01-01 10:00:00'

END_TIME=$2;

#END_TIME=`date +"%Y-%m-%d %H:%M:%S"`

#EXAMINE END_TIME IS NULL

if [ -z "$2" ]

then

echo "Please input LOGMNR'S END TIME,example '2019-01-01 10:00:

00'"

exit

out_file="/tmp/logfilelist"

#result=`tail -n 1 /tmp/logfilelist.lst`

#echo $result

#if [[ $result == 'SQL> spool off' ]]

#then

# echo "no archivelog between '$START_TIME' and '$END_TIME'"

# exit

#fi

RESULT=

RESULT=`sqlplus -s /nolog <<EOF

set echo off feedback off heading off underline off;

conn / as sysdba;

select name from v\\$archived_log where next_time > to_date('${START_TI

ME}', 'yyyy-mm-dd hh24:mi:ss') and next_time <to_date('${END_TIME}', 'y

yyy-mm-dd hh24:mi:ss') and name is not null;

exit;

EOF`

if [ -z "$RESULT" ];

then

echo "NO archivelog between '$START_TIME' and '$END_TIME',plesa

shellᚕ๜ਫሿlogmnrړຉ

e change time range"

exit

else

sqlplus / as sysdba >/dev/null 2>&1 <<EOF

set echo off

set sqlblanklines off

set feedback off

set heading off

set pagesize 50000

set linesize 50000

set newpage none

set pages 0

set trimspool on

set newpage none

set termout off

set trimout on

spool ${out_file}

select name from v\$archived_log where next_time > to_date('${START_TIM

E}', 'yyyy-mm-dd hh24:mi:ss') and next_time <to_date('${END_TIME}', 'yy

yy-mm-dd hh24:mi:ss') and name is not null;

spool off

exit

EOF

awk 'BEGIN{print "begin"}NR>1 && !/SQL/ {print "dbms_logmnr.add_logfile

(logfilename=>'\''"$1"'\'');"}END{print "end; \n /"}' /tmp/logfilelist.

lst > addlogfile.sql

sqlplus / as sysdba >/dev/null 2>&1 <<EOF

create table admin.archdump as select * from sys.v\$logmnr_contents whe

re 1=0;

truncate table admin.archdump;

start addlogfile.sql;

exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_cat

alog);

insert into admin.archdump(timestamp,sql_redo,sql_undo,operation,seg_ow

ner,seg_name,scn,start_scn,commit_scn,xidsqn,tx_name,table_name,usernam

e,os_username,machine_name,session#,serial#,session_info) select timest

amp,sql_redo,sql_undo,operation,seg_owner,seg_name,scn,start_scn,commit

_scn,xidsqn,tx_name,table_name,username,os_username,machine_name,sessio

n#,serial#,session_info from sys.v\$logmnr_contents;

commit;

of 3

5墨值下载

关注

评论