PowerShell 自动备份oracle并上传到ftp

一.windows平台

# Author: Edward.Zhou — <edgeman_03@163.com>
# 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 — <edgeman_03@163.com>
# 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 — <edgeman_03@163.com>
# 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