2
shell脚本实现logmnr分析
500
3页
5次
墨值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
fi
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
fi
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
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文档的来源(墨天轮),文档链接,文档作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

关注
最新上传
暂无内容,敬请期待...
下载排行榜
Top250 周榜 月榜
近期活动
全部
暂无活动,敬请期待...
相关课程
全部