imp乱码分析–解决建议

发表于 2011 年 11 月 17 日 由 惜分飞

最近有位朋友一直在为exp/imp操作的乱码问题纠结，总是搞不清楚为什么，而且经常莫名其妙的出现乱码，为此我做了一个实验，来说明这个问题的处理思路
一、准备工作

C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 11月 17 18:43:00 2011

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


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create table TEST_EXP
  2  (
  3    A1 NUMBER,
  4    A2 VARCHAR2(10 CHAR),
  5    A3 VARCHAR2(10),
  6    A4 NVARCHAR2(10),
  7    A5 CHAR(10),
  8    A6 NCHAR(10)
  9  );

表已创建。

SQL> comment on column TEST_EXP.A1
  2    is '数字类型----惜分飞';

注释已创建。

SQL> comment on column TEST_EXP.A2
  2    is 'varchar类型1----惜分飞';

注释已创建。

SQL> comment on column TEST_EXP.A3
  2    is 'varchar类型2----惜分飞';

注释已创建。

SQL> comment on column TEST_EXP.A4
  2    is 'nvarchar类型----惜分飞';

注释已创建。

SQL> comment on column TEST_EXP.A5
  2    is 'char类型----惜分飞';

注释已创建。

SQL> comment on column TEST_EXP.A6
  2    is 'nchar类型----惜分飞';

注释已创建。

SQL> insert into test_exp values(1,'xifenfeicf','xifenfeicf','xff','xifenfei','xifenfei');

已创建 1 行。

SQL> insert into test_exp values(1,'惜分飞来向大家问好啦',
2    '杭州惜分飞','杭州惜分飞','杭州惜分飞','杭州惜分飞');

已创建 1 行。

SQL> commit;

提交完成。

SQL> col parameter for a30
SQL> col value for a20
SQL> select * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%';

PARAMETER                      VALUE
------------------------------ --------------------
NLS_CHARACTERSET               ZHS16GBK
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options 断开

C:\Users\XIFENFEI>exp chf/xifenfei tables=test_exp 
file=d:\test_exp.dmp log=d:\test_exp.log

Export: Release 11.2.0.1.0 - Production on 星期四 11月 17 18:46:10 2011

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


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
. . 正在导出表                        TEST_EXP导出了           2 行
成功终止导出, 没有出现警告。

二、使用AL32UTF8编码导入

C:\Users\XIFENFEI>set NLS_LANG=american_america.AL32UTF8

C:\Users\XIFENFEI>imp chf/xifenfei tables=test_exp 
file=d:/test_exp.dmp log=d:/test_exp.log fromuser=chf touser=chf

Import: Release 11.2.0.1.0 - Production on Thu Nov 17 19:24:58 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing CHF's objects into CHF
. . importing table                     "TEST_EXP"          2 rows imported
Import terminated successfully without warnings.
--注意此处提示，编码发生了转换
--导出来文件编码为：ZHS16GBK 
--现在客户端编码为：AL32UTF8 
--导入服务器编码为：ZHS16GBK 
--现在的转换是ZHS16GBK-->AL32UTF8 -->ZHS16GBK
--其中ZHS16GBK-->AL32UTF8说成转换也许不太合适
--(因为ZHS16GBK是已经生产的dmp文件中数据的编码，而AL32UTF8是导入客户端的编码，这个到底是否转换待定)

C:\Users\XIFENFEI>sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 17 19:25:58 2011

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


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

SQL> col comments for a30
SQL> SELECT  COLUMN_NAME,comments FROM DBA_COL_COMMENTS WHERE owner='CHF' AND TABLE_NAME='TEST_EXP';

COLUMN_NAME                    COMMENTS
------------------------------ ------------------------------
A1                             数字类型----惜分飞
A2                             varchar类型1----惜分飞
A3                             varchar类型2----惜分飞
A4                             nvarchar类型----惜分飞
A5                             char类型----惜分飞
A6                             nchar类型----惜分飞

6 rows selected.

SQL>select * from test_exp;

        A1 A2                   A3         A4                   A5         A6
---------- -------------------- ---------- -------------------- ---------- --------------------
         1 xifenfeicf           xifenfeicf xff                  xifenfei   xifenfei
         1 惜分飞来向大家问好啦 杭州惜分飞 杭州惜分飞           杭州惜分飞 杭州惜分飞

--在新窗口查询，编码修改客户端编码造成影响

