mysql用資料泵備份資料_資料泵自動備份腳本

#!/bin/bash

#Created by Jason

. ~/.bash_profile

export BACKUPDIR=/oracle/arch

export EXPDPDIR=DMP_DIR

export PARALLEL=10

export BACKUPTIME=`date +%Y%m%d`

touch $BACKUPDIR/db_name.sql

echo "set lin 100" > $BACKUPDIR/db_name.sql

echo "select VALUE from v\$parameter where name='db_name';" >> $BACKUPDIR/db_name.sql

echo "exit" >> $BACKUPDIR/db_name.sql

DB_NAME=`sqlplus / as sysdba @$BACKUPDIR/db_name.sql |grep -A 2 VALUE |tail -1`

export BACKUPNAME=$DB_NAME'_'$BACKUPTIME

exec > $BACKUPDIR/oracle_expdp.log

echo '===THE BACKUP OF START TIME IS '$(date +%Y/%m/%d/%H:%M:%S)===

sqlplus -S / as sysdba <

prompt ######################################USERS################################################

set lin 200

col username format a25

col account_status format a18

col default_tablespace format a20

col temporary_tablespace format a20

select username,default_tablespace,account_status,temporary_tablespace from dba_users;

prompt ######################################TABLESPACE############################################

set line 200 pagesize 9999

col tablespace_name for a20

select b.tablespace_name,round(sum(b.bytes)/1024/1024,0) sum_MB,

round(sum(b.bytes)/1024/1024,0)-round(sum(nvl(a.bytes,0))/1024/1024,0) use_MB,

round(sum(nvl(a.bytes,0))/1024/1024,0) free_MB,

round((sum(b.bytes)-sum(nvl(a.bytes,0)))/sum(b.bytes),4)*100 use_precent

from (select tablespace_name,file_id,sum(bytes) bytes from dba_free_space group by tablespace_name,file_id) a,

dba_data_files b

where a.file_id(+)=b.file_id and a.tablespace_name(+)=b.tablespace_name

group by b.tablespace_name

union all

select b.tablespace_name,round(b.bytes/1024/1024,0) sum_MB,

round(nvl(a.bytes,0)/1024/1024,0) use_MB,

round(b.bytes/1024/1024,0)-round(nvl(a.bytes,0)/1024/1024,0) free_MB,

round(nvl(a.bytes,0)/b.bytes,4)*100 use_precent

from (select tablespace_name,sum(nvl(bytes_used,0)) bytes from gv\$temp_extent_pool group by tablespace_name) a,

(select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name)b

where a.tablespace_name(+)=b.tablespace_name

order by use_precent desc;

exit;

EOF

expdp \'/ as sysdba\'directory=$EXPDPDIR dumpfile="$BACKUPNAME"_%U.dmp logfile="$BACKUPNAME".log CLUSTER=N exclude=statistics compression=all full=y parallel=$PARALLEL

echo "#################################DMP FILE LIST##########################################"

rm -f db_name.sql

ls -lh $BACKUPDIR

echo "#################################BACKUP LOG#############################################"

tail -50 $BACKUPDIR/$BACKUPNAME.log

echo "#################################Starting tar...########################################"

cd $BACKUPDIR

tar -cf $BACKUPNAME.tar $BACKUPNAME*.dmp $BACKUPNAME*.log

rm -rf $BACKUPDIR/$BACKUPNAME*.dmp $BACKUPNAME*.log

ls -lh $BACKUPDIR

echo "#################################DELETE 2+ DMP FILE#####################################"

find $BACKUPDIR -mtime +2 -name "*.tar*" -exec rm -rf {} \;

ls -lh $BACKUPDIR

echo '===THE BACKUP OF END TIME IS '$(date +%Y/%m/%d/%H:%M:%S)===