ORA-7445(ksfqpcb)错误

Posted on 2012/06/12 by yangtingkun

在Oracle 10.2.0.4 RAC for Windows环境的告警日志中发现了这个错误。

Windows下的Oracle维护的不多，而Windows下的RAC就更少见了，而对应的这个错误也是比较少见的：

Wed Oct 13 13:00:07 2010
Errors IN file c:\app\oracle\product\10.2.0\admin\cam\bdump\cam1_arc1_6052.trc:
ORA-16038: 日志 2 SEQUENCE# 508 无法归档。
ORA-19504: 无法创建文件""
ORA-00312: 联机日志 2 线程 1: '+CAM/cam/onlinelog/group_2.266.724245379'
Wed Oct 13 13:00:32 2010
Errors IN file c:\app\oracle\product\10.2.0\admin\cam\bdump\cam1_m000_3972.trc:
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7FEFE115160] [ADDR:0xFFFFFFFFFFFFFFFF] [UNABLE_TO_READ] []

导致这个ORA-7445错误的是M000进程，说明是AWR快照生成时导致的错误，检查详细的错误信息：

*** ACTION NAME:(Auto-FLUSH Slave Action) 2010-10-13 13:00:32.504
*** MODULE NAME:(MMON_SLAVE) 2010-10-13 13:00:32.504
*** SERVICE NAME:(SYS$BACKGROUND) 2010-10-13 13:00:32.504
*** SESSION ID:(48.9158) 2010-10-13 13:00:32.504
*** 2010-10-13 13:00:32.504
ksedmp: internal OR fatal error
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7FEFE115160] [ADDR:0xFFFFFFFFFFFFFFFF] [UNABLE_TO_READ] []
CURRENT SQL statement FOR this SESSION:
INSERT INTO wrh$_comp_iostat   (SNAP_ID, DBID, INSTANCE_NUMBER, COMPONENT, FILE_TYPE,    IO_TYPE, OPERATION, BYTES, IO_COUNT) SELECT :snap_id, :dbid, :instance_number, 'RMAN', 'DATAFILE',        is_sync, TYPE, SUM(bytes), SUM(io_count) FROM  (SELECT decode(x.type, 1, 'READ', 'WRITE') AS TYPE,               CASE WHEN (bitand(x.flags, 2) = 0) THEN 'SYNC' ELSE 'ASYNC'                    END AS is_sync,               x.blocks * x.block_size AS bytes,               CASE WHEN (bitand(x.flags, 2) = 0)                    THEN (x.sync_count)                    ELSE (x.async_short_count + x.async_long_count +                          x.async_ready)                    END AS io_count        FROM   x$ksfqp x, v$dbfile df        WHERE  x.type IN (1,2)          AND  df.name = x.filename) GROUP BY is_sync, TYPE
CHECK trace file c:\app\oracle\product\10.2.0\db_1\rdbms\trace\cam1_ora_0.trc FOR preloading .sym file messages
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
000007FEFE115160              0000000000000000     000000000 000000000 000000000
                                                   000000000
ksfqpcb+289          CALL???  000007FEFE115160     000000000 000000000 2A2D4F668
                                                   0000003BA
qerfxFetch+1946      CALL???  ksfqpcb+289          021640168 000000000 000000000
                                                   000000001
rwsfcd+109           CALL???  qerfxFetch+1946      2A2D4F868 000007FFF 0239D6210
                                                   2A2D4E7F0
qerhjFetch+569       CALL???  rwsfcd+109           00001F430 005559DD8
                                                   7FF4DBC5C48 7FF0FDB2DC8
qergsFetch+637       CALL???  qerhjFetch+569       02030FCF8 000000108 000000000
                                                   0211452A8
rwsfcd+109           CALL???  qergsFetch+637       7FF4DBC56C0 7FF0E13002E
                                                   000000000 7FF3A922C50
insfch+132           CALL???  rwsfcd+109           0211452A8 021145350 021144A94
                                                   021145338
insdrv+762           CALL???  insfch+132           000000000 000000000
                                                   7FF4DBC5C48 0239D6210
inscovexe+470        CALL???  insdrv+762           02030C1D8 000000018 000000000
                                                   000000000
insExecStmtExecIniE  CALL???  inscovexe+470        2A2D53420 2A2D4D3E8 021145D98
ngine+99                                           000000002
insexe+453           CALL???  insExecStmtExecIniE  000000003 000000031 000000000
                              ngine+99             0239D6210
opiexe+4991          CALL???  insexe+453           2A2D52DA0 021145D98 000000102
                                                   000000000
kpoal8+2139          CALL???  opiexe+4991          000000049 000000003 021146380
                                                   000000001
opiodr+1136          CALL???  kpoal8+2139          00000005E 000000000 02114A0E8
                                                   0000003C8
kpoodrc+30           CALL???  opiodr+1136          00000005E 000000000 02114A0E8
                                                   000000000
rpiswu2+517          CALL???  kpoodrc+30           0239D69D0 000000000 000000000
                                                   0239D69D0
kpoodr+666           CALL???  rpiswu2+517          2B7048898 000000000 0202E9EF4
                                                   000000002
xupirtrc+2119        CALL???  kpoodr+666           01010CA90 0202F6238 02114A058
                                                   000433555
upirtrc+123          CALL???  xupirtrc+2119        000000000 0101463E7 000000000
                                                   02114A250
kpurcsc+153          CALL???  upirtrc+123          000000000 0056EBE5D 02114B930
                                                   0202F6328
kpuexecv8+1661       CALL???  kpurcsc+153          02114AA10 077338666 0202F6328
                                                   02114C901
kpuexec+1680         CALL???  kpuexecv8+1661       224D2829BFEF 0202FF120
                                                   000000065 0052B55FA
