ORA-27103 when Memory target parameter is set to more than 3 GB(11.1.0.7)

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-27103 when Memory target parameter is set to more than 3 GB(11.1.0.7)

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

朋友在数据库软件从11.1.0.6升级到11.1.0.7后，发现数据库无法打开，不能继续下一步升级
数据库启动

SQL> startup upgrade
ORA-03113: end-of-file on communication channel

alert日志

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.1.0/db_1/dbs/spfilecenterdb.ora
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  memory_target            = 12864M
  control_files            = "/u01/app/oracle/oradata/centerdb/control01.ctl"
  control_files            = "/u01/app/oracle/oradata/centerdb/control02.ctl"
  control_files            = "/u01/app/oracle/oradata/centerdb/control03.ctl"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  db_recovery_file_dest    = "/u01/app/oracle/flash_recovery_area"
  db_recovery_file_dest_size= 2G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=centerdbXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/centerdb/adump"
  audit_trail              = "DB"
  db_name                  = "centerdb"
  open_cursors             = 300
  diagnostic_dest          = "/u01/app/oracle"
Thu Mar 29 15:47:06 2012
PMON started with pid=2, OS id=16324
Thu Mar 29 15:47:06 2012
VKTM started with pid=3, OS id=16326 at elevated priority
VKTM running at (20)ms precision
Thu Mar 29 15:47:06 2012
DIAG started with pid=4, OS id=16330
Thu Mar 29 15:47:06 2012
DBRM started with pid=5, OS id=16332
Thu Mar 29 15:47:06 2012
PSP0 started with pid=6, OS id=16334
Thu Mar 29 15:47:06 2012
DIA0 started with pid=7, OS id=16336
Thu Mar 29 15:47:06 2012
MMAN started with pid=8, OS id=16338
Thu Mar 29 15:47:06 2012
DBW0 started with pid=9, OS id=16340
Thu Mar 29 15:47:06 2012
DBW1 started with pid=10, OS id=16342
Thu Mar 29 15:47:06 2012
DBW2 started with pid=11, OS id=16344
Thu Mar 29 15:47:06 2012
DBW3 started with pid=12, OS id=16346
Thu Mar 29 15:47:06 2012
DBW4 started with pid=13, OS id=16348
Thu Mar 29 15:47:06 2012
DBW5 started with pid=14, OS id=16350
Thu Mar 29 15:47:06 2012
LGWR started with pid=15, OS id=16352
Thu Mar 29 15:47:06 2012
CKPT started with pid=16, OS id=16354
Thu Mar 29 15:47:06 2012
SMON started with pid=17, OS id=16356
Thu Mar 29 15:47:06 2012
RECO started with pid=18, OS id=16358
Thu Mar 29 15:47:06 2012
MMON started with pid=19, OS id=16360
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Mar 29 15:47:06 2012
MMNL started with pid=20, OS id=16362
starting up 1 shared server(s) ...
Errors in file /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc:
ORA-27103: internal error
Additional information: -1
Additional information: 1
MMAN (ospid: 16338): terminating the instance due to error 27103
Instance terminated by MMAN, pid = 16338

这里可以发现memory_target在12g以上

trace文件内容

[oracle@fcdb trace]$ more /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc
Trace file /u01/app/oracle/diag/rdbms/centerdb/centerdb/trace/centerdb_mman_16338.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name:    Linux
Node name:      fcdb
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: centerdb
Redo thread mounted by this instance: 0 <none>
Oracle process number: 8
Unix process pid: 16338, image: oracle@fcdb (MMAN)
*** 2012-03-29 15:47:06.865
*** SESSION ID:(1648.1) 2012-03-29 15:47:06.865
*** CLIENT ID:() 2012-03-29 15:47:06.865
*** SERVICE NAME:() 2012-03-29 15:47:06.865
*** MODULE NAME:() 2012-03-29 15:47:06.865
*** ACTION NAME:() 2012-03-29 15:47:06.865
error 27103 detected in background process
ORA-27103: internal error
Additional information: -1
Additional information: 1
*** 2012-03-29 15:47:06.865
MMAN (ospid: 16338): terminating the instance due to error 27103

结合alert和trace文件查询MOS，发现ORA-27103 when Memory target parameter is set to more than 3 GB [ID 743012.1]描述相符，是由于Bug:7272646引起.
鉴于朋友的数据库还升级过程中，所以给出的处理建议是先把memory_target改为2.8G，执行完升级操作，然后打上Patch:7272646
同时官方还给出了另一种解决方案:设置SHMMAX小于4G，个人不推荐；如果系统内存比较大，会出现多个内存段，影响系统性能

模拟ORA-04043并解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：模拟ORA-04043并解决

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

创建两张模拟表

SQL> select * from v$version;
BANNER
------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> create table sys_xifenfei as
  2  select * from dba_tables;
Table created.
SQL> create table chf.chf_xifenfei as
  2  select * from dba_tables;
Table created.

启动数据库到mount状态查询表

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> desc dba_tables;
ERROR:
ORA-04043: object dba_tables does not exist
SQL> desc sys_xifenfei
ERROR:
ORA-04043: object sys_xifenfei does not exist
SQL> desc chf.chf_xifenfei
ERROR:
ORA-04043: object chf.chf_xifenfei does not exist

打开数据库查询

SQL> alter database open;
Database altered.
SQL> select count(*) from sys_xifenfei;
select count(*) from sys_xifenfei
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from chf.chf_xifenfei;
  COUNT(*)
----------
       868
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

解决问题

SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from dba_tables;
  COUNT(*)
----------
       869
SQL> select count(*) from sys_xifenfei;
  COUNT(*)
----------
       867

MOS解释
ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1]

Available workarounds are:
1) Don't describe the dba_* views at mount stage.
OR
2) If you issue DESC of any DBA_*views at mount stage,
    then shutdown and restart the DB instance.
OR
3) Flush the shared pool.
SQL> Alter system flush shared_pool;
and then reissue the failing command.

在10g中open库后提示也为类此ORA-04043: object dba_tables does not exist

ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

