1、执行oracle数据库中的存储过程
proc.sh脚本内容(IBM的AIX环境下)
ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0;export ORACLE_HOME
ORACLE_SID=commdb;export ORACLE_SID
$ORACLE_HOME/bin/sqlplus cfa/cfa< exec sp_B_20090827;
exit
!
2、备份文件名后缀为日期对数据库中的表进行exp备份
exp_tab_perday.sh脚本内容(IBM的AIX环境下)
DATE=`date +%Y%m%d_%T`;export DATE
ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0;export ORACLE_HOME
ORACLE_SID=commdb;export ORACLE_SID
$ORACLE_HOME/bin/exp cfa/cfa file=/oracle/dlbk_table$DATE.dmp log=/oracle/dlbk_table$DATE.log tables=cfa_income_ent_new,cfa_income_inst_old,cfa_income_ent_old
3、对exp的用户备份,进行恢复imp_user.sh脚本内容(RedHat AS4环境下)
export ORACLE_HOME=/opt/oracle/product/10g
$ORACLE_HOME/bin/imp cfa/cfa@orcl file=/home/oracle/dlbk_cfa2009-09-12.dmp log=/home/oracle/dlbk_cfa2009-09-12.dmp fromuser=cfa touser=cfa
4、将oracle数据库中表的数据用spool导成标准的txt格式
调度脚本spool_out.sh的内容(IBM的AIX环境下):
ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0;export ORACLE_HOME
ORACLE_SID=commdb;export ORACLE_SID
DATE=`date +%Y%m%d_%T`;export DATE
$ORACLE_HOME/bin/sqlplus cfa/cfa @/oracle/cfaout/spool.sql spool配置脚本spool.sql的内容
set heading on;
set feedback off;
set pagesize 0;
set linesize 8000;
set trimout on;
set trimspool on;
set term off;
SET NEWPAGE 1;
spool /oracle/cfaout/aix_ent_info_$DATE.txt;@/oracle/cfaout/select_ent_info.sql;
spool off;
spool /oracle/cfaout/aix_report_record_$DATE.txt;@/oracle/cfaout/select_report_record.sql;
spool off;
exit;
两select表成标准格式脚本内容:
select_ent_info.sql:
select CUSTOMERID||'|'||CORPID||'|'||ENTERPRISENAME||'|'||ENGLISHNAME||'|'||FICTITIOUSPERSON||'|'||ORGNATURE||'|'||FINANCETYPE||'|'||ENTERPRISEBELONG||'|'||INDUSTRYTYPE||'|'||INDUSTRYTYPE1||'|'||INDUSTRYTYPE2||'|'||PRIVATE||'|'||ECONOMYTYPE||'|'||ORGTYPE||'|'||MOSTBUSINESS||'|'||BUDGETTYPE||'|'||RCCURRENCY||'|'||REGISTERCAPITAL||'|'||PCCURRENCY||'|'||PAICLUPCAPITAL||'|'||FUNDSOURCE||'|'||TOTALASSETS||'|'||NETASSETS||'|'||ANNUALINCOME||'|'||SCOPE||'|'||LIMIT||'|'||CREDITDATE||'|'||LICENSENO||'|'||LICENSEDATE||'|'||LICENSEMATURITY||'|'||SETUPDATE||'|'||INSPECTIONYEAR||'|'||LOCKSITUATION||'|'||TAXNO||'|'||BANKLICENSE||'|'||BANKID||'|'||MANAGEAREA||'|'||BANCHAMOUNT||'|'||EXCHANGEID||'|'||REGISTERADD||'|'||CHARGEDEPARTMENT||'|'||OFFICEADD||'|'||OFFICEZIP||'|'||COUNTRYCODE||'|'||REGIONCODE||'|'||VILLAGECODE||'|'||VILLAGENAME||'|'||RELATIVETYPE||'|'||OFFICETEL||'|'||OFFICEFAX||'|'||WEBADD||'|'||EMAILADD||'|'||EMPLOYEENUMBER||'|'||MAINPRODUCTION||'|'||NEWTECHCORPORNOT||'|'||LISTINGCORPORNOT||'|'||HASIERIGHT||'|'||HASDIRECTORATE||'|'||BASICBANK||'|'||BASICACCOUNT||'|'||MANAGEINFO||'|'||CUSTOMERHISTORY||'|'||PROJECTFLAG||'|'||REALTYFLAG||'|'||WORKFIELDAREA||'|'||WORKFIELDFEE||'|'||ACCOUNTDATE||'|'||LOANCARDNO||'|'||LOANCARDPASSWORD||'|'||LOANCARDINSYEAR||'|'||LOANCARDINSRESULT||'|'||LOANFLAG||'|'||FINANCEORNOT||'|'||FINANCEBELONG||'|'||CREDITBELONG||'|'||CREDITLEVEL||'|'||EVALUATEDATE||'|'||OTHERCREDITLEVEL||'|'||OTHEREVALUATEDATE||'|'||OTHERORGNAME||'|'||INPUTORGID||'|'||INPUTUSERID||'|'||INPUTDATE||'|'||UPDATEORGID||'|'||UPDATEUSERID||'|'||UPDATEDATE||'|'||REMARK||'|'||TAXNO1||'|'||FICTITIOUSPERSONID||'|'||GROUPFLAG||'|'||EVALUATELEVEL||'|'||MYBANK||'|'||MYBANKACCOUNT||'|'||OTHERBANK||'|'||OTHERBANKACCOUNT||'|'||TEMPSAVEFLAG||'|'||FINANCEDEPTTEL||'|'||ECGROUPFLAG||'|'||SUPERCORPNAME||'|'||SUPERLOANCARDNO||'|'||SUPERCERTTYPE||'|'||SMEINDUSTRYTYPE||'|'||SELLSUM||'|'||SUPERCERTID||'|'||ISVOUCHCORP from aix_ent_info;
select_report_report.sql:
select REPORTNO||'|'||REPORTDATE||'|'||MODELNO||'|'||REPORTSCOPE||'|'||OBJECTNO from aix_report_record;
5、将标准的txt文件load进oracle数据库的shell脚本
sqlldr_in.sh(IBM的AIX环境下)
ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0;export ORACLE_HOME
ORACLE_SID=commdb;export ORACLE_SID
DATE=`date +%Y%m%d`;export DATE
$ORACLE_HOME/bin/sqlldr cfa/cfa control=ENT_INFO.ctl log=ENT_INFO_$DATE.log rows=10000 readsize=20000000 bindsize=20000000 $ORACLE_HOME/bin/sqlldr cfa/cfa control=REPORT_CATALOG.ctl log=REPORT_CATALOG_$DATE.log rows=10000 readsize=20000000 bindsize=20000000 $ORACLE_HOME/bin/sqlldr cfa/cfa control=REPORT_DATA.ctl log=REPORT_DATA_$DATE.log rows=10000 readsize=20000000 bindsize=20000000 $ORACLE_HOME/bin/sqlldr cfa/cfa control=REPORT_RECORD.ctl log=REPORT_RECORD_$DATE.log rows=10000 readsize=20000000 bindsize=20000000
相关的四张表的控制文件的内容:
ENT_INFO.ctl:==
LOAD DATA
INFILE '/oracle/cfain/ENT_INFO_$DATE.txt'
TRUNCATE
INTO TABLE AIX_ENT_INFO
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
CUSTOMERID "nvl(:CUSTOMERID ,'0')",CORPID "nvl(:CORPID ,'0')",ENTERPRISENAME "nvl(:ENTERPRISENAME ,'0')",ENGLISHNAME "nvl(:ENGLISHNAME ,'0')",FICTITIOUSPERSON "nvl(:FICTITIOUSPERSON ,'0')",ORGNATURE "nvl(:ORGNATURE ,'0')",FINANCETYPE "nvl(:FINANCETYPE ,'0')",ENTERPRISEBELONG "nvl(:ENTERPRISEBELONG ,'0')",INDUSTRYTYPE "nvl(:INDUSTRYTYPE ,'0')",INDUSTRYTYPE1 "nvl(:INDUSTRYTYPE1 ,'0')",INDUSTRYTYPE2 "nvl(:INDUSTRYTYPE2 ,'0')",PRIVATE "nvl(:PRIVATE ,'0')",ECONOMYTYPE "nvl(:ECONOMYTYPE ,'0')",ORGTYPE "nvl(:ORGTYPE ,'0')",MOSTBUSINESS "nvl(:MOSTBUSINESS ,'0')",BUDGETTYPE "nvl(:BUDGETTYPE ,'0')",RCCURRENCY "nvl(:RCCURRENCY ,'0')",REGISTERCAPITAL "nvl(:REGISTERCAPITAL ,'0')",PCCURRENCY "nvl(:PCCURRENCY ,'0')",PAICLUPCAPITAL "nvl(:PAICLUPCAPITAL ,'0')",FUNDSOURCE "nvl(:FUNDSOURCE ,'0')",TOTALASSETS "nvl(:TOTALASSETS ,'0')",NETASSETS "nvl(:NETASSETS ,'0')",ANNUALINCOME "nvl(:ANNUALINCOME ,'0')",SCOPE "nvl(:SCOPE ,'0')",LIMIT "nvl(:LIMIT ,'0')",CREDITDATE "nvl(:CREDITDATE ,'0')",LICENSENO "nvl(:LICENSENO ,'0')",LICENSEDATE "nvl(:LICENSEDATE ,'0')",LICENSEMATURITY "nvl(:LICENSEMATURITY ,'0')",SETUPDATE "nvl(:SETUPDATE ,'0')",INSPECTIONYEAR "nvl(:INSPECTIONYEAR ,'0')",LOCKSITUATION "nvl(:LOCKSITUATION ,'0')",TAXNO "nvl(:TAXNO ,'0')",BANKLICENSE "nvl(:BANKLICENSE ,'0')",BANKID "nvl(:BANKID ,'0')",MANAGEAREA "nvl(:MANAGEAREA ,'0')",BANCHAMOUNT "nvl(:BANCHAMOUNT ,'0')",EXCHANGEID "nvl(:EXCHANGEID ,'0')",REGISTERADD "nvl(:REGISTERADD ,'0')",CHARGEDEPARTMENT "nvl(:CHARGEDEPARTMENT ,'0')",OFFICEADD "nvl(:OFFICEADD ,'0')",OFFICEZIP "nvl(:OFFICEZIP ,'0')",COUNTRYCODE "nvl(:COUNTRYCODE ,'0')",REGIONCODE "nvl(:REGIONCODE ,'0')",VILLAGECODE "nvl(:VILLAGECODE ,'0')",VILLAGENAME "nvl(:VILLAGENAME ,'0')",RELATIVETYPE "nvl(:RELATIVETYPE ,'0')",OFFICETEL "nvl(:OFFICETEL ,'0')",OFFICEFAX "nvl(:OFFICEFAX ,'0')",WEBADD "nvl(:WEBADD ,'0')",EMAILADD "nvl(:EMAILADD ,'0')",EMPLOYEENUMBER "nvl(:EMPLOYEENUMBER ,'0')",MAINPRODUCTION "nvl(:MAINPRODUCTION ,'0')",NEWTECHCORPORNOT "nvl(:NEWTECHCORPORNOT ,'0')",LISTINGCORPORNOT "nvl(:LISTINGCORPORNOT ,'0')",HASIERIGHT "nvl(:HASIERIGHT ,'0')",HASDIRECTORATE "nvl(:HASDIRECTORATE ,'0')",BASICBANK "nvl(:BASICBANK ,'0')",BASICACCOUNT "nvl(:BASICACCOUNT ,'0')",MANAGEINFO "nvl(:MANAGEINFO ,'0')",CUSTOMERHISTORY "nvl(:CUSTOMERHISTORY ,'0')",PROJECTFLAG "nvl(:PROJECTFLAG ,'0')",REALTYFLAG "nvl(:REALTYFLAG ,'0')",WORKFIELDAREA "nvl(:WORKFIELDAREA ,'0')",WORKFIELDFEE "nvl(:WORKFIELDFEE ,'0')",ACCOUNTDATE "nvl(:ACCOUNTDATE ,'0')",LOANCARDNO "nvl(:LOANCARDNO ,'0')",LOANCARDPASSWORD "nvl(:LOANCARDPASSWORD ,'0')",LOANCARDINSYEAR "nvl(:LOANCARDINSYEAR ,'0')",LOANCARDINSRESULT "nvl(:LOANCARDINSRESULT ,'0')",LOANFLAG "nvl(:LOANFLAG ,'0')",FINANCEORNOT "nvl(:FINANCEORNOT ,'0')",FINANCEBELONG "nvl(:FINANCEBELONG ,'0')",CREDITBELONG "nvl(:CREDITBELONG ,'0')",CREDITLEVEL "nvl(:CREDITLEVEL ,'0')",EVALUATEDATE "nvl(:EVALUATEDATE ,'0')",OTHERCREDITLEVEL "nvl(:OTHERCREDITLEVEL ,'0')",OTHEREVALUATEDATE "nvl(:OTHEREVALUATEDATE ,'0')",OTHERORGNAME "nvl(:OTHERORGNAME ,'0')",INPUTORGID "nvl(:INPUTORGID ,'0')",INPUTUSERID "nvl(:INPUTUSERID ,'0')",INPUTDATE "nvl(:INPUTDATE ,'0')",UPDATEORGID "nvl(:UPDATEORGID ,'0')",UPDATEUSERID "nvl(:UPDATEUSERID ,'0')",UPDATEDATE "nvl(:UPDATEDATE ,'0')",REMARK "nvl(:REMARK ,'0')",TAXNO1 "nvl(:TAXNO1 ,'0')",FICTITIOUSPERSONID "nvl(:FICTITIOUSPERSONID,'0')",GROUPFLAG "nvl(:GROUPFLAG ,'0')",EVALUATELEVEL "nvl(:EVALUATELEVEL ,'0')",MYBANK "nvl(:MYBANK ,'0')",MYBANKACCOUNT "nvl(:MYBANKACCOUNT ,'0')",OTHERBANK "nvl(:OTHERBANK ,'0')",OTHERBANKACCOUNT "nvl(:OTHERBANKACCOUNT ,'0')",TEMPSAVEFLAG "nvl(:TEMPSAVEFLAG ,'0')",FINANCEDEPTTEL "nvl(:FINANCEDEPTTEL ,'0')",ECGROUPFLAG "nvl(:ECGROUPFLAG ,'0')",SUPERCORPNAME "nvl(:SUPERCORPNAME ,'0')",SUPERLOANCARDNO "nvl(:SUPERLOANCARDNO ,'0')",SUPERCERTTYPE "nvl(:SUPERCERTTYPE ,'0')",SMEINDUSTRYTYPE "nvl(:SMEINDUSTRYTYPE ,'0')",SELLSUM "nvl(:SELLSUM ,'0')",SUPERCERTID "nvl(:SUPERCERTID ,'0')",ISVOUCHCORP "nvl(:ISVOUCHCORP ,'0')"
)
REPORT_CATALOG.ctl:==
LOAD DATA
INFILE '/oracle/cfain/REPORT_CATALOG_$DATE.txt'
TRUNCATE
INTO TABLE AIX_REPORT_CATALOG
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
MODELNO "NVL(:MODELNO,'0')",MODELNAME "NVL(:MODELNAME,' ')",MODELTYPE "NVL(:MODELTYPE,'0')",MODELCLASS "NVL(:MODELCLASS,'0')"
)
REPORT_DATA.ctl:==
LOAD DATA
INFILE '/oracle/cfain/REPORT_DATA_$DATE.txt'
TRUNCATE
INTO TABLE AIX_REPORT_DATA
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
REPORTNO "NVL(:REPORTNO,'0')",ROWNO "NVL(:ROWNO,' ')",ROWNAME "NVL(:ROWNAME,'0')",COL2VALUE "NVL(:COL2VALUE,'0')"
)
REPORT_RECORD.ctl:==
LOAD DATA
INFILE '/oracle/cfain/REPORT_RECORD_$DATE.txt'
TRUNCATE
INTO TABLE AIX_REPORT_RECORD
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
REPORTNO "NVL(:REPORTNO,'0')",REPORTDATE "NVL(:REPORTDATE,' ')",MODELNO "NVL(:MODELNO,'0')",REPORTSCOPE "NVL(:REPORTSCOPE,'0')",OBJECTNO "NVL(:OBJECTNO,'0')"
)
那一天的数据文件名举例:
ENT_INFO_20090909.txt REPORT_CATALOG_20090909.txt REPORT_DATA_20090909.txt REPORT_RECORD_20090909.txt
6、用ftp配置Unix/Linux文件的自动传输
ftpget.sh文件内容:(RedHat AS4环境下)
export DATE=`date +%Y%m%d`;
ftp -n<
open 192.168.80.5
user oracle oracle
prompt off
mget dl_$DATE.dmp yh_$DATE.dmp
quit
!
注:顺带说明一下:linux下,ftp 的相关启动关闭命令如下:[root@localhost ~]# /etc/init.d/vsftpd start [root@localhost ~]# /etc/init.d/vsftpd stop [root@localhost ~]# /etc/init.d/vsftpd restart
说明:。sh脚本要有执行权限;ftp服务开启与关闭要在根用户下执行;本文用到的例子有在RedHat as4环境下的,有在IBM AIX环境下的,其中涉及到的环境变量等,都大同小异;脚本测试好之后可以配在Linux/Unix调度程序crontab中,以实现shell的自动定时运行!
阅读(1677) | 评论(0) | 转发(0) |