OCIStmtExecute+68    CALL???  kpuexec+1680         000000010 000000001 000000000
                                                   000000000
kewrose_oci_stmt_ex  CALL???  OCIStmtExecute+68    020000021 7FF812517B0
ec+80                                              004032B0D 0202FF120
kewrgwxf1_gwrsql_ex  CALL???  kewrose_oci_stmt_ex  000000000 000000000 00000004C
ft_1+378                      ec+80                000000000
kewrgwxf_gwrsql_exf  CALL???  kewrgwxf1_gwrsql_ex  000000000 000000000
t+571                         ft_1+378             7FFDEADBEEF 000000000
kewrews_execute_wr_  CALL???  kewrgwxf_gwrsql_exf  000000000 02114DEC8 034B245C0
SQL+70                        t+571                200000015
kewrftbs_flush_tabl  CALL???  kewrews_execute_wr_  003C89940 00001E650 00001E788
e_by_sql+203                  SQL+70               00001E770
kewrft_flush_table+  CALL???  kewrftbs_flush_tabl  000000000 00001E778 00001F758
260                           e_by_sql+203         7FF00000001
kewrftec_flush_tabl  CALL???  kewrft_flush_table+  000000001 000000000 02114E2E0
e_ehdlcx+826                  260                  000000000
kewrfat_flush_all_t  CALL???  kewrftec_flush_tabl  2BCC3004F 02114DEC0 000000000
ables+1395                    e_ehdlcx+826         000000000
kewrfos_flush_onesn  CALL???  kewrfat_flush_all_t  000000001 000000001 000000004
ap+191                        ables+1395           2B709CC28
kewrfsc_flush_snaps  CALL???  kewrfos_flush_onesn  7FF52E5B220 000000006
hot_c+652                     ap+191               7FF00000000 224D00000003
kewrafs_auto_flush_  CALL???  kewrfsc_flush_snaps  000000001 000000001 00000002E
slave+838                     hot_c+652            0004A89F3
kebm_slave_main+242  CALL???  kewrafs_auto_flush_  7FF75831318 0004A8075
                              slave+838            02114E5C0 2B7080C88
ksvrdp+1404          CALL???  kebm_slave_main+242  000000000 005273191 000000000
                                                   000000000
opirip+834           CALL???  ksvrdp+1404          656E5C310000001E 003C8B000
                                                   02114FA30 000000000
opidrv+860           CALL???  opirip+834           000000032 000000004 02114FD50
                                                   000000000
sou2o+52             CALL???  opidrv+860           000000032 000000004 02114FD50
                                                   000000003
opimai_real+272      CALL???  sou2o+52             000000000 000000000 000000000
                                                   000000000
opimai+96            CALL???  opimai_real+272      000000000 000000000 000000000
                                                   000000000
BackgroundThreadSta  CALL???  opimai+96            02114FEA8 000000001 000000000
rt+633                                             000000000
00000000770C495D     CALL???  BackgroundThreadSta  006CA7B50 000000000 000000000
                              rt+633               000000000
00000000772C8791     CALL???  00000000770C495D     000000000 000000000 000000000
                                                   000000000
--------------------- Binary Stack Dump ---------------------

从详细日志中可以确定这个ORA-7445错误是发生在ksfqpcb函数上，而且错误发生的SQL在插入WRH$_COMP_IOSTAT表。
根据MOS的记录，这个错误与Bug 8710008 : ORA-07445 [KSFQPCB+0054] ON INSERT TO WRH$_COMP_IOSTAT IN MMON描述的非常相似，除了报错函数和报错语句相同外，数据库的版本也都是10.2.0.4。Oracle在该问题描述时，将BUG指向一个未公开的BUG：8710249。虽然这个bug没有公开，但是Oracle在文档RMAN produces CORE dump during backup Ora-07445: [__gi_strncpy()+17] [ID 1094624.1]中对这个问题进行了描述。这个错误影响版本为10.2.0.4及以后的10.2版本，而这个bug的修复要到11.2中，目前没有在10.2中解决这个问题的方法。
不过客户的数据库的告警日志中只出现了一次这个错误，说明这个错误并非每次重现，而且这个错误出现之前，出现了大量的归档错误的信息，不能排除是由于归档无法完成，从而引发了这个WRH$_COMP_IOSTAT表的插入错误。对应当前的环境而言，如果可以避免归档的问题，很可能就不会引起这个ORA-7445错误。

Posted in BUG | Tagged , , , , , , , , | Leave a comment

ORA-600(723)错误

Posted on 2012/06/11 by yangtingkun

又是一个内存泄漏的问题。
这个错误号723与ORA-600[729]错误十分接近。而且二者都是内存泄漏相关的错误。错误信息为：

Sat Jun 09 16:35:19 2012
Errors IN file c:\app\oracle\product\10.2.0\admin\cam\udump\cam1_ora_6392.trc:
ORA-00600: internal error code, arguments: [723], [47560], [47560], [memory leak], [], [], [], []

详细错误信息摘录如下：

*** 2012-06-09 16:35:18.274
*** ACTION NAME:() 2012-06-09 16:35:18.227
*** MODULE NAME:() 2012-06-09 16:35:18.227
*** SERVICE NAME:() 2012-06-09 16:35:18.227
*** SESSION ID:(42.35) 2012-06-09 16:35:18.227
******** ERROR: PGA memory leak detected 47560 > 35792 ********
******************************************************
HEAP DUMP heap name="pga heap"  DESC=000000005EF9B060
 extent sz=0x20c0 alt=184 het=32767 rec=0 flg=3 opc=3
 parent=0000000000000000 owner=0000000000000000 nex=0000000000000000 xsz=0xfff0
EXTENT 0 addr=0000000059E00010
  Chunk        059e00020 sz=    65504    free      "               "
EXTENT 1 addr=0000000059DF0010
  Chunk        059df0020 sz=    65504    free      "               "
