一.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.txtRemove-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 ~/.profileexport ORACLE_SID=odb1export NLS_LANG=AMERICAN_AMERICA.ZHS16GBKHOSTNAME=`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=backupPASSWORD=backup # echo ${
BACKUPDIR}
/${
NOWDATE}
/${
HOSTNAME}
-${
ORACLE_SID}
-fullbackup_${
NOWTIME}
.dmpbzip2 -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 #上传备份至FTPHOST=1.1.1.1FTP_USERNAME=backupFTP_PASSWORD=backup cd ${
BACKUPDIR}
ftp -i -n -v <<EOFopen ${
HOST}
user ${
FTP_USERNAME}
${
FTP_PASSWORD}
binmkdir oradmpcd oradmpmkdir ${
HOSTNAME}
cd ${
HOSTNAME}
mkdir ${
NOWDATE}
cd ${
NOWDATE}
lcd ${
NOWDATE}
mput *cd ..cd ${
FTPOLDDATE}
mdelete .cd ..rmdir ${
FTPOLDDATE}
byeEOF #清除临时文件rm -fr /tmp/exp.pipe
