ORA-xxxxx – 第7页 – Database SOS

在11.2.0.2版本中sql tuning可能导致ORA-07445[qsmmixComputeClusteringFactor()+386]错误
系统版本和平台信息

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0/dbhome_1
System name:	Linux
Node name:	FPMS01DB
Release:	2.6.18-238.el5
Version:	#1 SMP Sun Dec 19 14:22:44 EST 2010
Machine:	x86_64
VM name:	VMWare Version: 6
Instance name: tis
Redo thread mounted by this instance: 1
Oracle process number: 41
Unix process pid: 16822, image: oracle@FPMS01DB (J003)

alert日志信息

Thu Jan 10 22:00:02 2013
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Thu Jan 10 22:02:39 2013
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4]
[PC:0x597FEB4, qsmmixComputeClusteringFactor()+386] [flags: 0x0, count: 1]
Errors in file /oracle/diag/rdbms/tis/tis/trace/tis_j001_11073.trc  (incident=80033):
ORA-07445: exception encountered: core dump [qsmmixComputeClusteringFactor()+386]
[SIGSEGV] [ADDR:0x4] [PC:0x597FEB4] [Address not mapped to object] []
Incident details in: /oracle/diag/rdbms/tis/tis/incident/incdir_80033/tis_j001_11073_i80033.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Jan 10 22:02:51 2013
Dumping diagnostic data in directory=[cdmp_20130110220251],
requested by (instance=1, osid=11073 (J001)), summary=[incident=80033].

通过这里可以看出来，出现ORA-07445错误的原因很可能和SQL TUNING相关

trace文件信息

Dump continued from file: /oracle/diag/rdbms/tis/tis/trace/tis_j003_16822.trc
ORA-07445: exception encountered: core dump [qsmmixComputeClusteringFactor()+386]
[SIGSEGV] [ADDR:0x4] [PC:0x597FEB4] [Address not mapped to object] []
========= Dump for incident 80008 (ORA 7445 [qsmmixComputeClusteringFactor()+386]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4]
[PC:0x597FEB4, qsmmixComputeClusteringFactor()+386] [flags: 0x0, count: 1]
Registers:
%rax: 0x00002b0b684593d0 %rbx: 0x0000000000000003 %rcx: 0x0000000000000000
%rdx: 0x00002b0b684593d0 %rdi: 0x00007fff758ceff8 %rsi: 0x0000000000000000
%rsp: 0x00007fff758ced00 %rbp: 0x00007fff758cf5d0  %r8: 0x00002b0b684593a8
 %r9: 0x00002b0b683c8e60 %r10: 0x0000000000000017 %r11: 0x0000000000000000
%r12: 0x00002b0b684197a0 %r13: 0x00002b0b68419928 %r14: 0x00002b0b66ad2430
%r15: 0x00002b0b66d95590 %rip: 0x000000000597feb4 %efl: 0x0000000000010202
  qsmmixComputeClusteringFactor()+366 (0x597fea0) call 0x597fc62
  qsmmixComputeClusteringFactor()+371 (0x597fea5) lea -0x5d8(%rbp),%rdi
  qsmmixComputeClusteringFactor()+378 (0x597feac) mov 0x60(%rax),%rsi
  qsmmixComputeClusteringFactor()+382 (0x597feb0) mov 0x60(%rax),%rcx
> qsmmixComputeClusteringFactor()+386 (0x597feb4) movzwl 0x4(%rcx),%edx
  qsmmixComputeClusteringFactor()+390 (0x597feb8) add $6,%rsi
  qsmmixComputeClusteringFactor()+394 (0x597febc) mov %rax,-0x20(%rbp)
  qsmmixComputeClusteringFactor()+398 (0x597fec0) call 0xa056f0
  qsmmixComputeClusteringFactor()+403 (0x597fec5) mov -0x8(%rbp),%edx
*** 2013-01-09 22:50:24.205
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=durgh9p25f6bb) -----
/* SQL Analyze(788,1) */ SELECT  distinct
			                   null  as isdistribution,
			                   null as seqno,
			                   to_char(T1.SEQNO) as TRADEID
…………
from T_XIFENFEI
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xc5b644e8     11816  package body SYS.DBMS_SQLTUNE_INTERNAL
0xc54ca918         7  SYS.WRI$_ADV_SQLTUNE
0xd55aec98       587  package body SYS.PRVT_ADVISOR
0xd55aec98      2655  package body SYS.PRVT_ADVISOR
0xc5f66c70       241  package body SYS.DBMS_ADVISOR
0xc5418dc8       821  package body SYS.DBMS_SQLTUNE
0xd50d38c0         4  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+36        call     kgdsdst()            000000000 ? 000000000 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000001 ? 000000003 ?
ksedst1()+98         call     skdstdst()           000000000 ? 000000000 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000000 ? 000000003 ?
ksedst()+34          call     ksedst1()            000000001 ? 000000001 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000000 ? 000000003 ?
dbkedDefDump()+2741  call     ksedst()             000000001 ? 000000001 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000000 ? 000000003 ?
ksedmp()+36          call     dbkedDefDump()       000000003 ? 000000003 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000000 ? 000000003 ?
ssexhd()+2366        call     ksedmp()             000000003 ? 000000003 ?
                                                   2B0B65B6C098 ? 000000001 ?
                                                   000000000 ? 000000003 ?
__sighandler()       call     ssexhd()             00000000B ? 2B0B65B74D70 ?
                                                   2B0B65B74C68 ? 000000001 ?
                                                   000000000 ? 000000003 ?
qsmmixComputeCluste  signal   __sighandler()       7FFF758CEFF8 ? 000000000 ?
ringFactor()+386                                   2B0B684593D0 ? 000000000 ?
                                                   2B0B684593A8 ? 2B0B683C8E60 ?
qsmmixGenFakeIdxSta  call     qsmmixComputeCluste  2B0B68419928 ? 7FFF758CEFF8 ?
ts()+1025                     ringFactor()         2B0B66D95590 ?
                                                   BFF0000000000000 ?
                                                   000000000 ? 2B0B683C8E60 ?
qsmmixPopulateIdxSt  call     qsmmixGenFakeIdxSta  2B0B66AD2430 ? 2B0B68419928 ?
ats()+71                      ts()                 2B0B684197A0 ? 2B0B66B92630 ?
                                                   40BAD30000000000 ?
                                                   2B0B683C8E60 ?
qsmmixSetKkotbixt()  call     qsmmixPopulateIdxSt  2B0B66B92630 ? 2B0B66AD2430 ?
+479                          ats()                2B0B68419928 ? 2B0B66B92630 ?
                                                   40BAD30000000000 ?
                                                   2B0B683C8E60 ?
qsmmixReturnCandToO  call     qsmmixSetKkotbixt()  2B0B66B92630 ? 2B0B66AD2430 ?
pt()+656                                           2B0B683F0090 ? 2B0B6845DCC8 ?
                                                   2B0B6845C2B8 ? 2B0B68462F08 ?