三、使用US7ASCII编码导入

C:\Users\XIFENFEI>set NLS_LANG=american_america.US7ASCII

C:\Users\XIFENFEI>imp chf/xifenfei tables=test_exp 
file=d:/test_exp.dmp log=d:/test_exp.log fromuser=chf touser=chf

Import: Release 11.2.0.1.0 - Production on Thu Nov 17 19:35:10 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing CHF's objects into CHF
. . importing table                     "TEST_EXP"          2 rows imported
Import terminated successfully without warnings.

SQL> col comments for a30
SQL> SELECT  COLUMN_NAME,comments FROM DBA_COL_COMMENTS WHERE owner='CHF' AND TABLE_NAME='TEST_EXP';

COLUMN_NAM COMMENTS
---------- ------------------------------
A1         ????----???
A2         varchar??1----???
A3         varchar??2----???
A4         nvarchar??----???
A5         char??----???
A6         nchar??----???

6 rows selected.
--sqlplus和plsql dev中均为乱码

SQL> select * from test_exp;

        A1 A2         A3         A4         A5         A6
---------- ---------- ---------- ---------- ---------- ----------
         1 xifenfeicf xifenfeicf xff        xifenfei   xifenfei
         1 ?????????? ?????      ?????      ?????      ?????
--在plsql dev中查询是正常，sqlplus中不正常
--这里为什么plsql dev中能够显示正常，而comment在plsql dev中显示不正常，还有待研究
--说明：这里由于ZHS16GBK转换为US7ASCII的过程不能识别汉字，所以会导致汉字变成了问号

四、使用ZHS16GBK编码

C:\Users\XIFENFEI>set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

C:\Users\XIFENFEI>imp chf/xifenfei tables=test_exp 
file=d:/test_exp.dmp log=d:/test_exp.log fromuser=chf touser=chf

Import: Release 11.2.0.1.0 - Production on 星期四 11月 17 20:26:39 2011

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


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

经由常规路径由 EXPORT:V11.02.00 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 CHF 的对象导入到 CHF
. . 正在导入表                      "TEST_EXP"导入了           2 行
成功终止导入, 没有出现警告。
--注意提示，没有发生任何的编码转换

QL> col comments for a30
SQL> SELECT  COLUMN_NAME,comments FROM DBA_COL_COMMENTS WHERE owner='CHF' AND TABLE_NAME='TEST_EXP';

COLUMN_NAME                    COMMENTS
------------------------------ ------------------------------
A1                             数字类型----惜分飞
A2                             varchar类型1----惜分飞
A3                             varchar类型2----惜分飞
A4                             nvarchar类型----惜分飞
A5                             char类型----惜分飞
A6                             nchar类型----惜分飞

6 rows selected.

SQL>select * from test_exp;

        A1 A2                   A3         A4                   A5         A6
---------- -------------------- ---------- -------------------- ---------- --------------------
         1 xifenfeicf           xifenfeicf xff                  xifenfei   xifenfei
         1 惜分飞来向大家问好啦 杭州惜分飞 杭州惜分飞           杭州惜分飞 杭州惜分飞

五、原因分析,解决建议
在导入过程中，最多会发生三次编码转换:
1、执行exp时，数据库中数据的编码会转换为导出客户端编码
2、执行imp时，dmp文件的编码转换为导入客户端编码
3、导入客户端编码转换为目标端数据库的数据库编码

在exp/imp操作的过程中，经常出现乱码的原因就是编码的相互转换的过程中出现了丢失或者相互不能转换导致。要解决这个问题，最好的办法就是通过NLS_LANG的灵活设置，减少编码转换的次数(如果相邻的转换操作编码一致，那么不会发生编码转换，如试验中的ZHS16GBK编码测试，就没有转换发生)，或者使得相互的转换能够兼容，可以最大程度的减少乱码的出现。
如果已经有了exp导出的dmp文件，然后在导入的过程中，出现乱码，一般的处理建议是nls_lang的编码设置和dmp文件的一致，让转换发生在导入客户端和数据库服务器间(要求:编码可以相互转换)

发表在 逻辑备份/恢复 | 4 条评论

深入理解LOG_ARCHIVE_DEST_n与STANDBY_ARCHIVE_DEST

发表于 2011 年 11 月 16 日 由 惜分飞

一、案例引入
朋友的dg配置如下，问我为什么归档日志都放在了use_db_recovery_file_dest

