oracle exp shell,PowerShell 自动备份oracle并上传到ftp

一.windows平台

1.rman自动备份方法

# Author: Edward.Zhou --

# 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 --

# 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 --

# 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 <

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