分类目录归档：Oracle备份恢复

通过ZHS16GBK和AL32UTF8字符编码分析exp/imp

发表于 2011 年 12 月 21 日 由 惜分飞

一、试验环境和试验准备工作
1.源端

[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus hr/xifenfei

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 21 14:07:24 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT userenv('language') FROM dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

SQL> create table xifenfei (name varchar2(6));

Table created.

SQL> insert into xifenfei values('惜分飞');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xifenfei;

NAME
------
惜分飞
惜分飞
惜分飞
惜分飞

2.目标端

[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 15:26:18 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> SELECT userenv('language') FROM dual;

USERENV('LANGUAGE')
----------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

二、导出测试表(xifenfei)
1.ZHS16GBK编码

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ exp hr/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei.log tables=xifenfei

Export: Release 10.2.0.5.0 - Production on Wed Dec 21 14:07:11 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                       XIFENFEI          4 rows exported
Export terminated successfully without warnings.

2.AL32UTF8编码

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ exp hr/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_new.log tables=xifenfei

Export: Release 10.2.0.5.0 - Production on Wed Dec 21 14:38:14 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                       XIFENFEI          4 rows exported
Export terminated successfully without warnings.

3.dmp文件比较
3.1)文件大小比较

[oracle@node1 ~]$ cd /tmp
[oracle@node1 tmp]$ ll *.dmp
-rw-r--r-- 1 oracle oinstall 16384 12-21 14:07 xifenfei.dmp
-rw-r--r-- 1 oracle oinstall 16384 12-21 14:38 xifenfei_new.dmp
虽然导出客户端设置了不步的编码，但是导出的文件大小相等

3.2)文件头部比较

--xifenfei.dmp
00000000h: 03 03 54 45 58 50 4F 52 54 3A 56 31 30 2E 30 32 ; ..TEXPORT:V10.02
00000010h: 2E 30 31 0A 44 48 52 0A 52 54 41 42 4C 45 53 0A ; .01.DHR.RTABLES.
00000020h: 38 31 39 32 0A 30 0A 33 32 0A 30 0A 03 54 03 54 ; 8192.0.32.0..T.T
00000030h: 07 D0 00 01 00 00 00 00 00 00 00 00 00 11 00 20 ; .?............ 
00000040h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;                 
00000050h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;                 
00000060h: 20 20 20 20 20 20 20 20 20 57 65 64 20 44 65 63 ;          Wed Dec
00000070h: 20 32 31 20 31 34 3A 37 3A 31 32 20 32 30 31 31 ;  21 14:7:12 2011
00000080h: 2F 74 6D 70 2F 78 69 66 65 6E 66 65 69 2E 64 6D ; /tmp/xifenfei.dm
00000090h: 70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; p...............

--xifenfei_new.dmp
00000000h: 03 03 69 45 58 50 4F 52 54 3A 56 31 30 2E 30 32 ; ..iEXPORT:V10.02
00000010h: 2E 30 31 0A 44 48 52 0A 52 54 41 42 4C 45 53 0A ; .01.DHR.RTABLES.
00000020h: 38 31 39 32 0A 30 0A 33 32 0A 30 0A 03 69 03 54 ; 8192.0.32.0..i.T
00000030h: 07 D0 00 01 00 00 00 00 00 00 00 00 00 15 00 20 ; .?............ 
00000040h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;                 
00000050h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;                 
00000060h: 20 20 20 20 20 20 20 20 57 65 64 20 44 65 63 20 ;         Wed Dec 
00000070h: 32 31 20 31 34 3A 33 38 3A 31 35 20 32 30 31 31 ; 21 14:38:15 2011
00000080h: 2F 74 6D 70 2F 78 69 66 65 6E 66 65 69 5F 6E 65 ; /tmp/xifenfei_ne
00000090h: 77 2E 64 6D 70 00 00 00 00 00 00 00 00 00 00 00 ; w.dmp...........

--ZHS16GBK dmp 文件 第2,3字节为 0354
--AL32UTF8 dmp 文件 第2,3字节为 0369

3.3)比较dmp文件内容

