Skip to content

Datapump-Impdp-Network

March 21, 2012

###############################################################
Schema Refresh or Import Over the Network,
without exporting or creating any dumpfile
###############################################################
Two Cases:
----------------
(A) Import Using privilege DBA User.
(B) Import Using NON-DBA User or schema owner.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
We need to Import Schema : (Test) from PROD to DEV
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Source                : (P041 - Prod)
Target                : (D041 - Dev)
Schema Name (Non DBA) : Test (Source and Target)
DBA  Username         : admin_dba

(A) Import Using privilege DBA User.

=======================================================================
All Below steps are performed on Target (D041) which is Development.
=======================================================================
1) Add Tnsentry of Source database in Target Tnsnames.ora file

Sample TNS of my source DBname : P041
P041 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = malracprd01)(PORT = 1521))
)
(CONNECT_DATA =
(SID = P041)
)
)

Check above TNS is working from Target (D041) dev:
——————————————————————
tnsping P041
sqlplus admin_dba/admin_dba@P041

2) Create Database link on Target (D041), pointing to PROD database (P041)

Create Public Database link p041_lnk connect to admin_dba identified by admin_dba  using ‘P041’;

Note : you need to have Source (PROD) DBUSER_Name having DBA privs: admin_dba

3) On Target Connect with DBuser which has dba privs, in below case admin_dba and execute below commands.

SQL> conn admin_dba/admin_dba

SQL> CREATE OR REPLACE DIRECTORY EXP_DUMP AS ‘/u02/exports/’;

4) Start the Import of Schema (TEST) from Target Server (D041), as below.

impdp admin_dba/admin_dba Schemas=test DIRECTORY=EXP_DUMP  NETWORK_LINK=p041_lnk logfile=impdp_test_network.log