qsmmixOptimizerGenI  call     qsmmixReturnCandToO  2B0B66B92630 ? 2B0B66AD2430 ?
dxCand()+601                  pt()                 2B0B68462F60 ? 2B0B6845DCC8 ?
                                                   2B0B6845C2B8 ? 2B0B68462F08 ?
qsmmixOptimizerSetu  call     qsmmixOptimizerGenI  0913F73A0 ? 2B0B6845C2B8 ?
pIdxCand()+351                dxCand()             000000009 ? 000000001 ?
                                                   2B0B6845C2B8 ? 2B0B68462F08 ?
kkoiqb()+13730       call     qsmmixOptimizerSetu  0913F73A0 ? 2B0B683EEFD8 ?
                              pIdxCand()           000000009 ? 000000001 ?
                                                   2B0B6845C2B8 ? 2B0B68462F08 ?
kkooqb()+632         call     kkoiqb()             2B0B66B6FF88 ? 000000000 ?
                                                   000000000 ? 000000001 ?
                                                   2B0B6845C2B8 ? 2B0B68462F08 ?
kkoqbc()+2359        call     kkooqb()             2B0B66B6FF88 ? 000000000 ?
                                                   000000000 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apakkoqb()+166       call     kkoqbc()             7FFF758D2DF0 ? 2B0B66B6FF88 ?
                                                   000000000 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apaqbdDescendents()  call     apakkoqb()           7FFF758D2DF0 ? 2B0B66B6FF88 ?
+457                                               0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apaqbdList()+71      call     apaqbdDescendents()  7FFF758D2DF0 ? 2B0B66B6FF88 ?
                                                   0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apaqbdDescendents()  call     apaqbdList()         7FFF758D2DF0 ? 2B0B66B6FF88 ?
+710                                               0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apaqbdList()+71      call     apaqbdDescendents()  0913F73A0 ? 2B0B66C975C8 ?
                                                   0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apaqbd()+9           call     apaqbdList()         0913F73A0 ? 2B0B66C975C8 ?
                                                   0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
apadrv()+861         call     apaqbd()             0913F73A0 ? 2B0B66C975C8 ?
                                                   0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
opitca()+1971        call     apadrv()             0913F73A0 ? 2B0B66C975C8 ?
                                                   0913F73A0 ? 000000004 ?
                                                   2B0B00000000 ? 2B0B00000000 ?
kksSetBindType()+76  call     opitca()             2B0B671A77F8 ? 0913F73A0 ?
05                                                 7FFF758D4D10 ? 000000004 ?
                                                   100000000 ? 2B0B00000000 ?
kksfbc()+10664       call     kksSetBindType()     7FFF758D4D10 ? 2B0B671A77F8 ?
                                                   7FFF758D4DA0 ? 000000102 ?
                                                   7FFF0000001F ? 000000000 ?
opiexe()+2268        call     kksfbc()             2B0B671A77F8 ? 000000003 ?
                                                   000000102 ? 000000000 ?
                                                   000000000 ? 7FFF758D5D70 ?
kpoal8()+2226        call     opiexe()             000000049 ? 000000003 ?
                                                   7FFF758D6310 ? 000000000 ?
                                                   000000000 ? 7FFF758D5D70 ?
opiodr()+910         call     kpoal8()             00000005E ? 000000000 ?
                                                   7FFF758DA288 ? 000000000 ?
                                                   000000000 ? 000000001 ?
kpoodrc()+31         call     opiodr()             00000005E ? 000000000 ?
                                                   7FFF758DA288 ? 000000000 ?
                                                   00989C970 ? 000000001 ?
rpiswu2()+1618       call     kpoodrc()            7FFF758D75C0 ? 000000000 ?
                                                   7FFF758DA288 ? 000000000 ?
                                                   00989C970 ? 000000001 ?
kpoodr()+617         call     rpiswu2()            0D3C52C80 ? 000000023 ?
                                                   2B0B65EDFAAC ? 000000004 ?
                                                   2B0B6C6B7340 ? 000000023 ?
upirtrc()+2417       call     kpoodr()             2B0B65EBF170 ? 00000005E ?
                                                   7FFF758DA288 ? 000000000 ?
                                                   2B0B6C6B7340 ? 000000023 ?
kpurcsc()+93         call     upirtrc()            2B0B65EBF170 ? 00000005E ?
                                                   7FFF758DA288 ? 7FFF758DA400 ?
                                                   7FFF758DB420 ? 2B0B65C7D308 ?
kpuexec()+10804      call     kpurcsc()            2B0B65EBF170 ? 00000005E ?
                                                   7FFF758DA288 ? 7FFF758DA400 ?
                                                   7FFF758DB420 ? 2B0B65C7D308 ?
OCIStmtExecute()+34  call     kpuexec()            2B0B65E3E5B8 ? 2B0B65E3E538 ?
                                                   7FFF758DA280 ? 000000000 ?
                                                   E0C28C4F00000000 ?
                                                   000000000 ?
qksanExecSql()+743   call     OCIStmtExecute()     2B0B65E3E5B8 ? 2B0B65E3E538 ?
                                                   7FFF758DA280 ? 000000000 ?
                                                   E0C28C4F00000000 ?
                                                   000000000 ?
qksanAnalyzeSql()+2  call     qksanExecSql()       7FFF758DDCA8 ? 2B0B65C7D308 ?
363                                                2B0B65C7D3E8 ? 2B0B65E3E538 ?
                                                   7FFF758DDC28 ? 7FFF758DE3A9 ?
qsmmixProcessQuery(  call     qksanAnalyzeSql()    7FFF758DDCA8 ? 2B0B67E90DA8 ?
)+1089                                             00000B3AB ? 2B0B65FBD218 ?
                                                   100000023 ? 7FFF758DE3A9 ?
qsmmixSqlTuneAnalyz  call     qsmmixProcessQuery(  2B0B65FBD218 ? 000000005 ?
eIdx()+449                    )                    2B0B67E90DA8 ? 2B0B65F37BF4 ?
                                                   2B0B65FBD218 ? 7FFF00000023 ?
kestsiIndexAnalyzeD  call     qsmmixSqlTuneAnalyz  7FFF758DE370 ? 000000005 ?
rv()+794                      eIdx()               2B0B67E90DA8 ? 2B0B65F37BF4 ?
                                                   2B0B65FBD218 ? 7FFF00000023 ?
kestsTuneSqlDrv()+3  call     kestsiIndexAnalyzeD  7FFF758E0548 ? 7FFF758E08C0 ?
83                            rv()                 7FFF758DFC78 ? 7FFF758E09B0 ?
                                                   7FFF758DED50 ? 7FFF758DED58 ?
