• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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
    

    您可能感兴趣的文章:
    • Oracle数据库自动备份脚本分享(超实用)
    • Oracle自动备份及自动备份步骤
    • Linux下通过脚本自动备份Oracle数据库并删除指定天数前的备份
    • oracle、mysql数据库停止与启动的批处理文件
    • 批处理写的 oracle 数据库备份还原工具
    • Oracle 批处理自动备份bat脚本语句的步骤详解
    上一篇:PowerShell批量修改AD用户密码属性的代码
    下一篇:PowerShell中match命令使用详解
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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