EXTENT 2 addr=0000000059DE0010
  Chunk        059de0020 sz=    65504    free      "               "
EXTENT 3 addr=0000000059DD0010
  Chunk        059dd0020 sz=    65504    free      "               "
EXTENT 4 addr=0000000059DC0010
  Chunk        059dc0020 sz=    65504    free      "               "
EXTENT 5 addr=0000000059DB0010
  Chunk        059db0020 sz=    65504    free      "               "
EXTENT 6 addr=0000000059DA0010
  Chunk        059da0020 sz=    65504    free      "               "
EXTENT 7 addr=0000000059D80010
  Chunk        059d80020 sz=   131040    free      "               "
EXTENT 8 addr=0000000059D60010
  Chunk        059d60020 sz=   131040    free      "               "
EXTENT 9 addr=0000000059D40010
  Chunk        059d40020 sz=   131040    free      "               "
EXTENT 10 addr=0000000059D20010
  Chunk        059d20020 sz=   131040    free      "               "
EXTENT 11 addr=0000000059D00010
  Chunk        059d00020 sz=   131040    free      "               "
EXTENT 12 addr=0000000059CE0010
  Chunk        059ce0020 sz=   131040    free      "               "
EXTENT 13 addr=0000000059CC0010
  Chunk        059cc0020 sz=   131040    free      "               "
EXTENT 14 addr=0000000059CA0010
  Chunk        059ca0020 sz=   131040    free      "               "
EXTENT 15 addr=0000000059C80010
  Chunk        059c80020 sz=   131040    free      "               "
EXTENT 16 addr=0000000059C60010
  Chunk        059c60020 sz=   131040    free      "               "
EXTENT 17 addr=0000000059C40010
  Chunk        059c40020 sz=   131040    free      "               "
EXTENT 18 addr=0000000059C20010
  Chunk        059c20020 sz=   131040    free      "               "
EXTENT 19 addr=0000000059C00010
  Chunk        059c00020 sz=   131040    free      "               "
EXTENT 20 addr=0000000059BE0010
  Chunk        059be0020 sz=   131040    free      "               "
EXTENT 21 addr=0000000059BC0010
  Chunk        059bc0020 sz=   131040    free      "               "
EXTENT 22 addr=0000000059BA0010
  Chunk        059ba0020 sz=   131040    free      "               "
EXTENT 23 addr=0000000059B90010
  Chunk        059b90020 sz=    65504    free      "               "
EXTENT 24 addr=0000000059B80010
  Chunk        059b80020 sz=    29848    free      "               "
  Chunk        059b874b8 sz=     1720    freeable  "krvxhds        "
Dump OF memory FROM 0x0000000059B874B8 TO 0x0000000059B87B70
.
.
.
*** 2012-06-09 16:35:19.100
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [723], [47560], [47560], [memory leak], [], [], [], []
CURRENT SQL information unavailable - no SGA.
CHECK trace file c:\app\oracle\product\10.2.0\db_1\rdbms\trace\cam1_ora_0.trc FOR preloading .sym file messages
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedmp+663           CALL???  ksedst+55            003C878B8 000000000 01DD6DAC8
                                                   000000000
ksfdmp+19            CALL???  ksedmp+663           000000003 05EF9B360 000000000
                                                   003CACC80
kgeriv+184           CALL???  ksfdmp+19            07FFFFFFF 05EF9EC20 000000000
                                                   05EF9B360
kgesiv+102           CALL???  kgeriv+184           05EF9B360 00001E650 003C8735C
                                                   000000000
ksesic3+125          CALL???  kgesiv+102           05EF9AD20 00000B9C8 00001F758
                                                   0004349F1
ksmdpg+1233          CALL???  ksesic3+125          0000002D3 000000000 00000B9C8
                                                   000000000
opidcl+515           CALL???  ksmdpg+1233          01B757AB8 01B745A58 01B759C10
                                                   004C2E706
opidrv+983           CALL???  opidcl+515           05EFAFF20 000000000 01DD6FB00
                                                   000000000
sou2o+52             CALL???  opidrv+983           00000003C 000000004 01DD6FB00
                                                   7FEFF3079D0
opimai_real+131      CALL???  sou2o+52             000000000 0774FB144 01DD6FB70
                                                   01DD6FC20
opimai+96            CALL???  opimai_real+131      7FFFFF6B258 01DD6FCA0
                                                   01DD6FC00 7FE00000034
OracleThreadStart+6  CALL???  opimai+96            000000000 003C874C4 000000050
40                                                 00000601C
00000000773A495D     CALL???  OracleThreadStart+6  01D56FF1C 000000000 000000000
                              40                   000000000
00000000774D8791     CALL???  00000000773A495D     000000000 000000000 000000000
                                                   000000000
--------------------- Binary Stack Dump ---------------------

Oracle有记录的关于ORA-600 [723]的bug有很多，不过当前的错误信息无法定位到已知的bug。
和729错误一样，如果这个内存泄漏不是非常频繁，可以忽略掉。否则可能需要通过升级来解决。
Oracle提供了10262事件来屏蔽这个错误，不过个人不推荐设置该事件。

Posted in BUG | Tagged , , , , | Leave a comment

ORA-600(kfioUnidentify01)错误

Posted on 2012/06/10 by yangtingkun

Oracle 10.2.0.4 RAC环境的ASM出现这个错误。
错误信息为：

