Data migration problem between databases of different character sets

zhaozj2021-02-16  81

Preparation (prepare test data, etc.):

BEPONE> Uname -a

OSF1 BEPONE V4.0 1229 Alpha

BEPONE>

BEPONE>

BEPONE> SQLPLUS / NOLOG

SQL * Plus: Release 8.1.7.0.0 - Production on Thu Jan 23 15:24:12 2003

(c) CopyRight 2000 Oracle Corporation. All Rights Reserved.

SQL> CONN Eygle / Eygle

Connected.

SQL> CREATE TABLE TESTNLS (a char (9));

Table created.

SQL> INSERT INTO TESTNLS VALUES ('Meet 98');

1 row created.

SQL> Commit

2 ;

COMMIT COMPLETE.

SQL> SELECT * from testnls;

A

---------

Comparison 98

SQL> EXIT

Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

Jserver Release 8.1.7.0.0 - Production

BEPONE>

Check from the client to see if it is normal:

Microsoft Windows 2000 [Version 5.00.2195]

(C) All rights reserved 1985-2000 Microsoft Corp.

C: /> SQLPlus Eygle / Eygle @ 205

SQL * Plus: Release 9.2.0.1.0 - Production On Wednesday, January 23 15:43:22 2002

CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

Jserver Release 8.1.7.0.0 - Production

SQL> SELECT * from testnls;

A

---------

Comparison 98

SQL>

Verify two methods of the server character set:

method one:

BEPONE> ENV | GREP NLS

NLS_LANG = American_America.zhs16GBK

ORA_NLS33 = / DATA / ORACLE / APP / ORACLE / PRODUCT / 8.1.7 / OCOMMON / NLS / Admin / Data

BEPONE>

SQL> SELECT VALUE $ from Props $

2 where name = 'NLS_NCHAR_CHARACTERSET';

Value $

-------------------------------------------------- ------------------------------

ZHS16GBK

SQL>

Method Two:

Export data using Oracle 817 EXP

BEPONE> Exp Eygle / Eygle File = / data / oracle / Eygle Log = / DATA / ORACLE / EYGLE ROWS = Y

Export: Release 8.1.7.0.0 - Production on Thu Jan 23 16:06:51 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

Jserver Release 8.1.7.0.0 - Production

Export done in zhs16gbk character set and zhs16gbk nchar character set

Exporting Pre-Schema Procedural Objects And Actions

. Exporting Foreign Function Library Names for User Eygle

EXPORTING OBJECT TYPE DEFINitions for User Eygle

About to Export Eygle's Objects ...

EXPORTING DATABASE LINKS

EXPORTING SEQUENCE NUMBERS

EXPORTING Cluster Definitions

. About to export Eygle's Tables Via Convenctional Path ...

. EXPORTING TABLE TEST 3 ROWS EXPORTED

. EXPORTING TABLE TESTNLS 1 ROWS EXPORTED

EXPORTING SYNONYMS

EXPORTING VIEWS

Exporting stored procedures

EXPORTING OPERATORS

EXPORTING REFERENTIAL INTEGRITY CONSTRAINTS

EXPORTING TRIGGERS

Exporting IndexTypes

EXPORTING BITMAP, FUNCTIONAL AND EXTENSIBLE INDEXES

EXPORTING PostTables Actions

EXPORTING SNAPSHOTS

EXPORTING SNAPSHOT LOGS

EXPORTING JOB Queues

EXPORTING REFRESH GROUPS AND CHILDREN

EXPORTING DIMENSIONS

EXPORTING POST-Schema Procedural Objects And Actions

EXPORTING STATISTICS

Export Terminated SuccessFully without Warnings.

BEPONE>

BEPONE>

BEPONE> CD / DATA / ORACLE

BEPONE> LS / DATA / ORACLE / EYGLE *

/DATA/Orcle/eygle.dmp /data/oracle/eygletest.dmp

/Data/oracle/eygle.log /data/oracle/eygletest.log

Open this DMP file using Utledit, find the 2nd and 3rd bytes:

SQL> SELECT NLS_CHARSET_NAME (TO_NUMBER ('0354', 'xxxx')) from DUAL

