PowerShell 自动备份oracle并上传到ftp
一.windows平台
1.rman自动备份方法
#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-Typedirectory-Path$BACKUPDIR
}
#rman备份
Write-Output"
run
{
allocatechanneld1typedisk;
allocatechanneld2typedisk;
allocatechanneld3typedisk;
crosscheckbackup;
sql'altersystemarchivelogcurrent';
backupascompressedbackupsetfulltag'$env:COMPUTERNAME-$env:ORACLE_SID-$NOWDATE'databaseformat'$BACKUPDIR\db_%d_%T_%s';
sql'altersystemarchivelogcurrent';
backupascompressedbackupsetarchivelogalltag'$env:COMPUTERNAME-$env:ORACLE_SID-$NOWDATE'format'$BACKUPDIR\arch_%d_%T_%s'deleteallinput;
backupascompressedbackupsetcurrentcontrolfiletag'$env:COMPUTERNAME-$env:ORACLE_SID-$NOWDATE'format'$BACKUPDIR\con_%d_%T_%s';
crosscheckarchivelogall;
deletenopromptexpiredbackup;
deletenopromptexpiredarchivelogall;
deletenopromptobsolete;
releasechanneld1;
releasechanneld2;
releasechanneld3;
}
"|Out-File-EncodingDefault-Force-FilePath$BACKUPDIR\rmanbak.txt
rmantarget/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-ModulePSFTP
$FTP_HOST="1.1.1.1"
$FTP_USERNAME="backup"
$FTP_PASSWORD=ConvertTo-SecureString"backup"-AsPlainText-Force
$Credentials=New-ObjectSystem.Management.Automation.PSCredential($FTP_USERNAME,$FTP_PASSWORD)
Set-FTPConnection-Credentials$Credentials-Serverftp://$FTP_HOST-SessionrmanbakSession-UsePassive-UseBinary-KeepAlive-ignoreCert
$Session=Get-FTPConnection-SessionrmanbakSession
if($Session-eq$null){
Set-FTPConnection-Credentials$Credentials-Serverftp://$FTP_HOST-SessionrmanbakSession-UsePassive-UseBinary-KeepAlive-ignoreCert
}
if((Get-FTPChildItem-Session$Session-Path/-ErrorActionSilentlyContinue-Filterrmanbak)-eq$null){
New-FTPItem-Session$Session-Path/-Name"rmanbak"
}
if((Get-FTPChildItem-Session$Session-Path/rmanbak-ErrorActionSilentlyContinue-Filter$env:COMPUTERNAME)-eq$null){
New-FTPItem-Session$Session-Path/rmanbak-Name$env:COMPUTERNAME
}
if((Get-FTPChildItem-Session$Session-Path/rmanbak/$env:COMPUTERNAME-ErrorActionSilentlyContinue-Filter$NOWDATE)-eq$null){
New-FTPItem-Session$Session-Path/rmanbak/$env:COMPUTERNAME-Name$NOWDATE
}
foreach($Filenamein(Get-ChildItem-Path$BACKUPDIR-Recurse-Filter*$NOWDATE1*))
{
Add-FTPItem-Session$Session-Overwrite-BufferSize102400-Path/rmanbak/$env:COMPUTERNAME/$NOWDATE-LocalPath$BACKUPDIR\$Filename
}
if((Get-FTPChildItem-Session$Session-Path/rmanbak/$env:COMPUTERNAME-ErrorActionSilentlyContinue-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
#mkdirrmanbak
#cdrmanbak
#mkdir$Env:COMPUTERNAME
#cd$Env:COMPUTERNAME
#mkdir$NOWDATE
#cd$NOWDATE
#lcd$NOWDATE
#mput*
#cd..\$FTPOLDDATE
#mdelete*
#cd..
#rmdir$FTPOLDDATE
#bye"|Out-File-Force-EncodingDefault-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-Typedirectory-Path$BACKUPDIR
}
if((Test-Path-Path$BACKUPDIR\$NOWDATE)-eq$false){
New-Item-Typedirectory-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/$PASSWORDfile=$BACKUPDIR\$NOWDATE\$env:COMPUTERNAME-$env:ORACLE_SID-fullbackup_$NOWTIME.dmplog=$BACKUPDIR\$NOWDATE\$env:COMPUTERNAME-$env:ORACLE_SID-fullbackup_$NOWTIME.logfull=ydirect=yconsistent=ybuffer=102400
#上传备份至FTP方法一
Import-ModulePSFTP
$FTP_HOST="1.1.1.1"
$FTP_USERNAME="backup"
$FTP_PASSWORD=ConvertTo-SecureString"backup"-AsPlainText-Force
$Credentials=New-ObjectSystem.Management.Automation.PSCredential($FTP_USERNAME,$FTP_PASSWORD)
Set-FTPConnection-Credentials$Credentials-Serverftp://$FTP_HOST-SessionoradmpSession-UsePassive-UseBinary-KeepAlive-ignoreCert
$Session=Get-FTPConnection-SessionoradmpSession
if($Session-eq$null){
Set-FTPConnection-Credentials$Credentials-Serverftp://$FTP_HOST-SessionoradmpSession-UsePassive-UseBinary-KeepAlive-ignoreCert
}
if((Get-FTPChildItem-Session$Session-Path/-ErrorActionSilentlyContinue-Filteroradmp)-eq$null){
New-FTPItem-Session$Session-Path/-Name"oradmp"
}
if((Get-FTPChildItem-Session$Session-Path/oradmp-ErrorActionSilentlyContinue-Filter$env:COMPUTERNAME)-eq$null){
New-FTPItem-Session$Session-Path/oradmp-Name$env:COMPUTERNAME
}
if((Get-FTPChildItem-Session$Session-Path/oradmp/$env:COMPUTERNAME-ErrorActionSilentlyContinue-Filter$NOWDATE)-eq$null){
New-FTPItem-Session$Session-Path/oradmp/$env:COMPUTERNAME-Name$NOWDATE
}
foreach($Filenamein(Get-ChildItem-Path$BACKUPDIR\$NOWDATE-Recurse))
{
Add-FTPItem-Session$Session-Overwrite-BufferSize102400-Path/oradmp/$env:COMPUTERNAME/$NOWDATE-LocalPath$BACKUPDIR\$NOWDATE\$Filename
}
if((Get-FTPChildItem-Session$Session-Path/oradmp/$env:COMPUTERNAME-ErrorActionSilentlyContinue-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-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"user$FTP_USERNAME$FTP_PASSWORD"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"bin"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"mkdiroradmp"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"cdoradmp"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"mkdir$Env:COMPUTERNAME"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"cd$Env:COMPUTERNAME"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"mkdir$NOWDATE"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"cd$NOWDATE"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"lcd$NOWDATE"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"mput*"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"cd..\$FTPOLDDATE"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"mdelete*"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"cd.."|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"rmdir$FTPOLDDATE"|Out-File-Append-Force-EncodingDefault-FilePath$BACKUPDIR\ftp.cfg
#Write-Output"bye"|Out-File-Append-Force-EncodingDefault-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
exportORACLE_SID=odb1
exportNLS_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'-1days'`
FTPOLDDATE=`date+%Y_%m_%d-d'-7days'`
#建议备份基本目录环境
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.pipep
#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.pipelog=${BACKUPDIR}/${NOWDATE}/${HOSTNAME}-${ORACLE_SID}-fullbackup_${NOWTIME}.logfull=ydirect=yconsistent=ybuffer=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
mkdiroradmp
cdoradmp
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