kesaiExecAction()+9  call     kestsTuneSqlDrv()    7FFF758E0540 ? 7FFF758E08C0 ?
81                                                 7FFF758DFC78 ? 7FFF758E09B0 ?
                                                   7FFF758DED50 ? 7FFF758DED58 ?
kesaiTuneSqlDrv()+6  call     kesaiExecAction()    7FFF758DF420 ? 7FFF758E08C0 ?
258                                                7FFF758E09B0 ? 7FFF758E05E0 ?
                                                   7FFF758E0A68 ? 7FFF758E0A60 ?
spefcifa()+225       call     kesaiTuneSqlDrv()    7FFF758E21B8 ? 2B0B65CA3180 ?
                                                   000000000 ? 7FFF758E05E0 ?
                                                   0C5FB0033 ? 2B0B65C362B8 ?
spefmccallstd()+421  call     spefcifa()           7FFF758E1CE0 ? 000000004 ?
                                                   2B0B65CA3108 ? 7FFF758E0F30 ?
                                                   0C5FB0033 ? 2B0B65C362B8 ?
pextproc()+36        call     spefmccallstd()      7FFF758E20E0 ? 7FFF758E1D88 ?
                                                   7FFF758E1AA0 ? 7FFF758E1CE0 ?
                                                   000000000 ? 2B0B65C362B8 ?
__PGOSF589_peftrust  call     pextproc()           7FFF758E20E0 ? 7FFF758E1D88 ?
ed()+145                                           7FFF758E1AA0 ? 7FFF758E1CE0 ?
                                                   000000000 ? 2B0B65C362B8 ?
__PGOSF633_psdexsp(  call     __PGOSF589_peftrust  7FFF758E20E0 ? 7FFF758E1D88 ?
)+255                         ed()                 7FFF758E1AA0 ? 7FFF758E1CE0 ?
                                                   000000000 ? 2B0B65C362B8 ?
rpiswu2()+1618       call     __PGOSF633_psdexsp(  7FFF758E18D0 ? 7FFF758E20E0 ?
                              )                    7FFF758E1AA0 ? 000020003 ?
                                                   0037500E0 ? 7FFF758E3100 ?
psdextp()+695        call     rpiswu2()            0D3C52C80 ? 000000000 ?
                                                   7FFF758E1910 ? 000000002 ?
                                                   7FFF758E1950 ? 000000000 ?
pefccal()+726        call     psdextp()            7FFF758E3100 ? 7FFF758E1D88 ?
                                                   7FFF758E1AA0 ? 000000000 ?
                                                   000020003 ? 7FFF758E20E0 ?
pefcal()+219         call     pefccal()            7FFF758E20E0 ? 000A324C2 ?
                                                   00B7C8EA0 ? 000000000 ?
                                                   000020003 ? 7FFF758E20E0 ?
pevm_FCAL()+164      call     pefcal()             7FFF758E20E0 ? 00B7C9050 ?
                                                   2B0B65C835B8 ? 000000000 ?
                                                   000020003 ? 7FFF758E20E0 ?
pfrinstr_FCAL()+70   call     pevm_FCAL()          2B0B65C835B8 ? 0A6FB80D8 ?
                                                   2B0B65C835B8 ? 0A6FB8030 ?
                                                   000020003 ? 7FFF758E20E0 ?
pfrrun_no_tool()+63  call     pfrinstr_FCAL()      2B0B65C835B8 ? 0A5405930 ?
                                                   2B0B65C83628 ? 0A6FB8030 ?
                                                   000020003 ? 7FFF758E20E0 ?
pfrrun()+622         call     pfrrun_no_tool()     2B0B65C835B8 ? 0A5405930 ?
                                                   2B0B65C83628 ? 0A6FB8030 ?
                                                   000020003 ? 7FFF758E20E0 ?
plsql_run()+644      call     pfrrun()             2B0B65C835B8 ? 000000000 ?
                                                   2B0B65C83628 ? 7FFF758E3100 ?
                                                   000020003 ? 0928C7098 ?
peicnt()+296         call     plsql_run()          2B0B65C835B8 ? 000000001 ?
                                                   000000000 ? 7FFF758E3100 ?
                                                   000020003 ? 000000000 ?
kkxexe()+521         call     peicnt()             7FFF758E3100 ? 2B0B65C835B8 ?
                                                   2B0B65CA4FD8 ? 7FFF758E3100 ?
                                                   2B0B65CA2F30 ? 000000000 ?
opiexe()+17478       call     kkxexe()             2B0B65C8BD58 ? 2B0B65C835B8 ?
                                                   000000000 ? 7FFF758E3100 ?
                                                   2B0B65CA2F30 ? 000000000 ?
kpoal8()+2226        call     opiexe()             000000049 ? 000000003 ?
                                                   7FFF758E4730 ? 7FFF758E3100 ?
                                                   2B0B65CA2F30 ? 000000000 ?
opiodr()+910         call     kpoal8()             00000005E ? 000000000 ?
                                                   7FFF758E81A8 ? 7FFF758E3100 ?
                                                   2B0B65CA2F30 ? 7FFF00000001 ?
kpoodr()+648         call     opiodr()             00000005E ? 000000000 ?
                                                   7FFF758E81A8 ? 000000000 ?
                                                   00989C970 ? 7FFF00000001 ?
upirtrc()+2417       call     kpoodr()             2B0B65C796A8 ? 00000005E ?
                                                   7FFF758E81A8 ? 000000000 ?
                                                   00989C970 ? 7FFF00000001 ?
kpurcsc()+93         call     upirtrc()            2B0B65C796A8 ? 00000005E ?
                                                   7FFF758E81A8 ? 7FFF758E8320 ?
                                                   7FFF758E9340 ? 2B0B65C7D308 ?
kpuexec()+11692      call     kpurcsc()            2B0B65C796A8 ? 00000005E ?
                                                   7FFF758E81A8 ? 7FFF758E8320 ?
                                                   7FFF758E9340 ? 2B0B65C7D308 ?
OCIStmtExecute()+34  call     kpuexec()            2B0B65C88290 ? 2B0B65C88210 ?
                                                   7FFF758E81A0 ? 000000001 ?
                                                   7FFF00000000 ? 000000000 ?
__PGOSF529_jslvec_e  call     OCIStmtExecute()     2B0B65C88290 ? 2B0B65C88210 ?
xeccb()+2207                                       7FFF758E81A0 ? 000000001 ?
                                                   7FFF00000000 ? 000000000 ?
jslvswu()+54         call     __PGOSF529_jslvec_e  7FFF758EC39C ? 2B0B65C88210 ?
                              xeccb()              2B0B65C88210 ? 000000001 ?
                                                   7FFF00000000 ? 000000000 ?
jslve_execute0()+22  call     jslvswu()            000000000 ? 7FFF00000000 ?
17                                                 000000000 ? 000000001 ?
                                                   7FFF00000000 ? 000000000 ?
jslve_execute()+327  call     jslve_execute0()     7FFF758EE2B4 ? 000005946 ?
                                                   000000002 ? 7FFF758EE2A0 ?
                                                   000000000 ? 0FFFFFFFF ?
rpiswu2()+1618       call     jslve_execute()      7FFF758EE150 ? 000000002 ?
                                                   7FFF758EE2B4 ? 000005946 ?
                                                   7FFF758EE2A0 ? 0FFFFFFFF ?
kkjex1e()+374        call     rpiswu2()            0D3C52C80 ? 000000000 ?
                                                   7FFF758EE170 ? 000000002 ?
                                                   7FFF758EE190 ? 000000000 ?
kkjsexe()+705        call     kkjex1e()            7FFF758EE2B4 ? 000005946 ?
                                                   000000002 ? 7FFF758EE2A0 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
kkjrdp()+689         call     kkjsexe()            7FFF758EE2B4 ? 000005946 ?
                                                   000000001 ? 7FFF758EE2A0 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
opirip()+953         call     kkjrdp()             7FFF758EE2B4 ? 000005946 ?
                                                   000000001 ? 7FFF758EE2A0 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
opidrv()+598         call     opirip()             000000032 ? 000000004 ?
                                                   7FFF758EFA28 ? 7FFF758EE2A0 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
sou2o()+98           call     opidrv()             000000032 ? 000000004 ?
                                                   7FFF758EFA28 ? 7FFF758EE2A0 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
opimai_real()+261    call     sou2o()              7FFF758EFA00 ? 000000032 ?
                                                   000000004 ? 7FFF758EFA28 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
ssthrdmain()+252     call     opimai_real()        000000000 ? 7FFF758EFBF0 ?
                                                   000000004 ? 7FFF758EFA28 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
main()+196           call     ssthrdmain()         000000003 ? 7FFF758EFBF0 ?
                                                   000000001 ? 000000000 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
__libc_start_main()  call     main()               000000003 ? 7FFF758EFD90 ?
+244                                               000000001 ? 000000000 ?
                                                   0D57A5F08 ? 7FFF758EE208 ?
_start()+36          call     __libc_start_main()  000A077C8 ? 000000001 ?
                                                   7FFF758EFD88 ? 000000000 ?
                                                   0D57A5F08 ? 000000003 ?
--------------------- Binary Stack Dump ---------------------

分析Stack可以发现，他们和MOS[]中的非常类此,可以断定是该Bug 9746210
skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp<- ssexhd <- sighandler <- qsmmixComputeClusteringFactor <- qsmmixGenFakeIdxStats <- qsmmixPopulateIdxStats <- qsmmixSetKkotbixtqsmmixReturnCandToOpt <- qsmmixOptimizerGenIdxCand <- qsmmixOptimizerSetupIdxCandResponse
处理建议

The is fixed in following versions:
 12.1 (Future Release)
 11.2.0.3 (Server Patch Set)
 11.2.0.2.4 Patch Set Update
 11.2.0.2 Bundle Patch 12 for Exadata Database
 11.2.0.2 Patch 11 on Windows Platforms
To resolve the issue:
Either
Upgrade to the over versions
Or
Apply Patch 9746210

补充说明
对于SQL TUNING功能,在觉得多少人的环境中都不需要这个,可以考虑禁用该功能来屏蔽该错误

BEGIN
dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;

参考文档
SQL Tuning query fails with ORA-7445 [qsmmixComputeClusteringFactor] [ID 1483654.1]
Error ORA-07445 Qsmmixcomputeclusteringfactor From SQL Tuning [ID 1359148.1]
Bug 9746210 – ORA-7445 [qsmmixComputeClusteringFactor] from SQL tuning [ID 9746210.8]

重建控制文件引发ORA-00218故障

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

标题：重建控制文件引发ORA-00218故障

遇到一个案例在数据库启动的时候报ORA-00218错误,而这个故障的引起原因是因为重建控制文件的时候,有一个控制文件无法创建,而导致了原有的控制文件被破坏,提醒:创建控制文件之前,最好对原有控制文件进行备份
数据库启动报ORA-00218错误

SQL> startup
ORACLE instance started.
Total System Global Area  285212672 bytes
Fixed Size                  2020224 bytes
Variable Size              92277888 bytes
Database Buffers          188743680 bytes
Redo Buffers                2170880 bytes
ORA-00218: block size 0 of control file
'/u01/app/oracle/oradata/zxy/control01.ctl' does not match DB_BLOCK_SIZE (0)

分析ORA-00218错误

Oracle10g Release 1 Message
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Error:	  ORA-00218  (ORA-218)
Text:	  block size %s of controlfile '%s' does not match DB_BLOCK_SIZE
	  (%s)
---------------------------------------------------------------------------
Cause:	The block size as stored in the controlfile header is different
	from the value of the initialization parameter DB_BLOCK_SIZE. This
	might be due to an incorrect setting of DB_BLOCK_SIZE, or else
	might indicate that the controlfile has either been corrupted or
	belongs to a different database.
Action:	Restore a good copy of the controlfile. If the controlfile is
	known to be clean set the DB_BLOCK_SIZE to match controlfile
	headers block size value.
Oracle 9.2 or Earlier Error Message
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Error:  ORA 218
Text:   control file <name> was created with block size <num> now is <num>
-------------------------------------------------------------------------------
Cause:  The physical block size, stored in the control file header, was
        different in physical block size returned by the O/S.
        This usually indicates that the control file was corrupted.
Action: Restore a good copy of the control file.
        For more information about control files and recovery, see the index
        entries on "control files," "control files, backing up," "control
        files, recovery and" in <Oracle7 Server Concepts>.

通过这里可以知道,很可能是控制文件header的db_block_size和参数文件中的db_block_size的大小不一致,从而导致了该问题,而从启动数据库的错误提示上看,是控制文件的block size 为0.

分析控制文件

--dbv检查控制文件
[oracle@zxy bdump]$ dbv file='/u01/app/oracle/oradata/zxy/control01.ctl' blocksize=16384
DBVERIFY: Release 10.2.0.1.0 - Production on Sun Jan 6 23:39:32 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/zxy/control01.ctl
DBVERIFY - Verification complete
Total Pages Examined         : 450
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 450
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 0 (0.0)
--strings读控制文件
[oracle@zxy bdump]$ strings /u01/app/oracle/oradata/zxy/control01.ctl
}|{z
--正常库dbv检查控制文件
E:\oracle\oradata\xifenfei>dbv file=CONTROL01.CTL blocksize=16384
DBVERIFY: Release 11.2.0.3.0 - Production on 星期一 1月 7 10:26:46 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - 开始验证: FILE = E:\ORACLE\ORADATA\XIFENFEI\CONTROL01.CTL
DBVERIFY - 验证完成
检查的页总数: 600
处理的页总数 (数据): 0
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其他): 65
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 535
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 39198 (65535.39198)

检查参数文件db_block_size

SQL> show parameter db_block_size
TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

通过分析我们知道spfile中的db_block_size是正确的,而控制文件通过dbv和strings检测均为空值,证明是控制文件异常导致该问题,对于该问题可以通过重建控制文件或者还原备份控制文件来解决问题.

分析问题原因

--参数文件配置
control_files            = /u01/app/oracle/oradata/zxy/control01.ctl, /tmp/oradata/control04.ctl
--alert日志
Sun Jan  6 21:42:50 2013
CREATE CONTROLFILE REUSE DATABASE "ZXY" RESETLOGS  NOARCHIVELOG
 …………
CHARACTER SET AL32UTF8
Sun Jan  6 21:42:50 2013
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Sun Jan  6 21:42:53 2013
Errors in file /u01/app/oracle/admin/zxy/udump/zxy_ora_3898.trc:
ORA-00200: control file could not be created
ORA-00202: control file: '/tmp/oradata/control04.ctl'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
--再次启动
control_files            = /u01/app/oracle/oradata/zxy/control01.ctl
--日志
ALTER DATABASE   MOUNT
Sun Jan  6 21:56:31 2013
ORA-00218: block size 0 of control file
'/u01/app/oracle/oradata/zxy/control01.ctl' does not match DB_BLOCK_SIZE (0)
Sun Jan  6 21:56:31 2013
ORA-218 signalled during: ALTER DATABASE   MOUNT...

创建控制文件,因为/tmp/oradata/目录不存在或者没有权限导致创建控制文件失败,而导致原来有的控制文件也失败

故障重现

--正常启动
control_files='/u01/oracle/oradata/XFF/control01.ctl'
SQL> startup pfile=/tmp/pfile
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1267164 bytes
Variable Size             109054500 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> alter database backup controlfile to trace as '/tmp/ctl';
Database altered.
--尝试重建控制文件
control_files='/u01/oracle/oradata/XFF/control01.ctl','/tmp/xifenfei/con.ctl'
SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/oracle/oradata/XFF/redo01.log'  SIZE 50M,
  9    GROUP 2 '/u01/oracle/oradata/XFF/redo02.log'  SIZE 50M,
 10    GROUP 3 '/u01/oracle/oradata/XFF/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/u01/oracle/oradata/XFF/system01.dbf',
 13    '/u01/oracle/oradata/XFF/undotbs01.dbf',
 14    '/u01/oracle/oradata/XFF/sysaux01.dbf',
 15    '/u01/oracle/oradata/XFF/users01.dbf',
 16    '/u01/oracle/oradata/XFF/xifenfei01.dbf',
 17    '/u01/oracle/oradata/XFF/users03.dbf'
 18  CHARACTER SET ZHS16GBK
 19  ;
CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/tmp/xifenfei/con.ctl'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
--使用原控制文件启动库
control_files='/u01/oracle/oradata/XFF/control01.ctl'
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1267164 bytes
Variable Size             109054500 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
ORA-00218: block size 0 of control file '/u01/oracle/oradata/XFF/control01.ctl'
does not match DB_BLOCK_SIZE (0)

补充参数文件中DB_BLOCK_SIZE不正确导致后果

DB_BLOCK_SIZE从8192修改为16384
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1267164 bytes
Variable Size             109054500 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 16384)

delete expired backup报ORA-19606错误处理

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

标题：delete expired backup报ORA-19606错误处理

数据库版本(exadata x2 1/4)

SQL> select *from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

使用delete expired backup报ORA-19606错

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_4 channel at 12/05/2012 13:35:07
ORA-19606: Cannot copy or restore to snapshot control file

报错原因

The snapshot controlfile is cataloged as a controlfile coy and is now obsolete.
RMAN cannot delete this file as it is used by rman.

处理方法[ID 1215493.1]

Since RMAN will continue to use that file as it's snapshot copy,
you must change the location/name that RMAN is using before it will allow you to delete the file.
1. Set new name (or location) for RMAN to use for snapshot controlfile:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCF_temp.ORA';
2. Remove the snapshot controlfile from the RMAN information as a controlfile copy.
delete 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA' on operating system.
3. Crosscheck and delete the file from RMAN:
crosscheck controlfilecopy 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
delete expired controlfilecopy 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
4. Set the snapshot controlfile name (or location) to original:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
5. Or to set the snapshot controlfile name back to default value:
CONFIGURE SNAPSHOT CONTROLFILE NAME clear;

处理方法就是把SNAPSHOT CONTROLFILE的配置路径改变,然后删除控制文件快照

ORA-00600[kcfrbd_3]故障解决

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

标题：ORA-00600[kcfrbd_3]故障解决

朋友一数据库因为断电,被重建控制文件等操作折腾的最后出现在启动的时候出现ORA-00600[kcfrbd_3]

Wed Dec 05 10:26:34 2012
Thread 1 advanced to log sequence 11
Thread 1 opened at log sequence 11
  Current log# 1 seq# 11 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Wed Dec 05 10:26:34 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 05 10:26:34 2012
SMON: enabling cache recovery
Wed Dec 05 10:26:35 2012
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Wed Dec 05 10:26:35 2012
SMON: enabling tx recovery
Wed Dec 05 10:26:35 2012
Database Characterset is ZHS16GBK
Wed Dec 05 10:26:35 2012
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_548.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [2279045], [1], [2277120], [2277120], [], []
replication_dependency_tracking turned off (no async multimaster replication found)
Wed Dec 05 10:26:36 2012
Fatal internal error happened while SMON was doing active transaction recovery.
Wed Dec 05 10:26:36 2012
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_548.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [2279045], [1], [2277120], [2277120], [], []
SMON: terminating instance due to error 474

这个错误很明显:数据库已经open成功了，但是因为有事务不能正常被回滚，然后数据库的smon进程异常，从而使得数据库不能正常启动，解决该问题的方法也是很简单，就是常规的undo处理思路(使用人工undo管理,event屏蔽事务,隐含参数屏蔽回滚段)，然后重建undo表空间，这个时候可以结合txchecker来检测是否有异常事务:如果有重要基表对象异常,需要重建库;如果是个别其他对象异常,可以通过重建该对象解决

ORA-00600[qmxtriCheckAndRewriteQb0]

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

标题：ORA-00600[qmxtriCheckAndRewriteQb0]

