How can I solve the problem of Chinese garbled characters when OracleDUL recovers clob?

Except for the reprinted content, this article is original site: reposted from lovewifelovelife-Roger's Oracle technology blog link: how to solve the problem of Chinese garbled characters when OracleDUL recovers clob? I was bored and studied how OracleDUL restored clob. This is a big gap between DUL and ODU. Use

In addition to the reprinted content, this article is original site: reposted from the Oracle technology blog of love wife love life-Roger. link address: how to solve the problem of Chinese garbled characters when Oracle DUL recovers clob? I was bored and studied how Oracle DUL restored clob. This is a big gap between DUL and ODU. Use

In addition to the reposted content, this site is original:Reposted from the Oracle technology blog of love wife & love life-Roger

Link:How can I solve the problem of Chinese garbled characters when Oracle DUL recovers clob?

I was bored and studied how Oracle DUL restored clob. This is a big gap between DUL and ODU. It is not easy to use.

However, this is boring. The following describes how to prepare a test environment. This article is for reference only. However, you can contact me for various Oracle data recovery methods!
+++ Prepare a test table

[Ora10g @ killdb ~] $ Sqlplus roger/roger? SQL * Plus: Release 10.2.0.5.0-Production on Sun Sep 14 03:58:10 2014? Copyright (c) 1982,201 0, Oracle. All Rights Reserved .?? Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options? Www.killdb.com> select * from test_clob ;? Id name ---------- --------------------------------------------- 1. I love China 2. Why did I killdb.com? Www.killdb.com> desc test_clob Name Null? Type ---------------------------------------- -------- ----------------------------- id number name clob? Www.killdb.com>? DUL> desc roger. test_clob; Table ROGER. TEST_CLOBobj # = 58448, dataobj # = 58448, ts # = 6, file # = 5, block # = 835 tab # = 0, segcols = 2, clucols = 0 Column information: icol #01 segcol #01 ID len 22 type 2 NUMBER (0,-127) icol #02 segcol #02 NAME len 4000 type 112 CLOB cs 852 (ZHS16GBK) LOB Segment: dataobj # = 58449, ts # = 6, file # = 5, block # = 843 chunk = 1 LOB Index: dataobj # = 58450, ts # = 6, file # = 5, block # = 851DUL>

Dump the data in the block to see how the data is actually stored in the block, as shown below:

tab 0, row 0, @0x1ee4tl: 51 fb: --H-FL-- lb: 0x1  cc: 2col  0: [ 2]  c1 02col  1: [44] 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 26 a3 cd 00 18 09 00 00 00 00 00 00 08 00 00 00 00 00 01 62 11 72 31 4e 2d 56 fdtab 0, row 1, @0x1eabtl: 57 fb: --H-FL-- lb: 0x1  cc: 2col  0: [ 2]  c1 03col  1: [50] 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 26 a3 ce 00 1e 09 00 00 00 00 00 00 0e 00 00 00 00 00 01 00 62 00 11 00 5e 00 05 00 72 00 06 00 4etab 0, row 2, @0x1e58tl: 83 fb: --H-FL-- lb: 0x1  cc: 2col  0: [ 2]  c1 04col  1: [76] 00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 26 a3 cf 00 38 09 00 00 00 00 00 00 28 00 00 00 00 00 01 00 00 00 6b 00 00 00 69 00 00 00 6c 00 00 00 6c 00 00 00 64 00 00 00 62 00 00 00 2e 00 00 00 63 00 00 00 6f 00 00 00 6dend_of_block_dump

Here we take the data in the first line as an example. We know that the data in the first line only needs four Chinese characters. Why is the dump col 1 so long?

The information of the previous 84 bytes is actually the lob header, and the following 8 bytes are the actual lob data. We can see that this is not our past

The Hex Encoding is unicode encoding. If you compare the unicode Chinese encoding table, the eight bytes exactly represent our four Chinese characters.

First, we will test dul. Normally, you will find that Chinese characters are garbled after the information extracted by dul is directly loaded by sqlldr.

#### No Transcoding