--xifenfei.dmp
000020ceh: 2B 30 30 3A 30 30 00 00 04 00 42 59 54 45 06 00 ; +00:00....BYTE..
000020deh: 55 4E 55 53 45 44 01 00 32 0B 00 49 4E 54 45 52 ; UNUSED..2..INTER
000020eeh: 50 52 45 54 45 44 0B 00 44 49 53 41 42 4C 45 3A ; PRETED..DISABLE:
000020feh: 41 4C 4C 00 00 0A 4D 45 54 52 49 43 53 54 0A 54 ; ALL...METRICST.T
0000210eh: 41 42 4C 45 20 22 58 49 46 45 4E 46 45 49 22 0A ; ABLE "XIFENFEI".
0000211eh: 43 52 45 41 54 45 20 54 41 42 4C 45 20 22 58 49 ; CREATE TABLE "XI
0000212eh: 46 45 4E 46 45 49 22 20 28 22 4E 41 4D 45 22 20 ; FENFEI" ("NAME" 
0000213eh: 56 41 52 43 48 41 52 32 28 36 29 29 20 20 50 43 ; VARCHAR2(6))  PC
0000214eh: 54 46 52 45 45 20 31 30 20 50 43 54 55 53 45 44 ; TFREE 10 PCTUSED
0000215eh: 20 34 30 20 49 4E 49 54 52 41 4E 53 20 31 20 4D ;  40 INITRANS 1 M
0000216eh: 41 58 54 52 41 4E 53 20 32 35 35 20 53 54 4F 52 ; AXTRANS 255 STOR
0000217eh: 41 47 45 28 49 4E 49 54 49 41 4C 20 36 35 35 33 ; AGE(INITIAL 6553
0000218eh: 36 20 4E 45 58 54 20 31 30 34 38 35 37 36 20 46 ; 6 NEXT 1048576 F
0000219eh: 52 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C ; REELISTS 1 FREEL
000021aeh: 49 53 54 20 47 52 4F 55 50 53 20 31 20 42 55 46 ; IST GROUPS 1 BUF
000021beh: 46 45 52 5F 50 4F 4F 4C 20 44 45 46 41 55 4C 54 ; FER_POOL DEFAULT
000021ceh: 29 20 54 41 42 4C 45 53 50 41 43 45 20 22 55 53 ; ) TABLESPACE "US
000021deh: 45 52 53 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 ; ERS" LOGGING NOC
000021eeh: 4F 4D 50 52 45 53 53 0A 49 4E 53 45 52 54 20 49 ; OMPRESS.INSERT I
000021feh: 4E 54 4F 20 22 58 49 46 45 4E 46 45 49 22 20 28 ; NTO "XIFENFEI" (
0000220eh: 22 4E 41 4D 45 22 29 20 56 41 4C 55 45 53 20 28 ; "NAME") VALUES (
0000221eh: 3A 31 29 0A 01 00 01 00 06 00 54 03 01 00 00 00 ; :1).......T.....
0000222eh: 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 06 00 CF A7 ; ....惜分飞....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....惜分飞..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..惜分飞...MET
0000225eh: 52 49 43 53 54 72 65 66 65 72 65 6E 74 69 61 6C ; RICSTreferential
0000226eh: 20 69 6E 74 65 67 72 69 74 79 20 63 6F 6E 73 74 ;  integrity const
0000227eh: 72 61 69 6E 74 73 0A 4D 45 54 52 49 43 45 54 20 ; raints.METRICET 
0000228eh: 34 0A 4D 45 54 52 49 43 53 54 74 72 69 67 67 65 ; 4.METRICSTtrigge
0000229eh: 72 73 0A 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 ; rs.METRICET 4.ME
000022aeh: 54 52 49 43 53 54 62 69 74 6D 61 70 2C 20 66 75 ; TRICSTbitmap, fu
000022beh: 6E 63 74 69 6F 6E 61 6C 20 61 6E 64 20 65 78 74 ; nctional and ext
000022ceh: 65 6E 73 69 62 6C 65 20 69 6E 64 65 78 65 73 0A ; ensible indexes.
000022deh: 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 54 52 49 ; METRICET 4.METRI
000022eeh: 43 53 54 70 6F 73 74 74 61 62 6C 65 73 20 61 63 ; CSTposttables ac
000022feh: 74 69 6F 6E 73 0A 4D 45 54 52 49 43 45 54 20 34 ; tions.METRICET 4
0000230eh: 0A 4D 45 54 52 49 43 53 54 50 6F 73 74 2D 69 6E ; .METRICSTPost-in
0000231eh: 73 74 20 70 72 6F 63 65 64 75 72 61 6C 20 61 63 ; st procedural ac
0000232eh: 74 69 6F 6E 73 20 0A 4D 45 54 52 49 43 45 54 20 ; tions .METRICET 
0000233eh: 34 0A 4D 45 54 52 49 43 45 54 47 30 0A 45 58 49 ; 4.METRICETG0.EXI
0000234eh: 54 0A 45 58 49 54 0A                            ; T.EXIT.

