DBlink+数据泵 过程+脚本

DB_LINK+数据泵远程同步

1.赋权限

grant CREATE DATABASE LINK,DROP PUBLIC DATABASE LINK,CREATE PUBLIC DATABASE LINK to scott

分别赋给两个库用户创建和删除DB_LINK的权限（实验时用的是devdb的scott用户与orcl的scott用户）

grant connect,resource to scott（连接访问的权限，一般都有）

2.创建public DB_LINK连接

create public database link DEVDB.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to scott identified by scott using 'DEVDB'

create public database link ORCL.COM connect to scott identified by scott using 'BB'

测试是否能连接上

select * from global_name@DEVDB.REGRESS.RDBMS.DEV.US.ORACLE.COM

select * from global_name@ORCL.com

3.在orcl库上建数据泵字典

create or replace directory exp_dir as '/home/oracle';

并附权限给scott

grant read, write on directory exp_dir to scott

4.给scott用户拷贝的权限

grant exp_full_database to scott

(没这个权限会报ORA-31631与ORA_39149的错)

5.orcl库的scott同步devdb库的scott

impdp scott/scott SCHEMAS=scott network_link=DEVDB.REGRESS.RDBMS.DEV.US.ORACLE.COM table_exists_action=replace 脚本

directory=exp_dir logfile=imp.log

#!/bin/bash

datetime=`/bin/date \

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1; export ORACLE_HOME;

PATH=$ORACLE_HOME/bin:$PATH;

export PATHexport ORACLE_BASE=/home/oracle/oracle export ORACLE_SID=orcl

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib export ORACLE_TERM=oinstall

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib export NLS_LANG=\

####################################impdp#########################################

/home/oracle/oracle/product/10.2.0/db_1/bin/impdp scott/scott SCHEMAS=scott directory=exp_dir network_link=DEVDB.REGRESS.RDBMS.DEV.US.ORACLE.COM logfile=imp_\

检查日志文件中ORA错误

grep '^ORA' /home/oracle/impdp_*.log >> grep_ORA.log

本文来源：https://www.bwwdw.com/article/4cat.html