Mon Sep 19 18:06:01 2011
Shutting down instance: further logons disabled
Mon Sep 19 18:06:01 2011
Shutting down instance (immediate)
License high water mark = 10
Mon Sep 19 18:06:01 2011
ERROR: diskgroup DATA was NOT mounted
Mon Sep 19 18:06:02 2011
Errors IN file /home/oracle/admin/+ASM/bdump/+asm2_pmon_954662.trc:
ORA-00600: internal error code, arguments: [kfioUnidentify01], [], [], [], [], [], [], []
Mon Sep 19 18:06:03 2011
Errors IN file /home/oracle/admin/+ASM/bdump/+asm2_pmon_954662.trc:
ORA-00600: internal error code, arguments: [kfioUnidentify01], [], [], [], [], [], [], []
Mon Sep 19 18:06:03 2011
PMON: terminating instance due TO error 472
Mon Sep 19 18:06:03 2011
Trace dumping IS performing id=[cdmp_20110919180603]
Mon Sep 19 18:06:04 2011
Shutting down instance (abort)
License high water mark = 10
Mon Sep 19 18:06:08 2011
Instance TERMINATED BY PMON, pid = 954662
Mon Sep 19 18:06:09 2011
Instance TERMINATED BY USER, pid = 963004

这个错误发生在ASM实例关闭的过程中，有PMON进程发出，且最终由PMON终止了实例的运行。这个现象和文档Bug 4708822 – OERI[kfiounidentify01] from PMON [ID 4708822.8]描述的非常接近，最大的区别在于，根据这篇文档，这个ASM上的问题影响版本为10.2.0.3，而在当前的10.2.0.4中以及解决了这个问题。
如果真的和Oracle描述的一致，这个PMON进程导致的实例关闭在10.2.0.4中不在会出现，那么这个问题应该归结到文档Bug 7499123 – Extra diagnostics for ORA-600 [kfioUnidentify01] closing a file in ASM [ID 7499123.8]，在关闭ASM文件时，可能会导致这个错误的产生，这与当前问题倒也有几分相似，不过Oracle对于这个问题的表述是缺少进一步分析的诊断信息，而10.2.0.5去fixed的结果也不过是在出现故障时将更多的信息写到trace中以便于进一步的分析，而与真正解决这个错误没有太大的关系。
好在这个问题出现并不频繁，且发生在ASM实例关闭过程中，没有给系统带来任何不良后果，可以简单的忽略这个问题。

Posted in BUG | Tagged , , , , , | Leave a comment

ORA-600(qcsgpvc3)错误

Posted on 2012/06/09 by yangtingkun

编译包导致的错误。
数据库版本为10.2.0.4 RAC for Linux X86-64，错误信息为：

Fri Nov 4 14:27:47 2011
Errors IN file /u01/admin/orcl/udump/orcl1_ora_15210.trc:
ORA-00600: internal error code, arguments: [qcsgpvc3], [], [], [], [], [], [], []

MOS上这个错误信息为：Bug 7172752 – OERI[qcsgpvc3] recompiling a package body [ID 7172752.8]。这个问题影响的版本为10.2.0.4和11.1.0.6，而Oracle在版本10.2.0.4.2、10.2.0.5、11.1.0.7和11.2.0.1中fixed了这个错误。
和以往对bug言之不详不同，这次Oracle给出重现问题的代码。虽然这个代码我在10.2.0.3上没有重现出来这个错误，不过根据代码可以大概看出是什么问题导致了错误的产生。问题应该是Oracle在编译包体时，没有区分表的列和过程的参数的区别，从而引发了这个错误。而Oracle给出的建议也是，避免过程或函数的变量与过程中参考的列名出现重名的情况。
那么这个问题即使出现也不难解决，只需要将过程或函数的参数进行修改既可。

Posted in BUG | Tagged , , , , , , , | Leave a comment

ORA-600(1616)和ORA-569错误

Posted on 2012/06/08 by yangtingkun

出现错误的数据库为Oracle 10.2.0.4 RAC for HP-UX。
在RAC的一个节点上出现了ORA-600[1616]错误和ORA-569错误：

