把ORACLE的存储过程导出来到txtoracle@server /arch/down_fp $ ls -altotal 56drwxrwx--- 7 oracle dba 4096 Apr 8 08:25 .drwxr-xr-x 5 oracle dba 4096 Apr 8 08:23 ..drwxrwx--- 2 oracle dba 4096 Apr 8 08:19 bakdrwxrwx--- 2 oracle dba 4096 Apr 13 00:10 dat-rwxrwx--- 1 oracle dba 1727 Apr 8 08:18 down_fp.sh-rwxrwx--- 1 oracle dba 968 Apr 8 08:02 down_fp.sql-rwxrwx--- 1 oracle dba 96 Apr 8 08:16 kk_ftp.shdrwxrwx--- 2 oracle dba 4096 Apr 13 00:10 sqldrwxrwx--- 2 oracle dba 4096 Apr 13 00:10 tmp-rwxrwx--- 1 oracle dba 281 Apr 8 08:25 user.txtoracle@server /arch/down_fp $ ls -al baktotal 8228drwxrwx--- 2 oracle dba 4096 Apr 8 08:19 .drwxrwx--- 7 oracle dba 4096 Apr 8 08:25 ..-rw-r--r-- 1 oracle dba 1214585 Apr 11 00:10 fp_0.tgz-rw-r--r-- 1 oracle dba 1214142 Apr 12 00:11 fp_1.tgz-rwxrwx--- 1 oracle dba 1183109 Apr 6 23:30 fp_2.tgz-rwxrwx--- 1 oracle dba 1148231 Mar 31 23:30 fp_3.tgz-rwxrwx--- 1 oracle dba 1188417 Apr 8 08:20 fp_4.tgz-rw-r--r-- 1 oracle dba 1211826 Apr 9 00:11 fp_5.tgz-rw-r--r-- 1 oracle dba 1212880 Apr 10 00:11 fp_6.tgz###########################################down_fp.sh的内容#读取user.txt中指定要备份的用户，依次备份代码为文本，并打包FTP到远程主机###########################################! /bin/shPATH=/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/oracle/bin:/oracle/app/oracle/product/8.1.6/binNLS_LANG=AMERICAN_AMERICA.ZHS16GBKTNS_ADMIN=/oracle/app/oracle/product/8.1.6/network/adminORACLE_SID=数据库的SIDLD_LIBRARY_PATH=:/oracle/app/oracle/product/8.1.6/libUSER=oracleLOGNAME=oracleLANG=en_USORACLE_BASE=/oracle/app/oracleORACLE_HOME=/oracle/app/oracle/product/8.1.6ORACLE_BIN=/oracle/app/oracle/product/8.1.6/binSHELL=/bin/shTERM=vt100export PATH NLS_LANG TNS_ADMIN ORACLE_SID LD_LIBRARY_PATH USER LOGNAME LANG ORACLE_BASE ORACLE_HOME SHELL TERMcd /arch/down_fp ##程序所在目录rm -rf sql/*for cStr in `cat user.txt`do #echo 'Down '$cStr' ...' echo $cStr >; tmp/a.txt sed -e 's/,/ /g' < tmp/a.txt >; tmp/b.txt awk '{print $1;}' tmp/b.txt >; tmp/c.txt read cDb < tmp/c.txt awk '{print $2;}' tmp/b.txt >; tmp/c.txt read cUser < tmp/c.txt cUserLong=$cDb'_'$cUser echo 'Down program of '$cUser@$cDb' ...' ##登录密码 cLoginStr='SYSTEM/MANAGER@'$cDb ##登录密码 sed -e 's/KIKI_CREATE/'$cUserLong'/g' < down_fp.sql >; tmp/c.txt sed -e 's/KIKI_USER/'$cUser'/g' < tmp/c.txt >; down_fp_tmp.sql echo '#' >; down_fp_tmp.sh echo 'sqlplus '$cLoginStr' << EOF' >;>; down_fp_tmp.sh echo '@down_fp_tmp.sql' >;>; down_fp_tmp.sh echo 'exit' >;>; down_fp_tmp.sh echo 'EOF' >;>; down_fp_tmp.sh ./down_fp_tmp.sh >; /dev/nulldone>; down_fp_tmp.shrm -rf tmp/?.txtecho 'Tar ...'mv dat/*.tgz bakcDate=`date +'%w'`cTarFile='dat/fp_'$cDate'.tgz'tar cvfz $cTarFile sql/*.*echo 'OK! Program backuped!'echo 'Ftp to remote server...'./kk_ftp.sh 10.10.10.10 /arch/down_fp/dat /bak/down_fp/dat_remoteecho 'Ftp OK!'###########################################down_fp.sql#取过程、包等代码##########################################set head off;column text format a4000;spool ./sql/KIKI_CREATE_procdure.sql;select decode(substr(ltrim(text),1,9),'PROCEDURE','create or replace ','procedure','create or replace ','')||text text from all_source WHERE OWNER='KIKI_USER' and TYPE='PROCEDURE';spool off;spool ./sql/KIKI_CREATE_function.sql;select decode(substr(ltrim(text),1,8),'FUNCTION','create or replace ','function','create or replace ','')||text text from all_source WHERE OWNER='KIKI_USER' and TYPE='FUNCTION';spool off;spool ./sql/KIKI_CREATE_package.sql;select decode(substr(ltrim(text),1,7),'PACKAGE','create or replace ','package','create or replace ','')||text text from all_source WHERE OWNER='KIKI_USER' and TYPE='PACKAGE';spool off;spool ./sql/KIKI_CREATE_package_body.sql;select decode(substr(ltrim(text),1,12),'PACKAGE BODY','create or replace ','package body','create or replace ','')||text text from all_source WHERE OWNER='KIKI_USER' and TYPE='PACKAGE BODY';spool off;###########################################kk_ftp.sh#把备份文件FTP到远程主机##########################################ftp -i -n <<EOFopen $1user USERNAME PASSWORDbinlcd $2cd $3promptmput *.tgz quitEOF###########################################user.txt#用逗号隔开要备份的主机名和用户名##########################################SERVER1,USER1SERVER2,USER2SERVER3,USER3