SCN说明
1、Oracle的SCN在每秒16384次commit的情况下可以维持534年，每秒16384次commit是Oracle早先认为的任何系统的极限commit强度；
2、Oracle里SCN的起点是1988年1月1日；
3、_minimum_giga_scn=n的含义是把SCN往前推进到nG，但请注意，只有在SCN小于nG的时候才会用到这个隐含参数，反之则Oracle会置这个隐含参数于不顾。

求模拟_minimum_giga_scn值
这里通过时间差，大概的模拟_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(300>290)

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2012-03-18 04:27:50
SQL> select months_between(sysdate,to_date('19880101','yyyymmdd')) from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE('19880101','YYYYMMDD'))
------------------------------------------------------
                                             290.55443
SQL> select 16384*60*60*24*31*300/(1024*1024*1024) SCN from dual;
       SCN
----------
12260.7422

启动数据库测试

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--------------------------------
*._minimum_giga_scn=12260
--------------------------------
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

分析ORA-01052

SQL> !oerr ora 1052
01052, 00000, "required destination LOG_ARCHIVE_DUPLEX_DEST is not specified"
// *Cause:  A valid destination for parameter LOG_ARCHIVE_DUPLEX_DEST was not
//          specified when parameter LOG_ARCHIVE_MIN_SUCCEED_DEST was set to
//          two.
// *Action: Either specify a value for parameter LOG_ARCHIVE_DUPLEX_DEST, or
//          reduce the value for parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to one.
SQL> show parameter LOG_ARCHIVE_DUPLEX_DEST;
NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
log_archive_duplex_dest              string
SQL> show parameter LOG_ARCHIVE_MIN_SUCCEED_DEST
NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
log_archive_min_succeed_dest         integer    1

这里可以看出，不是以为我上面的两个参数设置错误导致ORA-01052，而是因为推进scn过大导致ORA-01052的错误

减小_minimum_giga_scn测试
这里选择_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(280<290) [sql] SQL> select 16384*60*60*24*31*280/(1024*1024*1024) SCN from dual; SCN ---------- 11443.3594 SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. ----------------------------- *._minimum_giga_scn=11443 ----------------------------- SQL> startup pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 236000356 bytes Fixed Size 451684 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> select to_char(dbms_flashback.get_system_change_number(), 2 '9999999999999999') from dual; TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_ ---------------------------------- 12286827692251 SQL> select dbms_flashback.get_system_change_number()/(1024*1024*1024) from dual; DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()/(1024*1024*1024) ---------------------------------------------------------- 11443 [/sql] 通过试验可以发现，在我们的数据库遇到异常，需要恢复通过推进scn来恢复的时候，不是推进的越大越好；如果推进的太大可能导致ORA-01052错误，一般建议是比当前不一致的scn稍微大一点 参考:http://www.dbsnake.net/two-scn-internal-points.html

收集统计信息出现ORA-00600[ksxprqfre3]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：收集统计信息出现ORA-00600[ksxprqfre3]

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

alert日志出现ORA-00600[ksxprqfre3]错误

Sun Mar 18 22:11:44 2012
Errors in file /oracle9/app/admin/offon/bdump/offon2_p001_8220790.trc:
ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033D5A8], [], [], [], [], [], []
Sun Mar 18 22:11:44 2012
Errors in file /oracle9/app/admin/offon/bdump/offon2_p001_8220790.trc:
ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033E178], [], [], [], [], [], []

通过这里可以发现，是由于并发进程导致异常

trace文件内容

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_offon2
Release:        3
Version:        5
Machine:        00CA56A44C00
Instance name: offon2
Redo thread mounted by this instance: 2
Oracle process number: 328
Unix process pid: 8220790, image: oracle@zwq_offon2 (P001)
*** 2012-03-18 22:11:43.994
*** SESSION ID:(381.10338) 2012-03-18 22:11:43.989
krqh = 11033d5a8
rqh trace:
  Slot: 6    Op: 114  ksxpirqh: irqh requested
  Slot: 7    Op: 130  ksxpirqh: rqh SUCC
  Slot: 8    Op: 123  ksxpwait: receive mv to doneq
  Slot: 9    Op: 121  ksxprcv: rqh found on doneq
  Slot: 10   Op: 102  ksxprqfre: freed to queue
  Slot: 11   Op: 101  ksxprqalo: allocated
  Slot: 12   Op: 105  ksxpdteq: enqueue to delta q
  Slot: 13   Op: 117  ksxpvsnd: send via ksxpisnd
  Slot: 14   Op: 106  ksxpisnd: send requested
  Slot: 0    Op: 107  ksxpisnd: send submit OK
  Slot: 1    Op: 153  pre send completion cbk
  Slot: 2    Op: 102  ksxprqfre: freed to queue
  Slot: 3    Op: 114  ksxpirqh: irqh requested
  Slot: 4    Op: 130  ksxpirqh: rqh SUCC
  Slot: 5    Op: 153  pre send completion cbk
