-
加我微信
加我QQ
标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 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)
- 操作系统 (100)
- 数据库 (1,597)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (17)
- PostgreSQL恢复 (5)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
月归档：一月 2013
aix使用太多内存导致shared pool 相关latch异常
某客户有一服务器,shared pool 相关latch出现异常等待,影响系统性能.分析结果:因为系统空闲内存太少,使用太多Paging Space导致该异常;解决办法:1.增加内存，2.在业务接受范围内减小sga等其他和内存消耗相关参数
nmon查看剩余内存
x Physical PageSpace | pages/sec In Out | FileSystemCache x% Used 99.8% 34.9% | to Paging Space 0.0 0.0 | (numperm) 14.8% x% Free 0.2% 65.1% | to File System 0.0 33.0 | Process 63.9% xMB Used 21452.8MB 11446.1MB | Page Scans 0.0 | System 21.1% xMB Free (少)-->51.2MB 21321.9MB | Page Cycles 0.0 | Free 0.2% xTotal(MB) 21504.0MB 32768.0MB | Page Steals 0.0 | ------
topas查看内存配置
Disk Busy% KBPS TPS KB-Read KB-Writ MEMORY
Topas Monitor for host: p570b03 EVENTS/QUEUES FILE/TTY
Wed Jan 9 13:30:30 2013 Interval: 2 Cswitch 785 Readch 173.1K
Syscall 54407 Writech 213.1K
CPU User% Kern% Wait% Idle% Reads 118 Rawin 0
ALL 43.6 1.7 0.0 54.8 Writes 110 Ttyout 352
Forks 0 Igets 0
Network KBPS I-Pack O-Pack KB-In KB-Out Execs 0 Namei 5
Total 84.5 146.0 200.6 26.4 58.1 Runqueue 0.5 Dirblk 0
Waitqueue 0.0
Disk Busy% KBPS TPS KB-Read KB-Writ MEMORY
Total 0.0 164.6 17.0 0.0 164.6 PAGING Real,MB 21504
Faults 12408 % Comp 86 <---大部分计算内存
FileSystem KBPS TPS KB-Read KB-Writ Steals 0 % Noncomp 13 <---fs cache较少
Total 316.3 17.9 151.5 164.9 PgspIn 0 % Client 13
PgspOut 0
Name PID CPU% PgSp Owner PageIn 0 PAGING SPACE
oracle 6357252 16.7 8.4 oracle PageOut 42 Size,MB 32768
oracle 15401474 8.3 70.8 oracle Sios 42 % Used 35 <---使用比较多
oracle 12714542 8.3 8.3 oracle % Free 65
oracle 5767556 8.3 8.3 oracle NFS (calls/sec)
oracle 5898996 8.3 134.9 oracle SerV2 0 WPAR Activ 0
oracle 17629634 8.3 134.9 oracle CliV2 0 WPAR Total 0
oracle 13959694 0.0 8.4 oracle SerV3 0 Press: "h"-help
oracle 5439860 0.0 134.3 oracle CliV3 0 "q"-quit
内存参数配置
vmo -F -a --数据库相关参数 minperm% = 3 v_pinshm = 0 lru_file_repage = 0 maxclient% = 90 maxperm% = 90 strict_maxclient = 1 strict_maxperm = 0 page_steal_method = 1
因为是AIX 6.1,这里的vmo配置基本上是oracle 推荐值(大页没有配置,非必须选项)
会话进程占用内存
procmap 15466998
15466998 : oraclewasudb (LOCAL=NO)
100000000 97466K read/exec oracle
11000088d 2430K read/write oracle
9fffffff0000000 51K read/exec /usr/ccs/bin/usla64
9fffffff000cfe2 0K read/write /usr/ccs/bin/usla64
900000000b14930 2K read/exec /usr/lib/libC.a[shr3_64.o]
9001000a0122930 0K read/write /usr/lib/libC.a[shr3_64.o]
900000000af5b00 118K read/exec /usr/lib/libC.a[shrcore_64.o]
9001000a0319100 12K read/write /usr/lib/libC.a[shrcore_64.o]
900000000ad7000 118K read/exec /usr/lib/libC.a[ansicore_64.o]
9001000a030fe00 36K read/write /usr/lib/libC.a[ansicore_64.o]
900000000411468 0K read/exec /usr/lib/libicudata.a[shr_64.o]
9001000a0121468 0K read/write /usr/lib/libicudata.a[shr_64.o]
90000000040f738 2K read/exec /usr/lib/libC.a[shr2_64.o]
9001000a0323738 0K read/write /usr/lib/libC.a[shr2_64.o]
9000000008ec800 1699K read/exec /usr/lib/libC.a[ansi_64.o]
9001000a0324a00 277K read/write /usr/lib/libC.a[ansi_64.o]
9000000008c9b00 135K read/exec /usr/lib/libC.a[shr_64.o]
9001000a031db00 19K read/write /usr/lib/libC.a[shr_64.o]
900000000708180 1732K read/exec /usr/lib/libicuuc.a[shr_64.o]
9001000a036bdac 180K read/write /usr/lib/libicuuc.a[shr_64.o]
900000000493d80 2510K read/exec /usr/lib/libicui18n.a[shr_64.o]
9001000a0399148 270K read/write /usr/lib/libicui18n.a[shr_64.o]
900000000473200 91K read/exec /usr/lib/libsrc.a[shr_64.o]
9001000a01127a8 55K read/write /usr/lib/libsrc.a[shr_64.o]
90000000045a300 98K read/exec /usr/lib/libcorcfg.a[shr_64.o]
9001000a04147c8 18K read/write /usr/lib/libcorcfg.a[shr_64.o]
900000000b16200 750K read/exec /usr/lib/liblvm.a[shr_64.o]
9001000a03dd028 219K read/write /usr/lib/liblvm.a[shr_64.o]
900000000444f00 82K read/exec /usr/lib/libcfg.a[shr_64.o]
9001000a027b8f0 26K read/write /usr/lib/libcfg.a[shr_64.o]
90000000040e3a0 2K read/exec /usr/lib/libcrypt.a[shr_64.o]
9001000a0106948 0K read/write /usr/lib/libcrypt.a[shr_64.o]
90000000233c860 5K read/exec /usr/lib/libc.a[aio_64.o]
9001000a0437568 0K read/write /usr/lib/libc.a[aio_64.o]
9000000003efc00 120K read/exec /usr/lib/libodm.a[shr_64.o]
9001000a0107cc8 40K read/write /usr/lib/libodm.a[shr_64.o]
900000000bd2c80 147K read/exec /usr/lib/libperfstat.a[shr_64.o]
9001000a041a960 14K read/write /usr/lib/libperfstat.a[shr_64.o]
900000000bf8000 0K read/exec /usr/lib/libdl.a[shr_64.o]
9001000a041f000 0K read/write /usr/lib/libdl.a[shr_64.o]
9000000024ac100 8680K read/exec /oracle/product/10g/lib/libjox10.a[shr.o]
8001000a0000ca0 588K read/write /oracle/product/10g/lib/libjox10.a[shr.o]
900000000a96000 257K read/exec /usr/lib/libpthreads.a[shr_xpg5_64.o]
9001000a0283000 559K read/write /usr/lib/libpthreads.a[shr_xpg5_64.o]
900000000000800 4025K read/exec /usr/lib/libc.a[shr_64.o]
9001000a0000020 1047K read/write /usr/lib/libc.a[shr_64.o]
Total 123902K
在上表中，标记为read/write的内存即是进程的私有内存，每个会话大概占用内存近6M,数据库大概有80多个会话,占用内存大概,占用内存大概500M左右.
数据库参数配置
SQL> select sum(PGA_ALLOC_MEM)/1024/1024/1024,count(*) from v$process;
SUM(PGA_ALLOC_MEM)/1024/1024/1024 COUNT(*)
--------------------------------- ----------
2.46758329 84
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 500M
pga_aggregate_target big integer 2000M
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 12000M
sga_target big integer 10000M
SQL> show sga;
Total System Global Area 1.2583E+10 bytes
Fixed Size 2117744 bytes
Variable Size 7600082832 bytes
Database Buffers 4966055936 bytes
Redo Buffers 14655488 bytes
当前系统整体ORACLE使用内存汇总：sga 12G+pga 2.5G+process 0.5G,大概占用内存15G,留给系统内存6G左右,系统使用大量交换分区,导致系统性能下降,最明显的为:shared pool相关latch等待异常,具体awr为:
因为系统因为个别session需要大量内存设置_pga_max_size参数,导致部分会话系统占用2.5g内存,建议设置该参数为默认值,并对个别会话独立设置,设置pga_aggregate_target=1.5G,sga_target=sga_max_size=8.5G,awr结果为:
发表在 Oracle性能优化
3 条评论
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)