OGG复制进程监控-外部表

 编写shell脚本

脚本1
-bash-3.00$ more ogg.sh 
PATH=/prod/oracle/product/10.2.0/db_1/bin:/usr/bin:/etc:/opt/EMCpower/bin/64:/etc/emc/bin:/prod/oracle/product/10.2.0/db_1/bin:/ogg/ggs;export PATH;
cd /ogg/ggs
ggsci <<eof
info all
info repprod_a
info repprod_b
info repprod_c
eof
 脚本2
 -bash-3.00$ more oggmonitor.sh 
MAIL=/usr/mail/${LOGNAME:?}
ORACLE_BASE=/prod/oracle;export ORACLE_BASE
ORACLE_HOME=/prod/oracle/product/10.2.0/db_1;export ORACLE_HOME 
ORACLE_SID=prodystem;export ORACLE_SID
#export ORACLE_HOSTNAME=prodoem
PATH=$ORACLE_HOME/bin:$PATH;export PATH
EDITOR=vi;export EDITOR
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/ogg/ggs;export LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME/bin:/usr/ccs/bin:$ORACLE_HOME/OPatch:/ogg/ggs;export PATH
/ogg/ogg.sh>/ogg/123.log
-bash-3.00$ 

创建crontab
-bash-3.00$ crontab -l
10 * * * * /export/home/prod/archdel.sh
0 5,16 * * * /prod/oracle/oraclecheck/checkdb.sh
10,11,12,13,14,20,22,25,30,32,35,38,39,40,41,43,44,45,50,55,56,57,58 * * * * /ogg/oggmonitor.sh 
-bash-3.00$ 

---

创建外部表进行喽数据

create table oggmonitor(
                    pro varchar2(100),
                    status varchar2(100),
                    repname varchar2(100),
                    time1 varchar2(121),
                    time2 varchar2(100)
                    )
            organization external
                    (
                    type oracle_loader
                    default directory oggdump
                    access parameters(
                                    records delimited by newline
                                    badfile 'emp_new%a_%p.bad'
                                    logfile 'emp_new%a_%p.log'
                                    fields terminated by ' '
                                    optionally enclosed by ' '
                                    lrtrim missing field values are null
                                    reject rows with all null fields
                                    )
                    location ('123.log')
)
parallel 
reject limit unlimited;

---

进行查询

select replace(pro, ' ', '') pro,
       replace(repname, ' ', '') repname,
       replace(status, ' ', ''),
       replace(time1, ' ', '') time1,
       replace(time2, ' ', '') time2
  from oggmonitor
 WHERE
pro in ('REPLICAT', 'MANAGER')
 AND STATUS IN ('STOPPED', 'RUNNING');
 
 grant select on oggmonitor to public;
 
select * From oggmonitor where pro='REPLICAT' AND STATUS IN('STOPPED','ABEND')；

 

 

案例2 去重大文件内容

create table in_tb1
            (ename varchar2(4000))
            organization external
            (type oracle_loader default directory oggdump 
             
location('123.dat'));

select distinct substr(substr(ename,1,instr(ename,'LEN',1)-1),instr(ename,'SRC',1))||' '||substr(substr(ename,1,instr(ename,'SPT',1)-1),instr(ename,'PROTO',1))||' '|| substr(substr
(ename,1,instr(ename,'WINDOW',1)-1),instr(ename,'DPT',1)) from in_tb1;