Dump of memory from 0x000000011033D5A8 to 0x000000011033D660
11033D5A0                   00000001 1033D5A8          [.....3..]
11033D5B0 00000001 1033D5A8 00000001 1033D5B8  [.....3.......3..]
11033D5C0 00000001 1033D5B8 00000006 00000001  [.....3..........]
11033D5D0 00000000 00000000 00000000 00000000  [................]
11033D5E0 00000572 00000000 07000000 EE473B28  [...r.........G;(]
11033D5F0 00010000 00000000 00000001 1029CEA8  [.............)..]
11033D600 00000001 104382A0 07000000 F279B5A8  [.....C.......y..]
11033D610 00000000 00000572 00000000 00000000  [.......r........]
11033D620 00000000 00000000 00000000 13DCD225  [...............%]
11033D630 13DA7A25 00010001 26750000 00000000  [..z%....&u......]
11033D640 00000001 10339C70 6B996672 82997282  [.....3.pk.fr..r.]
11033D650 7B796665 69756A06 00000001 1033E228  [{yfeiuj......3.(]
*** 2012-03-18 22:11:43.996
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033D5A8], [], [], [], [], [], []
Current SQL statement for this session:
SELECT /*+ PIV_SSF */ SYS_OP_MSR(SYS_OP_COUNTCHG(SUBSTRB(ROWIDTOCHAR(A1.C0),1,15),1),COUNT(DISTINCT A1.C3),COUNT(*)) C0 FROM (SELECT /*+ INDEX_RRS(A2 "PK_TINEXWORK_DATA
") */ A2.ROWID C0,A2."WORKSHTSN" C1,A2."EXWORKSN" C2,SYS_OP_LBID(8574,'L',A2.ROWID) C3 FROM "OFFON"."TINEXWORK_DATA"  PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A2) A1
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               102973B94 ?
ksfdmp+0018          bl       01FD34D8
kgerinv+00e8         bl       _ptrgl
kgeasnmierr+004c     bl       kgerinv              127C7F9D7226029B ?
                                                   9770A0AFAF4BDC0 ?
                                                   12E0BE826D694B2F ?
                                                   000000000 ? 000000010 ?
ksxprqfre+0054       bl       kgeasnmierr          110006728 ? 110338C88 ?
                                                   1030069D4 ? 100000001 ?
                                                   000000002 ? 11033D5A8 ?
                                                   080000000 ?
                                                   7F7F7F7F7F7F7F7F ?
ksxpwait+06bc        bl       ksxprqfre            7000000D6D793D8 ?
ksliwat+02e0         bl       ksxpwait             1014F4508 ? 1101FB1C8 ?
kslwaitns+001c       bl       ksliwat              FFFFFFFFFFFC4B0 ? 000000004 ?
                                                   FFFFFFFFFFFC590 ? 11030BC60 ?
                                                   FFFFFFFFFFFC530 ?
                                                   7000000DDF1FBE0 ? 110006868 ?
kskthbwt+01b4        bl       kslwaitns            000000002 ? 7000000EAEEB7A0 ?
                                                   000000000 ? 000000000 ?
                                                   000000868 ? 7000000DAFEFB30 ?
                                                   7000000F4979B10 ?
kslwait+005c         bl       kskthbwt             FFFFFFFFFFFC6E0 ?
                                                   2422424800000000 ?
                                                   1015EDD34 ? 110305194 ?
                                                   FFFF0000000003 ? 000000000 ?
                                                   000000001 ? FFFFFFFFFFFF920 ?
ksxprcv+0184         bl       kslwait              102970928 ? 000000006 ?
                                                   000000000 ? 000000000 ?
                                                   110305148 ? 110006728 ?
kxfpqidqr+0600       bl       ksxprcv              11029CEA8 ? 000000008 ?
                                                   FFFFFFFFFFFD020 ?
                                                   FFFFFFFFFFFCFC0 ?
                                                   162DFEF4180 ? 000000000 ?
                                                   000000000 ? 000000000 ?
kxfpqdqr+02c0        bl       kxfpqidqr            11029CF80 ? 11029CF20 ?
                                                   FFFFFFFFFFFD7C0 ?
                                                   7000000EAE2DA30 ?
kxfxgs+0050          bl       kxfpqdqr             101518AF0 ? 000002000 ?
                                                   FFFFFFFFFFFD9F0 ? 000000000 ?
kxfxmai+0100         bl       kxfxgs               102970928 ? 000000060 ?
                                                   7000000EEF059F0 ?
                                                   70000000006D670 ?
kxfprdp+05b0         bl       _ptrgl
opirip+0390          bl       kxfprdp
opidrv+0300          bl       opirip               000000018 ? 0101FB1C8 ?
                                                   000000000 ?
sou2o+0028           bl       opidrv               320C000000 ? 0A00E8B50 ?
                                                   000000000 ?
main+01a4            bl       01FD2EF0
__start+0098         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
    SO: 7000000cf267198, type: 4, owner: 7000000cd2a53e0, flag: INIT/-/-/0x00
    (session) trans: 0, creator: 7000000cd2a53e0, flag: (48000041) USR/- -/-/-/-/-/-
              DID: 0002-0148-003F8809, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 0, prv: 0, sql: 0, psql: 0, user: 25/MONITOR
    O/S info: user: maint1, term: , ospid: 8220790, machine: zwq_acc2
              program: oracle@zwq_offon2 (P001)
    application name: SQL*Plus, hash value=3669949024
    waiting for 'PX Deq: reap credit' blocking sess=0x0 seq=182 wait_time=0
                =0, =0, =0
    temporary object counter: 0

这里可以得出信息如下：
1)系统环境AIX 5.3 9.2.0.8.0 RAC
2)报错的语句是收集TINEXWORK_DATA表相关统计信息
3)是由于SQL*Plus收集统计信息使用并发导致该错误

查询MOS发现
Bug 5887047: ORA-00600 [KSXPRQFRE3] TRACE WHEN GATHERING OPTIMIZER STATISTICS描述相符：
1）数据库版本9.2.0.8
2）收集统计信息操作导致
3）STACK TRACE一致

处理建议
针对官方没有给出明确的解决方案，因为该问题出现概率比较低，如果经常出现需要考虑升级数据库版本

10.2.0.5出现ORA-00600[kcblasm_1]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：10.2.0.5出现ORA-00600[kcblasm_1]

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

今天朋友执行一条如下sql