数据库报ORA-00600[qmxtriCheckAndRewriteQb0]

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 = /u01/oracle/product/10.2.0
System name:	AIX
Node name:	abc
Release:	3
Version:	5
Machine:	00C58A644C00
Instance name: XFF2
Redo thread mounted by this instance: 2
Oracle process number: 434
Unix process pid: 492340, image: oracle@abc
*** ACTION NAME:() 2012-11-12 08:46:47.132
*** MODULE NAME:() 2012-11-12 08:46:47.132
*** SERVICE NAME:(ORCL) 2012-11-12 08:46:47.132
*** CLIENT ID:() 2012-11-12 08:46:47.132
*** SESSION ID:(870.58602) 2012-11-12 08:46:47.132
*** 2012-11-12 08:46:47.132
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [qmxtriCheckAndRewriteQb0], [], [], [], [], [], [], []
Current SQL statement for this session:
SELECT EXTRACTVALUE(配置,'//SYSTEM[@XTH="'||:B1 ||'"]/FILE') ,
WHERE EXTRACTVALUE(配置,'//SYSTEM[@XTH="'||:B1 ||'"]/BM')=:B2  AND ROWNUM<2
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
70000021d535f70        25  procedure ZLTOOLS.ZL_MBRUNLOG_INSERT
7000002b6819368         1  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              000000000 ? 000000000 ?
ksedmp+0290          bl       ksedst               104A2C690 ?
ksfdmp+0018          bl       03F26C3C
kgerinv+00dc         bl       _ptrgl
kgeasnmierr+004c     bl       kgerinv              7000002F735A838 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   0FFFFBFFF ?
IPRA.$qmxtriCheckAn  bl       03F25970
dRewriteQb_rec+0194
IPRA.$qmxtriCheckAn  bl       IPRA.$qmxtriCheckAn  1000881EC ? 000000000 ?
dRewriteQb_rec+006c           dRewriteQb_rec       000000000 ?
IPRA.$qmxtriCheckAn  bl       IPRA.$qmxtriCheckAn  FFFFFFFFFFF07E0 ? 000000033 ?
dRewriteQb_rec+006c           dRewriteQb_rec       1056037F8 ?
qmxtriCheckAndRewri  bcl      dmqlKMlod+00c0       000000000 ? 110421CB0 ?
teQb+0094                                          FFFFFFFFFFE87C0 ?
qmxtrxq+0210         bl       03F252EC
qmxtrxop+00a4        bl       qmxtrxq              FFFFFFFFFFF25B8 ?
                                                   700000282F66DD0 ? 110195E98 ?
koksspend+02b0       bl       qmxtrxop             100346AB4 ?
kkmdrvend+01a8       bl       koksspend            000000001 ? 104B3A8A8 ?
                                                   000000000 ?
kkmdrv+004c          bl       kkmdrvend            FFFFFFFFFFE8BE0 ?
                                                   883843401048F2F8 ?
opiSem+13c0          bl       kkmdrv               000000000 ? 000000000 ?
                                                   000000000 ? 11022AC50 ?
opiDeferredSem+0234  bl       opiSem               FFFFFFFFFFE9CE0 ?
                                                   7000001E327CCE0 ? 000000111 ?
                                                   100000001 ?
opitca+01e8          bl       opiDeferredSem
kksFullTypeCheck+00  bl       03F25230
1c
rpiswu2+034c         bl       _ptrgl
kksSetBindType+0d28  bl       rpiswu2              70000030850C178 ?
                                                   3300000033 ?
                                                   FFFFFFFFFFF0570 ?
                                                   FFFFFFFFFFF0578 ?
                                                   7000002F6F0C700 ?
                                                   33104027D8 ?
                                                   FFFFFFFFFFF1F48 ? 000000000 ?
kksfbc+1054          bl       kksSetBindType       70000030F58F400 ? 1107CB418 ?
                                                   70000001003B800 ?
                                                   10200003000 ? 110000FF8 ?
                                                   7000000100ECAB8 ?
                                                   FFFFFFFFFFF1480 ?
                                                   481A408400003000 ?
opiexe+098c          bl       01F960BC
opipls+185c          bl       opiexe               FFFFFFFFFFF3900 ?
                                                   FFFFFFFFFFF39E8 ?
                                                   FFFFFFFFFFF38A0 ?
opiodr+0ae0          bl       _ptrgl
rpidrus+01bc         bl       opiodr               66FFFF54B0 ? 608736A20 ?
                                                   FFFFFFFFFFF67C0 ?
                                                   1510195E98 ?
skgmstack+00c8       bl       _ptrgl
rpidru+0088          bl       skgmstack            102320840 ? 000000000 ?
                                                   000000002 ? 000000000 ?
                                                   FFFFFFFFFFF5F88 ?
rpiswu2+034c         bl       _ptrgl
rpidrv+095c          bl       rpiswu2              70000030850C178 ? 110469C28 ?
                                                   11044AA58 ? 000000000 ?
                                                   FFFFFFFFFFF5D60 ?
                                                   3300000000 ? 000000000 ?
                                                   000000000 ?
psddr0+02bc          bl       03F266D4
psdnal+01d0          bl       psddr0               1500000000 ? 6600000000 ?
                                                   FFFFFFFFFFF67C0 ?
                                                   30100BACC8 ?
pevm_EXECC+01f8      bl       _ptrgl
pfrinstr_EXECC+0070  bl       pevm_EXECC           10147B2A4 ? 000000000 ?
                                                   700000262828B72 ?
pfrrun_no_tool+005c  bl       _ptrgl
pfrrun+1014          bl       pfrrun_no_tool       FFFFFFFFFFF6B20 ?
                                                   7000002B6819368 ? 3100ECBB0 ?
plsql_run+06b4       bl       pfrrun               1107D84A8 ?
peicnt+0224          bl       plsql_run            1107D84A8 ? 10001102676F8 ?
                                                   000000000 ?
kkxexe+0250          bl       peicnt               FFFFFFFFFFF7E38 ? 1107D84A8 ?
opiexe+2ef8          bl       kkxexe               11047E1C8 ?
kpoal8+0edc          bl       opiexe               FFFFFFFFFFFB454 ?
                                                   FFFFFFFFFFFB1A8 ?
                                                   FFFFFFFFFFF9628 ?
opiodr+0ae0          bl       _ptrgl
ttcpip+1020          bl       _ptrgl
opitsk+1124          bl       01F96AC8
opiino+0990          bl       opitsk               0FFFFD490 ? 000000000 ?
opiodr+0ae0          bl       _ptrgl
opidrv+0484          bl       01F95914
sou2o+0090           bl       opidrv               3C02D99B7C ? 4A076D928 ?
                                                   FFFFFFFFFFFF390 ?
opimai_real+01bc     bl       01F93294
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0098         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------

通过这个trace的部分信息可以得到:
1.操作系统版本AIX x64(5.3)
2.数据库版本10.2.0.4
3.sql语句调用EXTRACTVALUE函数
4.Call Stack Trace信息

查询MOS[ID 467350.1]发现匹配信息

