export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/11gR1/db
export ORACLE_SID=ORA
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib
export LD_LIBRARY_PATH
SHLIB_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32
export SHLIB_PATH
# Set shell search paths:
export PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH:/usr/local/bin
# CLASSPATH must include the following JRE locations:
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export DBCA_RAW_CONFIG=/opt/oracle/rawdevpath
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LANG=C
export LC_CTYPE=C
Day=`date -d "1 days ago" '+%Y%m%d'`
Month=`date -d "1 days ago" '+%Y%m'`
para1=`ps -ef | grep oracle | grep pmon | grep -v grep | awk '{print $8}'`;if [ ! -n "$para1" ]; then exit; fi
# sqlplus -s / as sysdba <<EOF
sqlplus -s user/password <<EOF
set head off;
set pagesize 0;
set linesize 160;
col DTIME1 format a20;
define dir='/home/oracle/shell/daily';
spool &dir/tmp_dailyswitch.lst;
select t.DTIME||','||t.PORTID||','||t.SWITCHID||','||t.INTERBANDWIDTH||','||t.INBYTES||','||t.INSINGLEPKG||','||
t.INLOSTPKG||','||t.INERRPKG||','||t.OUTBYTES||','||t.OUTSINGLEPKG||','||t.OUTLOSTPKG||','||t.OUTERRPKG||','||
t.INBROADCASTPKG||','||t.OUTBROADCASTPKG||','||t.UNKNOWNPORTLOSTPKG from t_base_switchport_${Day} t where
t.switchid='NE=C3MgrZone1111111112333' and t.portid in ('Ten-GigabitEthernet1/4/0/4','Ten-GigabitEthernet1/5/0/4');
spool off;
spool &dir/tmp_dailyroute.lst;
select t.DTIME||','||t.PORTID||','||t.ROUTERID||','||t.INTERBANDWIDTH||','||t.INBYTES||','||t.INSINGLEPKG||','||
t.INLOSTPKG||','||t.INERRPKG||','||t.OUTBYTES||','||t.OUTSINGLEPKG||','||t.OUTLOSTPKG||','||t.OUTERRPKG||','||
t.INBROADCASTPKG||','||t.OUTBROADCASTPKG||','||t.UNKNOWNPORTLOSTPKG from t_base_routerport_${Day} t where
routerid='NE=C3MgrZone1111111112333' and t.portid in ('xe-0/0/0','xe-0/0/1','xe-0/1/0','xe-1/0/0','xe-1/0/1');
spool off;
define dir
quit
EOF
echo
"DTIME,PORTID,SWITCHID,INTERBANDWIDTH,INBYTES,INSINGLEPKG,INLOSTPKG,INERRPKG,OUTBYTES,OUTSINGLEPKG,OUTLOSTPKG,OUTERRPKG,INB
ROADCASTPKG,OUTBROADCASTPKG,UNKNOWNPORTLOSTPKG" > /home/oracle/shell/daily/dailyswitch/dailyswitch_${Day}
cat /home/oracle/shell/daily/tmp_dailyswitch.lst | grep . >> /home/oracle/shell/daily/dailyswitch/dailyswitch_${Day}
echo
"DTIME,PORTID,ROUTERID,INTERBANDWIDTH,INBYTES,INSINGLEPKG,INLOSTPKG,INERRPKG,OUTBYTES,OUTSINGLEPKG,OUTLOSTPKG,OUTERRPKG,INB
ROADCASTPKG,OUTBROADCASTPKG,UNKNOWNPORTLOSTPKG" > /home/oracle/shell/daily/dailyroute/dailyroute_${Day}
cat /home/oracle/shell/daily/tmp_dailyroute.lst | grep . >> /home/oracle/shell/daily/dailyroute/dailyroute_${Day}
exit 0
在excel表打开用分列选择逗号为分隔符。
