oracle下定時執(zhí)行過程腳本
2024-08-29 13:43:39
供稿：網友
1.windows下的Oracle定時執(zhí)行
1）.首先定義一個定義執(zhí)行的bat文件，并將這個bat文件加入at 執(zhí)行計劃
2）.定義這個bat文件，關聯(lián)到所要執(zhí)行的過程
2.linux下的oracle定時備份
1).備份腳本
2).定時通過FTP傳輸
3).加入crontab隊列
linux下與windows最大的區(qū)別是要加入一系列的環(huán)境變量
windows下的定時執(zhí)行腳本
/************************************exec.bat*********************************************/
net stop schedule
net start schedule
at 23:00 /every:Monday ,Tuesday,Wednesday,Thursday ,Friday,Saturday,Sunday d:/shell/PRo.bat
或 /every:5 ,10,15,20 ,25,30,Sunday d:/shell/pro.bat
或 /every:M,T,W,TH,F,S,Su d:/shell/pro.bat
運行這個bat文件后，就加入計劃任務隊列
/***********************************pro.bat***********************************************/
set ORACLE_SID=ORCLYJJK
echo %date%pro.sql腳本開始執(zhí)行，時間：%time% >> d:/shell/pro.log
d:/oracle/ora81/bin/svrmgrl @d:/shell/pro.sql
或 sqlplus user/pass @d:/shell/pro.sql
echo %date%pro.sql腳本結束執(zhí)行，時間：%time% >> d:/shell/pro.log
/***********************************pro.sql************************************************/
spool d:/backup/bk
connect internal/oracle
shutdown immediate
host copy d:/oracle/oradata/ora8i/*.* d:/backup
startup
spool off
//冷備份的腳本
execute pro_loop;
exit;
//執(zhí)行過程
Linux下的定時執(zhí)行腳本
/**********************************************.bash_profile*****************************************************/# .bash_profile
# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi
# User specific environment and startup programs
PATH=$PATH:$HOME/binDISPLAY=10.249.129.65:0.0;eXPort DISPLAYORACLE_BASE=/home/oracle/OraHome1/prodUCt;export ORACLE_BASEORACLE_HOME=/home/oracle/OraHome1/product;export ORACLE_HOME
ORACLE_SID=oradbyj;export ORACLE_SIDORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data;export ORA_NLS33NLS_LANG=american_america.zhs16cgb231280;export NLS_LANGLD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATHCLASSPATH=.:$ORACLE_HOME/jdbc/lib/class111.zip;export CLASSPATHPATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/dbs:/etc:/bin;export PATH./envlinux.shexport LD_ASSUME_KERNEL=2.2.5export LDEMULATION=elf_i386_glibc21export GCC_EXEC_PREFIX=/usr/i386-glibc21-linux/lib/gcc-lib/unset LANG LAGUAGE LC_COLLATE LC_MESSAGES LC_CTYPE LC_MONETARY LC_NUMERIC LC_TIME#export PATH#unset USERNAME
/*********************************************vipback.sh*******************************************************/##用于將oracle數據導出 進行備份dateDATE=`date +%w`ORACLE_HOME=/home/oracle/OraHome1/productexport ORACLE_HOMEORACLE_SID=oradbyjexport ORACLE_SIDORACLE_TERM=xtermexport ORACLE_TERMORACLE_OWNER=oracleexport ORACLE_OWNERNLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"export NLS_LANGTNS_ADMIN=/home/oracle/OraHome1/product/network/adminexport TNS_ADMINCLASSPATH=$ORACLE_HOME/jdbc/lib/classes111.zipexport CLASSPATH# Set up the search paths:PATH=/usr/local/jre/bin:/usr/local/jdk/bin:/bin:/shinPATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/binPATH=$PATH:/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:.export PATHcd /backup/yjvip/#rm *.*rqOne=`date +%x%t%X`echo '---------------------------------------------'>>/home/oracle/jobs/crontabfile.logecho '過程vipback運行start時間：'$rqOne>>/home/oracle/jobs/crontabfile.logexp parfile=/home/oracle/jobs/parfile.par file=yjvip$DATE.dmp log=yjvip$DATE.logrqOne=`date +%x%t%X`echo '過程vipback運行結束時間：'$rqOne>>/home/oracle/jobs/crontabfile.log
/******************************************* ftp.sh **********************************************************/#rqOne=`date +%x%t%X`#/bin/echo '過程ftpback運行start時間：'$rqOne >>/home/oracle/jobs/ftp.log#ftp 10.249.129.66 #rqOne=`date +%x%t%X`#/bin/echo '過程ftpback運行結束時間：'$rqOne >>/home/oracle/jobs/ftp.log#su - oracle -c "/home/oracle/jobs/vipback.sh"#echo '------------------------------------'>>/home/oracle/jobs/crontabfile.log#/home/oracle/jobs/vipback.shcd /backup/yjvip/#chown -R oracle:oinstall *rqOne=`date +%x%t%X`echo 'ftp運行start時間：'$rqOne>>/home/oracle/jobs/ftp.log/usr/bin/ftp 10.249.129.66rqOne=`date +%x%t%X`
echo 'ftp運行結束時間：'$rqOne>>/home/oracle/jobs/ftp.log
/******************************************* .netrc **********************************************************/default login vippub passwd vipdoonemacdef initpassivebinaryprompt offcd /vipbaklcd /backup/yjvipmput *.*bye
/****************************************** pro.sh ***********************************************************/##用于編譯視圖和腳本的sql腳本#Set ORACLEenvironmentORACLE_HOME=/home/oracle/OraHome1/productexport ORACLE_HOMEORACLE_SID=oradbyjexport ORACLE_SID/home/oracle/OraHome1/product/bin/sqlplus vipdl/vipdl123 @/home/oracle/jobs/pro.sql/home/oracle/OraHome1/product/bin/sqlplus vipdl/vipdl123 @/home/oracle/jobs/view.sql/*****************************************pro.sql *************************************************************/set feedback offset heading offspool /home/oracle/jobs/pro2.sql;select 'alter procedure 'OBJECT_NAME' compile;' from sys.all_probe_objects where OWNER='VIPDL' and OBJECT_TYPE='PROCEDURE' and STATUS='INVALID';spool off;@/home/oracle/jobs/pro2.sql;exit;/****************************************view.sql*************************************************************/set feedback offset heading offspool /home/oracle/jobs/view2.sql;select 'alter view 'OBJECT_NAME' compile;' from sys.all_probe_objects whereOWNER='VIPDL' and OBJECT_TYPE='VIEW' and STATUS='INVALID';spool off;@/home/oracle/jobs/view2.sql;exit;
/***************************************crontab**************************************************************/##將以上腳本加入定時觸發(fā)器#20 02 * * * /home/oracle/jobs/Vpmn_sell.sh30 05 * * * /home/oracle/jobs/Alert_everyday.sh30 21 * * * /home/oracle/jobs/vipback.sh30 23 * * 0,1,3,5 /home/oracle/jobs/ftp.sh30 17 * * * /home/oracle/jobs/pro.sh
/////////////////////////////////////////////////////// 完 ///////////////////////////////////////////////////////////////////////////////////////////
