-
加我微信(17813235971)
加我QQ(107644445)
标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-01595 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (103)
- 数据库 (1,768)
- DB2 (22)
- MySQL (77)
- Oracle (1,609)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (166)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (69)
- Oracle Bug (8)
- Oracle RAC (54)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (591)
- Oracle安装升级 (97)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (86)
- PostgreSQL (30)
- pdu工具 (6)
- PostgreSQL恢复 (9)
- SQL Server (32)
- SQL Server恢复 (13)
- TimesTen (7)
- 达梦数据库 (3)
- 达梦恢复 (1)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (39)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (22)
-
最近发表
- ORA-600 kokiasg1故障分析(obj$中核心字典序列全部被恶意删除)
- ORA-00756 ORA-10567故障数据0丢失恢复
- 数据库文件变成32k故障恢复
- tcp连接过多导致监听TNS-12532 TNS-12560 TNS-00502错误
- 文件系统格式化MySQL数据库恢复
- .sstop勒索加密数据库恢复
- 解决一次硬件恢复之后数据文件0kb的故障恢复case
- Error in invoking target ‘libasmclntsh19.ohso libasmperl19.ohso client_sharedlib’问题处理
- ORA-01171: datafile N going offline due to error advancing checkpoint
- linux环境oracle数据库被文件系统勒索加密为.babyk扩展名溯源
- ORA-600 ksvworkmsgalloc: bad reaper
- ORA-600 krccfl_chunk故障处理
- Oracle Recovery Tools恢复案例总结—202505
- ORA-600 kddummy_blkchk 数据库循环重启
- 记录一次asm disk加入到vg通过恢复直接open库的案例
- CHECKDB 发现了 N 个分配错误和 M 个一致性错误
- 达梦数据库dm.ctl文件异常恢复
- Oracle Recovery Tools修复ORA-00742、ORA-600 ktbair2: illegal inheritance故障
- 可能是 tempdb 空间用尽或某个系统表不一致故障处理
- 11.2.0.4库中遇到ORA-600 kcratr_nab_less_than_odr报错
分类目录归档：ORA-xxxxx
创建控制文件遭遇ORA-600 kccscf_1
数据库版本10.2.0.1创建控制文件出现ORA-00600[kccscf_1]错误
SQL> CREATE CONTROLFILE REUSE DATABASE "ITPLUSDW" RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292000 7 LOGFILE 8 GROUP 1 '/backup/oracle/oradata/redo01.log' SIZE 50M, 9 GROUP 2 '/backup/oracle/oradata/redo02.log' SIZE 50M, 10 GROUP 3 '/backup/oracle/oradata/redo03.log' SIZE 50M 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/backup/oracle/oradata/system01.dbf', 14 '/backup/oracle/oradata/undotbs01.dbf', 15 '/backup/oracle/oradata/sysaux01.dbf', 16 '/backup/oracle/oradata/users01.dbf', 17 '/backup/oracle/oradata/itplus_data', 18 '/backup/oracle/oradata/invertories_data', 19 '/backup/oracle/oradata/inventories_data01', 20 '/backup/oracle/oradata/itplus_discover', 21 '/backup/oracle/oradata/inventories_data02', 22 '/backup/oracle/oradata/inventories_data03', 23 '/backup/oracle/oradata/inventories_data05', 24 '/backup/oracle/oradata/inventories_data04' 25 CHARACTER SET ZHS16CGB231280 26 ; CREATE CONTROLFILE REUSE DATABASE "ITPLUSDW" RESETLOGS ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-00600: internal error code, arguments: [kccscf_1], [9], [292000], [65535],[], [], [], []
alert日志错误
Default Temporary Tablespace will be necessary for a locally managed database in future release Tue Feb 26 15:49:36 2013 Errors in file /backup/oracle/admin/udump/itplustest_ora_25001.trc: ORA-00600: internal error code, arguments: [kccscf_1], [9], [292000], [65535], [], [], [], [] Tue Feb 26 15:49:36 2013 Errors in file /backup/oracle/admin/udump/itplustest_ora_25001.trc: ORA-00600: internal error code, arguments: [kccscf_1], [9], [292000], [65535], [], [], [], [] ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "ITPLUSDW" NORESETLOGS ARCHIVELOG
查询mos发现Create Control File fails with ORA-600 [kccscf_1] [ID 387210.1],中有描述，该bug在10.2.0.1到11.1.0.6版本中,如果MAXLOGHISTORY超过了65535均有可能出现，在10.2.0.4和11.1.0.6中修复，部分版本有Patch 4877360修复该问题。在不能升级或者没有patch的情况下，解决该问题的思路是设置MAXLOGHISTORY为65535或者更小值,重建控制文件
SQL TUNING导致ORA-07445[qsmmixComputeClusteringFactor()+386]
在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故障
遇到一个案例在数据库启动的时候报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)