PowerShell 自动备份oracle并上传到ftp

一.windows平台

1.rman自动备份方法

# author: edward.zhou -- <[e-mail]>
# purpose: windows平台下采用powershell使用rman自动备份oracle并上传备份到ftp,并根据日期自动保留所需备份
# created: 2015/8/12
 
$env:oracle_sid="orcl"
$env:nls_lang="american_america.al32utf8"
 
$nowdate=get-date -uformat %y_%m_%d
$nowdate1=get-date -uformat %y%m%d
$nowtime=get-date -uformat %y%m%d%h%m%s
$olddate=((get-date).adddays(-2)).tostring('yyyymmdd')
$ftpolddate=((get-date).adddays(-2)).tostring('yyyy_mm_dd')
 
#建立备份目录基本环境
$backupdir="c:\bak\rman"
 
if ((test-path -path $backupdir) -eq $false) {
  new-item -type directory -path $backupdir
}
 
#rman备份
 
write-output "
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
crosscheck backup;
sql 'alter system archive log current';
backup as compressed backupset full tag '$env:computername-$env:oracle_sid-$nowdate' database format '$backupdir\db_%d_%t_%s';
sql 'alter system archive log current';
backup as compressed backupset archivelog all tag '$env:computername-$env:oracle_sid-$nowdate' format '$backupdir\arch_%d_%t_%s' delete all input;
backup as compressed backupset current controlfile tag '$env:computername-$env:oracle_sid-$nowdate' format '$backupdir\con_%d_%t_%s';
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt expired archivelog all;
delete noprompt obsolete;
release channel d1;
release channel d2;
release channel d3;
}
" | out-file -encoding default -force -filepath $backupdir\rmanbak.txt 
 
rman target / cmdfile="$backupdir\rmanbak.txt" log="$backupdir\$env:computername-$env:oracle_sid-rmanbak-$nowtime.log"
remove-item -force -path $backupdir\rmanbak.txt
remove-item -force -path $backupdir\* -include *$olddate* -recurse
 
 
#上传备份至ftp方法一
import-module psftp
$ftp_host="1.1.1.1"
$ftp_username="backup"
$ftp_password=convertto-securestring "backup" -asplaintext -force
$credentials=new-object system.management.automation.pscredential($ftp_username,$ftp_password)
set-ftpconnection -credentials $credentials -server ftp://$ftp_host -session rmanbaksession -usepassive -usebinary -keepalive -ignorecert
$session=get-ftpconnection -session rmanbaksession
 
if ($session -eq $null) {
  set-ftpconnection -credentials $credentials -server ftp://$ftp_host -session rmanbaksession -usepassive -usebinary -keepalive -ignorecert
}
if ((get-ftpchilditem -session $session -path / -erroraction silentlycontinue -filter rmanbak) -eq $null) {
  new-ftpitem -session $session -path / -name "rmanbak"
}
if ((get-ftpchilditem -session $session -path /rmanbak -erroraction silentlycontinue -filter $env:computername) -eq $null) {
  new-ftpitem -session $session -path /rmanbak -name $env:computername
}
if ((get-ftpchilditem -session $session -path /rmanbak/$env:computername -erroraction silentlycontinue -filter $nowdate) -eq $null) {
  new-ftpitem -session $session -path /rmanbak/$env:computername -name $nowdate
}
foreach($filename in (get-childitem -path $backupdir -recurse -filter *$nowdate1*))
  {
    add-ftpitem -session $session -overwrite -buffersize 102400 -path /rmanbak/$env:computername/$nowdate -localpath $backupdir\$filename
  }
if ((get-ftpchilditem -session $session -path /rmanbak/$env:computername -erroraction silentlycontinue -filter $ftpolddate) -ne $null) {
  remove-ftpitem -path /rmanbak/$env:computername/$ftpolddate -session $session -recurse
}
 
#上传备份至ftp方法二
#$ftp_host="1.1.1.1"
#$ftp_username="backup"
#$ftp_password="backup"
#
#set-location -path $backupdir
#
#write-output "open $ftp_host
#user $ftp_username $ftp_password
#bin
#mkdir rmanbak
#cd rmanbak
#mkdir $env:computername
#cd $env:computername
#mkdir $nowdate
#cd $nowdate
#lcd $nowdate
#mput *
#cd ..\$ftpolddate
#mdelete *
#cd ..
#rmdir $ftpolddate
#bye" | out-file -force -encoding default -filepath $backupdir\ftp.cfg
#ftp -i -n -v -s:$backupdir\ftp.cfg
#remove-item -force -path $backupdir\ftp.cfg

2.exp自动备份方法

# author: edward.zhou -- <[e-mail]>
# purpose: windows平台下采用powershell使用exp自动备份oracle并上传备份到ftp,并根据日期自动保留所需备份
# created: 2015/8/8
 
$env:oracle_sid="orcl"
$env:nls_lang="american_america.al32utf8"
 
$nowdate=get-date -uformat %y_%m_%d
$nowtime=get-date -uformat %y%m%d%h%m%s
$olddate=((get-date).adddays(-1)).tostring('yyyy_mm_dd')
$ftpolddate=((get-date).adddays(-7)).tostring('yyyy_mm_dd')
 
#建立备份目录基本环境
$backupdir="d:\bak\dmp"
 
if ((test-path -path $backupdir) -eq $false) {
  new-item -type directory -path $backupdir
}
if ((test-path -path $backupdir\$nowdate) -eq $false) {
  new-item -type directory -path $backupdir\$nowdate
}
if ((test-path -path $backupdir\$olddate) -eq $true) {
  remove-item -recurse -force -path $backupdir\$olddate
}
 
