Resolution of character set problem in Oracle database porting

It is a good strategy to use the Oracle Import Export Tool (Import/export) for porting between Oracle databases. Although it is possible to reconstruct the database structure using the reverse Engineering in the Sybase power designer, the more complex data import process can be used, but the job queues, snapshots, and so on will have to be created manually. Export can output the whole database, the specified user, the specified table and the relevant data dictionary, and the output of export to the exported binary file contains the commands needed to completely reconstruct all the selected objects.

I am in for a power plant mis (Oracle database) data using the Oracle Import Export tool from the Windows NT platform to migrate to the digital UNIX platform on the issue of the character set and summed up the experience with everyone to share.

1. Transplant environment
Original operating system platform: Windows NT
Database: Oracle 8.0.5 for Windows NT
Server: HP NetServer LH3
Target Operating system platform: Digital Unix Alpha V4.0
Database: Oracle 8.0.4 for Digital Unix
Server: AlphaServer ES40 minicomputer

2. Data export
With the Oracle Export tool for data export on an NT Server, the Oracle Export tool has both command-line and graphical interfaces.
I directly use the command line data export:
C:> exp80 Gxmisdba/manager file=c:expdat.dmp log=c:export.log
The specified user is about to be exported ...
. Exporting external function library names for user gxmisdba
. Exporting user Gxmisdba Object type definitions
About to export GXMISDBA objects ...
. Exporting database Links
. Exporting ordinal
. Exporting cluster definitions
. The table to be exported gxmisdba through the general path ...
. . Exporting table AAAAA 0 rows are exported
. . Exporting table Evt_carrier_configuration 0 rows are exported
. . Exporting table TBL_AJ_AGKS 331 rows are exported
.
.
.
. Exporting synonyms
. Exporting views
. Process of exporting storage
. Exporting Resources consistency constraints
. Exporting triggers
. Exporting late table activity
. Exporting snapshots
. Exporting snapshot logs
. Exporting job queues
. Exporting refresh groups and subgroups
Successfully terminates the export without warning.

3. Data import
The exported output is transferred to the Digital UNIX server via the FTP command on the NT Server EXPDAT.DMP (using binary transfer mode).
Data import with Oracle for Digital Unix Data import Tool command line
$imp Gxmisdba/manager file=/expdat.dmp full=y log=u01import.log
Connected to:oracle8 Enterprise Edition release 8.0.4.0.0-production
Pl/sql Release 8.0.4.0.0-production
Export file created by export:v08.00.05 via conventional path
. Importing Gxmisdbas objects into GXMISDBA
. . Importing table "AAAAA" 0 rows imported
. . Importing table "evt_carrier_configuration" 0 rows imported
. . Importing table "TBL_AJ_STK" 331 rows imported
Imp-00017:following statement failed with ORACLE error 2437:
"ALTER TABLE" tbl_kj_jlry "ADD CONSTRAINT" Pk_tbl_kj_jlry "PRIMARY KEY (" Fld_kj_jlry_bh "," FLD_KJ_JLRY_XM ") USING INDEX PC Tfree Initrans 2 Maxtrans 255 STORAGE (INITIAL 10240 NEXT 10240 minextents 1 maxextents 2147483645 pctincrease-FREEL Ists 1 freelist GROUPS 1 buffer_pool DEFAULT) "
"ENABLE novalidate"
Imp-00003:oracle Error 2437 encountered
Ora-02437:cannot Enable (GXMISDBA. Pk_tbl_kj_jlry)-primary key violated
.
.
.
Import terminated successfully with warnings.
Data import more than 20 similar errors, after analyzing the error in the "TBL_AJ_STK" table, found that the "FLD_KJ_JLRY_XM" field value (one of the key components) for Chinese characters and in the Digital UNIX server Oracle database "Fld_kj_ Jlry_xm field value is displayed as "????" (viewed with Oracle SQL Plus on the client), resulting in keyword collisions.
Show "????" on a line at client Oracle SQL Plus The value of the field, such as the change to the Chinese value "test", after submission, with the SQL statement to view, just modify the line display "????" The field value becomes "Test", which shows that the Oracle dataset on the Digital Unin server can store Chinese characters, but the Oracle 8.0.4 for Digital unin Import Tool imp failed to Oracle 8.0.5 for Windows NT Imp80 the exported Chinese data for conversion.

4. View Character Set parameters
4.1 View Oracle 8.0.5 for Windows NT props$ content
Sql> Connect Sys/change_on_install
sql> Col value$ format A40
Sql> select name,value$ from props$;
NAME value$
---------------------------------------
DICT. BASE 2
Nls_language American
Nls_territory AMERICA
Nls_currency $
Nls_iso_currency AMERICA
Nls_numeric_characters.,
Nls_calendar Gregorian
Nls_date_format Dd-mon-yy
Nls_date_language American
Nls_characterset ZHS16GBK
Nls_sort BINARY
Nls_nchar_characterset ZHS16GBK
Nls_rdbms_version 8.0.5.0.0
Global_db_name ORACLE. World
Export_views_version 7
15 rows have been selected.

