#!/bin/sh
export ORACLE_BASE=/data/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=nccd
export PATH=$ORACLE_HOME/bin:$HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export LANG=C
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
wj=/data/backup
bakdir=/data/backup/dmpback
USER_FILE=/data/backup/Oracle_user.txt
BAKLOG=/data/backup/log/ora_bak_$(date +%Y%m%d).log
cd $wj
#rm -rf $bakdir/*
#echo "删除原备份文件$bakdir">>$BAKLOG
delday=15
lso=$(cat $USER_FILE | wc -l)
echo $lso
cd /data/backup
for((line=0;"$lso" > "$line";line++));
do
userdata=$(cat $USER_FILE |head -n $line|tail -n 1|awk -F/ '{print $1}')
passdata=$(cat $USER_FILE |head -n $line|tail -n 1|awk -F/ '{print $2}')
date>>$BAKLOG
echo "这是第"$line"行" >>$BAKLOG
echo $userdata "密码" $passdata >>$BAKLOG
if [ "$userdata" = "" ];then
echo "$userdata为空">>$BAKLOG
else
echo "exp $userdata/$passdata@nccd grants=y file=$bakdir/$userdata"_"$(date +%Y%m%d).dmp log=$bakdir/$userdata"_"$(date +%Y%m%d).log owner=$userdata" >>$BAKLOG
# expdp $userdata/$passdata@nccd grants=y file=$bakdir/$userdata"_"$(date +%Y%m%d).dmp log=$bakdir/$userdata"_"$(date +%Y%m%d).log owner=$userdata
expdp $userdata/$passdata@nccd directory=DUMPBACK dumpfile=$userdata"_"$(date +%Y%m%d).dmp logfile=$userdata"_"$(date +%Y%m%d).log schemas=$userdata;
echo "test">>$BAKLOG
if [ $? -eq 0 ] ; then
tar -zcPf $bakdir/$userdata"_"$(date +%Y%m%d).tar.gz $bakdir/$userdata"_"$(date +%Y%m%d).dmp $bakdir/$userdata"_"$(date +%Y%m%d).log
if [ $? -eq 0 ] ; then
echo "$bakdir/$userdata"_"$(date +%Y%m%d).tar.gz压缩成功!">>$BAKLOG
sleep 1
find $bakdir -mtime +$delday -name "*.tar.gz" -exec rm -rf {} \;
sleep 10;
rm -rf $bakdir/*.log
rm -rf $bakdir/*.dmp
# find $bakdir -mtime +$delday -name "*.dmp" -exec rm -rf {} \;
# find $bakdir -mtime +$delday -name "*.log" -exec rm -rf {} \;
find $wj -mtime +$delday -name "*.log" -exec rm -rf {} \;
echo "删除dmp、log文件成功!">>$BAKLOG
scp $bakdir/$userdata"_"$(date +%Y%m%d).tar.gz root@192.168.200.41:/data/36/oracle
if [ $? -eq 0 ] ; then
# rm -rf $bakdir/$userdata"_"$(date +%Y%m%d).tar.gz
echo "$userdata"_"$(date +%Y%m%d).tar.gz传送成功!">>$BAKLOG
echo -e "\n">>$BAKLOG
else
echo "$userdata"_"$(date +%Y%m%d).tar.gz传送失败!">>$BAKLOG
fi
sleep 1
else
echo "$userdata"_"$(date +%Y%m%d).tar.gz压缩失败!">>$BAKLOG
fi
else
return 1
fi
fi
done
echo "结束">>$BAKLOG
echo -e "*********************************************************************************************************\n*********************************************************************************************************">>$BAKLOG
exit
#数据还原
#还原至原数据库 imp $userdata/$passdat@nccd file=$bakdir/$userdata"_"$(date +%Y%m%d).dmp log=$bakdir/$userdata"_"$(date +%Y%m%d).log full=y
#imp mas/mas file=mas_20200712.dmp fromuser=mas touser=mas rows=n
#imp mas/mas@nccd file=mas_20200712.dmp log=mas_20200712.log full=y
#导入到其他数据库 imp test/test file=ticp_dsvm_20200416.dmp log=ticp_dsvm_20200416.log fromuser=ticp_dsvm touser=test ignore=y
#其他导入操作请参考 https://blog.csdn.net/songyanfei1205/article/details/79753279
#1.新建目录:/data/backup/dmpback 并赋予目录oracle用户权限； (chown -R -v oracle:oinstall /data/backup/dmpback)
#2.（su - oracle）创建Directory: create or replace directory DUMPBACK as '/data/backup/dmpback';
#3.(sqlplus / as sysdba)用户赋权:grant read,write on directory DUMPBACK to radiuscenter;
#4.(sqlplus / as sysdba)导出:expdp radiuscenter/radiuscenter@nccd directory=DUMPBACK dumpfile=radiuscenter.dmp logfile=radiuscenter.log SCHEMAS=radiuscenter ;
#5.(sqlplus / as sysdba)导入:impdp userb/userb schemas=usera directory=DUMPBACK dumpfile=imp1.dmp logfile=imp1.log remap_schema=usera:userb
impdp 'ymcd_rwzx'/'rwzx_2021&*abc' schemas=ymcd_rwzx directory=DUMPBACK dumpfile=EXPDP_ymcd_rwzx2021052501.dmp logfile=EXPDP_ymcd_rwzx20210525.log remap_schema=ymcd_rwzx:ymcd_rwzx
#6.(导入指定表空间，用户)impdp system/admin@DNACLIENT directory=DATA_PUMP_DIR dumpfile=example.DMP REMAP_SCHEMA=olduser:newuser remap_tablespace=EXAMPLE:newtablespace,EXAMPLE_TEMP:newtablespace_temp
