一.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:bakrman" 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 '$BACKUPDIRdb_%d_%T_%s'; sql 'alter system archive log current'; backup as compressed backupset archivelog all tag '$env:COMPUTERNAME-$env:ORACLE_SID-$NOWDATE' format '$BACKUPDIRarch_%d_%T_%s' delete all input; backup as compressed backupset current controlfile tag '$env:COMPUTERNAME-$env:ORACLE_SID-$NOWDATE' format '$BACKUPDIRcon_%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 $BACKUPDIRrmanbak.txt rman target / cmdfile="$BACKUPDIRrmanbak.txt" log="$BACKUPDIR$env:COMPUTERNAME-$env:ORACLE_SID-rmanbak-$NOWTIME.log" Remove-Item -Force -Path $BACKUPDIRrmanbak.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 $BACKUPDIRftp.cfg #ftp -i -n -v -s:$BACKUPDIRftp.cfg #Remove-Item -Force -Path $BACKUPDIRftp.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:bakdmp" 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 $BACKUPDIRftp.cfg #Write-Output "user $FTP_USERNAME $FTP_PASSWORD" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "bin" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "mkdir oradmp" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "cd oradmp" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "mkdir $Env:COMPUTERNAME" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "cd $Env:COMPUTERNAME" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "mkdir $NOWDATE" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "cd $NOWDATE" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "lcd $NOWDATE" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "mput *" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "cd ..$FTPOLDDATE" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "mdelete *" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "cd .." | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "rmdir $FTPOLDDATE" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #Write-Output "bye" | Out-File -Append -Force -Encoding Default -FilePath $BACKUPDIRftp.cfg #ftp -i -n -v -s:$BACKUPDIRftp.cfg #Remove-Item -Force -Path $BACKUPDIRftp.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 #清除临时文件 rm -fr /tmp/exp.pipe