Sat Apr 7 03:03:09 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_smon_27471.trc:
ORA-00569: Failed TO acquire global enqueue.
Sat Apr 7 03:04:14 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_smon_27471.trc:
ORA-00569: Failed TO acquire global enqueue.
Sat Apr 7 03:04:15 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_smon_27471.trc:
ORA-00569: Failed TO acquire global enqueue.
.
.
.
Sat Apr 7 03:06:35 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_smon_27471.trc:
ORA-00569: Failed TO acquire global enqueue.
Sat Apr 7 03:07:42 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_smon_27471.trc:
ORA-00569: Failed TO acquire global enqueue.
Sat Apr 7 03:07:42 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_mmon_27483.trc:
ORA-00600: internal error code, arguments: [1616], [30], [1], [], [], [], [], []
Sat Apr 7 03:07:44 2012
Trace dumping IS performing id=[cdmp_20120407030744]
Sat Apr 7 03:07:44 2012
ORA-600 encountered WHEN generating server alert SMG-3503
Sat Apr 7 03:07:57 2012
Reconfiguration started (OLD inc 24, NEW inc 26)
List OF nodes:
1
Global Resource Directory frozen
* dead instance detected - DOMAIN 0 invalid = TRUE 
Communication channels reestablished
Master broadcasted resource hash VALUE bitmaps
Non-LOCAL Process blocks cleaned OUT
Sat Apr 7 03:08:03 2012
LMS 0: 1 GCS shadows cancelled, 0 closed
Sat Apr 7 03:08:03 2012
LMS 1: 1 GCS shadows cancelled, 0 closed
SET master node info 
Submitted ALL remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
ALL grantable enqueues GRANTED
Sat Apr 7 03:08:13 2012
Reconfiguration started (OLD inc 26, NEW inc 28)
List OF nodes:
0 1
Nested reconfiguration detected. 
Global Resource Directory frozen
Communication channels reestablished
* DOMAIN 0 valid = 0 according TO instance 0 
Sat Apr 7 03:08:13 2012
Master broadcasted resource hash VALUE bitmaps
Non-LOCAL Process blocks cleaned OUT
Sat Apr 7 03:08:17 2012
LMS 1: 0 GCS shadows cancelled, 0 closed
Sat Apr 7 03:08:17 2012
LMS 0: 0 GCS shadows cancelled, 0 closed
SET master node info 
Sat Apr 7 03:08:39 2012
Submitted ALL remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
ALL grantable enqueues GRANTED
Sat Apr 7 03:08:51 2012
LMS 0: 7542 GCS shadows traversed, 4001 replayed
Sat Apr 7 03:08:51 2012
LMS 1: 7472 GCS shadows traversed, 4001 replayed
Sat Apr 7 03:08:52 2012
LMS 0: 7481 GCS shadows traversed, 4001 replayed
Sat Apr 7 03:08:52 2012
LMS 1: 7522 GCS shadows traversed, 4001 replayed
Sat Apr 7 03:08:52 2012
LMS 0: 7450 GCS shadows traversed, 4001 replayed
.
.
.
Sat Apr 7 03:08:52 2012
LMS 1: 7550 GCS shadows traversed, 4001 replayed
Sat Apr 7 03:08:52 2012
LMS 0: 6396 GCS shadows traversed, 3431 replayed
Sat Apr 7 03:08:52 2012
LMS 1: 5339 GCS shadows traversed, 2878 replayed
Sat Apr 7 03:08:52 2012
Submitted ALL GCS remote-cache requests
Sat Apr 7 03:08:52 2012
Instance recovery: looking FOR dead threads
Fix WRITE IN gcs resources
Sat Apr 7 03:08:52 2012
Beginning instance recovery OF 1 threads
Reconfiguration complete
Sat Apr 7 03:08:53 2012
parallel recovery started WITH 7 processes
Sat Apr 7 03:08:53 2012
Started redo scan
Sat Apr 7 03:08:53 2012
Completed redo scan
2550 redo blocks READ, 152 DATA blocks need recovery
Sat Apr 7 03:08:54 2012
Started redo application at
Thread 1: logseq 75561, block 256542
Sat Apr 7 03:08:54 2012
Recovery OF Online Redo Log: Thread 1 GROUP 1 Seq 75561 Reading mem 0
Mem# 0: +DATA/orcl/onlinelog/group_1.261.611597975
Sat Apr 7 03:08:54 2012
Completed redo application
Sat Apr 7 03:08:54 2012
Completed instance recovery at
Thread 1: logseq 75561, block 259092, scn 52674829295
149 DATA blocks READ, 137 DATA blocks written, 2550 redo blocks READ
Switch log FOR thread 1 TO SEQUENCE 75562

虽然ORA-600[1616]错误在MOS中完全没有记载，但是根据错误信息分析，这个ORA-600错误应该是ORA-569错误导致的，而ORA-569的错误显然和另外一个实例的状态有关。可以看到在问题发生不久之后，另外一个节点发生了关闭和启动的操作。
那么检查一下另外一个节点对应时刻的日志：