--xifenfei_new.dmp
000020ceh: 2B 30 30 3A 30 30 00 00 04 00 42 59 54 45 06 00 ; +00:00....BYTE..
000020deh: 55 4E 55 53 45 44 01 00 32 0B 00 49 4E 54 45 52 ; UNUSED..2..INTER
000020eeh: 50 52 45 54 45 44 0B 00 44 49 53 41 42 4C 45 3A ; PRETED..DISABLE:
000020feh: 41 4C 4C 00 00 0A 4D 45 54 52 49 43 53 54 0A 54 ; ALL...METRICST.T
0000210eh: 41 42 4C 45 20 22 58 49 46 45 4E 46 45 49 22 0A ; ABLE "XIFENFEI".
0000211eh: 43 52 45 41 54 45 20 54 41 42 4C 45 20 22 58 49 ; CREATE TABLE "XI
0000212eh: 46 45 4E 46 45 49 22 20 28 22 4E 41 4D 45 22 20 ; FENFEI" ("NAME" 
0000213eh: 56 41 52 43 48 41 52 32 28 36 29 29 20 20 50 43 ; VARCHAR2(6))  PC
0000214eh: 54 46 52 45 45 20 31 30 20 50 43 54 55 53 45 44 ; TFREE 10 PCTUSED
0000215eh: 20 34 30 20 49 4E 49 54 52 41 4E 53 20 31 20 4D ;  40 INITRANS 1 M
0000216eh: 41 58 54 52 41 4E 53 20 32 35 35 20 53 54 4F 52 ; AXTRANS 255 STOR
0000217eh: 41 47 45 28 49 4E 49 54 49 41 4C 20 36 35 35 33 ; AGE(INITIAL 6553
0000218eh: 36 20 4E 45 58 54 20 31 30 34 38 35 37 36 20 46 ; 6 NEXT 1048576 F
0000219eh: 52 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C ; REELISTS 1 FREEL
000021aeh: 49 53 54 20 47 52 4F 55 50 53 20 31 20 42 55 46 ; IST GROUPS 1 BUF
000021beh: 46 45 52 5F 50 4F 4F 4C 20 44 45 46 41 55 4C 54 ; FER_POOL DEFAULT
000021ceh: 29 20 54 41 42 4C 45 53 50 41 43 45 20 22 55 53 ; ) TABLESPACE "US
000021deh: 45 52 53 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 ; ERS" LOGGING NOC
000021eeh: 4F 4D 50 52 45 53 53 0A 49 4E 53 45 52 54 20 49 ; OMPRESS.INSERT I
000021feh: 4E 54 4F 20 22 58 49 46 45 4E 46 45 49 22 20 28 ; NTO "XIFENFEI" (
0000220eh: 22 4E 41 4D 45 22 29 20 56 41 4C 55 45 53 20 28 ; "NAME") VALUES (
0000221eh: 3A 31 29 0A 01 00 01 00 06 00 54 03 01 00 00 00 ; :1).......T.....
0000222eh: 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 06 00 CF A7 ; ....惜分飞....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....惜分飞..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..惜分飞...MET
0000225eh: 52 49 43 53 54 72 65 66 65 72 65 6E 74 69 61 6C ; RICSTreferential
0000226eh: 20 69 6E 74 65 67 72 69 74 79 20 63 6F 6E 73 74 ;  integrity const
0000227eh: 72 61 69 6E 74 73 0A 4D 45 54 52 49 43 45 54 20 ; raints.METRICET 
0000228eh: 34 0A 4D 45 54 52 49 43 53 54 74 72 69 67 67 65 ; 4.METRICSTtrigge
0000229eh: 72 73 0A 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 ; rs.METRICET 4.ME
000022aeh: 54 52 49 43 53 54 62 69 74 6D 61 70 2C 20 66 75 ; TRICSTbitmap, fu
000022beh: 6E 63 74 69 6F 6E 61 6C 20 61 6E 64 20 65 78 74 ; nctional and ext
000022ceh: 65 6E 73 69 62 6C 65 20 69 6E 64 65 78 65 73 0A ; ensible indexes.
000022deh: 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 54 52 49 ; METRICET 4.METRI
000022eeh: 43 53 54 70 6F 73 74 74 61 62 6C 65 73 20 61 63 ; CSTposttables ac
000022feh: 74 69 6F 6E 73 0A 4D 45 54 52 49 43 45 54 20 34 ; tions.METRICET 4
0000230eh: 0A 4D 45 54 52 49 43 53 54 50 6F 73 74 2D 69 6E ; .METRICSTPost-in
0000231eh: 73 74 20 70 72 6F 63 65 64 75 72 61 6C 20 61 63 ; st procedural ac
0000232eh: 74 69 6F 6E 73 20 0A 4D 45 54 52 49 43 45 54 20 ; tions .METRICET 
0000233eh: 34 0A 4D 45 54 52 49 43 45 54 47 30 0A 45 58 49 ; 4.METRICETG0.EXI
0000234eh: 54 0A 45 58 49 54 0A                            ; T.EXIT.
--通过比较知道，不同客户端编码，导出的数据内容相同，
--都是一个汉字对应两个字节(说明是按照服务端编码导出)

