#!/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 <<EOF
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)===
