Monday, December 30, 2013

Network export datapump & import datapump steps or export import steps:

Prerequisites:

1        Source and Destination database must be connected through the network.
2        Both databases must be in open mode.
3    Source database (db1) tns detail must exist at destination (db2).

Following are the steps:
1      Crate a public database link at db2 connecting to db1 using system user.

SQL> create public database link db1_link connect to system identified by sys using 'db1';
or
SQL>CREATE public DATABASE LINK db1_link 
CONNECT TO USERNAME IDENTIFIED BY PASSWORD USING
'(DESCRIPTION=(ADDRESS_LIST=(
ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=xxxx)))
(CONNECT_DATA=(SID=xxxx))
)'
/

Database link created.

Crosscheck the db link is working.

SQL> select * from dual@ db1_link;

D
-
X

You may face following error during impdp, if db link is not public.
ORA-39001: invalid argument value
ORA-39200: Link name "fmstest" is invalid.
ORA-02019: connection description for remote database not found

  
2.       Import using Impdp command at db2 (Destination)

[oracle@~]$ export ORACLE_SID=test01
[oracle@~]$ impdp system/password schemas= test_schema1 network_link= db1_link

Import: Release 11.2.0.2.0 - Production on Fri Jun 17 07:00:57 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** schemas= test_schema1 network_link= db1_link
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
--------------------------------------------------------------------------



It should be public because users other than SYSTEM are the ones likely to be using it

Your next task is to create a directory where oracle can store the dump from the source machine while it is working:
CREATE DIRECTORY dmpdir AS '/opt/oracle'; < this was on other sites, didn't work for me as the directory didn't exist
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

fixed it by
Drop directory dmpdir
CREATE DIRECTORY dmpdir AS '/usr/lib/oracle/xe/app/
oracle/product/';
not necessarily the best choice of directory, but one that existed and not full of other files (i could clean it easily)

Finally grant write access to the user you wish to do the copying to that directory
GRANT read, write ON DIRECTORY dmpdir TO BLAH;

Now in the command line, on the machine you are copying to:
navigate to ORACLE_HOME/bin
and run the following command:

impdp BLAH DIRECTORY=dmpdir NETWORK_LINK=blahsyslink;

Enter BLAH's password (for local system) when prompted

No comments:

Post a Comment