Unix/Windows平台数据泵备份常用脚本

数据泵导出脚本

以下有四个脚本，前2个为unix平台和windows平台逻辑导出脚本，后2个为通过psftp和ftp从unix平台获取dmp文件的脚本。

Unix平台导出脚本

#!/usr/bin/ksh
#######################################
#     Version 1.0
#     Author:Robin Han(186-9589-1286)
#     Date:2010/01/01
#     First,it will do expdp for schema HR,OE,SH
#     Once completed,it will move dmp files,log files to the folder with the current date 
#     Second,it will delete the old dump files over 5 days since expdped.
########################################

. /home/oracle/.profile     #如果.profile或.bash_profile中设置有环境变量，可以先刷环境变量，后面的export就不需要了
export NLS_LANG=American_America.ZHS16GBK
export ORACLE_BASE=/pgold/oracle/product
export ORACLE_HOME=/pgold/oracle/product/112
export ORACLE_SID=pgold1
export PATH=$PATH:$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/etc:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin

expdp system/pgold12 directory=dpump schemas=HR,OE,SH dumpfile=pgold_$(date +"%Y%m%d")_%U.dmp logfile=pgold_$(date +"%Y%m%d").log parallel=4 filesize=8g compression=all cluster=n

mkdir /pgold/dpump/$(date +"%Y%m%d")
mv /pgold/dpump/pgold* /pgold/dpump/$(date +"%Y%m%d")
find /pgold/dpump/* -type d -mtime +5 -exec rm -fr {} \;

Windows平台导出脚本

REM   **************************
REM   Version 1.0
REM   Author:Robin.Han(186-9589-1286)
REM   Created:2010/01/01
REM   **************************

set EXPDATE=%date:~0,4%%date:~5,2%%date:~8,2%
set EXPTIME=%time:~0,2%%time:~3,2%%time:~6,2%

if "%EXPTIME:~0,1%"==" " set "EXPTIME=0%EXPTIME:~1%"
set FILENAME=%EXPDATE%%EXPTIME%
set ORACLE_SID=pgold
expdp system/oracle directory=dpump dumpfile=pgold_%FILENAME%.dmp schemas=HR,OE,SH  logfile=pgold_%filename%.log compression=all
mkdir %EXPDATE%
move pgold*.dmp %EXPDATE%
move pgold*.log %EXPDATE%
forfiles /p "E:\dpump" /s /m *.* /d -15 /c "cmd /c del @path"  
exit

#expdp system/oracle directory=dpump parfile=E:\dpump\expdp.par schemas=HR,OE,SH  logfile=pgold_%filename%.log compression=all filesize=5G parallel=6
expdp.par
dumpfile=pgold_%U.dmp 

REM   **************************
REM   Version 1.0
REM   Author:Robin.Han(186-9589-1286)
REM   Created:2010/01/01
REM   It will download dmp files to directory E:\dpump.if the required files are exist,it will exit.
REM   It will record the sftp start time,end time to file sftp_daily_log.txt
REM   **************************

set FF=%date:~0,4%%date:~5,2%%date:~8,2%
E:
cd E:\dpump
if exist pgold_%FF%*.dmp (exit) 
if exist %FF% (echo %date:~0,10% %time:~0,8%,dmpfiles are already downloaded >expdp_downloaded.txt && exit)

echo cd /pgold/dpump/%FF% > getdmp.bat
echo lcd E:\dpump >> getdmp.bat
echo mget pgold* >> getdmp.bat
echo exit >> getdmp.bat

echo sftp begin: %date:~0,10% %time:~0,8% >sftp_daily_log.txt
E:\dpump\psftp.exe 192.168.0.110 -l oracle -pw welcome1 -b getdmp.bat
echo sftp end: %date:~0,10% %time:~0,8% >>sftp_daily_log.txt
mkdir E:\dpump\%FF%
move pgold_%FF%* E:\dpump\%FF%
forfiles /p "E:\dpump" /s /m *.* /d -15 /c "cmd /c del @path"  
exit

REM   **************************
REM   Version 1.0
REM   Author:Robin.Han(186-9589-1286)
REM   Created:2010/01/01
REM   It will download dmp files to directory E:\dpump.if the required files are exist,it will exit.
REM   It will record the ftp start time,end time to file ftp_daily_log.txt
REM   **************************

set FF=%date:~0,4%%date:~5,2%%date:~8,2%
E:
cd E:\dpump
if exist pgold_%FF%*.dmp (exit) 
if exist %FF% (echo %date:~0,10% %time:~0,8%,dmpfiles are already downloaded >expdp_downloaded.txt && exit)

echo open 192.168.0.110 >ftp.txt
echo user oracle welcome1 >>ftp.txt
echo cd /pgold/dpump/%date:~0,4%%date:~5,2%%date:~8,2% >>ftp.txt
echo lcd E:\dpump >>ftp.txt
echo bi >>ftp.txt
echo mget *.dmp >>ftp.txt
echo bye >>ftp.txt

echo sftp begin: %date:~0,10% %time:~0,8% >sftp_daily_log.txt
ftp -i -n -s:ftp.txt
echo sftp end: %date:~0,10% %time:~0,8% >>sftp_daily_log.txt

mkdir E:\dpump\%FF%
move pgold_%FF%* E:\dpump\%FF%
forfiles /p "E:\dpump" /s /m *.* /d -15 /c "cmd /c del @path"  
exit

Putty下载地址

http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

http://www.putty.ws/

http://www.putty.nl/