Export and Import of EXPDP and IMPDP of Oracle10g

Use the copy function of DB_LINK to remotely back up and restore Data Using expdp and impdp. Delete the dept and emp of scott user on server B before testing.

Use the copy function of DB_LINK to remotely back up and restore Data Using expdp and impdp. Delete the dept and emp of scott user on server B before testing.

Lab environment:

Server A: 172.21.1.30

Server B: 172.21.1.40

The two servers run two Oracle databases respectively. This experiment uses the scott user as an example for testing.

Purpose:

Use the copy function of DB_LINK to remotely back up and restore Data Using expdp and impdp. Before the test, delete the dept and emp of scott user on server B. This test only uses impdp to dept the scott user under server A database, import the emp table to the scott user of database B.

Procedure:

1. log on to the database as sys on server A and server B, and create directory and db_link. The command is as follows:

Session A:> create directory dp01 as '/bk/dp01 ';

Directory created.

Session A:> create public database link ln_db40 connect to scott identified by tiger using 'ip40 ';

Database link created.

Session A:> grant connect, resource, exp_full_database, imp_full_database to scott identified by tiger;

Grant succeeded.

Session B:> create directory dp02 as '/bk/dp02 ';

Directory created.

Session B:> create public database link ln_db30 connect to scott identified by tiger using 'ip30 ';

Database link created.

Session B:> grant connect, resource, exp_full_database, imp_full_database to scott identified by tiger;

Grant succeeded.

2. After confirming that db_link in the above steps is correct, do the following:

A. Server A: (export the dept and emp tables under scott in the local database to exp01.dmp)

[Oracle @ session A: dp01] $ expdp scott/tiger directory = dp01 dumpfile = exp01.dmp network_link = ln_db30 tables = emp, dept

Export: Release 10.2.0.1.0-Production on Wednesday, 03 August, 2011 14:39:05

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT". "SYS_EXPORT_SCHEMA_01": scott/******** @ ip30 directory = dp01 dumpfile = exp01.dmp network_link = ln_db30 tables = emp, dept
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
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
Processing object type SCHEMA_EXPORT/TABLE/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
.. Exported "SCOTT". "SYS_EXPORT_SCHEMA_01" 172.8 KB 1073 rows
.. Exported "SCOTT". "DEPT" 5.656 KB 4 rows
.. Exported "SCOTT". "EMP" 7.820 KB 14 rows
Master table "SCOTT". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
**************************************** **************************************
Dump file set for SCOTT. SYS_EXPORT_SCHEMA_01 is:
/Bk/dp01/exp01.dmp
Job "SCOTT". "SYS_EXPORT_SCHEMA_01" successfully completed at 14:39:37

[Oracle @ session A: dp01] $ ls

Exp01.dmp export. log import. log

B. Copy the backup file exp01.dmp from server A to server B:

[Oracle @ session A: dp01] $ scp-r exp01.dmp 172.21.1.40:/bk/dp02/

C. Run the following command on server A to import the dept and emp tables to scott user of server B:

[Oracle @ session A: dp01] $ impdp scott/tiger @ ip40 directory = dp02 dumpfile = exp01.dmp tables = emp, dept

Import: Release 10.2.0.1.0-Production on Wednesday, 03 August, 2011 14:34:57

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT". "SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT". "SYS_IMPORT_TABLE_01": scott/********* @ ip40 directory = dp02 dumpfile = exp01.dmp tables = emp, dept
Processing object type TABLE_EXPORT/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.. Imported "SCOTT". "DEPT" 5.656 KB 4 rows
.. Imported "SCOTT". "EMP" 7.820 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SCOTT". "SYS_IMPORT_TABLE_01" successfully completed at 14:34:55

3. log on to the database of server A using the sys user for testing and check whether the dept and emp tables of scott user B exist:

$ Sqlplus/as sysdba

Session A:> conn scott/tiger

Connected.

Session A:> select * from tab;

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

Session A:> select * from tab @ ln_db40;

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

It is not difficult to see from the above experiment that the dept and emp tables have been successfully imported to scott users of B.

EXPDP can export data from all databases, as long as it can connect to the database or the database it logs on
The data of the exported database can be read, because the data of the exported database can be read through the database chain.

Test as follows:

There are three servers: A, B, and C. The following command is executed in:

[Oracle @ session A: dp01] $ expdp scott/tiger @ ip40 directory = dp02 dumpfile = exp01.dmp network_link = ln_db50 tables = emp, dept

Export: Release 10.2.0.1.0-Production on Wednesday, 03 August, 2011 14:39:05

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT". "SYS_EXPORT_SCHEMA_01": scott/******** @ ip40 directory = dp02 dumpfile = exp01.dmp network_link = ln_db50 tables = emp, dept
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
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
Processing object type SCHEMA_EXPORT/TABLE/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
.. Exported "SCOTT". "SYS_EXPORT_SCHEMA_01" 172.8 KB 1073 rows
.. Exported "SCOTT". "DEPT" 5.656 KB 4 rows
.. Exported "SCOTT". "EMP" 7.820 KB 14 rows
Master table "SCOTT". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
**************************************** **************************************
Dump file set for SCOTT. SYS_EXPORT_SCHEMA_01 is:
/Bk/dp01/exp01.dmp
Job "SCOTT". "SYS_EXPORT_SCHEMA_01" successfully completed at 14:39:37

Note:

Expdp is on server;

Scott/tiger @ ip40 connect to the database of server B;

Network_link = ln_db30 is the scott user's database chain in the database of server B. It points to scott of server C,
SCOTT10 users only have two tables dept, emp.

From the above operation, we can see that expdp can be a client program, but the exported data (DMP) must be stored in its Login

Directory of the database (in this example, the database directory of machine B is dp02), and the exported data is

The database chain (network_link) is read from the database of machine C.

Note: In this process, database A user scott and database C user scott (or database chain user) must be

Grant the EXP_FULL_DATABASE permission.

Nelwork_link means to import datafile from the local database directly to the remote database, and the exp operation is omitted in the middle,

Nelwork_link = source_database_link. Check that the nelwork_link parameter is an existing target database name.

Database link exists.

Add two restrictions:

Nelwork_link parameter Restrictions

Network imports do not support the use of evolved types.

A: When the NETWORK_LINK parameter is used in conjunction with the TABLES parameter,

Only whole tables can be imported (not partitions of tables ).

Therefore, if the network_link parameter is added when multiple tables are imported using impdp, the following error is returned:

UDI-00011: parameter dumpfile is incompatible with parameter network_link.

B: If the USERID that is executing the import job has the IMP_FULL_DATABASE role on

Target database, then that user must also have the EXP_FULL_DATABASE role on the source

Database. The only types of database links supported by Data Pump Import are: public,

Fixed-user, and connected-user. Current-user database links are not supported.