主库
log_archive_dest_1='location=/U01/app/oracle/oradata/bfodb/arch,valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
log_archive_dest_2='service=tnsname,ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1   reopen=300  register net_timeout=180  valid_for=(online_logfile,primary_role)'
standby_archive_dest='location=use_db_recovery_file_dest'

备库
log_archive_dest_1='location=/U01/app/oracle/oradata/bfodb/arch,valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
log_archive_dest_2='location=location=use_db_recovery_file_dest,valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
standby_archive_dest='location=use_db_recovery_file_dest'

我很惯性的回答，直接传输过来的日志放到LOG_ARCHIVE_DEST_n下面，fal_*过来的归档放置在standby_archive_dest中，也没有过多的思考为什么，因为我们的库都是这样的规则，我已经认为是一种准则了。这个规则也整合符合了他们的要求(都在use_db_recovery_file_dest)中，没有仔细的去看他们的配置，当朋友说到valid_for的属性的时候，我感觉有点不对头了。
1)他们的log_archive_dest_1配置的是ONLINE_LOGFILE，这个是备库，所以肯定不会放到这里
2)难道是通过log_archive_dest_2使得直接传输过来的日志放置到了use_db_recovery_file_dest中？让朋友查询v$standby_log,发现他们的库没有使用standby redo logfile,也就是说，log_archive_dest_2不可能用来传输日志了，现在剩下来可以传输日志的，只有standby_archive_dest了。
3)问题解决了，都传输到use_db_recovery_file_dest，因为LOG_ARCHIVE_DEST_n都不能用(只是从排除法证明)

二、问题深入分析
standby_archive_dest和log_archive_dest_*到底有什么关系，在什么情况下传输到对应的目录中？

如果备库利用standby redo log在备库端自动归档，那么归档日志将会被放置到LOG_ARCHIVE_DEST_n
如果备库是利用主库的arch进程传输过来的归档，那么将会被放置到STANDBY_ARCHIVE_DEST
补充说明：
1)主库的LOG_ARCHIVE_DEST_n='service'默认的arch传输方式，primary会远程将archived log传输到standby_archive_dest下

2)fal_*是通过arch传输过来的，所以使用的是STANDBY_ARCHIVE_DEST目录，如果不存在这个目录，就使用LOG_ARCHIVE_DEST_n

3)If both parameters are specified, the STANDBY_ARCHIVE_DEST initialization parameter 
  overrides the directory location specified with the LOG_ARCHIVE_DEST_n parameter.
如果STANDBY_ARCHIVE_DEST和LOG_ARCHIVE_DEST_n两个参数指定,STANDBY_ARCHIVE_DEST初始化覆盖LOG_ARCHIVE_DEST_n目录指定位置参数。

4)If none of the initialization parameters have been specified, then archived redo log files are stored in the default location for the STANDBY_ARCHIVE_DEST initialization parameter.
如果STANDBY_ARCHIVE_DEST和LOG_ARCHIVE_DEST_n都没有配置，将会把归档日志放到STANDBY_ARCHIVE_DEST的默认目录

5)如果STANDBY_ARCHIVE_DEST未配置，而配置了LOG_ARCHIVE_DEST_n,那么产生的归档将放置到LOG_ARCHIVE_DEST_n

6)建议：STANDBY_ARCHIVE_DEST和LOG_ARCHIVE_DEST_n=‘location’的配置相同

7)在11g，已经不建议使用STANDBY_ARCHIVE_DEST，也就是说建议配置dg的时候尽量使用standby redo logfile
发表在 Data Guard | 2 条评论

Oracle常用用户权限视图

发表于 2011 年 11 月 16 日 由 惜分飞

DBA_SYS_PRIVS 用户所拥有的系统权限

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 16 13:26:09 2011

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


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

SQL> create user xff identified by xifenfei;

User created.

SQL> grant connect to xff;

Grant succeeded.

SQL> select * from DBA_SYS_PRIVS where grantee='XFF';

no rows selected

SQL> REVOKE CONNECT FROM XFF;

Revoke succeeded.

SQL> grant create session to xff;

Grant succeeded.

SQL>  select * from DBA_SYS_PRIVS where grantee='XFF';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
XFF                            CREATE SESSION                           NO

SQL> grant select  on chf.t_1 to xff;

Grant succeeded.