Cause
Bug 6030982 ORA-600 [QMXTRICHECKANDREWRITEQB0] WITH QUERY USING EXTRACTVALUE FUNCTION
Solution
This bug is going to be fixed in furture 10.2.0.5.0 and 11g
At the mean time , user can workaround by
set
event = "19027 trace name context forever, level 1"
within init.ora or spfile file then bounce database.
or
SQL> alter session set events ='19027 trace name context forever, level 1';
SQL> Alter system flush shared_pool;
-- Execute affected query

通过mos可以确定:
1.是因为数据库执行EXTRACTVALUE函数遇到该bug
2.在11g和10.2.0.5中修复该bug
3.可以通过设置event = “19027 trace name context forever, level 1″来临时解决该问题

个人处理建议
1.如果数据库方便升级,那建议升级处理
2.如果数据库不便立马升级,建议在业务低估时设置session event 19027,然后 flush shared_pool,执行报错sql,如果问题解决,在合适时间设置system event来临时屏蔽该问题.

expdp遭遇ORA-39006/ORA-39213故障解决

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

标题：expdp遭遇ORA-39006/ORA-39213故障解决

expdp导出数据遇到ORA-39006/ORA-39213错误,通过执行执行dbms_metadata_util.load_stylesheets解决
expdp工作异常

--导出awr信息
SQL> @?/rdbms/admin/awrextr.sql
…………
Exception encountered in AWR_EXTRACT
ORA-39006: internal error
ORA-39213: Metadata processing is not available
begin
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4710
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 656
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 962
ORA-06512: at line 3
--导出一个表
$ expdp "'/ as sysdba'" dumpfile=xifenfei.dmp tables=scott.t_xifenfei
Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 31 October, 2012 13:03:20
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

错误提示

$ oerr ora 39006
39006, 00000, "internal error"
// *Cause:  An unexpected error occurred while processing a Data Pump job.
//          Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS
//          will further describe the error.
// *Action: Contact Oracle Customer Support.
$ oerr ora 39213
39213, 00000, "Metadata processing is not available"
// *Cause:  The Data Pump could not use the Metadata API.  Typically,
//          this is caused by the XSL stylesheets not being set up properly.
// *Action: Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets
//          to reload the stylesheets.

解决ORA-39006/ORA-39213问题

--查询数据库已经安装组件
SQL> col COMP_NAME for a35
SQL> select comp_name, version, status from dba_registry;
COMP_NAME                           VERSION                        STATUS
----------------------------------- ------------------------------ ----------------------
Oracle Database Catalog Views       10.2.0.1.0                     VALID
Oracle Database Packages and Types  10.2.0.1.0                     VALID
Oracle Workspace Manager            10.2.0.1.0                     VALID
JServer JAVA Virtual Machine        10.2.0.1.0                     VALID
Oracle XDK                          10.2.0.1.0                     VALID
Oracle Database Java Packages       10.2.0.1.0                     VALID
Oracle Expression Filter            10.2.0.1.0                     VALID
Oracle Data Mining                  10.2.0.1.0                     VALID
Oracle Text                         10.2.0.1.0                     VALID
Oracle XML Database                 10.2.0.1.0                     VALID
Oracle Rules Manager                10.2.0.1.0                     VALID
Oracle interMedia                   10.2.0.1.0                     VALID
OLAP Analytic Workspace             10.2.0.1.0                     VALID
Oracle OLAP API                     10.2.0.1.0                     VALID
OLAP Catalog                        10.2.0.1.0                     VALID
Spatial                             10.2.0.1.0                     VALID
Oracle Enterprise Manager           10.2.0.1.0                     VALID
17 rows selected.
--如果缺少下面组件,使用下面对应的程序安装
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
--使用下面脚本安装（根据组件选择）
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/xdk/admin/initxml.sql
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/catjava.sql
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
--执行sys.dbms_metadata_util.load_stylesheets
SQL> execute sys.dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.

测试expdp导出

$ expdp "'/ as sysdba'" dumpfile=xifenfei.dmp tables=scott.t_xifenfei  Directory=AWR_DIR
Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 31 October, 2012 14:18:04
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01":  '/******** AS SYSDBA' dumpfile=xifenfei.dmp
tables=scott.t_xifenfei Directory=AWR_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 7 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T_XIFENFEI"                        5.374 MB   57376 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /data/enmotech/xifenfei.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:18:11

测试证明，在不缺少相关组件的情况下,使用dbms_metadata_util.load_stylesheets可以解决expdp导出报ORA-39006/ORA-39213错误；如果缺少组件,需要先安装对应组件,然后再执行dbms_metadata_util.load_stylesheets解决该问题

rman备份出现ORA-19625/ORA-27054解决

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

标题：rman备份出现ORA-19625/ORA-27054解决

RAC环境NFS挂载归档日志使用rman备份出现ORA-19625/ORA-27054错误分析
系统运行环境

OS:AIX 6100-06
DB:11.1.0.6.0 RAC
归档：挂载NFS

rman执行archive log时候报错

sql statement: alter system archive log  current
Starting backup at 25-OCT-11
current log archived
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/17/2012 13:03:51
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /rarchlogA/1_13775_764866137.dbf
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 6

这里由于RAC存放归档使用了NFS文件系统,在使用rman备份归档日志执行alter system archive log current的时候发生如下错误.

相关目录挂载情况

$ df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
…………
/dev/lv_arch      50.00     43.24   14%      166     1% /arch1
oradb2:/arch2     50.00     35.31   30%      463     1% /arch2
/dev/baklv        90.00     83.82    7%       10     1% /backup
$ mount:
…………
        /dev/lv_oracle   /oracle          jfs2   Oct 14 11:27 rw,log=/dev8
         /dev/lv_arch     /arch1           jfs2   Oct 14 11:27 rw,log=/dev8
     oradb2   /arch2           /arch2           nfs3   Oct 14 11:47

通过这里可以知道,这里使用默认的参数挂载NFS,从而使得NFS在rman工作时候不能正常工作。

错误原因

From  Oracle 10G R2 , Oracle checks the options with which a NFS mount is mounted on the filesystem.
and this is done to ensure that no corruption of the database can happen as incorrectly
mounted NFS volumes can result in data corruption.
There are no single set of NFS mount options that work across all Oracle platforms
Please ensure that you have the proper mount options specified by the NAS vendor /Vendor user guide
The exact checks used for an NFS mounted disk vary between platforms but in general
the basic checks will include the following checks
a) The mount table (eg; /etc/mnttab) can be read to check the mount options
b) The NFS mount is mounted with the &quot;hard&quot; option
c) The mount options include rsize&gt;=32768 and wsize&gt;=32768
d) For RAC environments, where NFS disks are supported, the &quot;noac&quot; mount option is used.

解决方案
1.临时解决方案
As suggested in the bug the workaround recommended is to use the Event 10298.
alter system set events ‘10298 trace name context forever, level 32’;

2.永久解决方案
具体见：http://www.xifenfei.com/3269.html

