shell脚本实现logmnr分析

shell脚本实现logmnr分析

#!/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_TIME}', 'yyyy-mm-dd hh24:mi:ss') and next_time <to_date('${END_TIME}', 'yyyy-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',plesae 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_TIME}', 'yyyy-mm-dd hh24:mi:ss') and next_time <to_date('${END_TIME}', 'yyyy-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 where 1=0;
truncate table admin.archdump;
start addlogfile.sql;
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
insert into admin.archdump(timestamp,sql_redo,sql_undo,operation,seg_owner,seg_name,scn,start_scn,commit_scn,xidsqn,tx_name,table_name,username,os_username,machine_name,session#,serial#,session_info) select timestamp,sql_redo,sql_undo,operation,seg_owner,seg_name,scn,start_scn,commit_scn,xidsqn,tx_name,table_name,username,os_username,machine_name,session#,serial#,session_info from sys.v\$logmnr_contents;
commit;
exec dbms_logmnr.end_logmnr;
exit
EOF
echo "Please query archivelog contents on admin.archdump by PLSQL!"

示例：

[root@db ~]# su - oracle
[oracle@db ~]$ sh logmnr.sh '2019-05-28 15:11:00' '2019-05-28 15:12:00'       
Please query archivelog contents on admin.archdump by PLSQL!

select timestamp,sql_redo,operation,seg_owner,seg_name,scn,
start_scn,commit_scn,xidsqn,tx_name,table_name,username,os_username,machine_name,
session#,serial#,session_info 
from admin.archdump where seg_name='TEST';

另附脚本

链接:https://pan.baidu.com/s/1bocyJHt1VrxRvSEUY44GmA  密码:h5n1