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
-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 #清除临时文件 rm
-fr
/tmp/exp.pipe |