4.2 View the props$ content of Oracle 8.0.4 for Digital Unin
Sql> Connect Sys/change_on_install
sql> Col value$ format A40
Sql> select name,value$ from props$;
NAME value$
---------------------------------------
DICT. BASE 2
Nls_language American
Nls_territory AMERICA
Nls_currency $
Nls_iso_currency AMERICA
Nls_numeric_characters.,
Nls_calendar Gregorian
Nls_date_format Dd-mon-yy
Nls_date_language American
Nls_characterset zhs16cgb231280
Nls_sort BINARY
Nls_nchar_characterset zhs16cgb231280
Nls_rdbms_version 8.0.4.0.0
Global_db_name ORCL. World
Export_views_version 7
Rows selected.
Oracle 8.0.4 for Digital Unin was found to use the Chinese character set zhs16cgb231280 proposed by Oracle in the Digital UNIX environment, and the character sets were different, so I was in Digital Unin Oracle on the server, select the same character set as NT ZHS16GBK (Chinese Simplified Chinese characters 16 GB library). After the installation is complete, you confirm that the Oracle 8.0.4 for Digital unin and Oracle 8.0.5 for Windows NT have the same character set by viewing the contents of the props$. The Oracle 8.0.4 for Digital unin Import tool was then used to re-import the data, but the same error was reported, and the problem was not resolved.

5. The problem-solving approach
I later found out that the direct use of Oracle 8.0.5 for Windows NT import Tools on Oracle 8.0.5 for Windows NT servers (or workstations equipped with Oracle 8.0.5 for Windows 95/98) IMP80 remote to O Racle 8.0.4 for Digital unin database for data import, the problem has been solved.
5.1 On NT Servers, modify Tnsnames.ora (or through Oracle Net8 Easy config) to set the database connection string Gxmis (which you can set itself) to point to Oracle 8.0.4 for Digital unin server.

5.2 Remote Data import on NT Servers
C:>IMP80 gxmisdba/manager@gxmis file=c:expdat.dmpfull=y log=c:import.log

Connected to: Oracle8 Enterprise Edition Release 8.0.4.0.0-production
Pl/sql Release 8.0.4.0.0-production
To export a file created by export:v08.00.05 through a regular path
. Importing GXMISDBA objects to GXMISDBA
. . Importing table "AAAAA" 0 rows are imported
. . Importing table "Evt_carrier_configuration" 0 rows are imported
. . Importing table "TBL_AJ_AGKS" 331 rows are imported
.
.
.
Ready to enable constraints ...
Successfully terminated import

5.3 The Oracle 8.0.4 for Digital Unin character set is again changed to zhs16cgb231280, Data remote import test, the data also import successfully. Explains that the zhs16cgb231280 character set can be compatible with the ZHS16GBK character set.

6. Summary of Experience
6.1 Export tool exp with 8.0.4 for Digital Unin on the Oracle 8.0.4 for Digital Unin server (character set ZHS16GBK) exports the database that is normal (can store and display Chinese).
$ exp Gxmisdba/manager file=/u01/expdat.dmp Log=/u01/export.log
Displays a successful export.
Import by IMP with the Oracle 8.0.4 for Digital unin Import Tool
$imp Gxmisdba/manager file=/u01/expdat.dmp full=y log=u01import.log
The error recurs again.

6.2 The FTP command on the NT Server will be downloaded to the NT Server on the exported output of the Digital Unin server on the Oracle 8.0.4 for the EXPDAT.DMP, and remote import with IMP80.
C:>IMP80 gxmisdba/manager@gxmis file=c:expdat.dmp full=y log=c:import.log
Connected to: Oracle8 Enterprise Edition Release 8.0.4.0.0–production
Pl/sql Release 8.0.4.0.0–production
IMP-00016: Required character set conversion is not supported (from Type 1 to 852)
IMP-00000: Failed to terminate import successfully

6.3 Remote export of data on a digital Unin server on an NT Server (backup)
C:>EXP80 gxmisdba/manager@gxmis file=c:expdat.dmp log=c:export.log
Displays a successful export. And then make a remote import
C:>IMP80 gxmisdba/manager@gxmis file=c:expdat.dmp full=y log=c:import.log
Displays a successful import. View Chinese with client Oracle SQL Plus for normal display.
This explains that data migration, backup, and data recovery for databases containing Chinese on Oracle 8.0.4 for Digital unin servers should not be Digital with an Oracle 8.0.4 for Unin IMP,EXP itself with an import export tool. You should use tools such as IMP80,EXP80 that you can import and export in Chinese.