SELECT YF,
       KHBH,
       MAX(KHXM),
       MAX(YYBDM),
       MAX(YYBMC),
       MIN(ZJZH),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(ZJYE, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(ZJYE, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(ZQSZ, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(ZQSZ, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(ZC, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(ZC, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(DWJZ, 1)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(DWJZ, 1)
             ELSE
              0
           END),
       MAX(NVL(ZC, 0)) ZGZC,
       SUM(NVL(ZRZC, 0)) ZRZC,
       SUM(NVL(ZCZC, 0)) ZCZC,
       SUM(NVL(FDYK, 0)) FDYK,
       SUM(NVL(ZRGF, 0)) ZRGF,
       SUM(NVL(ZCGF, 0)) ZCGF,
       AVG(NVL(ZC, 0)) PJZC,
       SUM(NVL(CJJE, 0)) CJJE,
       SUM(NVL(YJ, 0)) YJ,
       SUM(NVL(ZJFSS, 0)) ZJFSS,
       SUM(NVL(ZYJ, 0)) ZYJ,
       SUM(NVL(CJCS, 0)) CJCS
  FROM CRM_FX2_KHZJHZ P
 WHERE YF = 201203
 GROUP BY YF, KHBH;

出现ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], []错误

数据库版本

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

通过MOS发现该错误在10.2.0.5上有相关描述

This problem is hit only in 10.2.0.5, up to PSU 10.2.0.5.4 in which problem is fixed.
Problem is affecting 10.2.0.5 to 10.2.0.5.3.
Cause
This is is a known and common problem hit in 10.2.0.5, investigated and corrected in unpublished Bug 7612454.
The problem was introduced in 10.2.0.5, by the fix of Bug:7523755.
This has been corrected in PSU 10.2.0.5.4 and 11.2.

解决方法

To quickest way to the solution of this problem is to apply PSU 10.2.0.5.4, Patch 12419392 or later.
All alternative solutions for this problem are listed below:
•	Upgrade the database to 11.2.
OR
•	Apply 10.2.0.5.4 Patch Set Update (Patch 12419392) or later PSUs where bug is fixed.
The available PSUs are mentioned in "10.2.0.5 Patch Set Updates - List of Fixes in each PSU" (Document 1337394.1)
OR
•	Apply interim Patch 7612454 on top of 10.2.0.5 (10.2.0.5.0-10.2.0.5.3):
o	For UNIX / Linux platforms apply Patch 7612454 available for download on MOS.
o	For Windows platforms apply Patch 3 or higher.
Please check Document 342443.1 for latest patches available for Windows on top of 10.2.0.5.

这个错误在10.2.0.5上很容易发生，官方也没有给出发生这个错误的原因，以及如何改写相关sql，给出的方案是升级到10.2.0.5.4或者更高。这里可以看出升级需要慎重，可能导致原库有小部分sql不能正常执行。

ORA-7445[__milli_memcpy]分析

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-7445[__milli_memcpy]分析

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

帮朋友分析日志的过程中发现在两个节点上多次出现类此错误
节点1日志分析

Mon Mar  5 08:19:20 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc:
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Mon Mar  5 08:19:21 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc:
ORA-07445: exception encountered: core dump [kghalf()+961] [SIGSEGV] [Invalid permissions for mapped object] [0x000000000] [] []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Mon Mar  5 08:19:22 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc:
ORA-07445: exception encountered: core dump [kghalf()+464] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFF0] [] []
ORA-07445: exception encountered: core dump [kghalf()+961] [SIGSEGV] [Invalid permissions for mapped object] [0x000000000] [] []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []

对应trace文件

/u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name:	HP-UX
Node name:	HDDB1
Release:	B.11.23
Version:	U
Machine:	ia64
Instance name: hddms1
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 18556, image: oracle@HDDB1 (TNS V1-V3)
*** 2012-03-05 08:19:20.675
*** SESSION ID:(100.40701) 2012-03-05 08:19:20.602
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x9fffffffbf580000, PC: [0x400000000182a9f0, __milli_memcpy()+2448]
  r1: 6000000000524900       r20:                8       br5:                0
  r2:                0       r21: 9fffffffbf4c21ec       br6: 40000000018ad000
  r3:               37       r22:                8       br7: e000000120001400
  r4:                0       r23: c00000005e3bac98        ip: 400000000182a9f0
  r5: 60000000005311d8       r24: 9fffffffbf4c2268      iipa:                0
  r6:         40000004       r25: 9fffffffbf4c2268       cfm:       2a54295428
  r7: 9fffffffffff1ba8       r26: 9fffffffbf580000        um:               1a
  r8: 9fffffffbf4c2268       r27: 9fffffffbf57ff00       rsc:               1f
  r9:             8000       r28: 9fffffffbf580008       bsp: 9fffffffbf802150
 r10: 9fffffffbf4ba260       r29: 9fffffffbf57ff08  bspstore: 9fffffffbf802150
 r11:                0       r30:                0      rnat:                0
 r12: 9ffffffffffe5240       r31:                0       ccv:         20000000
 r13: 9fffffffbf5a1420      NaTs:                0      unat:                0
 r14: 9fffffffbf4c23e8       PRs: ffffffffffff4837      fpsr:    9804c8274433f
 r15: 9fffffffbf580028       br0: 400000000202a7e0       pfs: c000000000001b3e
 r16:  3fffffffffffdff       br1:                0        lc:  3ffffffffffce89
 r17: 9fffffffbf4c2258       br2:                0        ec:                5
 r18:                0       br3:                0       isr: 9fffffffbf802150
 r19:               80       br4:                0       ifa:                0
Reason code: 0008
*** 2012-03-05 08:19:20.735
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Current SQL statement for this session:
update pmis.PM_BZ_DC_DWRFHSJ set ZGFDFH=:v_result where PARINST_ID=:v_pid and T_ID>:v_mdtbegin and T_ID<=:v_mdtend
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c00000008975ebc0        49  procedure PMIS.PM_DC_SJJGGC_EXECINF
c0000000971ef530         2  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+528         call     ksedst()             000000001 ?
                                                   C050000000000996 ?
                                                   4000000002A53E60 ?
ssexhd()+1184        call     ksedmp()             000000003 ?
                                                   C0500000000010A9 ?
                                                   40000000025E1420 ?
                                                   FFFFFFFFFFFF9075 ?
                                                   9FFFFFFFFFFDF2E0 ?
                                                   6000000000514428 ?
                                                   6000000000514420 ?
                                                   60000000005178E0 ?
<kernel>             call     ssexhd()             400000000086EFB0 ?
                                                   000000015 ? 000000007 ?
                                                   C050000000000085 ?
__milli_memcpy()+24  call     <kernel>
48
$cold_updgrh()+1088  call     __milli_memcpy()     000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   9FFFFFFFBF564348 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
upduaw()+224         call     $cold_updgrh()       C0000000A2665550 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   C000000000000C1E ?
                                                   4000000001916600 ?
                                                   000000000 ? 00000E137 ?
kdusru()+720         call     upduaw()             C0000000A2665550 ?
                                                   C000000000005BBF ?
                                                   4000000001912AC0 ?
                                                   00000E135 ?
                                                   9FFFFFFFFFFE52B0 ?
                                                   C0000000A2665660 ?
                                                   9FFFFFFFFFFE63F0 ?
                                                   9FFFFFFFBF5590D8 ?
kauupd()+544         call     kdusru()             9FFFFFFFBF4C5D24 ?
                                                   000000000 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   000000000 ?
                                                   C00000008B5D1108 ?
                                                   C00000008B5D1138 ?
                                                   000000018 ?
                                                   9FFFFFFFBF5591E0 ?
updrow()+3024        call     kauupd()             9FFFFFFFFFFE65D0 ?
                                                   000000000 ?
                                                   6000000000530010 ?
                                                   000000000 ?
                                                   C00000009202CF38 ?
                                                   00000010B ? 00001AA7A ?
                                                   C000000086F2B778 ?
qerupFetch()+608     call     updrow()             9FFFFFFFBF4C89F0 ?
                                                   C0000000A2665550 ?
                                                   C000000000000C1D ?
                                                   40000000018F5BF0 ?
                                                   000000000 ?
updaul()+1008        call     qerupFetch()         C0000000A0DB24D0 ?
                                                   000000000 ?
                                                   C0000000A2665550 ?
                                                   000007FFF ?
updThreePhaseExe()+  call     updaul()             C000000091A57FA0 ?
432                                                000000006 ? 000000000 ?
                                                   C0000000000019BB ?
                                                   40000000019DF9B0 ?
                                                   000008E23 ?
                                                   60000000001D30F0 ?
                                                   0000000BD ?
updexe()+624         call     updThreePhaseExe()   C000000091A57FA0 ?
                                                   000000000 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   9FFFFFFFFFFF1AF0 ?
                                                   C000000000000FA6 ?
                                                   40000000019DFCA0 ?
                                                   000000000 ?
opiexe()+6944        call     updexe()             C000000091A57FA0 ?
                                                   9FFFFFFFBF4C8A00 ?
                                                   C000000000002858 ?
                                                   4000000001857620 ?
                                                   000008E25 ?
                                                   9FFFFFFFFFFF1AD0 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   9FFFFFFFBF4C88D4 ?
opipls()+3488        call     opiexe()             000000004 ?
                                                   9FFFFFFFFFFF2F50 ?
                                                   9FFFFFFFFFFF1D30 ?
                                                   C000000000002C60 ?
                                                   40000000019852D0 ?
                                                   000000000 ? 000007123 ?
                                                   9FFFFFFFFFFF1B40 ?
opiodr()+3088        call     opipls()             9FFFFFFFFFFF2F50 ?
                                                   9FFFFFFFFFFF2F58 ?
                                                   000000004 ?
                                                   C00000000000214A ?
                                                   40000000018747B0 ?
                                                   00000E123 ?
                                                   9FFFFFFFFFFF2E70 ?
                                                   9FFFFFFFFFFF2DE0 ?
rpidrus()+304        call     opiodr()             9FFFFFFFFFFF3810 ?
                                                   9FFFFFFFFFFF37E0 ?
                                                   9FFFFFFFFFFF36F8 ?
                                                   9FFFFFFFFFFF36F4 ?
skgmstack()+288      call     rpidrus()            9FFFFFFFFFFF4DA0 ?
                                                   C000000000000716 ?
                                                   4000000001862FE0 ?
                                                   9FFFFFFFFFFF47F0 ?
                                                   600000000004F3C0 ?
                                                   9FFFFFFFFFFF4DB8 ?
                                                   6000000000524900 ?
                                                   9FFFFFFFFFFF4DA0 ?
rpidru()+256         call     skgmstack()          9FFFFFFFFFFF4DC0 ?
                                                   600000000004EDF0 ?
                                                   00000F618 ?
                                                   400000000129E400 ?
                                                   9FFFFFFFFFFF4DA0 ?
rpiswu2()+784        call     rpidru()             00000057B ?
                                                   C000000000001329 ?
                                                   4000000001870CE0 ?
rpidrv()+2096        call     rpiswu2()            C00000007D7B3708 ?
                                                   9FFFFFFFFFFF5510 ?
                                                   6000000000515CE8 ?
                                                   60000000000502E8 ?
                                                   9FFFFFFFFFFF5520 ?
                                                   000000205 ?
                                                   6000000000530010 ?
                                                   6000000000531090 ?
psddr0()+256         call     rpidrv()             000000000 ? 000000066 ?
                                                   9FFFFFFFFFFF6630 ?
                                                   00000003A ?
psdnal()+672         call     psddr0()             000000000 ? 000000066 ?
                                                   9FFFFFFFFFFF6630 ?
                                                   000000030 ?
pevm_EXIM()+400      call     psdnal()             9FFFFFFFFFFF84A0 ?
                                                   9FFFFFFFFFFF8440 ?
                                                   6000000000531A04 ?
                                                   600000000064BAA0 ?
                                                   C000000090880CC8 ?
                                                   600000000063EA80 ?
pfrrun()+37120       call     pevm_EXIM()          9FFFFFFFBF567AD8 ?
                                                   C000000090880CC8 ?
                                                   C000000000001532 ?
                                                   400000000173DAD0 ?
                                                   000006269 ?
                                                   4000000002190B40 ?
                                                   9FFFFFFFBF4BA068 ?
                                                   9FFFFFFFBF5C08A0 ?
peicnt()+544         call     pfrrun()             9FFFFFFFBF567CDC ?
                                                   C000000000000D1D ?
                                                   4000000001676FE0 ?
kkxexe()+832         call     peicnt()             9FFFFFFFFFFF84A0 ?
                                                   9FFFFFFFBF567AD8 ?
                                                   C000000000000818 ?
                                                   40000000021A3A80 ?
                                                   00000FEAB ?
                                                   9FFFFFFFFFFF7EB0 ?
                                                   9FFFFFFFBF567CDC ?
                                                   000000000 ?
opiexe()+12592       call     kkxexe()             9FFFFFFFFFFF8430 ?
                                                   C000000000002858 ?
                                                   4000000001858C30 ?
                                                   0000080AB ?
                                                   9FFFFFFFFFFF8420 ?
                                                   9FFFFFFFBF4F3A6C ?
                                                   9FFFFFFFBF4F3A68 ?
                                                   9FFFFFFFFFFF84A0 ?
opiall0()+3456       call     opiexe()             000000004 ?
                                                   9FFFFFFFFFFF9A80 ?
                                                   9FFFFFFFFFFF8830 ?
                                                   C0000000000024D1 ?
                                                   40000000017A3350 ?
                                                   000000000 ? 00000C82B ?
                                                   9FFFFFFFFFFF8640 ?
kpoal8()+2272        call     opiall0()            000000002 ?
                                                   9FFFFFFFFFFF9A10 ?
                                                   9FFFFFFFFFFFA2B4 ?
                                                   000000000 ?
                                                   9FFFFFFFFFFF9954 ?
                                                   9FFFFFFFFFFFA2BC ?
                                                   000000000 ?
                                                   FFFFFFFFFFFFDFFF ?
opiodr()+3088        call     kpoal8()             9FFFFFFFFFFFA0B0 ?
                                                   000000000 ? 000000000 ?
                                                   C00000000000214A ?
                                                   40000000018747B0 ?
                                                   00000F82F ?
                                                   9FFFFFFFFFFFA130 ?
                                                   9FFFFFFFFFFFA1D0 ?
ttcpip()+1888        call     opiodr()             9FFFFFFFFFFFA460 ?
                                                   9FFFFFFFFFFFA430 ?
                                                   9FFFFFFFFFFFA348 ?
                                                   9FFFFFFFFFFFA344 ?
                                                   C0000000000018B9 ?
                                                   400000000172CB70 ?
                                                   000000000 ? 00000C0AB ?
opitsk()+1920        call     ttcpip()             6000000000052C40 ?
                                                   000000002 ?
                                                   9FFFFFFFFFFFB950 ?
                                                   6000000000273AA0 ?
                                                   9FFFFFFFFFFFBAB0 ?
                                                   9FFFFFFFFFFFB8C4 ?
                                                   9FFFFFFFBF75A660 ?
                                                   4000000000D2C7AA ?
opiino()+2656        call     opitsk()             000000000 ? 000000000 ?
                                                   C000000000000D1F ?
                                                   400000000236CE90 ?
                                                   000000000 ?
opiodr()+3088        call     opiino()             60000000005DEC18 ?
                                                   6000000000531170 ?
                                                   9FFFFFFFFFFFF4C0 ?
                                                   C00000000000214A ?
                                                   40000000018747B0 ?
                                                   00000E825 ?
                                                   9FFFFFFFFFFFD7C0 ?
                                                   9FFFFFFFFFFFF4D0 ?
opidrv()+1088        call     opiodr()             9FFFFFFFFFFFDF10 ?
                                                   9FFFFFFFFFFFDEE0 ?
                                                   9FFFFFFFFFFFDDF8 ?
                                                   9FFFFFFFFFFFDDF4 ?
sou2o()+48           call     opidrv()             9FFFFFFFFFFFEF60 ?
                                                   000000004 ?
                                                   9FFFFFFFFFFFF4C0 ?
main()+352           call     sou2o()              9FFFFFFFFFFFF4E0 ?
                                                   00000003C ? 000000004 ?
                                                   9FFFFFFFFFFFF4C0 ?
main_opd_entry()+80  call     main()               000000000 ?
                                                   9FFFFFFFFFFFF9B0 ?
                                                   C000000000000004 ?
                                                   C00000000002FA60 ?
--------------------- Binary Stack Dump ---------------------

观察节点2，发现类此错误不同之处有

Wed Mar  7 08:19:11 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Wed Mar  7 08:19:12 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0x9FFFFFFFBF55A550], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Wed Mar  7 08:19:13 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-07445: exception encountered: core dump [kgidmp()+1568] [SIGSEGV] [Address not mapped to object] [0xB38F00000000D9] [] []
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0x9FFFFFFFBF55A550], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Wed Mar  7 08:19:14 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-07445: exception encountered: core dump [kghalf()+993] [SIGSEGV] [Address not mapped to object] [0xC0000000A8B01DA0] [] []
ORA-07445: exception encountered: core dump [kgidmp()+1568] [SIGSEGV] [Address not mapped to object] [0xB38F00000000D9] [] []
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0x9FFFFFFFBF55A550], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []

