Export backup via database link - Fix ORA-39149 & ORA-31631:

Export backup via database link ( Oracle 10g and above)

Pr-requisites:

1) Both remote and local databases should communicate each other. Add both instance entry in tnsnames.ora file.
2) Create one user in Remote database( PROD_RMT) like below:

create user DBLINK_USER
  default tablespace USERS
  temporary tablespace TEMP
  profile DEFAULT;
grant CONNECT to DBLINK_USER;
grant EXP_FULL_DATABASE to DBLINK_USER;


3) Create database link:
CREATE DATABASE LINK remote_dblink CONNECT TO DBLINK_USER IDENTIFIED BY DBLINK_USER USING 'PROD_RMT';

4) Example o export script:

expdp
network_link=remote_dblink
directory=EXPORT
dumpfile=HR_PROD_RMT.dmp
logfile=HR_PROD_RMT_exp.log
schemas=HR

Issues:

1) When grant not available, you may face below error. I have created the above export script in a .par file and executed with giving "EXP_FULL_DATABASE" grant to the user.


$ expdp parfile=test_dblink.par

Export: Release 11.2.0.3.0 - Production on Wed Sep 2 17:51:38 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user


Solution:


grant EXP_FULL_DATABASE to DBLINK_USER;


Note:

In the case of exports, the NETWORK_LINK parameter identifies the database link pointing to the source server. The objects are exported from the source server in the normal manner, but written to a directory object on the local server, rather than one on the source server. Both the local and remote users require the EXP_FULL_DATABASE role granted to them.

For imports, the NETWORK_LINK parameter also identifies the database link pointing to the source server. The difference here is the objects are imported directly from the source into the local server without being written to a dump file. Although there is no need for a DUMPFILE parameter, a directory object is still required for the logs associated with the operation. Both the local and remote users require the IMP_FULL_DATABASE role granted to them.

Search from my post for export and import:
1) Export or Import - Taking consistent backup using expdp
2) Export or Import - Taking full backup using expdp excluding unused big tables
3) Export or Import - Taking backup to import in downgrade oracle version
4) Export or Import - Remap / overwrite schema using impdp

Check progress Export / Import: See the sample example:

-- Import progress

SQL> select sid, serial#, sofar, totalwork
  2  from v$session_longops;

       SID    SERIAL#      SOFAR  TOTALWORK
---------- ---------- ---------- ----------
       587          3       1054      38723

SQL> /

       SID    SERIAL#      SOFAR  TOTALWORK
---------- ---------- ---------- ----------
       587          3       2101      38723

SQL> /

       SID    SERIAL#      SOFAR  TOTALWORK
---------- ---------- ---------- ----------
       587          3      38718      38723


Thanks .