oracle 脚本定时,Oracle下定时执行过程脚本

1.windows下的oracle定时执行

1).首先定义一个定义执行的bat文件，并将这个bat文件加入at 执行计划

2).定义这个bat文件，关联到所要执行的过程

2.Linux下的oracle定时备份

1).备份脚本

2).定时通过ftp传输

3).加入crontab队列

下与windows最大的区别是要加入一系列的环境变量

windows下的定时执行脚本

/************************************exec.bat*************************************/

net stop schedule

net start schedule

at 23:00 /every:Monday ,Tuesday,Wednesday,Thursday ,Friday,Saturday,Sunday d:shellpro.bat

或 /every:5 ,10,15,20 ,25,30,Sunday d:shellpro.bat

或 /every:M,T,W,TH,F,S,Su d:shellpro.bat

运行这个bat文件后，就加入计划任务队列

/***********************************pro.bat****************************************/

set ORACLE_SID=ORCLYJJK

echo %date%pro.sql脚本开始执行，时间：%time% >> d:shellpro.log

d:oracleora81binsvrmgrl @d:shellpro.sql

或 sqlplus user/pass @d:shellpro.sql

echo %date%pro.sql脚本结束执行，时间：%time% >> d:shellpro.log

/***********************************pro.sql****************************************/

spool d:backupbk

connect internal/oracle

shutdown immediate

host copy d:oracleoradataora8i*.* d:backup

startup

spool off

//冷备份的脚本

execute pro_loop;

exit;

//执行过程

Linux下的定时执行脚本

/***********************************.bash_profile***************************************/

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

DISPLAY=10.249.129.65:0.0;export DISPLAY

ORACLE_BASE=/home/oracle/OraHome1/product;export ORACLE_BASE

ORACLE_HOME=/home/oracle/OraHome1/product;export ORACLE_HOME

ORACLE_SID=oradbyj;export ORACLE_SID

ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data;export ORA_NLS33

NLS_LANG=american_america.zhs16cgb231280;export NLS_LANG

LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH

CLASSPATH=.:$ORACLE_HOME/jdbc/lib/class111.zip;export CLASSPATH

PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/dbs:/etc:/bin;export PATH

./env.sh

export LD_ASSUME_KERNEL=2.2.5

export LDEMULATION=elf_i386_glibc21

export GCC_EXEC_PREFIX=/usr/i386-glibc21-linux/lib/gcc-lib/

unset LANG LAGUAGE LC_COLLATE LC_MESSAGES LC_CTYPE LC_MONETARY LC_NUMERIC LC_TIM

E

#export PATH

#unset USERNAME

/*********************************************vipback.sh**********************************/

##用于将oracle数据导出 进行备份

date

DATE=`date +%w`

ORACLE_HOME=/home/oracle/OraHome1/product

export ORACLE_HOME

ORACLE_SID=oradbyj

export ORACLE_SID

ORACLE_TERM=xterm

export ORACLE_TERM

ORACLE_OWNER=oracle

export ORACLE_OWNER

NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"

export NLS_LANG

TNS_ADMIN=/home/oracle/OraHome1/product/network/admin

export TNS_ADMIN

CLASSPATH=$ORACLE_HOME/jdbc/lib/classes111.zip

export CLASSPATH

# Set up the search paths:

PATH=/usr/local/jre/bin:/usr/local/jdk/bin:/bin:/shin

PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin

PATH=$PATH:/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:.

export PATH

cd /backup/yjvip/

#rm *.*

rqOne=`date +%x%t%X`

echo '---------------------------------------------'>>/home/oracle/jobs/crontabf

ile.log

echo '过程vipback运行start时间：'$rqOne>>/home/oracle/jobs/crontabfile.log

exp parfile=/home/oracle/jobs/parfile.par file=yjvip$DATE.dmp log=yjvip$DATE.log

rqOne=`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.sh

cd /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.66

rqOne=`date +%x%t%X`

echo 'ftp运行结束时间：'$rqOne>>/home/oracle/jobs/ftp.log

/******************************************* .netrc *****************************************/

default login vippub passwd vipdoone

macdef init

passive

binary

prompt off

cd /vipbak

lcd /backup/yjvip

mput *.*

bye

/****************************************** pro.sh *******************************************/

##用于编译视图和脚本的sql脚本

#Set ORACLEenvironment

ORACLE_HOME=/home/oracle/OraHome1/product

export ORACLE_HOME

ORACLE_SID=oradbyj

export 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 off

set heading off

spool /home/oracle/jobs/pro2.sql;

select 'alter procedure '||OBJECT_NAME||' compile;' from sys.all_probe_objects w

here OWNER='VIPDL' and OBJECT_TYPE='PROCEDURE' and STATUS='INVALID';

spool off;

@/home/oracle/jobs/pro2.sql;

exit;

/****************************************view.sql**********************************************/

set feedback off

set heading off

spool /home/oracle/jobs/view2.sql;

select 'alter view '||OBJECT_NAME||' compile;' from sys.all_probe_objects where

OWNER='VIPDL' and OBJECT_TYPE='VIEW' and STATUS='INVALID';

spool off;

@/home/oracle/jobs/view2.sql;

exit;

/***************************************crontab************************************************/

#

#将以上脚本加入定时触发器

#

20 02 * * * /home/oracle/jobs/Vpmn_sell.sh

30 05 * * * /home/oracle/jobs/Alert_everyday.sh

30 21 * * * /home/oracle/jobs/vipback.sh

30 23 * * 0,1,3,5 /home/oracle/jobs/ftp.sh

30 17 * * * /home/oracle/jobs/pro.sh

/// 完 ///

【责编:landss】

--------------------next---------------------