对应trace文件不同之处

*** 2012-03-07 08:19:11.582
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
No current SQL statement being executed.
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c000000090c4a818      1356  procedure PMIS.PM_DC_SJJGGC_QDX
c0000000a25512c0        43  procedure PMIS.PM_DC_SJJGGC_EXECINF
c00000009a8306e8         2  anonymous block

通过这两个节点的错误记录，都包含PMIS.PM_DC_SJJGGC_EXECINF和anonymous，所以初步怀疑是某个程序在这个时间定运行某个任务(包含PMIS.PM_DC_SJJGGC_EXECINF过程)导致。
继续观察trace日志发现两个节点都是类此情况相同的程序，相同的主机

  SO: c00000007d7b3708, type: 4, owner: c00000007d6d0190, flag: INIT/-/-/0x00
    (session) trans: c000000082974470, creator: c00000007d6d0190, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-002A-0000693D, short-term DID: 0000-0000-00000000
              txn branch: 0000000000000000
              oct: 6, prv: 0, sql: c0000000a229ab38, psql: c000000084e6b510, user: 517/PMIS
    O/S info: user: Administrator, term: DMIS_SWITCH2, ospid: 5600:3020, machine: WORKGROUP\DMIS_SWITCH2
              program: PMTDCtrl.exe
    application name: PMTDCtrl.exe, hash value=0
    last wait for 'enqueue' blocking sess=0x0 seq=32849 wait_time=127
                name|mode=54540004, id1=1, id2=10
    temporary object counter: 0