-- Unload table data DUL> unload table roger. test_clob ;. unloading (index organized) table failed: Warning: Recreating file "LOB01400353.ctl" 0 rows unloadedPreparing lob metadata from lob indexReading LOB01400353.dat 0 entries loaded and sorted 0 entries. unloading table TEST_CLOBDUL: Warning: Recreating file "ROGER_TEST_CLOB.ctl" 3 rows unloadedDUL>? -- Create a test table www.killdb.com> create table test_clob_1 as select * from test_clob where 1 = 0 ;? Table created .? Www.killdb.com>? -- Modify the table name in the ctl file ROGER_TEST_CLOB.ctl and change the content to the following: load dataCHARACTERSET ZHS16GBKinfile 'Roger _ TEST_CLOB.dat 'insertinto table "ROGER ". "TEST_CLOB_1" fields terminated by whitespace ("ID" CHAR (1) enclosed by X '7c', "NAME" LOBFILE (LF58449) terminated by eof nullif LF58449 = 'none ', LF58449 filler char (20) enclosed by X '7c') Description: Actually, the table name is replaced .? -- Load data to test_clob_1 [ora10g @ killdb dul] $ sqlldr roger/roger control = ROGER_TEST_CLOB.ctl? SQL * Loader: Release 10.2.0.5.0-Production on Sun Sep 14 04:11:03 2014? Copyright (c) 1982,200 7, Oracle. All rights reserved .? Commit point reached-logical record count 3 [ora10g @ killdb dul] $? -- Verify the test_clob_1 data www.killdb.com> show userUSER is "ROGER" www.killdb.com> select * from test_clob_1 ;? Id name ---------- ----------------------------------------------------------- 1 br1N-V 2 B ^ rN 3? Www.killdb.com>

We can see that it is almost all garbled.

At first, I opened the lob file generated by unload with UE and compared it to find that it exists in unicode mode. Therefore, we need to transcode it.

#### Transcoded Test
Use the iconv tool that comes with Linux for transcoding. This tool is very powerful and supports multiple encodings, as shown below:

[ora10g@killdb dul]$ iconv -l|grep GBCN-GB//CSGB2312//CSISO58GB1988//EBCDIC-CP-GB//GB//GB2312//GB13000//GB18030//GBK//GB_1988-80//GB_198880//ISO646-GB//[ora10g@killdb dul]$ iconv -l|grep UCS10646-1:1993/UCS4/CSUCS4//ISO-10646/UCS2/ISO-10646/UCS4/UCS-2//UCS-2BE//UCS-2LE//UCS-4//UCS-4BE//UCS-4LE//UCS2//UCS4//?[ora10g@killdb dul]$ mv LF0002.lob LF0002.lob.old[ora10g@killdb dul]$ iconv -f UCS-2BE -t gb2312 LF0002.lob.old > LF0002.lob

Before loading data, clear the table truncate:

Www.killdb.com> truncate table test_clob_1 ;? Table truncated .? ++ Load data [ora10g @ killdb dul] $ sqlldr roger/roger control = ROGER_TEST_CLOB.ctl? SQL * Loader: Release 10.2.0.5.0-Production on Sun Sep 14 04:27:43 2014? Copyright (c) 1982,200 7, Oracle. All rights reserved .? Commit point reached-logical record count 3 [ora10g @ killdb dul] $? +++ Verify test_clob_1 data? Www.killdb.com> select * from test_clob_1 ;? Id name ---------- -------------------------------------------------------- 1 br1N-V 2 I handsome burst 3? Www.killdb.com>

We can see that the processed 2nd pieces of data are displayed normally.

It can be seen that, combined with the iconv tool, Oracle DUL can perfectly support clob's Chinese restoration.

No related posts.

In addition to the reprinted content, this article is original site: reposted from the Oracle technology blog of love wife & love life-Roger. link address: how to solve the problem of Chinese garbled characters when Oracle DUL recovers clob? I was bored and studied how Oracle DUL restored clob. This is a big gap between DUL and ODU. It is not easy to use. However, this is boring. The following describes how to prepare a test environment. This article is for reference only. However, you can contact me for various Oracle data recovery methods! +++ Prepare the test table [ora10g @ killdb ~] $ Sqlplus roger/roger SQL * Plus: Release 10.2.0.5.0-Production on Sun Sep 14 03:58:10 2014 Copyright (c) 1982,201 0, Oracle. all Rights Reserved. connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-Production With [...]