Oracle imports data to another database through the data pump network without generating DMP files

Source: Internet
Author: User

Oracle imports data to another database through the data pump network without generating DMP files

This document records an Oracle Data Import experience for future review. Because the project needs to import the database (USER1) on server A to the database on server B (USER1), and do not want to use the common EMP/IMP method, by generating an intermediate DMP file, remember that the Data Pump emp dp/IMPDP can directly import data from one database to another without generating an intermediate file, so I found some relevant information, the script is as follows:

Impdp system/system @ ORCLB network_link = db_a_user1 logfile = impdp_db_a_user1.log DIRECTORY = TEMP_A_USER1 parallel = 8;

Execution error. The error message is as follows:

LRM-00104: '32; 'is not a legal integer of 'parallel'

After checking the information, it is said that parallel cannot be placed at the end of the syntax, so the adjusted script is as follows:

Impdp system/system @ ORCLB network_link = db_a_user1 logfile = impdp_db_a_user1.log parallel = 8 DIRECTORY = TEMP_A_USER1;

 

An error still occurred while executing the task again. The error message is as follows:

After checking the information, it is found that only public dblink must be created. The original dblink must be deleted and re-created. The error is solved.

Original dblink script:

CREATEDATABASELINKdb_a_user1

CONNECTTOUSER1IDENTIFIEDBYUSER1

USING

'(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

)';

 

Adjusted script:

CREATEPUBLICDATABASELINKdb_a_user1

CONNECTTOUSER1IDENTIFIEDBYUSER1

USING

'(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

)';

 

However, a new error message is displayed:

The directory (DIRECTORY) TEMP_A_USER1 is invalid. You need to put directory = TEMP_A_USER1 in front of the script after checking the information. The adjusted script is as follows:

Impdp system/system @ orclb directory = TEMP_A_USER1 parallel = 8 network_link = db_a_user1 logfile = impdp_db_a_user1.log;

 

Execute again with another error. The error message is as follows:

After checking Dba_Directories, it is found that the creation of TEMP_A_USER1 is faulty. Because server B is an AIX midrange computer, the existing Directory of TEMP_A_USER1 may not have operation permissions. Therefore, delete TEMP_A_USER1 first, create TEMP_A_USER1 again under the system user and grant the UTL_FILE execution permission.

CREATEDIRECTORYTEMP_A_USER1AS '/tmp ';

GRANTREAD, WRITEONDIRECTORYTEMP_A_USER1TOsystem;

Grantexecuteon sys. UTL_FILE TOsystem;

 

Execute the script again

Impdp system/system @ orclb directory = TEMP_A_USER1 parallel = 8 network_link = db_a_user1 logfile = impdp_db_a_user1.log;

If no error occurs, the data is successfully imported from server A to the database of server B.

Conclusion: we can see from the above experiences that the following content should be taken into account when importing databases over the network (without generating DMP files:

1. Create A public db link from server B database to server A database;

2. Create a Directory under system, grant it read and write permissions, and grant the execution permission of SYS. UTL_FILE;

3. Execute the script parameter location. DIRECTORY = TEMP_A_USER1 must be placed in front, parallel = 8 cannot be placed at the end, and whether the Oracle bug has not been determined.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.