现在更加怀疑是PMTDCtrl.exe运行某过程导致数据库出现这些错误

错误总结
查看MOS发现该问题是一个bug导致(Bug 6166690:ORA-7445 [MILLI_MEMCPY] WHEN REF CURSOR AS OUT PARAMETER)
错误原因:It is caused by a Dynamic SQL used to obtain a REF CURSOR from a procedure (that opens the cursor).
处理建议:通过个bug，我们可以分析PMIS.PM_DC_SJJGGC_EXECINF过程，看看是否符合bug描述,如果符合建议改写过程或者打上补丁(Patch 2709343)
补丁:p2709343_92080_HPUX-IA64

记录一次ORA-600[13013]处理过程

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：记录一次ORA-600[13013]处理过程

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在一次数据库的异常处理完成后，发现alert日志中出现ORA-600[13013]错误

Thu Mar 08 23:29:37 2012
Errors in file /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_24137.trc  (incident=38681):
ORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/chf/chf/incident/incdir_38681/chf_smon_24137_i38681.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 1 out of maximum 100 non-fatal internal errors.

trace文件中信息
从这里可以看出是对sys.col_usage$表进行update操作导致该错误发生

Dump continued from file: /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_24137.trc
ORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], []
========= Dump for incident 38681 (ORA 600 [13013]) ========
*** 2012-03-08 23:29:37.400
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=3c1kubcdjnppq) -----
update sys.col_usage$ set   equality_preds    = equality_preds    + decode(bitand(:flag,1),0,0,1),   equijoin_preds    = equijoin_preds    + decode(bitand(:flag,2),0,0
,1),   nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1),   range_preds       = range_preds       + decode(bitand(:flag,8),0,0,1),   like_preds
    = like_preds        + decode(bitand(:flag,16),0,0,1),   null_preds        = null_preds        + decode(bitand(:flag,32),0,0,1),  timestamp = :time where obj# = :ob