Thu Apr 5 23:06:35 2012
Thread 1 advanced TO log SEQUENCE 75470
CURRENT log# 6 seq# 75470 mem# 0: +DATA/orcl/onlinelog/group_6.258.611597995
Thu Apr 5 23:59:38 2012
Thread 1 advanced TO log SEQUENCE 75471
CURRENT log# 2 seq# 75471 mem# 0: +DATA/orcl/onlinelog/group_2.260.611597981
Fri Apr 6 00:20:12 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:15 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:15 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:16 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:16 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:17 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:17 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:18 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:18 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:19 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:19 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:20 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:20 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:20 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:21 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:21 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:22 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:22 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
Fri Apr 6 00:20:23 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLLJ.OLD_000 have been marked unusable
.
.
.
Fri Apr 6 10:55:20 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLTJ.OLD_000 have been marked unusable
Fri Apr 6 10:55:22 2012
SOME indexes OR INDEX [sub]partitions OF TABLE ORCLTJ.OLD_000 have been marked unusable
Fri Apr 6 10:58:54 2012
Thread 1 advanced TO log SEQUENCE 75530
CURRENT log# 6 seq# 75530 mem# 0: +DATA/orcl/onlinelog/group_6.258.611597995
Fri Apr 6 11:20:34 2012
Thread 1 advanced TO log SEQUENCE 75531
CURRENT log# 2 seq# 75531 mem# 0: +DATA/orcl/onlinelog/group_2.260.611597981
.
.
.
Fri Apr 6 16:53:40 2012
Thread 1 advanced TO log SEQUENCE 75543
CURRENT log# 2 seq# 75543 mem# 0: +DATA/orcl/onlinelog/group_2.260.611597981
Fri Apr 6 17:07:15 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl1_smon_15533.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 4120 bytes OF shared memory ("shared pool","select increment$,minvalue,m...","Typecheck","kgghteInit")
Fri Apr 6 17:07:22 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl1_smon_15533.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 4120 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","kgghteInit")
Fri Apr 6 17:07:23 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl1_smon_15533.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 4120 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","kgghteInit")
Fri Apr 6 17:07:40 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl1_smon_15533.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 4120 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","kgghteInit")
Fri Apr 6 17:07:41 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl1_smon_15533.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 4120 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","kgghteInit")
Fri Apr 6 17:15:53 2012
Thread 1 advanced TO log SEQUENCE 75544
CURRENT log# 5 seq# 75544 mem# 0: +DATA/orcl/onlinelog/group_5.259.611597989
Fri Apr 6 17:34:28 2012
Thread 1 advanced TO log SEQUENCE 75545
CURRENT log# 1 seq# 75545 mem# 0: +DATA/orcl/onlinelog/group_1.261.611597975
.
.
.
Sat Apr 7 03:00:22 2012
Thread 1 advanced TO log SEQUENCE 75561
CURRENT log# 1 seq# 75561 mem# 0: +DATA/orcl/onlinelog/group_1.261.611597975
Sat Apr 7 03:03:18 2012
WARNING: ran OUT OF shared pool FOR GES resource object.
Sat Apr 7 03:03:49 2012
WARNING: ran OUT OF shared pool FOR GES resource object.
Sat Apr 7 03:04:20 2012
WARNING: ran OUT OF shared pool FOR GES resource object.
Sat Apr 7 03:04:51 2012
WARNING: ran OUT OF shared pool FOR GES resource object.
Sat Apr 7 03:05:22 2012
WARNING: ran OUT OF shared pool FOR GES resource object.
Sat Apr 7 03:05:54 2012
WARNING: ran OUT OF shared pool FOR GES resource object.
Sat Apr 7 03:06:24 2012
WARNING: ran OUT OF shared pool FOR GES resource object.
Sat Apr 7 03:07:42 2012
WARNING: ran OUT OF shared pool FOR GES resource object.
Sat Apr 7 03:07:44 2012
Trace dumping IS performing id=[cdmp_20120407030744]
Sat Apr 7 03:07:45 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl1_lmd0_15494.trc:
ORA-00600: internal error code, arguments: [kjrpar:noalloc], [1], [385], [0x60000000000C09B8], [0xC0000002137539D8], [0xC000000213752748], [], []
Sat Apr 7 03:07:46 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl1_lmd0_15494.trc:
ORA-00600: internal error code, arguments: [kjrpar:noalloc], [1], [385], [0x60000000000C09B8], [0xC0000002137539D8], [0xC000000213752748], [], []
Sat Apr 7 03:07:54 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl1_lmd0_15494.trc:
ORA-00600: internal error code, arguments: [kjrpar:noalloc], [1], [385], [0x60000000000C09B8], [0xC0000002137539D8], [0xC000000213752748], [], []
Sat Apr 7 03:07:54 2012
LMD0: terminating instance due TO error 482
Sat Apr 7 03:07:54 2012
Shutting down instance (abort)
License high water mark = 56
Sat Apr 7 03:07:58 2012
Instance TERMINATED BY LMD0, pid = 15494
Sat Apr 7 03:07:59 2012
Instance TERMINATED BY USER, pid = 8118
Sat Apr 7 03:08:05 2012
Starting ORACLE instance (normal)
Sat Apr 7 03:08:05 2012
Specified VALUE OF sga_max_size IS too small, bumping TO 6627000320
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Interface TYPE 1 lan901 192.168.0.0 configured FROM OCR FOR USE AS a cluster interconnect
Interface TYPE 1 lan900 198.5.1.0 configured FROM OCR FOR USE AS a public interface
Picked latch-free SCN scheme 3
Autotune OF undo retention IS turned off. 
LICENSE_MAX_USERS = 0
SYS auditing IS disabled
My pid IS 8415, priority IS -179, running WITH SCHED_NOAGE policy 8. 
ksdpec: called FOR event 13740 prior TO event GROUP initialization
Starting up ORACLE RDBMS Version: 10.2.0.3.0.
System parameters WITH non-DEFAULT VALUES:
processes = 300
sessions = 335
timed_statistics = FALSE
sga_max_size = 6627000320
lock_sga = FALSE
shared_pool_size = 4294967296
large_pool_size = 67108864
java_pool_size = 33554432
streams_pool_size = 16777216
shared_pool_reserved_size= 263402290
spfile = +DATA/orcl/spfileorcl.ora
sga_target = 0
control_files = +DATA/orcl/controlfile/current1.ora, +ORCL_DATA/orcl/controlfile/current2.ora
db_block_size = 8192
db_cache_size = 2147483648
db_writer_processes = 2
compatible = 10.2.0.2.0
log_archive_dest_1 = LOCATION=/u01/app/oradata/orcl_1_arch1/arch2tape
log_archive_dest_2 = LOCATION=/u01/app/oradata/orcl_1_arch2/arch2tape
log_archive_format = ORCL_%t_%s_%r.ARC
log_buffer = 52428800
log_checkpoint_timeout = 1800
db_file_multiblock_read_count= 8
cluster_database = TRUE
cluster_database_instances= 2
db_create_file_dest = +ORCL_DATA
db_create_online_log_dest_1= +DATA
thread = 1
fast_start_mttr_target = 600
instance_number = 1
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain = 
dispatchers = (PROTOCOL=TCP)(DISPATCHERS=1)
max_shared_servers = 32
max_dispatchers = 2
local_listener = 
remote_listener = LISTENERS_ORCL
job_queue_processes = 10
background_dump_dest = /u01/app/oracle/admin/ORCL/bdump
user_dump_dest = /u01/app/oracle/admin/ORCL/udump
max_dump_file_size = 52428800
core_dump_dest = /u01/app/oracle/admin/ORCL/cdump
audit_file_dest = /u01/app/oracle/admin/ORCL/adump
hpux_sched_noage = 178
open_links = 30
db_name = ORCL
open_cursors = 300
star_transformation_enabled= TRUE
pga_aggregate_target = 1073741824
Cluster communication IS configured TO USE the following interface(s) FOR this instance
192.168.0.111
Sat Apr 7 03:08:10 2012
cluster interconnect IPC version:Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
PMON started WITH pid=2, OS id=8596
DIAG started WITH pid=4, OS id=8606
PSP0 started WITH pid=6, OS id=8608
LMON started WITH pid=8, OS id=8614
LMD0 started WITH pid=10, OS id=8616
LMS0 started WITH pid=12, OS id=8618
LMS1 started WITH pid=3, OS id=8620
MMAN started WITH pid=14, OS id=8623
DBW0 started WITH pid=16, OS id=8625
DBW1 started WITH pid=5, OS id=8644
LGWR started WITH pid=7, OS id=8657
CKPT started WITH pid=18, OS id=8659
SMON started WITH pid=20, OS id=8661
RECO started WITH pid=22, OS id=8663
CJQ0 started WITH pid=24, OS id=8665
MMON started WITH pid=26, OS id=8667
Sat Apr 7 03:08:12 2012
starting up 1 dispatcher(s) FOR network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started WITH pid=28, OS id=8669

