-
加我公众号(orasos)
加我微信(17813235971)
加我QQ(107644445)
标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 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-600 kcratr_nab_less_than_odr 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 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,879)
- DB2 (22)
- MySQL (82)
- Oracle (1,705)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (72)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (659)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- Oracle 19c 202610补丁（RUs+OJVM）-19.33
- 由于con$.con#值达到极限，触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
- OBET-Oracle Block Editor Tool使用说明
- 几乎动用了所有手段的Oracle故障恢复
- Oracle Block Edit Tool (obet) 功能增强–2026.07
- Oracle 19c 202607补丁（RUs+OJVM）-19.32
- Patch_SCN快速修复ORA-01555数据库open故障
- 快速处理 ORA-01210: data file header is media corrupt 故障
分类目录归档：Oracle
ORA-600 kghstack_underflow_internal_2
aix平台运行11.2.0.4 rac,突然一个节点crash,lms2进程报ORA-600 kghstack_underflow_internal_2错误
Thu Aug 03 18:43:16 2023 Errors in file /u01/oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_lms2_2884404.trc (incident=761244): ORA-00600: internal error code, arguments: [kghstack_underflow_internal_2], [0x11074D658], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/oracle/app/oracle/diag/rdbms/xff/xff2/incident/incdir_761244/xff2_lms2_2884404_i761244.trc Errors in file /u01/oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_lms2_2884404.trc (incident=761245): ORA-00600: internal error code, arguments: [kghstack_underflow_internal_2], [0x11AB5BBF0], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [kghstack_underflow_internal_2], [0x11074D658], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/oracle/app/oracle/diag/rdbms/xff/xff2/incident/incdir_761245/xff2_lms2_2884404_i761245.trc Thu Aug 03 18:43:19 2023 Dumping diagnostic data in directory=[cdmp_20230803184319], requested by (instance=2, osid=2884404 (LMS2)), summary=[incident=761245]. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Thu Aug 03 18:43:23 2023 Sweep [inc][761245]: completed Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /u01/oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_lms2_2884404.trc: ORA-00600: internal error code, arguments: [kghstack_underflow_internal_2], [0x11074D658], [], [], [], [], [], [], [], [], [], [] Sweep [inc][761244]: completed Sweep [inc2][761245]: completed Sweep [inc2][761244]: completed Thu Aug 03 18:43:29 2023 Errors in file /u01/oracle/app/oracle/diag/rdbms/xff/xff2/trace/xff2_lms2_2884404.trc: ORA-00600: internal error code, arguments: [kghstack_underflow_internal_2], [0x11074D658], [], [], [], [], [], [], [], [], [], [] LMS2 (ospid: 2884404): terminating the instance due to error 484
分析trace文件中的Call Stack Trace信息
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+40 bl 0000000109B3EE38 000000000 ? 000000001 ?
000000003 ? 000000000 ?
000000000 ? 000000001 ?
000000003 ? 000000000 ?
ksedst1()+112 call skdstdst() 1777D9901C4FD34D ?
4840284100000000 ?
FFFFFFFFFFECE20 ?
2A501377F67A7 ? 10A742204 ?
000000000 ? 1107486C0 ?
2050033FFFECE28 ?
ksedst()+40 call ksedst1() FFFFFFFFFFFE0002 ?
0000060F1 ? 000000001 ?
10A46AD18 ? 000000000 ?
000000000 ? 000002004 ?
000000001 ?
dbkedDefDump()+1516 call ksedst() 000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 300000003 ?
ksedmp()+72 call dbkedDefDump() 3107486C0 ? 110000A28 ?
FFFFFFFFFFED630 ? 1106ABC70 ?
100125778 ? FFFFFFFFFFED5B0 ?
FFFFFFFFFFEDA30 ? 1106ABC70 ?
ksfdmp()+100 call ksedmp() 000000002 ? 000000000 ?
000000002 ? 10AF71A68 ?
10A0720F8 ? 000000000 ?
1108EC608 ? 1107486C0 ?
dbgexPhaseII()+1904 call ksfdmp() FFFFFFFFFFFE0002 ?
0000060F1 ? 000000002 ?
000000000 ? 000000002 ?
10A0720F0 ? 000000000 ?
001050005 ?
dbgexProcessError() call dbgexPhaseII() 1107486C0 ? 1108EFB28 ?
+1556 0000B9D9D ? 200000000 ?
FFFFFFFFFFEE548 ? 000000104 ?
FFFFFFFFFFEDBB0 ?
FB400000000 ?
dbgeExecuteForError call dbgexProcessError() 1107486C0 ? 1108EC608 ?
()+72 100000000 ? 000000000 ?
FFFFFFFFFFF29E0 ?
2840288000000012 ?
10013DA4C ? 1108EE350 ?
dbgePostErrorKGE()+ call dbgeExecuteForError 000000002 ? 000000128 ?
2044 () FFFFFFFFFFFE0002 ?
215265335E5162 ?
3726000000000001 ?
10A46AD18 ? 10A46CB00 ?
FFFFFFFFFFF1D30 ?
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 000000001 ? 10A46AD18 ?
68 25800000000 ? 109E7A740 ?
000000000 ? 000000038 ?
FFFFFFFFFFF2800 ? 11AB1AC50 ?
kgeadse()+380 call dbkePostKGE_kgsf() 900000000512C74 ?
9001000A008DAD0 ? 000000000 ?
9001000A008DAD0 ?
8000000FFFF2C40 ?
7000147E8F28C98 ? 400000008 ?
1100054A0 ?
kgerinv_internal()+ call kgeadse() 7FFFFFFFFFFFFFFF ?
48 FFFFFFFFFFFEF8FF ?
000000019 ? 110476528 ?
000000001 ? 000000017 ?
00000000B ? 000000000 ?
kgerinv()+48 call kgerinv_internal() FFFFFFFFFFFEF8FF ?
FFFFFFFFFFFFFFFF ?
FFFFFFFFFFFFFFFF ?
7FFFFFFFFFFFFFFF ?
1001648E0 ? FFFFFFFFFFF25E0 ?
1106ABC70 ? 11073B3C0 ?
kgeasnmierr()+72 call kgerinv() 000000000 ? 215265335E5162 ?
372600383A0F5000 ?
000000004 ? 10A328F7C ?
FFFFFFFFFFF2898 ? 000000002 ?
0FFFFFFFF ?
kghstack_underflow_ call kgeasnmierr() 11AB967A0 ? 000000000 ?
internal()+280 FFFFFFFFFFF2860 ? 100000001 ?
000000002 ? 11AB5BBF0 ?
000000000 ? 11AB96778 ?
kghstack_free()+716 call kghstack_underflow_ 10A328F7C ? 110A2FEC0 ?
internal() 000000004 ? 000000000 ?
000000000 ? 000000000 ?
000000080 ? 80000000000000 ?
ktudda()+912 call kghstack_free() 11AB5BBF0 ? 7215265335E5162 ?
3726000000000008 ?
000000102 ? 109E747E0 ?
FFFFFFFFFFF2A90 ? 000000048 ?
28408880FFFFFFFF ?
kcbtdu()+1636 call ktudda() 70001383A0F4014 ? 000000000 ?
1FE800000000 ? 07F7F7F7F ?
FFFFFFFF80808080 ?
000000000 ? 000000030 ?
FFFFFFFFFFF2B30 ?
kcbzdh()+3200 call kcbtdu() 35900000359 ? 100000001 ?
000000001 ? 200000001 ?
000000001 ? 00000005D ?
200066665D20 ? 000000000 ?
kcbzpnd()+504 call kcbzdh() 70001383F6D64B8 ? 000002004 ?
2107486C0 ? 10A74269E ?
1107486C0 ? FFFFFFFFFFF3B30 ?
FFFFFFFFFFF38E0 ? 000000000 ?
kcbdnb()+724 call kcbzpnd() 10A74267C ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 0001CE860 ?
000000000 ? 000000000 ?
dbkedDefDump()+5528 call kcbdnb() 200000000 ? 000000000 ?
000000000 ? 000000000 ?
1100224D0 ? 000000018 ?
110001366 ? 000000000 ?
ksedmp()+72 call dbkedDefDump() 3107486C0 ? 110000A28 ?
FFFFFFFFFFF3FC0 ? 1106ABC70 ?
100125778 ? 000000000 ?
FFFFFFFFFFF3FB0 ? 1106ABC70 ?
ksfdmp()+100 call ksedmp() 000000002 ? 000000000 ?
000000002 ? 10AF71A68 ?
10A0720F8 ? 000000000 ?
1109DE650 ? 1107486C0 ?
dbgexPhaseII()+1904 call ksfdmp() 11074B65C ? 000000001 ?
000000002 ? 000000000 ?
000000002 ? 10A0720F0 ?
000000000 ? 001050005 ?
dbgexProcessError() call dbgexPhaseII() 1107486C0 ? 1109DC860 ?
+1556 0000B9D9C ? 200000000 ?
FFFFFFFFFFF4ED8 ? 000000082 ?
FFFFFFFFFFF4560 ?
88A4422A00000000 ?
dbgeExecuteForError call dbgexProcessError() 1107486C0 ? 1109DE650 ?
()+72 100000000 ? 000000000 ?
000000000 ? 000000000 ?
0DFFFFFFF ? 1109E0398 ?
dbgePostErrorKGE()+ call dbgeExecuteForError 00000000A ? 000000000 ?
2044 () 000000001 ? 000000001 ?
000000000 ? 000000000 ?
FFFFFFFFFFFB4E0 ? 000000000 ?
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 000000000 ? FFFFFFFFFFF96B0 ?
68 2580000000A ? 109E7A740 ?
000000000 ? 000000000 ?
FFFFFFFFFFF9190 ? 11AB1AC50 ?
kgeadse()+380 call dbkePostKGE_kgsf() 000000001 ? 000000008 ?
000000000 ? 10A30EA38 ?
110000C20 ? 700014771160D68 ?
700014772ADB3A8 ? 000000001 ?
kgerinv_internal()+ call kgeadse() 000000003 ? 000000000 ?
48 11074B65C ? 000000001 ?
000000000 ? FFFFFFFFFFF96B0 ?
00000000A ? 000000001 ?
kgerinv()+48 call kgerinv_internal() 000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
kgeasnmierr()+72 call kgerinv() 000000000 ? 000000000 ?
000000000 ? 000000000 ?
FFFFFFFFFFF92B0 ?
48102840FFFFA5B0 ?
11AB5BBB8 ? 11074D658 ?
kghstack_underflow_ call kgeasnmierr() 022028200 ? 022202820 ?
internal()+280 11AB5BBB8 ? 100000001 ?
000000002 ? 11074D658 ?
0442C2394 ? 000002000 ?
kghstack_free()+716 call kghstack_underflow_ FFFFFFFFFFF92B0 ?
internal() FFFFFFFFFFF95B8 ?
FFFFFFFFFFF92B0 ? 000000001 ?
FFFFFFFFFFF92B0 ?
FFFFFFFFFFF95E8 ?
FFFFFFFFFFF95B8 ? 11074B650 ?
ktundo()+924 call kghstack_free() 0DEADBEEF ? 11074D668 ?
11074B654 ? 300000000 ?
1FFFFB4E0 ? FFFFFFFFFFFB4E0 ?
FFFFFFFFFFF94C0 ?
FFFFFFFFFFF9470 ?
kturCRBackoutOneChg call ktundo() 19FFFFB5E0 ?
()+848 494CEDB3FFFF9E50 ?
FFFFFFFFFFF9E48 ? 000000000 ?
000000000 ? FFFFFFFFFFFA5B0 ?
100000000 ? FFFFFFFFFFFB4E0 ?
ktrgcm()+5816 call kturCRBackoutOneChg FFFFFFFFFFFA5B0 ?
() 19FFFFA440 ?
FFFFFFFFFFFA5B8 ? 000000000 ?
1FFFFA478 ? FFFFFFFFFFFB4E0 ?
000000000 ? 000000000 ?
ktrget3()+832 call ktrgcm() FFFFFFFFFFFAC80 ? 000000000 ?
000000000 ? 000000003 ?
058F7501F ? 000000001 ?
000000004 ? 000000003 ?
ktrget2()+104 call ktrget3() 000000002 ? 700000000014488 ?
7000147E9C41A50 ? 000000022 ?
110A123A0 ? 000000000 ?
FFFFFFFFFFFB080 ? 110A123B8 ?
kclgeneratecr()+654 call ktrget2() FFFFFFFFFFFB4D0 ? 110AA1610 ?
0 14F11E4E00 ? 0F11E4E00 ?
357FED028 ? 000030000 ?
7000147E9C41A50 ?
700000000014488 ?
kclgcr()+812 call kclgeneratecr() 11A209508 ? FFFFFFFFFFFBFC0 ?
FFFFFFFFFFFBC18 ? 000000000 ?
0FFFFBB10 ? 01A275AC8 ?
1761D7F302ED25AC ?
20000011A275AC8 ?
kclcrrf()+536 call kclgcr() FFFFFFFFFFFBC20 ?
FFFFFFFFFFFBD00 ? 101F5080C ?
000000000 ? 0000003E8 ?
000000028 ? 0000000C8 ?
FFFFFFFFFFFBF88 ?
kjblcrcbk()+896 call kclcrrf() 000000001 ? 000000000 ?
7000147EB0F07B8 ?
7000147576C4471 ?
401472C30C7F0 ?
7000147576C4408 ?
7000147576C3190 ?
7000147576C7170 ?
kjblpcr()+304 call kjblcrcbk() FFFFFFFFFFFBDA8 ? 000000038 ?
7000147FABBDB48 ? 600000006 ?
000000016 ? 11A209468 ?
000000013 ? 0001C2153 ?
kjbmpbast()+1792 call kjblpcr() 000000012 ? 000000168 ?
000000002 ? 70001109FDB8148 ?
357000000000357 ?
7000144F31F7750 ?
895000000000895 ? 000000000 ?
kjmxmpm()+760 call kjbmpbast() 1000000000000 ? 80000001E ?
000000000 ? 11A2951C8 ?
C000000000 ? 000000000 ?
1000000000000 ? 000000000 ?
kjmpbmsg()+3508 call kjmxmpm() 000000000 ? 11A3769E0 ?
FFFFFFFFFFFC380 ? 06DBFBAEF ?
101E13820 ? 11A3769E0 ?
7000147E339AE08 ?
FFFFFFFFFFFC210 ?
kjmsm()+13416 call kjmpbmsg() 11A209448 ? 7000147E339AE08 ?
100000019 ? 100000000 ?
000000000 ? 000000000 ?
000000000 ? 7000000000168FD ?
ksbrdp()+2216 call kjmsm() 7000000000168E0 ?
7000000000168FC ? 048244028 ?
000000E00 ? 1108B69F0 ?
100637768 ? 000000001 ?
700000007 ?
opirip()+1620 call ksbrdp() FFFFFFFFFFFFE22 ? 10AFA5FC8 ?
FFFFFFFFFFFDC10 ? 000000000 ?
000000001 ? 000000000 ?
01380038F ? 000000001 ?
opidrv()+608 call opirip() 10AFA23B0 ? 410134118 ?
FFFFFFFFFFFED80 ?
2F7530312F ? 108A7E8C4 ?
1106ABC70 ?
652F70726F647563 ?
1106ABC70 ?
sou2o()+136 call opidrv() 3208A885B0 ? 400000000 ?
FFFFFFFFFFFED80 ?
23001801CD0000 ? 000000010 ?
1106ABC70 ? 000000000 ?
000000000 ?
opimai_real()+188 call sou2o() FFFFFFFFFFFEDF0 ?
4424444B00000001 ?
9000000000D73CC ?
BADC0FFEE0DDF00D ?
000000003 ? 9001000A008DAD0 ?
A0000000A000000 ? 10B6A8F30 ?
ssthrdmain()+276 call opimai_real() 9001000A0011A60 ?
FFFFFFFFFFFF148 ?
FFFFFFFFFFFEEF0 ? 10B6E9280 ?
90000000008582C ?
9001000A008DAD0 ?
FFFFFFFFFFFEED0 ?
9001000A008DAD0 ?
main()+204 call ssthrdmain() 3F0003660 ? FFFFFFFFFFFF238 ?
FFFFFFFFFFFF2A0 ?
9FFFFFFF000D658 ?
9FFFFFFF00009A0 ? 000000000 ?
000000000 ? 9FFFFFFF000D658 ?
__start()+112 call main() 000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
查询mos对比相关信息,参考: LMON or LMS Process Crashes Instance With ORA-600 [kghstack_underflow_internal_2] (Doc ID 2003278.1)信息
The LMON or LMS process crash the instance with an error like: ORA-00600: internal error code, arguments: [kghstack_underflow_internal_2], [0x110A10838], [], [], [], [], [], [], [], [], [], [] ORA-1092 : opitsk aborting process Instance terminated by LMS1, pid = 14024818 Review of the generated tracefiles reveals a call stack similar to: ... kghstack_underflow_internal kghstack_free kccgrd kjxgrf_rr_read kjxgrDD_rr_read kjxgrimember kjxggpoll kjfmact kjfdact kjfcln ksbrdp ... - OR - ... kghstack_underflow_internal kghstack_free ktundo kturcrbackoutonechg ktrgcm ktrget3 ktrget2 kclgcr ...
确认为Bug 18687067 – ORA-600 [KGHSTACK_UNDERFLOW_INTERNAL_2] closed as duplicate of Bug 20675347 – ORA-07445 [KGHSTACK_OVERFLOW_INTERNAL()+644]（The bug is caused by an AIX compiler issue causing volatile variables in the Oracle kernel not to be handled properly.）,解决方案升级数据库到12.1及其以上版本或者打上patch 20675347
WRH$_LATCH, WRH$_SYSSTAT, WRH$_PARAMETER对象较大
通过awrinfo查看发现sysaux中以下对象大小属于top N
********************************** (3b) Space usage within AWR Components (> 500K) ********************************** COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE --------- --------- --------------------------------------------------------------------- --------------- FIXED 136.0 WRH$_PARAMETER_PK.WRH$_PARAME_1600597976_0 - 68% INDEX PARTITION FIXED 128.0 WRH$_LATCH.WRH$_LATCH_1600597976_0 - 98% TABLE PARTITION FIXED 104.0 WRH$_PARAMETER.WRH$_PARAME_1600597976_0 - 97% TABLE PARTITION FIXED 88.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_1600597976_0 - 99% INDEX PARTITION FIXED 88.0 WRH$_SYSSTAT.WRH$_SYSSTA_1600597976_0 - 90% TABLE PARTITION FIXED 80.0 WRH$_LATCH_PK.WRH$_LATCH_1600597976_0 - 99% INDEX PARTITION
查新mos发现类似文档:WRH$_LATCH, WRH$_SYSSTAT, and WRH$_PARAMETER Consume the Majority of Space within SYSAUX (Doc ID 2099998.1)
对应的bug为：Bug 14084247 – ORA-1555 or ORA-12571 Failed AWR purge can lead to continued SYSAUX space use (Doc ID 14084247.8)
处理操作
SQL> SELECT COUNT(1) HOW_MANY 2 FROM sys.WRH$_PARAMETER a 3 WHERE NOT EXISTS 4 (SELECT 1 5 FROM sys.wrm$_snapshot 6 WHERE snap_id = a.snap_id 7 AND dbid = a.dbid 8 AND instance_number = a.instance_number 9 ); HOW_MANY ---------- 2406788 SQL> DELETE FROM sys.WRH$_LATCH a 2 WHERE NOT EXISTS 3 (SELECT 1 4 FROM sys.wrm$_snapshot b 5 WHERE b.snap_id = a.snap_id 6 AND dbid=(SELECT dbid FROM v$database) 7 AND b.dbid = a.dbid 8 AND b.instance_number = a.instance_number); 已删除2411808行。 SQL> SQL> DELETE FROM sys.WRH$_SYSSTAT a 2 WHERE NOT EXISTS 3 (SELECT 1 4 FROM sys.wrm$_snapshot b 5 WHERE b.snap_id = a.snap_id 6 AND dbid=(SELECT dbid FROM v$database) 7 AND b.dbid = a.dbid 8 AND b.instance_number = a.instance_number); 已删除2747472行。 SQL> SQL> DELETE FROM sys.WRH$_PARAMETER a 2 WHERE NOT EXISTS 3 (SELECT 1 4 FROM sys.wrm$_snapshot b 5 WHERE b.snap_id = a.snap_id 6 AND dbid=(SELECT dbid FROM v$database) 7 AND b.dbid = a.dbid 8 AND b.instance_number = a.instance_number); 已删除2406788行。 SQL> SQL> COMMIT; 提交完成。 SQL> ALTER TABLE WRH$_LATCH ENABLE ROW MOVEMENT; 表已更改。 SQL> ALTER TABLE WRH$_LATCH SHRINK SPACE COMPACT; 表已更改。 SQL> ALTER TABLE WRH$_LATCH SHRINK SPACE; 表已更改。 SQL> ALTER TABLE WRH$_LATCH SHRINK SPACE CASCADE; 表已更改。 SQL> SQL> ALTER TABLE WRH$_PARAMETER ENABLE ROW MOVEMENT; 表已更改。 SQL> ALTER TABLE WRH$_PARAMETER SHRINK SPACE COMPACT; 表已更改。 SQL> ALTER TABLE WRH$_PARAMETER SHRINK SPACE; 表已更改。 SQL> ALTER TABLE WRH$_PARAMETER SHRINK SPACE CASCADE; 表已更改。 SQL> ALTER TABLE WRH$_SYSSTAT ENABLE ROW MOVEMENT; 表已更改。 SQL> ALTER TABLE WRH$_SYSSTAT SHRINK SPACE COMPACT; 表已更改。 SQL> ALTER TABLE WRH$_SYSSTAT SHRINK SPACE; 表已更改。 SQL> ALTER TABLE WRH$_SYSSTAT SHRINK SPACE CASCADE; 表已更改。 SQL> ALTER TABLE WRH$_SYSSTAT disable ROW MOVEMENT; 表已更改。 SQL> ALTER TABLE WRH$_PARAMETER disable ROW MOVEMENT; 表已更改。 SQL> ALTER TABLE WRH$_LATCH disable ROW MOVEMENT; 表已更改。
再次查看这些TOP对象消失
********************************** (3b) Space usage within AWR Components (> 500K) ********************************** COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE --------- --------- --------------------------------------------------------------------- --------------- FIXED 56.0 WRH$_SERVICE_STAT_PK.WRH$_SERVIC_1600597976_0 - 64% INDEX PARTITION FIXED 29.0 WRH$_SERVICE_STAT.WRH$_SERVIC_1600597976_0 - 95% TABLE PARTITION FIXED 26.0 WRH$_ROWCACHE_SUMMARY.WRH$_ROWCAC_1600597976_0 - 96% TABLE PARTITION FIXED 21.0 WRH$_MVPARAMETER.WRH$_MVPARA_1600597976_0 - 95% TABLE PARTITION FIXED 17.0 WRH$_ROWCACHE_SUMMARY_PK.WRH$_ROWCAC_1600597976_0 - 98% INDEX PARTITION FIXED 17.0 WRH$_MVPARAMETER_PK.WRH$_MVPARA_1600597976_0 - 97% INDEX PARTITION FIXED 12.0 WRH$_SYSMETRIC_HISTORY - 45% TABLE
发表在 Oracle
评论关闭
Oracle 19C 报ORA-704 ORA-01555故障处理
异常断电导致数据库无法启动,尝试对数据文件进行recover操作,报ORA-00283 ORA-00742 ORA-00312错误,由于redo写丢失无法正常应用
D:\check_db>sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 7月 30 07:49:19 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. 连接到: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> recover datafile 1; ORA-00283: 恢复会话因错误而取消 ORA-00742: 日志读取在线程 1 序列 9274 块 18057 中检测到写入丢失情况 ORA-00312: 联机日志 1 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG'
屏蔽数据一致性,尝试强制打开库,报ORA-00604,ORA-00704,ORA-01555错误
SQL> alter database open resetlogs; alter database open resetlogs * 第 1 行出现错误: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 1 ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9_4165470211$" too small 进程 ID: 4036 会话 ID: 2277 序列号: 40707
alert日志对应错误
2023-07-30T06:54:43.457383+08:00 .... (PID:5836): Clearing online redo logfile 1 complete .... (PID:5836): Clearing online redo logfile 2 complete .... (PID:5836): Clearing online redo logfile 3 complete Resetting resetlogs activation ID 3572089731 (0xd4e9c383) Online log D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG: Thread 1 Group 1 was previously cleared Online log D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG: Thread 1 Group 2 was previously cleared Online log D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO03.LOG: Thread 1 Group 3 was previously cleared 2023-07-30T06:54:43.863676+08:00 Setting recovery target incarnation to 2 2023-07-30T06:54:44.816771+08:00 Ping without log force is disabled: instance mounted in exclusive mode. Endian type of dictionary set to little 2023-07-30T06:54:44.957395+08:00 Assigning activation ID 3664275149 (0xda6866cd) 2023-07-30T06:54:44.957395+08:00 TT00 (PID:4640): Gap Manager starting 2023-07-30T06:54:45.004305+08:00 Redo log for group 1, sequence 1 is not located on DAX storage 2023-07-30T06:54:46.176153+08:00 Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG Successful open of redo thread 1 2023-07-30T06:54:46.191771+08:00 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set stopping change tracking 2023-07-30T06:54:46.223036+08:00 TT03 (PID:1816): Sleep 5 seconds and then try to clear SRLs in 2 time(s) 2023-07-30T06:54:46.332398+08:00 ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000000017b852a7 ): 2023-07-30T06:54:46.332398+08:00 select ctime, mtime, stime from obj$ where obj# = :1 2023-07-30T06:54:46.332398+08:00 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5836.trc: ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 9 (名称为 "_SYSSMU9_4165470211$") 过小 2023-07-30T06:54:46.332398+08:00 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5836.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 9 (名称为 "_SYSSMU9_4165470211$") 过小 2023-07-30T06:54:46.348028+08:00 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5836.trc: ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 9 (名称为 "_SYSSMU9_4165470211$") 过小 Error 704 happened during db open, shutting down database Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_5836.trc (incident=474502): ORA-00603: ORACLE 服务器会话因致命错误而终止 ORA-01092: ORACLE 实例终止。强制断开连接 ORA-00704: 引导程序进程失败 ORA-00704: 引导程序进程失败 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01555: 快照过旧: 回退段号 9 (名称为 "_SYSSMU9_4165470211$") 过小 Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_474502\xff_ora_5836_i474502.trc 2023-07-30T06:54:47.785549+08:00 opiodr aborting process unknown ospid (5836) as a result of ORA-603 2023-07-30T06:54:47.816792+08:00 ORA-603 : opitsk aborting process License high water mark = 6 USER (ospid: (prelim)): terminating the instance due to ORA error
这类错误比较常见,参考以前类似恢复:
在数据库open过程中常遇到ORA-01555汇总
数据库open过程遭遇ORA-1555对应sql语句补充
Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障
使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障
对于本次故障,通过Oracle Recovery Tools工具快速处理
open数据库成功
SQL> alter database open; 数据库已更改。 SQL> SQL> SQL> select status,count(1) from v$datafile group by status; STATUS COUNT(1) -------------- ---------- SYSTEM 1 ONLINE 61