#exp备份
$username="system"
$password="system"
 
exp $username/$password file=$backupdir\$nowdate\$env:computername-$env:oracle_sid-fullbackup_$nowtime.dmp log=$backupdir\$nowdate\$env:computername-$env:oracle_sid-fullbackup_$nowtime.log full=y direct=y consistent=y buffer=102400
 
#上传备份至ftp方法一
import-module psftp
$ftp_host="1.1.1.1"
$ftp_username="backup"
$ftp_password=convertto-securestring "backup" -asplaintext -force
$credentials=new-object system.management.automation.pscredential($ftp_username,$ftp_password)
set-ftpconnection -credentials $credentials -server ftp://$ftp_host -session oradmpsession -usepassive -usebinary -keepalive -ignorecert
$session=get-ftpconnection -session oradmpsession
 
if ($session -eq $null) {
  set-ftpconnection -credentials $credentials -server ftp://$ftp_host -session oradmpsession -usepassive -usebinary -keepalive -ignorecert
}
if ((get-ftpchilditem -session $session -path / -erroraction silentlycontinue -filter oradmp) -eq $null) {
  new-ftpitem -session $session -path / -name "oradmp"
}
if ((get-ftpchilditem -session $session -path /oradmp -erroraction silentlycontinue -filter $env:computername) -eq $null) {
  new-ftpitem -session $session -path /oradmp -name $env:computername
}
if ((get-ftpchilditem -session $session -path /oradmp/$env:computername -erroraction silentlycontinue -filter $nowdate) -eq $null) {
  new-ftpitem -session $session -path /oradmp/$env:computername -name $nowdate
}
foreach($filename in (get-childitem -path $backupdir\$nowdate -recurse))
  {
    add-ftpitem -session $session -overwrite -buffersize 102400 -path /oradmp/$env:computername/$nowdate -localpath $backupdir\$nowdate\$filename
  }
if ((get-ftpchilditem -session $session -path /oradmp/$env:computername -erroraction silentlycontinue -filter $ftpolddate) -ne $null) {
  remove-ftpitem -path /oradmp/$env:computername/$ftpolddate -session $session -recurse
}
 
#上传备份至ftp方法二
#$ftp_host="1.1.1.1"
#$ftp_username="backup"
#$ftp_password="backup"
#
#set-location -path $backupdir
#
#write-output "open $ftp_host" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg 
#write-output "user $ftp_username $ftp_password" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg 
#write-output "bin" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg 
#write-output "mkdir oradmp" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg 
#write-output "cd oradmp" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg 
#write-output "mkdir $env:computername" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg 
#write-output "cd $env:computername" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg 
#write-output "mkdir $nowdate" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg 
#write-output "cd $nowdate" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg 
#write-output "lcd $nowdate" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg 
#write-output "mput *" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg
#write-output "cd ..\$ftpolddate" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg
#write-output "mdelete *" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg
#write-output "cd .." | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg
#write-output "rmdir $ftpolddate" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg
#write-output "bye" | out-file -append -force -encoding default -filepath $backupdir\ftp.cfg
#ftp -i -n -v -s:$backupdir\ftp.cfg
#remove-item -force -path $backupdir\ftp.cfg

二.unix平台

#!/bin/ksh
# author: edward.zhou -- <[e-mail]>
# purpose: 使用exp自动备份oracle并上传数据到ftp
# created: 2015/8/8
 
source ~/.profile
export oracle_sid=odb1
export nls_lang=american_america.zhs16gbk
hostname=`hostname -s`
nowdate=`date +%y_%m_%d`
nowtime=`date +%y%m%d%h%m%s`
olddate=`date +%y_%m_%d -d '-1 days'`
ftpolddate=`date +%y_%m_%d -d '-7 days'`
 
#建议备份基本目录环境
backupdir=/tmp/dmp
[ -d ${backupdir} ] || mkdir -p ${backupdir}
[ -d ${backupdir}/${nowdate} ] || mkdir -p ${backupdir}/${nowdate}
[ ! -d ${backupdir}/${olddate} ] || rm -fr ${backupdir}/${olddate}
 
#创建pipe管道
[ -p /tmp/exp.pipe ] || mknod /tmp/exp.pipe p
# if [ ! -p /tmp/exp.pipe ];then
  # mknod /tmp/exp.pipe
# fi
 
#exp备份
username=backup
password=backup
 
# echo ${backupdir}/${nowdate}/${hostname}-${oracle_sid}-fullbackup_${nowtime}.dmp
bzip2 -9 < /tmp/exp.pipe > ${backupdir}/${nowdate}/${hostname}-${oracle_sid}-fullbackup_${nowtime}.dmp.bz2 &
exp ${username}/${password} file=/tmp/exp.pipe log=${backupdir}/${nowdate}/${hostname}-${oracle_sid}-fullbackup_${nowtime}.log full=y direct=y consistent=y buffer=102400
 
#上传备份至ftp
host=1.1.1.1
ftp_username=backup
ftp_password=backup
 
cd ${backupdir}
 
ftp -i -n -v <<eof
open ${host}
user ${ftp_username} ${ftp_password}
bin
mkdir oradmp
cd oradmp
mkdir ${hostname}
cd ${hostname}
mkdir ${nowdate}
cd ${nowdate}
lcd ${nowdate}
mput *
cd ..
cd ${ftpolddate}
mdelete .
cd ..
rmdir ${ftpolddate}
bye
eof
 
#清除临时文件
rm -fr /tmp/exp.pipe