月归档：三月 2014

解决imp导入数据报IMP-00098错误

发表于 2014 年 3 月 16 日 由 惜分飞

晚上十点多，准备睡觉了，一个朋友在qq上找到我,说他们数据库imp异常,希望我帮忙看看
大概情况
使用exp/imp升级并迁移数据库从win 10.2.0.1 升级到linux 11.2.0.3 由于硬盘空间不够，使用exp把win上面数据导出来，格式化掉win的数据文件所在硬盘格式化并加入到linux系统中(也就是说，故障之时，只有dmp文件，没有了数据文件）,因为原库没有了,dmp又报错,所以担心了起来.
imp报错信息

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
. . importing table                "ART_T_ARCTYPE"          0 rows imported
. . importing table                "ART_T_ARTICLE"          0 rows imported
……
. . importing table            "EVT_T_ACCEPT_CODE"          1 rows imported
. . importing table            "EVT_T_ACCEPT_FLOW"
 illegal lob length marker 51166
 bytesread = 00000000000
 TABLE =EVT_T_ACCEPT_FLOW   EVT_T_ACCEPT_FLOW
IMP-00098: INTERNAL ERROR: impgst2
IMP-00028: partial import of previous table rolled back: 680071 rows rolled back
IMP-00008: unrecognized statement in the export file:
  ??????????????????
IMP-00008: unrecognized statement in the export file:
  .:
IMP-00008: unrecognized statement in the export file:
  $;
IMP-00008: unrecognized statement in the export file:
  ||$
…………

这里可以知道数据库exp的客户端是版本是10.2.0.1,使用编码是US7ASCII,但是导入的库编码是ZHS16GBK

exp日志信息

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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

EXP-00091: Exporting questionable statistics.
. . exporting table              EVT_T_ACCEPT_FLOW    3470071 rows exported
. . exporting table             EVT_T_ACCEPT_FLOW1    2707606 rows exported
……
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

这里我们可以知道数据库编码是ZHS16GBK,exp客户端编码为US7ASCII,因此到导出过程中发生了一次由ZHS16GBK到US7ASCII的编码转换

检查新库发现,其db编码为ZHS16GBK,新库的NLS设置为:nls_lang=AMERICAN_AMERICA.ZHS16GBK
整个导出流程是:db编码为ZHS16GBK的数据库被客户端编码为US7ASCII的exp导出,然后被编码为ZHS16GBK的imp导入到db编码为ZHS16GBK的数据库中

故障可能性定位
1.可能在从ZHS16GBK到US7ASCII的编码转换中发生数据异常,也就是说dmp文件本身异常(我们最不希望出现的结果)
2.由于数据库导出过场会发生编码转换(ZHS16GBK->US7ASCII),而导入过程未发生编码转换(ZHS16GBK->ZHS16GBK),从而出现异常

分析并解决
1. 使用dul扫描dmp文件发现汉字都能够识别,而且exp成功的记录条数在dul扫描中都完全正常恢复出来,证明dmp文件是正常的
2. 基于dmp文件正常,编码转换的过程,得出结论在该库的imp过程中设置nls_lang=AMERICAN_AMERICA.US7ASCII问题应该就可以正常解决.果不其然,设置了NLS_LANG之后imp导入数据OK

得出结论
1. 做升级,迁移尽快保留多一份数据,这次吓得不轻
2. exp/imp最好前后客户端编码一致,否则可能被转换晕
如果遭遇真的dmp损坏情况,类似:IMP-00098: INTERNAL ERROR: impgst2,可以联系我们,提供专业处理

发表在 逻辑备份/恢复 | 标签为 , | 2 条评论

含is null sql语句优化

发表于 2014 年 3 月 14 日 由 惜分飞

原sql语句与执行计划

SQL> set autot trace
SQL> WITH AL AS (SELECT * FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL) 
   2 SELECT SWP.ID SWP_ID, AL.* FROM AL FULL OUTER JOIN XIFENFEI_LOG_SWAP SWP ON SWP.ID = AL.ID;

54 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 888046630

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |    24 | 11064 | 24658   (2)| 00:04:56 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |     |
|   2 |   LOAD AS SELECT           |                             |       |       |            |     |
|*  3 |    TABLE ACCESS FULL       | XIFENFEI_LOG                |    23 |  2576 | 24652   (2)| 00:04:56 |
|   4 |   VIEW                     |                             |    24 | 11064 |     6  (17)| 00:00:01 |
|   5 |    UNION-ALL               |                             |       |       |            |     |
|   6 |     NESTED LOOPS OUTER     |                             |    23 | 10465 |     2   (0)| 00:00:01 |
|   7 |      VIEW                  |                             |    23 | 10304 |     2   (0)| 00:00:01 |
|   8 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6605_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
|*  9 |      INDEX UNIQUE SCAN     | XIFENFEI_LOG_SWP_PK         |     1 |     7 |     0   (0)| 00:00:01 |
|* 10 |     HASH JOIN ANTI         |                             |     1 |    20 |     4  (25)| 00:00:01 |
|  11 |      INDEX FULL SCAN       | XIFENFEI_LOG_SWP_PK         |    20 |   140 |     1   (0)| 00:00:01 |
|  12 |      VIEW                  |                             |    23 |   299 |     2   (0)| 00:00:01 |
|  13 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6605_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("CLEAR_TIME" IS NULL)
   9 - access("SWP"."ID"(+)="AL"."ID")
  10 - access("SWP"."ID"="AL"."ID")


Statistics
----------------------------------------------------------
          2  recursive calls
          8  db block gets
     111504  consistent gets
          1  physical reads
        692  redo size
       8075  bytes sent via SQL*Net to client
        502  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         54  rows processed

这里很明显占用资源多，执行时间长的都在XIFENFEI_LOG表的全表扫描上,而该表的where 条件是CLEAR_TIME is null.


分析CLEAR_TIME 列null值的分布

SQL> SELECT count(*) FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL;

  COUNT(*)
----------
        48

SQL> SELECT count(*) FROM XIFENFEI_LOG WHERE CLEAR_TIME IS not NULL;

  COUNT(*)
----------
   6899211

通过这里分析可以知道,CLEAR_TIME is null的值非常少,如果能够创建一个index,取到CLEAR_TIME 列null的值,那效率将非常搞.但是有oracle index知识的人都知道,B树index是不包含null列,因此一般性index无法满足该需求.这里思考创建含常数的复合index,而且把CLEAR_TIME放在前面,因为后面的常数一定存在,因此CLEAR_TIME中含有null的记录也就包含在该复合index中.

创建含常数复合index

SQL> create index ind_XIFENFEI_LOG_null on XIFENFEI_LOG (CLEAR_TIME,0) online;

Index created.

再次查看执行计划

SQL> WITH AL AS (SELECT * FROM XIFENFEI_LOG WHERE CLEAR_TIME IS NULL) 
  2  SELECT SWP.ID SWP_ID, AL.* FROM AL FULL OUTER JOIN XIFENFEI_LOG_SWAP SWP ON SWP.ID = AL.ID;

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2359331571

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |    24 | 11064 |    25   (4)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION    |                             |       |       |            |          |
|   2 |   LOAD AS SELECT              |                             |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| XIFENFEI_LOG                |    23 |  2576 |    19   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_XIFENFEI_LOG_NULL       |    23 |       |     3   (0)| 00:00:01 |
|   5 |   VIEW                        |                             |    24 | 11064 |     6  (17)| 00:00:01 |
|   6 |    UNION-ALL                  |                             |       |       |            |          |
|   7 |     NESTED LOOPS OUTER        |                             |    23 | 10465 |     2   (0)| 00:00:01 |
|   8 |      VIEW                     |                             |    23 | 10304 |     2   (0)| 00:00:01 |
|   9 |       TABLE ACCESS FULL       | SYS_TEMP_0FD9D660D_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN        | XIFENFEI_LOG_SWP_PK         |     1 |     7 |     0   (0)| 00:00:01 |
|* 11 |     HASH JOIN ANTI            |                             |     1 |    20 |     4  (25)| 00:00:01 |
|  12 |      INDEX FULL SCAN          | XIFENFEI_LOG_SWP_PK         |    20 |   140 |     1   (0)| 00:00:01 |
|  13 |      VIEW                     |                             |    23 |   299 |     2   (0)| 00:00:01 |
|  14 |       TABLE ACCESS FULL       | SYS_TEMP_0FD9D660D_51B4E691 |    23 |  2576 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CLEAR_TIME" IS NULL)
  10 - access("SWP"."ID"(+)="AL"."ID")
  11 - access("SWP"."ID"="AL"."ID")


Statistics
----------------------------------------------------------
          2  recursive calls
          8  db block gets
         33  consistent gets
          1  physical reads
        648  redo size
       7688  bytes sent via SQL*Net to client
        502  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

这里可以发现,该sql使用了创建的含常数的复合index,sql执行时间从4分56秒,提高到现在的1秒钟,逻辑读从当初的111504减小到现在的33,巧用含常数的复合索引使得sql执行效率极大提高.

发表在 Oracle性能优化 | 标签为 | 评论关闭

ORACLE_HOME不一致导致实例无法通过本地认证登录

发表于 2014 年 3 月 14 日 由 惜分飞

遇到网友咨询，类似故障重现:Liunx平台ORACLE数据库运行正常，ORACLE_SID正确，但是本地登录提示idle instance，tns方式可以正常登录现象

[oracle@xifenfei ~]$ ps -ef|grep pmon
oracle   26295     1  0 04:11 ?        00:00:01 ora_pmon_XFF
oracle   27997 27966  0 05:48 pts/0    00:00:00 grep pmon
[oracle@xifenfei ~]$ env|grep ORA
ORACLE_SID=XFF
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1
[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 05:48:30 2013

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

Connected to an idle instance.

SQL> 
[oracle@xifenfei ~]$ sqlplus sys/xifenfei@XFF as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 05:54:49 2013

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


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

SQL>

对于这样的现象，通过试验重现，并且通过oradebug ipc进行说明。
补充知识点:oracle本地认证是通过ipc进行的，而ipc是直接访问共享内存段的

系统当前状态
系统未启动然后数据库情况

[oracle@xifenfei ~]$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
[oracle@xifenfei ~]$ ps -ef|grep pmon|grep -v grep
[oracle@xifenfei ~]$

启动数据库
确定环境变量ORACLE_SID,ORACLE_HOME

[oracle@xifenfei ~]$ env|grep ORA
ORACLE_SID=XFF
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1
[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 04:10:22 2013

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  260046848 bytes
Fixed Size                  1266896 bytes
Variable Size              83888944 bytes
Database Buffers          167772160 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.

--做debug ipc
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/oracle/admin/XFF/udump/xff_ora_26852.trc


*** SESSION ID:(152.25) 2013-04-20 04:43:00.983
Dump of unix-generic skgm context
areaflags            000000e7
realmflags           0000000f
mapsize              00000800
protectsize          00001000
lcmsize              00001000
seglen               00200000
largestsize  00000000ffffffff
smallestsize 0000000000400000
stacklimit         0xbdb87e6c
stackdir                   -1
mode                      640
magic                acc01ade
Handle:             0xe781de0 `/u01/oracle/oracle/product/10.2.0/db_1XFF' --->注意($ORACLE_HOME$ORACLE_SID)
Dump of unix-generic realm handle `/u01/oracle/oracle/product/10.2.0/db_1XFF', flags = 00000000
 Area #0 `Fixed Size' containing Subareas 0-0
  Total size 00000000001354d0 Minimum Subarea size 00000000
   Area  Subarea    Shmid      Stable Addr      Actual Addr   --->主要Shmid
      0        0  3080192 0x00000020000000 0x00000020000000
                              Subarea size     Segment size
                          0000000000136000 000000000fa00000
 Area #1 `Variable Size' containing Subareas 2-2
  Total size 000000000f000000 Minimum Subarea size 00400000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
      1        2  3080192 0x00000020800000 0x00000020800000
                              Subarea size     Segment size
                          000000000f000000 000000000fa00000
 Area #2 `Redo Buffers' containing Subareas 1-1
  Total size 00000000006ca000 Minimum Subarea size 00000000
   Area  Subarea    Shmid      Stable Addr      Actual Addr
   Area  Subarea    Shmid      Stable Addr      Actual Addr

[oracle@xifenfei ~]$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x6fd58208 3080192    oracle    640        262144000  17

这里证明ipc的处理是通过$ORACLE_HOME$ORACLE_SID进行的,修改ORACLE_HOME，进一步验证

修改ORACLE_HOME

[oracle@xifenfei ~]$ export ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1/
[oracle@xifenfei ~]$ env|grep ORA
ORACLE_SID=XFF
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/oracle/product/10.2.0/db_1/

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 04:11:46 2013

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

Connected to an idle instance.

SQL> startup 
ORACLE instance started.

Total System Global Area  260046848 bytes
Fixed Size                  1266896 bytes
Variable Size              83888944 bytes
Database Buffers          167772160 bytes
Redo Buffers                7118848 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

[oracle@xifenfei ~]$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x6fd58208 3080192    oracle    640        262144000  18                      
0x079d1b38 3112964    oracle    640        262144000  13              

--启动两个同样的sid实例
[oracle@xifenfei ~]$ ps -ef|grep pmon|grep -v grep
oracle   26211     1  0 04:10 ?        00:00:00 ora_pmon_XFF
oracle   26295     1  0 04:11 ?        00:00:00 ora_pmon_XFF        

SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
SQL> oradebug tracefile_name
/u01/oracle/admin/XFF/udump/xff_ora_27708.trc

*** SESSION ID:(159.5) 2013-04-20 05:32:00.969
Dump of unix-generic skgm context
areaflags            000000e7
realmflags           0000000f
mapsize              00000800
protectsize          00001000
lcmsize              00001000
seglen               00200000
largestsize  00000000ffffffff
smallestsize 0000000000400000
stacklimit         0xbdb5979c
stackdir                   -1
mode                      640
magic                acc01ade
Handle:             0xd99ede0 `/u01/oracle/oracle/product/10.2.0/db_1/XFF'--->注意
Dump of unix-generic realm handle `/u01/oracle/oracle/product/10.2.0/db_1/XFF', flags = 00000000

进一步证明在linux/unix系统，oracle数据库的内存段是通过ORACLE_HOME和ORACLE_SID结合起来识别的,只要ORACLE_HOME或者ORACLE_SID不一样就不能通过IPC访问实例的内存段,也就不能登录数据库

发表在 Oracle | 一条评论