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