Import: Release 11.2.0.1.0 – Production on Tue Mar 20 05:13:28 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting “admin_dba”.”SYS_IMPORT_SCHEMA_01″:  admin_dba/******** Schemas=test DIRECTORY=EXP_DUMP NETWORK_LINK=p041_lnk logfile=impdp_test_network.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”TEST” already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported “TEST”.”DEPT”                                    4 rows
. . imported “TEST”.”EMP”                                     2 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “E517137”.”SYS_IMPORT_SCHEMA_01″ completed with 1 error(s) at 05:14:48
———————————————————————————————————————————–

Above Import error can be ignored as TEST user was already existing on my target database (D041).

————————————————————————————————————————————
Target : D041
===============
SQL> conn test/test
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
DEPT                           TABLE
EMP                            TABLE

SQL> select * from emp;

ENO ENAME                       DNO
———- ——————– ———-
1 sam                           1
2 tom                           2

SQL> exit
Note : If needed above import can be excuted from source (PROD) as well ,
but import command Line will change from admin_dba/admin_dba => admin_dba/admin_dba@D041 as below,
Assuming Source (PROD) will have the TNS entry of D041.

impdp admin_dba/admin_dba@D041 Schemas=test DIRECTORY=EXP_DUMP  NETWORK_LINK=p041_lnk logfile=impdp_test_network.log

####################################################################################################################################

(B) Import Using NON-DBA User or schema owner (test).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1) Add Tnsentry of Source database in Target Tnsnames.ora file

Sample TNS of my source DBname : P041
P041 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = malracprd01)(PORT = 1521))
)
(CONNECT_DATA =
(SID = P041)
)
)

Check above TNS is working from Target (D041) dev:
——————————————————————
tnsping P041
sqlplus test/test@P041

2) Create public or private Database link on Target (D041), pointing to PROD database (P041).

Test schema need to have Create Database link privs.(grant CREATE DATABASE LINK to test;)

Create Public Database link mtest_p041_lnk connect to test identified by test using ‘P041’; (Public)
OR
Create Database link mtest_p041_lnk connect to test identified by test using ‘P041’; (Private)

3) You Need Database Directory Object in Target D041.

IF you are Using existing database directory object for import , then “test” user need to have read,write privs on directory object.

GRANT READ, WRITE ON DIRECTORY EXP_DUMP_DIR TO test

OR

If you want directory object to be onwned by “test” schema.
——————————————————————————-
test schema must have CREATE ANY DIRECTORY system privilege to create directories. (grant CREATE ANY DIRECTORY to test;)

conn test/test
CREATE OR REPLACE DIRECTORY EXP_DUMP_DIR AS ‘/u17/oradata/exports/’;
4) Start Import Into D041 database, either from Target or Source server.

For any specific tables of test schema:
=========================================
From Target (D041):
===================
impdp test/test TABLES=EMP DIRECTORY=EXP_DUMP_DIR NETWORK_LINK=mtest_p041_lnk logfile=impdp_mtest_network.log

OR

From source (P041):
===================

impdp test/test@D041 TABLES=EMP DIRECTORY=EXP_DUMP_DIR NETWORK_LINK=mtest_p041_lnk logfile=impdp_mtest_network.log

Import: Release 11.2.0.1.0 – Production on Mon Mar 19 05:53:28 2012

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, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting “TEST”.”SYS_IMPORT_TABLE_01″:  test/********@D041 TABLES=mtest DIRECTORY=EXP_DUMP_DIR NETWORK_LINK=mtest_p041_lnk logfile=impdp_mtest_network.log
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported “TEST”.”EMP”                                   12 rows
Job “TEST”.”SYS_IMPORT_TABLE_01″ successfully completed at 05:53:46
For Entire test schema:
=========================================
impdp test/test DIRECTORY=EXP_DUMP_DIR NETWORK_LINK=mtest_p041_lnk logfile=impdp_mtest_network.log

———————————-End of Import Over Network—————————–

Quickly Check the Import Log for any Error:
==============================================
cat impdp_mtest_network.log|grep ORA- |sort -nk 1 |uniq

ORA-01435: user does not exist
ORA-01917: user or role ‘POC_ADMIN_ROLE’ does not exist
ORA-01917: user or role ‘POC_READ_ROLE’ does not exist
ORA-01917: user or role ‘V530878’ does not exist
ORA-01919: role ‘DEFAULT_ROLE’ does not exist
ORA-01919: role ‘DEVELOPER_ROLE’ does not exist
ORA-31625: Schema V530878 is needed to import this object, but is unaccessible
ORA-31684: Object type FUNCTION:”POC”.”LONG2STR” already exists
ORA-31684: Object type PACKAGE_BODY:”POC”.”PKG_UTIL” already exists
ORA-31684: Object type PACKAGE:”POC”.”PKG_UTIL” already exists
ORA-31684: Object type USER:”POC” already exists
ORA-39082: Object type ALTER_FUNCTION:”POC”.”LONG2STR” created with compilation warnings
ORA-39082: Object type VIEW:”POC”.”V_COMBINESECTABLES” created with compilation warnings
ORA-39083: Object type INDEX failed to create with error:
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-39111: Dependent object type OBJECT_GRANT:”POC” skipped, base object type PACKAGE:”POC”.”PKG_UTIL” already exists
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:”V530878″.”XIE_SECURITY_DATA_DAILY” creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:”V530878″.”XIE_TEST_COLUMN” creation failed

Quickly Check the Import Log for all ORA-error
================================================
cat nohup.out|grep ORA- |cut -d: -f1|sort -n|uniq

ORA-01435
ORA-01917
ORA-01919
ORA-31625
ORA-31684
ORA-39082
ORA-39083
ORA-39111
ORA-39112

Oracle Notes for any issues :
==============================
1. ORA-31623 When Submitting a Datapump Job (Doc ID 308388.1)
2. OERR: ORA-31623 a job is not attached to this session via the specified handle (Doc ID 288670.1)
3. UDE-31623 Error with Data Pump Export (Doc ID 1080775.1)
4. MOS Doc 262557.1 (10g: DataPump Export and Import Job and Attached Client Sessions)

Advertisements

From → Oracle

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: