Oracle data migration Solution

The following methods do not consider incremental data updates, real-time data synchronization, and logical data conversion. If you have these requirements, we recommend that you use a third-party ETL tool or

The following methods do not consider incremental data updates, real-time data synchronization, and logical data conversion. If you have these requirements, we recommend that you use a third-party ETL tool or

Data Migration transfers data from one place to another. Main application scenarios include: Building a test environment based on the formal system, copying data from the Intranet to the Internet, and upgrading the database server hardware. Different migration methods can be adopted based on the data volume and system architecture to be migrated.

Note: The following methods do not consider incremental data updates, real-time data synchronization, and logical data conversion. If you have these requirements, we recommend that you use third-party ETL tools or other Oracle Data Synchronization technologies.

I. Common examples

1.1 how to build a test environment at the customer's site?

In the general solution, use the imp/exp tool to export data directly in the source database, and then import the data to the target database. The execution speed of IMP/EXP is mainly limited by the disk and network.

Data volume: 1.5 GB

Export time: 5 minutes

Import time: 23 minutes

Exported file size: 641 MB

Export and Import environment: single CPU, MB memory. To maximize the speed, direct path export, maximum I/O buffer setting, and import and export files are all executed on the server.

1.2 is there any faster way?

Yes, impdp/expdp is still used. Instead of exporting and importing data, you can directly import the data from the source database to the target database.

CMD> Impdp testi @ target library directory = DMPDIR schemas = TESTI

Network_link = source database dblink remap_schema = TESTI: TESTA

The preceding statement imports the TESTI user data of the source database to the TESTA user of the target database.

This operation is the most convenient tool for data migration in the LAN, but it may also be the slowest tool.

1.3 is there any faster way?

Yes. Use impdp/expdp instead. Export is also performed in the source database and import is performed in the target database. The operation speed can be greatly improved. The speed of IMPDP/EXPDP is mainly limited by the disk and has nothing to do with the network.

Original data size: 1.5 GB

Expdp export operation time: 5 minutes

Impdp import operation time: 22 minutes

Exported file size: 588 MB

Export and Import environment: single CPU, MB memory, degree of parallelism = 1

?? Didn't you say it would be faster? Why is the speed similar to 3.1 imp/exp?

See the explanation in the fourth part.

1.4 are you sure you want to hurry up?

Use tablespace migration. Export the metadata of the tablespace and copy it together with the data file to the new database. Execute metadata import. Generally, the data volume imported and exported is less than 5 MB. The speed is quite fast, but there are many restrictions.

Export time: 1 minute

Import time: 3 minutes

Export File: 60 MB + data file 1.5 GB

1.5 how can I transfer data from a linux environment to a windows environment?

View v $ transportable_platform. If the data encoding is consistent, you can directly copy the data file. Otherwise, rman, impdp/expdp, or imp/exp is used.

1.6 if you have an excel data table that needs to be updated remotely to the customer database, how can you update it?

Use pl/SQL developer to copy, paste, and submit data.

1.6 If You Need to migrate several tables from the official database to the test database, how can you get it faster?

Use dblink + script or impdp for remote import.