jn and intcol# = :coln

MOS中关于ORA-600 [13013]描述

Format: ORA-600 [13013] [a] [b] {c} [d] [e] [f]
Arg [a] Passcount
Arg [b] Data Object number
Arg {c} Tablespace Decimal Relative DBA (RDBA) of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Decimal RDBA of block being updated (Typically same as {c})
Arg [f] Code

验证MOS中描述

SQL> select dbms_utility.data_block_address_file(4198427) rfile,
  2  dbms_utility.data_block_address_block(4198427) blocks
  3  from dual;
     RFILE     BLOCKS
---------- ----------
         1       4123
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 1
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 1
Enter value for block_id: 4123
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 4123 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE PARTI
----- ------------ ---------- ---------- -----
SYS   COL_USAGE$   TABLE      SYSTEM
--和trace文件中异常表一致
SQL>  select object_type,object_name from dba_objects where object_id=518;
OBJECT_TYPE         OBJECT_NAME
------------------- ------------------------------
TABLE               COL_USAGE$
--也和trace文件中异常表一致

分析异常表

SQL> ANALYZE TABLE sys.COL_USAGE$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.COL_USAGE$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
SQL> select index_name,COLUMN_NAME,COLUMN_POSITION FROM DBA_IND_COLUMNS
2    WHERE TABLE_NAME='COL_USAGE$';
INDEX_NAME      COLUMN_NAM COLUMN_POSITION
--------------- ---------- ---------------
I_COL_USAGE$    OBJ#                     1
I_COL_USAGE$    INTCOL#                  2
SQL> set autot trace exp
SQL>  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t1
  3   MINUS
  4   SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null;
no rows selected
--无记录返回
Execution Plan
----------------------------------------------------------
Plan hash value: 399371572
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time
   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |  4262 | 76716 |    27  (71)| 00:00:01 |
|   1 |  MINUS              |              |       |       |            |          |
|   2 |   SORT UNIQUE       |              |  4262 | 38358 |     9  (12)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| COL_USAGE$   |  4262 | 38358 |     8   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|              |  4262 | 38358 |    18   (6)| 00:00:01 |
|*  5 |    INDEX FULL SCAN  | I_COL_USAGE$ |  4262 | 38358 |    17   (0)| 00:00:01 |
------------------------------------------------------------------------------------
--验证表两个sql是否正确(一个全表扫描，另个index 快速扫描)
SQL>  SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  3   MINUS
  4  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t1;
      OBJ#    INTCOL#
---------- ----------
4294951004          2
4294951004          3
4294951004          4
4294951004         26
4294951004         27
4294951037          4
4294951037          5
4294951037          6
4294951037          9
4294951037         10
4294951840         11
      OBJ#    INTCOL#
---------- ----------
4294951840         12
4294951906          4
4294952709          3
4294952867          4
4294952867          9
16 rows selected.
--证明index中的记录比表中多了16条

解决问题并验证

SQL> alter index sys.I_COL_USAGE$ rebuild online;
Index altered.
SQL>  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
 FROM sys.COL_USAGE$ t1
  2    3   MINUS
  4   SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  6  ;
no rows selected
SQL>  SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  3   MINUS
  4  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t1;
no rows selected

这次出现此问题的原因是因为在更新语句中使用索引找到一条记录，然后到表中去查询时该记录不存在，出现此错误，一般解决方法是重建索引
官方关于ORA-600[13013]说明

Getting ORA-01476 during execution of DBMS_STATS.GATHER_SCHEMA_STATS

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Getting ORA-01476 during execution of DBMS_STATS.GATHER_SCHEMA_STATS

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

alert日志

Fri Jan 27 22:00:09 2012
GATHER_STATS_JOB encountered errors.  Check the trace file.
Fri Jan 27 22:00:09 2012
Errors in file /oracle10/admin/ocs/bdump/ocs1_j001_29138.trc:
ORA-01476: divisor is equal to zero