ORACLE 11.2.0.3 生成awr html文件报SYS.DBMS_WORKLOAD_REPOSITORY异常

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

标题：ORACLE 11.2.0.3 生成awr html文件报SYS.DBMS_WORKLOAD_REPOSITORY异常

在想分析数据库性能的关键时刻,突然发现awr不能正常的工作,那就和你上了战场突然发现枪没有子弹一样的郁闷,今天就遇到了11.2.0.3在win的环境中awr生成html不能正常工作.通过查询mos发现该问题出现在各种平台中(win,linux,aix等),提醒大家注意该问题.
数据库版本

SQL> SELECT * FROM V$VERSION;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

awr报错(html)

SQL> @?/rdbms/admin/awrrpt.sql
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 919
ORA-06512: 在 line 1

设置errorstack

SQL> alter session set events '6502 trace name errorstack level 12';
会话已更改。

分析错误

----- Error Stack Dump -----
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
----- Current SQL Statement for this session (sql_id=572fbaj0fdw2b) -----
select output from table(dbms_workload_repository.awr_report_html( :dbid,
                                                            :inst_num,
                                                            :bid, :eid,
                                                            :rpt_options ))
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
94348684       919  package body SYS.DBMS_WORKLOAD_REPOSITORY
983BAD54         1  anonymous block
----- Call Stack Trace -----
_skdstdst()+121      CALLrel  _kgdsdst()           19D99520 2
_ksedst1()+93        CALLrel  _skdstdst()          19D99520 0 1 485816 4863B2
                                                   485816
_ksedst()+49         CALLrel  _ksedst1()           0 1
_dbkedDefDump()+368  CALLrel  _ksedst()            0
6
_ksedmp()+44         CALLrel  _dbkedDefDump()      C 0
_dbkdaKsdActDriver(  CALLreg  00000000             C
)+4209
…………

通过查询mos发现Bug 13575143一致，可以确定是该bug,但是通过进一步测试证明不光是awrrpt会出现该错误,awr的相关报告中,只要是展示html结果的都有可能出现类此错误(比如awrrpti.sql/awrddrpt.sql/awrddrpi.sql等等).同时这里通过进一步分析发现其实该bug的起源是Bug 6458801(REPLACE on a CLOB can corrupt multibyte data ID 6458801.8),不过该bug说明已经在11.2.0.1中修复,其实通过这里的分析发现并没有真正的在11.2.0.3中修复该bug,针对该问题没有官方没有提供较好解决方法,只能是用过WORKAROUND来临时解决

They are able to generate the AWR report in the .txt format

large pool太小导致shared server异常

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

标题：large pool太小导致shared server异常

数据库出现如下错误

Fri Oct  5 09:33:54 2012
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20

重启后错误提示变为

Successfully onlined Undo Tablespace 1.
Fri Oct  5 09:34:41 2012
SMON: enabling tx recovery
Fri Oct  5 09:34:41 2012
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Fri Oct  5 13:53:50 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_25948.trc:
ORA-04031: unable to allocate 72 bytes of shared memory
("large pool","unknown object","session heap","trigger condition node")
Fri Oct  5 13:53:50 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_25948.trc:
ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], []
ORA-04031: unable to allocate 72 bytes of shared memory
("large pool","unknown object","session heap","trigger condition node")
Fri Oct  5 13:54:52 2012
found dead shared server 'S000', pid = (11, 1)
Fri Oct  5 17:25:59 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_31081.trc:
ORA-04031: unable to allocate 72 bytes of shared memory
("large pool","unknown object","session heap","trigger condition node")
Fri Oct  5 17:25:59 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_31081.trc:
ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], []
ORA-04031: unable to allocate 72 bytes of shared memory
("large pool","unknown object","session heap","trigger condition node")

通过这里的错误,我们可以看到是large pool不能分配72 bytes的连续内存空间而使得S000进程报错.那这两者有什么联系:我们知道S000是shared server的进程,那shared server为什么导致large pool不足呢？查询官方文档得出,如下三种情况会使用large pool

Session memory for the shared server and the Oracle XA interface
(used where transactions interact with more than one database)
I/O server processes
Oracle backup and restore operations

主要也就是shared server/parallel query buffers/backup restore这几个操作会使用到large pool.在该案例中很明显的可以看到是因为shared server进程需要分配large pool中一部分空间,而没有连续空间从而出现该错误.数据库相关参数配置

SQL> show parameter mts;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mts_circuits                         integer     555
mts_dispatchers                      string      (PROTOCOL=TCP) (SERVICE=oraXDB)
mts_listener_address                 string
mts_max_dispatchers                  integer     5
mts_max_servers                      integer     20
mts_multiple_listeners               boolean     FALSE
mts_servers                          integer     1
mts_service                          string      ora
mts_sessions                         integer     550
SQL> show parameter large;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 16777216

这里可以看出来,数据库明显配置了MTS,因为数据库在启动时候,最少会建立一个shared server进程,而这个时候因为large pool太小(16M),导致该进程无法正常建立,从而出现上述alert中相关错误,临时处理方法增加large pool.后续需要关注业务特点,考虑是否可以采用Oracle Dedicated server模式来处理.

ORA-00600[kccpb_sanity_check_2]

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

标题：ORA-00600[kccpb_sanity_check_2]

alert日志出现如下错误
数据库在mount的时候，因为出现ORA-00600[kccpb_sanity_check_2]错误导致数据库不能正常被mount成功

--sqlplus中报错
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 4294967296 bytes
Fixed Size                  2273256 bytes
Variable Size            1486573592 bytes
Database Buffers         2801795072 bytes
Redo Buffers                4325376 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-600: internal error code, arguments: [kccpb_sanity_check_2], [2825],
[2824], [0x000000000], [], [], [], []
--alert日志
Mon Sep 24 16:35:37 2012
ALTER DATABASE   MOUNT
Mon Sep 24 16:35:41 2012
Errors in file /opt/app/oracle/admin/lhgk/udump/lhgk_ora_17034.trc:
ORA-00600: internal error code, arguments: [kccpb_sanity_check_2], [2825], [2824], [0x000000000], [], [], [], []
Mon Sep 24 16:35:41 2012
ORA-600 signalled during: ALTER DATABASE   MOUNT...

错误原因

ORA-600 [kccpb_sanity_check_2] indicates that the seq# of the last read block is
higher than the seq# of the control file header block. This is indication of
the lost write of the header block during commit of the previous cf
transaction.

解决方法

1) restore a backup of a controlfile and recover
OR
2) recreate the controlfile
OR
3) restore the database from last good backup and recover
NOTE:  If you do not have any special backup of control file to restore and you are using Multiple Control File
copies in your pfile/init.ora/spfile you can attempt to mount the database using each control file one by one.
If you are able to mount the database with any of these control file copies you can then issue
'alter database backup controlfile to trace' to recreate controlfile.

分类目录归档：ORA-xxxxx