NLS_CHARSET_NAME (to_number ('03

----------------------------------------

ZHS16GBK

SQL>

Transfer this DMP to the remote host: bepone> ​​FTP HPL1

Connected to HPL1.

220 HPL1 FTP Server (Version 1.1.214.4 (phne_23950) Tue May 22 05:49:01 GMT 2001) Ready.

Name (HPL1: Oracle): Oracle

331 Password Required for Oracle.

PASSWORD:

230 User Oracle Logged IN.

Remote System Type IS UNIX.

Using binary model to transfer files.

FTP> bin

200 Type Set to I.

FTP> Prompt *

Interactive mode OFF.

FTP> CD / ORA

250 CWD Command Successful.

FTP> MPUT Eygle *

Local: Eygle.dmp Remote: Eygle.dmp

200 Port Command Successful.

150 Opening Binary Mode Data Connection for Eygle.dmp.

226 Transfer Complete.

16384 BYTES SENT IN 0.00083 Seconds (1.9e 04 kbytes / s)

Local: Eygle.log Remote: Eygle.log

200 Port Command Successful.

150 opening binary model data connection for eygle.log.

226 Transfer Complete.

1206 BYTES SENT IN 0 Seconds (1.2 kbytes / s)

Local: Eygletest.dmp Remote: Eygletest.dmp

200 Port Command Successful.

150 Opening Binary Mode Data Connection for Eygletest.dmp.

226 Transfer Complete.

16384 BYTES SENT IN 0.016 Seconds (1e 03 KBytes / S)

Local: Eygletest.log Remote: Eygletest.log

200 Port Command Successful.

150 opening binary model data connection for Eygletest.log.

226 Transfer Complete.

1155 BYTES SENT IN 0 Seconds (1.1 kbytes / s)

FTP> BYE

221 Goodbye.

BEPONE>

$ Hostname

HPL1

$

$

$ uname -a

HP-UX HPL1 B.11.11 U 9000/800 1124444645 Unlimited-User License

$

$

$ cd / ora

$ ls eygle *

Eygletest.dmp Eygletest.log

$

View the character set of the remote database:

$ ENV | GREP NLS

NLS_LANG = American_america.utf8

ORA_NLS33 = / ORA / OCOMMON / NLS / Admin / Data

$

$ SQLPLUS "/ as sysdba"

SQL * Plus: Release 9.2.0.1.0 - Production On Thu Jan 23 15:49:42 2003

Copyright (C) 1982, 2002, Oracle Corporation. All Rights Reserved.Connected TO:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

With the partitioning and real application clusters Options

JServer Release 9.2.0.1.0 - Production

SQL> Conn Eygle / Eygle @ hpcin

connected.

SQL> SELECT NLS_CHARSET_NAME (TO_NUMBER ('0367', 'xxxx')) from Dual;

NLS_CHARSET_NAME (TO_NUMBER ('0367', 'XXXX'))

-------------------------------------------------- ----------------

UTF8

SQL>

Use the Edit Tool to modify the DMP character set:

Change the second and third bytes of Eygle.dmp from 0354 to 0367

Use Oracle 920's IMP import data:

$ IMP Eygle / Eygle File = / ORA / Eygle

Import: Release 9.2.0.1.0 - Production on Thu Jan 23 16:07:31 2003

CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

With the partitioning and real application clusters Options

JServer Release 9.2.0.1.0 - Production

Export File Created by Export: V08.01.07 Via Conventional Path

Import done in uTF8 character set and al16utf16 nchar character set

Import Server Uses WE8ISO8859P1 Character Set (Possible Charset Conversion)

Export Server Uses ZHS16GBK Nchar Character Set (Possible Ncharset Conversion)

IMPORTING EYGLE's Objects INTO Eygle

.. Importing Table "Test" 3 ROWS IMPORTED

IMPORTING TABLE "testnls" 1 ROWS IMPORTED

Import Terminated SuccessFully without Warnings.

$

Test effect:

转载请注明原文地址:https://www.9cbs.com/read-13920.html

New Post(0)