从节点1上的告警日志基本上就可以判断出问题的原因了。显然在问题发生的前一天夜里，执行了大量的分区维护操作，致使大量的分区索引失效。这个分区维护的操作导致大量的分区源数据被加载到共享池中，随后不久，Oracle就出现了共享池不足的ORA-4031错误。在节点2出现ORA-569错误的同时，节点1出现了WARNING: ran out of shared pool for GES resource object信息，说明这时节点1上共享池不足导致了节点2上全局队列失败。
而后节点1由于资源不足导致了ORA-600[kjrpar:noalloc]错误的出现，并导致了实例的崩溃，关于这个错误的更详细描述可以参考：https://yangtingkun.net/?p=620。
这两个错误实际上还是由于RAC另外节点上共享池资源不足导致的，要避免这两个错误，还是要解决ORA-4031的错误。

Posted in BUG | Tagged , , , , | Leave a comment

ORA-600(ksires_1)错误

Posted on 2012/06/07 by yangtingkun

Oracle RAC 10.2.0.4 for HP-UX环境出现ORA-600[ksires_1]错误。
错误信息为：

Mon DEC 13 23:00:13 2010
Errors IN file /u01/app/oracle/admin/orcl/bdump/orcl2_m000_1861.trc:
ORA-00600: internal error code, arguments: [ksires_1], [], [], [], [], [], [], []
Mon DEC 13 23:00:15 2010
Errors IN file /u01/app/oracle/admin/orcl/bdump/orcl2_m000_1861.trc:
ORA-00600: internal error code, arguments: [ksires_1], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [ksires_1], [Tue DEC 14 09:55:39 2010
Restarting dead background process MMON
MMON started WITH pid=24, OS id=29770

这个错误在两个节点上都出现过，而且有时发生在MNNN进程上，有时发生在ORA进程上。查询MOS发现，导致问题的原因是/var/tmp目录下crs的sockets文件被清除：Many ORA-600: [ksires_1] In RAC [ID 1311870.1]。
这个问题可能发生在10.2.0.4以后的任何非Windows平台上。如果要避免这个错误，只需避免/var/tmp目录下的文件被删除既可。

Posted in BUG | Tagged , , , , , | Leave a comment

11g数据泵NETWORK_LINK功能增强

Posted on 2012/06/06 by yangtingkun

11g增强了数据泵的NETWORK_LINK功能，使得远端的导入或导出也支持分区粒度。
在10g的时候，数据泵的NETWORK_LINK只支持表粒度：

[orat3@hpserver2 ~]$ expdp test/test network_link=ora10204 dumpfile=tpart.dp logfile=test.log directory=dd TABLES=t_part:p1
Export: Release 10.2.0.4.0 - 64bit Production ON Monday, 4 June, 2012 0:38:31
Copyright (c) 2003, 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
ORA-39001: invalid argument VALUE
ORA-39203: Partition selection IS NOT supported OVER a network link.

很明显10g并不支持通过网络方式的分区选择功能。而且对于分区表而言，无论是导出还是导入，都是已一个整体完成的。
而在11g中，无论是导入还是导出，NETWORK_LINK方式都支持分区粒度：

solaris*orcl-/home/oracle$ expdp test/test network_link=t111g dumpfile=t_part_hash.dp directory=d_output nologfile=y TABLES=t_part_hash:sys_p61
Export: Release 11.2.0.3.0 - Production ON Mon Jun 4 08:24:54 2012
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 AND REAL Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** network_link=t111g dumpfile=t_part_hash.dp directory=d_output nologfile=y tables=t_part_hash:sys_p61 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."T_PART_HASH":"SYS_P61" 5.476 KB 13 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/home/oracle/t_part_hash.dp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 08:25:57

这个功能没有在新特性文档中体现，但是却是一个不错的功能提升。

Posted in ORACLE | Tagged , , , , , | Leave a comment

ORA-7445(kxfrSortGranuleInstance)错误

Posted on 2012/06/05 by yangtingkun

10.2.0.4 RAC for HP-UX上出现了这个错误。

错误信息为：

Sat DEC 31 14:48:42 2011
Errors IN file /u01/app/oracle/admin/ORCL/udump/orcl1_ora_26183.trc:
ORA-07445: exception encountered: core dump [kxfrSortGranuleInstance()+1248] [SIGBUS] [Invalid address alignment] [0x9FFFFFFFBDF45674] [] []
Sat DEC 31 14:49:26 2011
Thread 1 advanced TO log SEQUENCE 68840
CURRENT log# 5 seq# 68840 mem# 0: +DATA/orcl/onlinelog/group_5.259.611597989
Sat DEC 31 14:50:58 2011
Trace dumping IS performing id=[cdmp_20111231145058]
Sat DEC 31 14:52:18 2011
SMON: Parallel TRANSACTION recovery tried
Sat DEC 31 14:59:28 2011
Thread 1 advanced TO log SEQUENCE 68841
CURRENT log# 1 seq# 68841 mem# 0: +DATA/orcl/onlinelog/group_1.261.611597975
Sat DEC 31 15:00:32 2011
Errors IN file /u01/app/oracle/admin/ORCL/udump/orcl1_ora_17986.trc:
ORA-07445: exception encountered: core dump [kxfrSortGranuleInstance()+1248] [SIGBUS] [Invalid address alignment] [0x9FFFFFFFBD051674] [] []
Sat DEC 31 15:00:59 2011
Trace dumping IS performing id=[cdmp_20111231150059]
Sat DEC 31 15:01:32 2011
SMON: Parallel TRANSACTION recovery tried

这个错误出现了两次，每次随后都会出现SMON进程的并行事务恢复，说明这个问题应该是DML语句导致的，由于缺少TRACE文件，无法进一步分析。
而根据MOS检查相关的信息，发现了一个类似的问题描述：Bug 6461550 : ORA-7445 [KXFRSORTGRANULEINSTANCE] ERROR OCCURRES。这个bug同样是在HP-UX上出现，而且版本也与当前的版本十分接近为10.2.0.3。从这个错误描述看，问题多半与并行DML以及并行查询有关。虽然Oracle认为这个bug的状态是不可重现，但是从Oracle对于问题的分类看，Oracle将其归为存储空间的管理。
Oracle没有明确这个bug，因此也不会有影响版本和修正计划。不过从已有的分析看，这个问题出现在10.2.0.4及之前版本，而避免并行DML可能有助于避免这个错误的产生。

Posted in BUG | Tagged , , , | Leave a comment

ORA-600(qsmqSetupTableMetadata-2)错误

Posted on 2012/06/04 by yangtingkun

客户10.2.0.4 RAC for HP-UX 11.31在物化视图刷新时出现这个错误。
错误信息如下：

Thu May 3 22:54:44 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl1_j000_21657.trc:
ORA-00600: internal error code, arguments: [qsmqSetupTableMetadata-2], [], [], [], [], [], [], []
Thu May 3 22:55:20 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl1_j000_21657.trc:
ORA-12012: error ON auto EXECUTE OF job 718
ORA-12008: error IN materialized VIEW refresh path
ORA-04045: errors during recompilation/revalidation OF ORCL.MV_REP
ORA-00600: internal error code, arguments: [qsmqSetupTableMetadata-2], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Thu May 3 22:55:20 2012
Trace dumping IS performing id=[cdmp_20120503225520]
Thu May 3 22:57:28 2012
Trace dumping IS performing id=[cdmp_20120503225728]

显然第一次出现这个问题是发生在物化视图的刷新过程中，而第二次是同样是物化视图的刷新，不过由于Oracle会尝试编译出现错误的物化视图，从而产生了更多的错误信息。
根据版本和错误信息很容易确定问题属于BUG：Bug 5764671 OERI:qsmqSetupTableMetadata-2 an ALTER MATERIALIZED VIEW。一个使用了PCT属性的物化视图，查询语句中包含了ORDER BY就会导致这个错误。
这个问题影响10.2.0.3和10.2.0.4，Oracle在10.2.0.5和11.1.0.6中进行了fixed，如果不像打补丁，最简单的方式就是去掉物化视图中的ORDER BY，而后对物化视图的查询时再指定ORDER BY语句。

Posted in BUG | Tagged , , , , , | Leave a comment

将TIMESTAMP类型的差值转化为秒的方法

Posted on 2012/06/03 by yangtingkun

两个TIMESTAMP之差得到的是INTERVAL类型，而有时我们只需要得到两个时间相差的秒数，如果变成INTERVAL之后，想要获取这个值会非常麻烦。
比较常见的方法是使用EXTRACT来抽取获得的INTERVAL类型的日、时、分和秒来分别计算并求和：

SQL> CREATE TABLE t_timestamp (id NUMBER, t1 TIMESTAMP, t2 TIMESTAMP);
TABLE created.
SQL> INSERT INTO t_timestamp 
2 VALUES (1, to_timestamp('20120603222324', 'yyyymmddhh24miss'), to_timestamp('20120526152354', 'yyyymmddhh24miss'));
1 ROW created.
SQL> commit;
Commit complete.
SQL> SELECT t1 - t2 FROM t_timestamp WHERE id = 1;
T1-T2
---------------------------------------------------------------------------
+000000008 06:59:30.000000
SQL> WITH t AS (SELECT t1 - t2 INTERVAL FROM t_timestamp WHERE id = 1)
2 SELECT EXTRACT(DAY FROM INTERVAL) * 86400
3 + EXTRACT(HOUR FROM INTERVAL) * 3600
4 + EXTRACT(MINUTE FROM INTERVAL) * 60
5 + EXTRACT(SECOND FROM INTERVAL) INTERVAL
6 FROM t; 
INTERVAL
----------
716370

对于不需要考虑毫秒的情况而言，这种计算过于麻烦了，而对于DATE类型而言，计算差值非常方便，直接就可以返回两个日期相差的天数，在乘以86400就可以得到结果。
可惜的是，无论是ROUND还是TRUNC参数，都不支持TIMESTAMP类型：

SQL> SELECT trunc(t1, 'ss') FROM t_timestamp WHERE id = 1;
SELECT trunc(t1, 'ss') FROM t_timestamp WHERE id = 1
*
ERROR at line 1:
ORA-01899: bad PRECISION specifier
 
SQL> SELECT round(t1, 'ss') FROM t_timestamp WHERE id = 1;
SELECT round(t1, 'ss') FROM t_timestamp WHERE id = 1
*
ERROR at line 1:
ORA-01899: bad PRECISION specifier

其实对于这个问题，最简单的方法是利用隐式转换，变成DATE类型的操作：

SQL> SELECT (t1 - 0 - (t2 - 0)) * 86400 FROM t_timestamp;
(T1-0-(T2-0))*86400
-------------------
716370

当然最标准的方法还是显示的转换：

SQL> SELECT (CAST(t1 AS DATE) - CAST(t2 AS DATE)) * 86400 FROM t_timestamp;
(CAST(T1ASDATE)-CAST(T2ASDATE))*86400
-------------------------------------
716370

显然这种方便比利用EXTRACT要简单得多。

Posted in ORACLE | Tagged , , , , | Leave a comment