三、导入测试表(xifenfei)
1.导入xifenfei.dmp(ZHS16GBK编码导出)
1.1)客户端编码设置ZHS16GBK

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
 log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test

Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:16:27 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞          0 rows imported
Import terminated successfully with warnings.

1.2)客户端编码设置为AL32UTF8

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test

Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:21:41 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋         0 rows imported
Import terminated successfully with warnings.

2.导入xifenfei_new.dmp(AL32UTF8编码导出)
2.1)客户端编码设置ZHS16GBK

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test

Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:39:53 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞          0 rows imported
Import terminated successfully with warnings.

2.2)客户端编码设置为AL32UTF8

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test

Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:41:12 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋         0 rows imported
Import terminated successfully with warnings.

四、修改列长度，导入成功
无论源端客户端使用何种编码导出，目标端使用何种编码导入(仅限我这里说的AL32UTF8和ZHS16GBK),如果客户端编码是ZHS16GBK，验证数据的时候，可以省略掉设置编码的过程。

--导入报错后，登录数据库，修改列长度，因为目标端数据库编码是AL32UTF8，
--1个汉字占用3个字节修改列的程度满足错误提示的最大程度。
SQL> alter table xifenfei modify name varchar2(9);

Table altered.

[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test IGNORE=y

Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:25:07 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"          4 rows imported
Import terminated successfully without warnings.

--因为目标端客户端编码为AL32UTF8，而linux展示平台编码为gbk，所以出现乱码
--修改客户端编码后，查询乱码问题消失
[oracle@node1 ~]$ sqlplus test/xifenfei

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 14:25:14 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select * from xifenfei;

NAME
---------
鎯滃垎椋
鎯滃垎椋
鎯滃垎椋
鎯滃垎椋

SQL> exit

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ sqlplus test/xifenfei

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 14:25:52 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select * from xifenfei;

NAME
------------------
惜分飞
惜分飞
惜分飞
惜分飞

五、试验总结
1.当源端字符编码为ZHS16GBK，目标端编码为AL32UTF8，客户端随便为其中的一种编码，迁移数据不会出现乱码，但是会出现列长度不够现象。反过来不行，因为utf8中的部分字符转换到gbk中肯定会不支持
2.设置了源端客户端编码，仅仅是导出来的dmp文件头部有编码字符标示不一样，存储数据还是按照服务端存储
3.打破神话，exp/imp导入要不乱码，导出和导入的客户端编码要一致

发表在 逻辑备份/恢复 | 评论关闭

处理fast_recovery_area无剩余空间案例

发表于 2011 年 12 月 19 日 由 惜分飞

一、打开数据库报错

[oracle@node1 ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 19 15:42:04 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 19489
Session ID: 96 Serial number: 1

二、错误信息
1.alert日志内容

Mon Dec 19 15:23:29 2011
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 3, block 93169, scn 12899730
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: /opt/oracle/oradata/ora11g/redo03.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 3, block 93169, scn 12919731
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Mon Dec 19 15:23:29 2011
LGWR: STARTING ARCH PROCESSES
Mon Dec 19 15:23:29 2011
ARC0 started with pid=27, OS id=19539 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Dec 19 15:23:30 2011
ARC1 started with pid=28, OS id=19548 
Mon Dec 19 15:23:30 2011
ARC2 started with pid=29, OS id=19550 
Mon Dec 19 15:23:30 2011
ARC3 started with pid=30, OS id=19552 
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_arc2_19550.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 4322230272 字节) 已使用 100.00%, 尚有 0 字节可用。
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARC2: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_2_%u_.arc'
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_19489.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4322230272 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_%u_.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ora11g - Archival Error
ORA-16038: 日志 2 sequence# 2 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 2 线程 1: '/opt/oracle/oradata/ora11g/redo02.log'
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_19489.trc:
ORA-16038: log 1 sequence# 1 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'
USER (ospid: 19489): terminating the instance due to error 16038
Mon Dec 19 15:23:31 2011
System state dump requested by (instance=1, osid=19489), summary=[abnormal instance termination].
System State dumped to trace file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_19463.trc
Dumping diagnostic data in directory=[cdmp_20111219152331], requested by (instance=1, osid=19489), summary=[abnormal instance termination].
Instance terminated by USER, pid = 19489

2.trace文件信息(ora11g_ora_19489.trc)

*** 2011-12-19 15:23:31.026 4320 krsh.c
ARCH: Error 19809 Creating archive log file to '/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_%u_.arc'
*** 2011-12-19 15:23:31.026 2932 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'
ORA-16038: log 1 sequence# 1 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/ora11g/redo01.log'
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+461<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+53<-ksuitm()+1332<-kcfopd()+5962<-adbdrv()+51834<-opiexe()+18384<-opiosq0()+3870<-kpooprx()
+274<-kpoal8()+829<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidrv()+570
<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252 
----- End of Abridged Call Stack Trace -----

这个错误很明显：因为数据库归档日志放置在fast_recovery_area中，而空间已满，导致联机日志sequence# 2不能被归档，数据库无法打开

三、修改db_recovery_file_dest_size,打开数据库

SQL>  Alter system set db_recovery_file_dest_size=20G scope=both;

System altered.

SQL> alter database open;

Database altered.

设置较大db_recovery_file_dest_size，先打开数据库，再解决问题(减少down机时间是dba一大准则)

四、删除历史归档日志
从sequence# 为2中很明显看出来，数据库进行了resetlogs打开，所以前面的归档日志，在原则上已经无效(不再使用原始备份集恢复)，因为数据库的控制文件中，无原归档日志信息，所以无法使用rman删除归档日志。那只能使用os命令先删除掉历史归档日志，然后再使用rman处理

[root@node1 archivelog]# find ./ -mtime +1| xargs rm -rf
[root@node1 archivelog]# ll
总计 72
drwxr-x--- 2 oracle oinstall 4096 12-18 22:35 2011_12_18
drwxr-x--- 2 oracle oinstall 4096 12-19 13:25 2011_12_19

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_10_31/o1_mf_1_5_7bxbhkof_.arc RECID=1 STAMP=766015219
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_11_01/o1_mf_1_6_7bxw2gpo_.arc RECID=2 STAMP=766033231
……………………………………
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_105_7gc3co97_.arc RECID=132 STAMP=770306728
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_106_7gc3cv1w_.arc RECID=123 STAMP=770306728
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_107_7gc3mbpr_.arc RECID=127 STAMP=770306728
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_1_7gxtrlnq_.arc RECID=134 STAMP=770312597
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_2_7gxtrloz_.arc RECID=135 STAMP=770312597
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_3_7gxtrodg_.arc RECID=136 STAMP=770312599
validation failed for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_12/o1_mf_1_1_7gc3ojqw_.arc RECID=126 STAMP=770306728
……………………………………
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_20_7gxlq29k_.arc RECID=113 STAMP=770306728
validation succeeded for archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_19/o1_mf_1_21_7gxl3zdm_.arc RECID=114 STAMP=770306728
Crosschecked 136 objects

RMAN> DELETE EXPIRED  archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
List of Archived Log Copies for database with db_unique_name ORA11G
……………………………………
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
……………………………………
deleted archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_16/o1_mf_1_14_7gpood3n_.arc RECID=115 STAMP=770306728
deleted archived log
archived log file name=/opt/oracle/fast_recovery_area/ORA11G/archivelog/2011_12_17/o1_mf_1_15_7gqhvvhh_.arc RECID=106 STAMP=770306727
Deleted 124 EXPIRED objects

五、补充说明
1.使用os命令删除fast_recovery_area内容后，需要使用crosscheck检测(如:archivelog all,backup等)。
2.然后使用 DELETE EXPIRED命令删除(archivelog all,backup等)
3.fast_recovery_area设置合适大小+合适的策略
4.resetlogs打开数据库后，做好备份
5.fast_recovery_area无剩余空间处理思路
5.1)如果数据库不能登录：重启至mount，增大fast_recovery_area，open数据库，然后使用rman删除历史垃圾数据(备份集,日志,闪回日志等)
5.2)如果数据库可以使用sys登录，增大fast_recovery_area(使其数据库可以正常工作)，然后使用rman处理垃圾数据

