Oracle Database migration using Oracle Import and Export tools

Source: Internet
Author: User

As one of the mainstream databases in the current market, Oracle databases are developed on many applications. Due to the reasons for upgrading Oracle databases and different application providers, several Oracle versions may exist in an application unit, such as Oracle 7, Oracle 8, Oracle 8i, and Oracle 9i .. Considering the convenience of database centralization, maintenance, and backup, and avoiding repeated investment in equipment, when applications are compatible, porting Oracle databases of several versions to a mainstream version is an optimal choice for database administrators.

This causes the migration from a lower version of the Oracle database to a higher version and from a later version of the Oracle database to a lower version, it even includes system platform migration, such as migration from a Windows NT System to a Unix system. The Oracle Import/Export tool Export/Import can be used to facilitate the above porting operations.

Oracle Import/Export tool Export/Import) is mainly used for logical backup of the database, using Export Database dump binary file as the Database Import input to achieve the purpose of transplantation. There are two rules for reference:

Rule 1: You can Import data from earlier versions of Oracle to later versions of Oracle, but it is limited to adjacent versions of Oracle, such as from Oracle 7 to Oracle 8. For conversions between two non-adjacent versions, such as from Oracle 6 to Oracle 8, data should be first input to the intermediate version-Oracle 7, and then transferred from the intermediate database to a later version.

Rule 2: Oracle's Export/Import tool can only connect to a database with a higher or the same Export/Import version. For example, the Oracle 8.0.5 Export/Import version 8.0.5.0.0) can be connected to the Oracle 8.1.6 database version 8.1.6.0.0). The Import files exported from the higher version of Export cannot be read by the lower version; import files exported from earlier versions of Export can be read from later versions.

Using the above two rules, I will discuss the problems I encountered when using Oracle's Import/Export tool Export/Import for a single unit, it is expected to serve as an example.

This organization has Oracle database versions including Oracle 8.1.6 For NT, Oracle 8.0.5 For NT, Oracle 8.0.4 For Digital Unix, and Oracle 7.3 For NT. Considering that Oracle 8.0.4 For Digital Unix runs on a minicomputer and the unit backup system is also For this minicomputer, we decided to port the Oracle 8.1.6 For NT, Oracle 8.0.5 For NT, and Oracle 7.3 For NT databases to Oracle 8.0.4 For Digital Unix.

1. Port Oracle 8.0.5 For NT

Because the version difference is very small, you can directly use Oracle8.0.5's exp80 to export the database's dump file, and then use the FTPbinary transmission mode) to directly upload it to the minicomputer where Oracle 8.0.4 is located, using imp of Oracle 8.0.4 to import data has always encountered the character set problem, that is, all fields containing Chinese characters are displayed "?????? ", Failed to port. Later, we found that, on the Oracle8.0.5 server, we directly use the imp80 of Oracle8.0.5 to remotely import the dump file exported from exp80. the character set problem is solved and transplanted successfully.

For Oracle 8.0.4 For Digital Unix users, it is recommended that you do not back up data using Oracle 8.0.4 For Digital Unix exp and imp tools, the Windows client programs exp80 and imp80 that apply this version. I have performed an experiment: I have created a new user and a table in the Oracle 8.0.4 for Digital UNIN database. I inserted some Chinese rows in the column of the table through the SQL plus client, after submission, use exp data to export data, use imp data to import data, and use SQL plus client to view the data. The Chinese value in the table is displayed "??? ").
2. Port Oracle 7.3

Use the export of Oracle7.3 to export the database's dump file, copy it to the Oracle8.0.5 server, and then use the imp80 of the Oracle8.0.5 server to directly import it to the Oracle 8.0.5 database. This is the migration from Oracle 8.0.5 For NT to Oracle 8.0.4 For Digital Unix.
3. Port Oracle 8.1.6 For NT

The preliminary assumption is that Oracle 8.0.5 is used too much. Because the version is higher than Oracle 8.0.5, some verification is performed on the reference rules.

The dumped file exported from the Export of the 3.1 higher version cannot be read by the Import of the lower version.
Export the dump binary file ora816.dmp on the Oracle 8.1.6 database server.
Import data using Imp80 on the Oracle 8.0.5 database server.
D:> Imp80 house/password file = d: \ ora816.dmp log = d: \ ora816.log full = y
/* Log display */
Connected to: Oracle8 Release 8.0.5.0.0-Production
PL/SQL Release 8.0.5.0.0-Production
IMP-00010: Invalid exported file. Title Verification Failed.
IMP-00021: OS error-error code: decimal 2, hexadecimal 0x2)
IMP-00000: Import failed

3.2 Oracle's Export/Import tool can only connect to databases with higher or the same Export/Import version.
A) Oracle 8.1.6 Remote Data Import
Export the dump binary file ora816.dmp on the Oracle 8.1.6 database server.
Remotely import data to the Oracle8.0.5 database on the Oracle 8.1.6 Database Server
D:> Imp house/password @ orclfrom816to805 file = d: \ ora816.dmp log = d: \ ora816.log full = y
(Orclfrom816to805 is the database connection string directed by Oracle8.1.6 database to Oracle8.0.5 database)
/* Log display */
Connect to: Oracle8 Release 8.0.5.0.0-Production
PL/SQL Release 8.0.5.0.0-Production
......
EXPORT files created by EXPORT: V08.01.06 in the normal path
The ZHS16GBK Character Set and ZHS16GBK NCHAR character set have been imported.
IMP-00003: ORACLE error 2248
ORA-02248: Invalid alter session Option
IMP-00000: Import failed
B) remotely export the Oracle 8.1.6 database with exp80 on the Oracle 8.0.5 database server.
D:> exp80 house/password @ orclfrom805to816 file = d: \ ora816.dmp log = d: \ ora816.log
(Orclfromfrom805to816 is the database connection string directed by Oracle8.0.5 database to Oracle8.1.6 database)
/* Log display */
Connect to: Oracle8i Enterprise Edition Release 8.1.6.0.0-Production
With the Partitioning option
JServer Release 8.1.6.0.0-Production
The ZHS16GBK and ZHS16GBK NCHAR character sets have been exported.
Will export the specified user...
......
The export is successfully terminated without warning.
Import the remotely exported dump file on the Oracle 8.0.5 database server using imp80.
D:> imp80 house/password file = d: \ ora816.dmp full = y log = d: \ ora816 _ I. log
Show imported successfully
At this moment, the migration will become the migration from Oracle 8.0.5 For NT to Oracle 8.0.4 For Digital Unix.
The Oracle Import/Export tool Export/Import can be used for database logical backup and database transplantation to improve database performance, performing an import/export operation on the database can reorganize the data and eliminate database fragments, greatly improving the database performance.


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.