############crontab設(shè)置###########
30 1 * * * /home/oracle/backup/backupsh/expfull.sh
?
?
############expfull.sh設(shè)置###########
/home/oracle/backup/backupsh/tables.sh
cat /home/oracle/backup/backupsh/exp_temp.parfile > /home/oracle/backup/backupsh/expfull.parfile
cat /home/oracle/backup/backupsh/tables.lst >> /home/oracle/backup/backupsh/expfull.parfile
/home/oracle/backup/backupsh/start_exp.sh
?
?
############start_exp.sh設(shè)置###########
source /home/oracle/.bash_profile
BACK_HOME=/home/oracle/backup
BACK_DEST=$BACK_HOME/expfull
SH_HOME=/home/oracle/backup/backupsh
export $BACK_HOME
export $BACK_DEST
export $SH_HOME
DBA="599128198@qq.com"
DATE=`date +"%m%d:%H.%M"`
IP=""
exp_log_file=$SH_HOME/expfull.log
MAIL_COMMAND=/bin/mailx
$ORACLE_HOME/bin/exp parfile=$SH_HOME/expfull.parfile;
cat $exp_log_file |$MAIL_COMMAND -s "exp backup message from $IP $DATE " $DBA
?
###########exp_temp.parfile##############
userid=xzsp/xzsp
filesize=2048M
file=(/home/oracle/backup/fulldmp1.dmp,
/home/oracle/backup/fulldmp2.dmp,
/home/oracle/backup/fulldmp3.dmp,
/home/oracle/backup/fulldmp4.dmp)
rows=y
log=/home/oracle/backup/backupsh/expfull.log
compress=n
buffer=40960000
direct=y
?
##############tables.sh###################
sqlplus -S "/ as sysdba" >/dev/null <<EOF
set echo off
set termout off
set feedback off
set heading off
set linesize 120
set pagesize 0
?
select to_char(sysdate,'''yyyy-mm-dd hh:mm:ss''') from dual;
spool /home/oracle/backup/backupsh/tables.lst
SELECT 'tables=(' || CHR(10) FROM DUAL;
SELECT owner || '.' || table_name || ',' || CHR(10)
FROM dba_tables
WHERE owner NOT IN ('SYS','SYSTEM','CTXSYS','ORDSYS','MDSYS')
????AND table_name NOT IN
???? ('SMS_LOG','SMS_RESPREPT_ERROR','SMS_STATUSREPT','PUSH_USER_INFO_LOG')
ORDER BY owner;
SELECT 'SYS.DUAL' FROM DUAL;
SELECT ')' FROM DUAL;
spool off
EOF
?
說明:ORACLE10G 以后解決這個(gè)問題更方便,使用exclude include。用法如下:
EXCLUDE = object_type[:name_clause][,…]
INCLUDE = object_type[:name_clause][,…]
例如:
Expdp <other_paramters> SCHEMAS=XZSP EXCLUDE=SEQUENCE,INDEX,TABLE:”LIKE %EMP”
IMpdp <other_paramters> SCHEMAS=XZSP INCLUDE=FUNCTION,PACKAGE,PROCEDUGE ,TABLE:”IN (‘EMP’,’DEMP’)”
Linux下使用需要使用轉(zhuǎn)義字符“\”,當(dāng)然最好使用參數(shù)文件
############定義參數(shù)文件parfile.par ##############
Directory=expdir
Schemas=rman
Dumpfile=expdp_rman.dmp
Include=TABLE:”IN(‘DB’,’TS’)”
執(zhí)行導(dǎo)出命令如下
Expdp \’/ as sysdba \’ parfile.par
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