发表在 rman备份/恢复 | 一条评论

使用dbms_backup_restore包恢复数据库

发表于 2011 年 12 月 19 日 由 惜分飞

Oracle提供了一个包:DBMS_BACKUP_RESTORE包是由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的.catproc.sql 脚本运行后会调用这两个包.所以是每个数据库都有的这个包是Oracle服务器和操作系统之间IO操作的接口.由恢复管理器直接调用。而且据说这两个脚本的功能是内建到Oracle的一些库文件中的.
由此可见，我们可以在数据库 nomount 情况下调用这些package ,来达到我们的恢复目的
1、启动数据库到nomount状态

[oracle@node1 ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 19 13:34:22 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes

2、恢复controlfile

SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/opt/oracle/oradata/ora11g/control01.ctl');
  8  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/ora11g_02mu1avd_1_1.rman', params=>null);
  9  sys.dbms_backup_restore.deviceDeallocate;
 10  END;
 11  /

PL/SQL procedure successfully completed.

3、恢复数据文件

SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>1,toname=>'/opt/oracle/oradata/ora11g/system01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>2,toname=>'/opt/oracle/oradata/ora11g/sysaux01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>3,toname=>'/opt/oracle/oradata/ora11g/undotbs01.dbf');
  8    9   10  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>4,toname=>'/opt/oracle/oradata/ora11g/users01.dbf');
 11  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>5,toname=>'/opt/oracle/oradata/ora11g/example01.dbf');
 12  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/ora11g_01mu1aqq_1_1.rman', params=>null);
 13  sys.dbms_backup_restore.deviceDeallocate;
 14  END;
 15  /

PL/SQL procedure successfully completed.

4、启动数据库

SQL> alter database mount;
SQL> alter database recover until cancel using backup controlfile;
SQL> alter database open resetlogs;

5、特别说明
在oracle 10g及其以后版本中，因为rman中有catalog with start命令，可以实现rman备份的加载，所以不需要使用dbms_backup_restore包处理，在oracle 9i及其以前版本中，可能因为没有catalog库，控制文件中又没有了备份集信息，需要采用这种方法处理数据文件还原，然后根据实际情况，使用ALTER DATABASE REGISTER LOGFILE 添加日志，进行恢复

发表在 rman备份/恢复 | 2 条评论