一台为开发库,数据量小,防止出现意外,经理要求开发库到另外一台闲置库简单的数据同步。
源库:
SQL> create directory dump_dir as '/source/dump';
SQL> grant all on directory dump_dir to test;
目标库:
SQL> create directory dump_dir as '/dest/dump';
SQL> grant all on directory dump_dir to test;
shell脚本如下:
more expdp_impdp_tongbu.sh
#!/bin/sh
#目录变量
V_SOURCE_DIR='/source/dump/'
V_DEST_DIR='/dest/dump/'
V_FNAME="test_`date'+%Y_%m_%d'`"
V_SPOOL='/data/scripts/test_spool.sql'
#一天保留一份
rm -f ${V_SOURCE_DIR}${V_FNAME}.*
rm -f ${V_SPOOL}
#导出
expdp test/test@testdb_10 directory=dump_dir dumpfile=${V_FNAME}.dmp logfile=${V_FNAME}.log schemas=test
#dump文件从源端传到目标端
scp ${V_SOURCE_DIR}${V_FNAME}.dmp 192.168.1.9:${V_DEST_DIR}
##生成要删除的表的语句,以免导入时已有的表报错
sqlplus test/test@db11g_9 << eof
set feedback off heading off verify off trimspool off
set pagesize 0 linesize 100
define v_spool ='/data/scripts/test_spool.sql'
spool &v_spool
SELECT 'DROP TABLE '|| TNAME ||' CASCADE CONSTRAINTS PURGE;' FROM TAB;
spool off
eof
#处理掉带SQL>的行
col -b <${V_SPOOL} | sed '-e /SQL>/d' > '/data/scripts/tmp/jcxx.sql'
#执行上述生成的删除脚本
sqlplus test/test@db11g_9 << eof
set feedback offheading off verify off trimspool off
set pagesize 0 linesize 100
define v_spool ='/data/scripts/test.sql'
@&v_spool
eof
#导入
impdp test/test@db11g_9 directory=dump_dir dumpfile=${V_FNAME}.dmplogfile=${V_FNAME}.log schemas=test