trace内容

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle10/app/product/db/10.2.0
System name:    HP-UX
Node name:      ocsdb1
Release:        B.11.23
Version:        U
Machine:        ia64
Instance name: ocs1
Redo thread mounted by this instance: 1
Oracle process number: 60
Unix process pid: 29138, image: oracle@ocsdb1 (J001)
*** ACTION NAME:(GATHER_STATS_JOB) 2012-01-27 22:00:09.308
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-27 22:00:09.308
*** SERVICE NAME:(SYS$USERS) 2012-01-27 22:00:09.308
*** SESSION ID:(988.31342) 2012-01-27 22:00:09.307
ORA-01476: divisor is equal to zero
*** 2012-01-27 22:00:09.417
GATHER_STATS_JOB: GATHER_TABLE_STATS('"OCS_SM"','"HLP_SMS_SEND"','""', ...)
ORA-01476: divisor is equal to zero

错误原因
oracle unpublished Bug 5645718

解决方法
1.Setting event 38041 at level 16

sql> connect / as sysdba
sql> alter system set events '38041 trace name context forever, level 16';

2.Patch 6319761

手动提交分布式事务一例

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：手动提交分布式事务一例

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一.alert文件中出现了很多类此记录

Fri Feb 10 05:25:01 2012
Errors in file /tmp/recover/ahcx216_reco_7956.trc:
ORA-12154: TNS:could not resolve service name
Fri Feb 10 05:25:01 2012
Errors in file /tmp/recover/ahcx216_reco_7956.trc:
ORA-12154: TNS:could not resolve service name

这里可以看出来两个信息：
1.出错的进程是rec0进程，而该进程的作用是解决分布式事务失败后遗留问题(事务提交或者回滚)
2.错误信息是tns不能被解析

二.查看trace文件

/tmp/recover/ahcx216_reco_7956.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0/db_1
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.9-89.0.0.0.1.ELhugemem
Version:        #1 SMP Tue May 19 04:38:38 EDT 2009
Machine:        i686
Instance name: ahcx216
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 7956, image: oracle@localhost.localdomain (RECO)
*** SESSION ID:(6.1) 2012-02-10 04:58:24.886
*** 2012-02-10 04:58:24.886
ERROR, tran=6.1.712757, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name
………………
*** 2012-02-10 05:25:01.580
ERROR, tran=6.1.712757, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name
*** 2012-02-10 05:25:01.627
ERROR, tran=12.19.99059, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name

通过这里我们可以看出事务id分别为12.19.99059和6.1.712757有问题

三.查看dba_2pc_pending视图

SQL> select local_tran_id,state,fail_time,retry_time from dba_2pc_pending;
LOCAL_TRAN_ID     STATE            FAIL_TIME           RETRY_TIME
-------------- ---------------- --------------       --------------
6.1.712757	collecting	2010/12/1 13:39:03   2012/2/10 5:38:52
12.19.99059	collecting	2010/12/1 15:56:26   2012/2/10 5:38:52

可以看出，果然有两个分布式事务在2010年12月1日出了问题(本库是一个问题库，在把库拉起来后发现该问题)
因为是异机恢复，而且间隔时间较长，很多tns的信息都已经不存在，所以需要手工提交分布式事务

四.手动提交事务

SQL> BEGIN
  2  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');
  3  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
  4  END;
  5  /
BEGIN
*
第 1 行出现错误:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 2
SQL> alter session set "_smu_debug_mode"=4;
Session altered.
-------------------------------------------------------------
--设置UNDO_SUPPRESS_ERRORS=true也可以解决此问题
alter system set UNDO_SUPPRESS_ERRORS = TRUE;
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('<事务ID>');
commit;
alter system set UNDO_SUPPRESS_ERRORS = false;
--------------------------------------------------------------
SQL> commit;
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');
PL/SQL procedure successfully completed.
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059'); END;
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
--第一个分布式事务处理后，未提交导致
SQL> commit;
Commit complete.
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

五.补充说明
开始在另一个会话中，执行失败原因

SQL> alter session set "_smu_debug_mode"=4;
会话已更改。
SQL> commit;
提交完成。
SQL> BEGIN
  2  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');
  3  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
  4  END;
  5  /
BEGIN
*
第 1 行出现错误:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 3

因为这里的begin end中包含了两个事务的清理，在清理完第一个事务之后，需要提交才能够清理第二个，这里因为没有提交，导致ORA-01453错误。

sqlplus版本不正确，导致ORA-12560

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：sqlplus版本不正确，导致ORA-12560

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

sqlplus登录数据库报ORA-12560

C:\Users\XIFENFEI>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 23:33:31 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
C:\Users\XIFENFEI>sqlplus -v
SQL*Plus: Release 11.2.0.2.0 Production

在这里发现sqlplus的版本信息不正确，我的数据库安装的11.2.0.1，这么怎么显示sqlplus是11.2.0.2，然后进入%ORACLE_HOME%/bin目录下面执行sqlplus，登录成功

C:\Users\XIFENFEI>cd e:\oracle\11_2_0\BIN
e:\oracle\11_2_0\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 2月 14 23:44:38 2012
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>

出现了这样的问题，考虑应该是环境变量设置不当导致，查看环境变量发现下面一部分

E:\oracle\timesten\bin;E:\oracle\timesten\ttoracle_home\instantclient_11_2;e:\oracle\11_2_0\bin;

问题的答案就是在这里，因为我的电脑是先安装Oracle，前几天安装了TimesTen，导致环境变量发生了改变，使得我在默认情况下使用sqlplus的时候，自动调用了TimesTen中的sqlplus导致

C:\Users\XIFENFEI>cd E:\oracle\timesten\ttoracle_home\instantclient_11_2
E:\oracle\timesten\ttoracle_home\instantclient_11_2>dir sqlplus*
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35
 E:\oracle\timesten\ttoracle_home\instantclient_11_2 的目录
2010/11/19  00:50           655,360 sqlplus.exe
2011/11/08  04:12            84,524 sqlplus.sym
2011/11/08  04:12               554 SQLPLUS_README
               3 个文件        740,438 字节
               0 个目录  9,825,832,960 可用字节