Oracle expdp备份脚本

一、Linux expdp备份脚本

1.备份至挂载磁盘

#!/bin/sh
#expdp_data.sh
source /home/oracle/.bash_profile

#date_d=`date +%Y%m%d%H%M%S`
date_d=`date +%Y-%m-%d`
mount_point=/backup
backup_path=/dbbak/expdp
keep_days=15

remote_path=/backup/expdp

if [ ! -d $backup_path ]; then 
    mkdir -p $backup_path   
fi

mkdir -p $backup_path/$date_d
logfile=$backup_path/$date_d/rsync_${date_d}.log



find $backup_path/* -type d -mtime +${keep_days} |xargs rm -rf


sids=('cyhis1' 'cyemr1')

for sid in "${sids[@]}"
do

    export ORACLE_SID=$sid
    echo "###################### $ORACLE_SID Backup Start: `date "+%Y-%m-%d %H:%M:%S"` ########################"
    expdp \"/ as sysdba\" directory=expdir  dumpfile=${sid}_${date_d}_%U.dmp parallel=8 COMPRESSION=DATA_ONLY cluster=n logfile=${sid}_${date_d}.dmp.log full=y
    sleep 60

done

sleep 60

mv $backup_path/*.dmp* $backup_path/$date_d

sleep 60


if mount | grep -q " on ${mount_point} type "; then

    if [ ! -d $remote_path ]; then
        mkdir -p $remote_path
    fi

    rsync -rlptDvz $backup_path/*  $remote_path >> $logfile
    find $backup_path/* -type d -mtime +${keep_days} |xargs rm -rf
else
    echo "`date +%Y%m%d` Please check if the directory $backup_path exists!" >> $logfile
    exit 1
fi



reserved_num=3
file_num=$(find $remote_path/* -type d|wc -l)
echo $file_num
delete(){

    for i in `find $remote_path/* -type d`;do

        if [ $file_num -gt $reserved_num ]; then
            rm -rf $i
            echo "$date_time $i has been deleted."
        fi
        let "file_num--"
    done

}

echo "Keep 4 redundant backups..."

delete >> $remote_path/delete_backup.log

2.备份至本地磁盘

#!/bin/sh
#expdp_data.sh
source /home/oracle/.bash_profile

#date_d=`date +%Y%m%d%H%M%S`
date_d=`date +%Y-%m-%d`
backup_path=/home/backup/expdp
keep_days=30
oracle_sid=zygfs

if [ ! -d $backup_path ]; then
    mkdir -p $backup_path
fi

mkdir -p $backup_path/$date_d


find $backup_path/* -type d -mtime +${keep_days} |xargs rm -rf



echo "###################### $ORACLE_SID Backup Start: `date "+%Y-%m-%d %H:%M:%S"` ########################"
expdp \"/ as sysdba\" directory=expdir  dumpfile=${oracle_sid}_${date_d}_%U.dmp parallel=8 COMPRESSION=DATA_ONLY clust
er=n logfile=${oracle_sid}_${date_d}.dmp.log full=y
sleep 60



mv $backup_path/*.dmp* $backup_path/$date_d

sleep 60


reserved_num=3
file_num=$(find $backup_path/* -type d|wc -l)
echo $file_num
delete(){

    for i in `find $backup_path/* -type d`;do

        if [ $file_num -gt $reserved_num ]; then
            rm -rf $i
            echo "$date_time $i has been deleted."
        fi
        let "file_num--"
    done

}

delete >> $backup_path/delete_backup.log

二、Windows expdp备份脚本

rem Windows bat备份脚本

@echo off

color 0a

cd /d %~dp0
set ORACLE_SID=hyrisdb
set ORACLE_HOME=E:\app\product\12.2.0\dbhome_1

set "BACKUP_DAY=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%"
set "SCRIPT_PATH=D:\Backup\Oracle\script"
set "DIRECTORY=backup_dir"

%ORACLE_HOME%\bin\expdp ' / as sysdba' directory=%DIRECTORY% dumpfile=%ORACLE_SID%_%BACKUP_DAY%.dmp  full=y logfile=expdp_%ORACLE_SID%_%BACKUP_DAY%.log






rem Windows bat删除备份脚本

@echo off

color 0a


forfiles /p "E:\dbbak\expdp\bak" /s /m *.DMP /d -14 /c "cmd /c del  @path"
C:\Windows\System32\forfiles.exe /p "E:\dbbak\expdp\bak" /s /m *.log /d -14 /c "cmd /c del  @path"
forfiles /p "E:\dbbak\rman\bak" /s /m *.log /d -7 /c "cmd /c del  @path"