SQL>  select * from DBA_SYS_PRIVS where grantee='XFF';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
XFF                            CREATE SESSION                           NO
--说明只能查询系统权限，不能查询角色，不能查询用户权限

DBA_SYS_PRIVS 用户所拥有的角色

SQL> grant resource to xff;

授权成功。

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='XFF';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
XFF                            RESOURCE                       NO  YES

ROLE_SYS_PRIVS 角色所拥有的系统权限

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='CONNECT';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';

no rows selected

SQL> SELECT * FROM SESSION_ROLES;

ROLE
------------------------------
CONNECT

SQL> CONN / AS SYSDBA
Connected.
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

8 rows selected.
--很多时候只能使用sysdba(或者具体特定权限)才能够查询角色有哪些系统权限

ROLE_ROLE_PRIVS: 角色被赋予的角色

SQL> SELECT *FROM ROLE_ROLE_PRIVS WHERE ROLE='DBA';

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            OLAP_DBA                       NO
DBA                            SCHEDULER_ADMIN                YES
DBA                            DELETE_CATALOG_ROLE            YES
DBA                            EXECUTE_CATALOG_ROLE           YES
DBA                            WM_ADMIN_ROLE                  NO
DBA                            EXP_FULL_DATABASE              NO
DBA                            SELECT_CATALOG_ROLE            YES
DBA                            JAVA_DEPLOY                    NO
DBA                            GATHER_SYSTEM_STATISTICS       NO
DBA                            JAVA_ADMIN                     NO
DBA                            XDBADMIN                       NO

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            IMP_FULL_DATABASE              NO
DBA                            XDBWEBSERVICES                 NO

13 rows selected.

SESSION_PRIVS 当前用户所拥有的全部权限

SQL> conn xff/xifenfei
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> conn / as sysdba
Connected.
SQL> revoke create session from xff;

Revoke succeeded.

SQL> grant connect to xff;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
--只能查看系统权限或者角色中包含的系统权限，不能查看用户权限

SESSION_ROLES: 当前用户被激活的角色

SQL> SELECT * from SESSION_ROLES;

no rows selected

SQL> show user;
USER is "SYS"
SQL> conn xff/xifenfei
Connected.
SQL> SELECT *FROM SESSION_ROLES;

ROLE
------------------------------
CONNECT
--sysdba查询无role选项，全部是由系统权限构成

查询某用户的所有系统权限

SQL> SELECT PRIVILEGE, ADMIN_OPTION
  2    FROM DBA_SYS_PRIVS
  3   WHERE GRANTEE = &USERNAME
  4  UNION
  5  --角色转换为权限
  6  SELECT PRIVILEGE, ADMIN_OPTION
  7    FROM ROLE_SYS_PRIVS
  8   WHERE ROLE IN
  9         (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
 10  UNION
 11  --角色的角色转为权限
 12  SELECT PRIVILEGE, ADMIN_OPTION
 13    FROM ROLE_SYS_PRIVS
 14   WHERE ROLE IN (SELECT GRANTED_ROLE
 15                    FROM ROLE_ROLE_PRIVS
 16                   WHERE ROLE IN (SELECT GRANTED_ROLE
 17                                    FROM DBA_ROLE_PRIVS
 18                                   WHERE GRANTEE = &USERNAME));
输入 username 的值:  'XFF'
原值    3:  WHERE GRANTEE = &USERNAME
新值    3:  WHERE GRANTEE = 'XFF'
输入 username 的值:  'XFF'
原值    9:        (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME)
新值    9:        (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'XFF')
输入 username 的值:  'XFF'
原值   18:                                  WHERE GRANTEE = &USERNAME))
新值   18:                                  WHERE GRANTEE = 'XFF'))

PRIVILEGE                                ADM
---------------------------------------- ---
CREATE CLUSTER                           NO
CREATE INDEXTYPE                         NO
CREATE OPERATOR                          NO
CREATE PROCEDURE                         NO
CREATE SEQUENCE                          NO
CREATE SESSION                           NO
CREATE TABLE                             NO
CREATE TRIGGER                           NO
CREATE TYPE                              NO
UNLIMITED TABLESPACE                     NO

已选择10行。

表相关权限视图

SELECT *FROM TABLE_PRIVILEGES;
SELECT * FROM dba_TAB_PRIVS;
SELECT * FROM ROLE_TAB_PRIVS;
发表在 Oracle | 2 条评论