sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-00600 [ksfdrmms1]遇到坏块的解决办法

时间:2016-11-15 09:39   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库异常并存在报错ORA-00600 [ksfdrmms1]和ORA-01578,分析原因为存在Oracle坏块导致。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 一次远程协助的恢复 遇到异灵事件

某个客户的windows 10201的库,alert报错如下:

Tue Jul 05 15:28:52 2011
Hex dump of (file 3, block 2674) in trace file e:\oracle\product\10.2.0\admin\

orcl\bdump\orcl_p001_1916.trc
Corrupt block relative dba: 0x00c00a72 (file 3, block 2674)
Tue Jul 05 15:28:52 2011
RECOVERY OF THREAD 1 STUCK AT BLOCK 2095 OF FILE 2
Fractured block found during crash/instance recovery
Data in bad block:
type: 33 format: 2 rdba: 0x00c00a72
last change scn: 0×0000.0e5676f3 seq: 0×1 flg: 0×04
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0xad4b2101
check value in block header: 0xe187
computed block checksum: 0xdbb8
Reread of rdba: 0x00c00a72 (file 3, block 2674) found same corrupted data
Tue Jul 05 15:28:52 2011

Tue Jul 05 16:32:22 2011
Hex dump of (file 2, block 321) in trace file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p003_2164.trc
Corrupt block relative dba: 0×00800141 (file 2, block 321)
Fractured block found during crash/instance recovery
Data in bad block:
type: 1 format: 6 rdba: 0x0141a226
last change scn: 0x8ad2.8ad20141 seq: 0×0 flg: 0×00
spare1: 0×26 spare2: 0xa2 spare3: 0x893e
consistency value in tail: 0×26020000
check value in block header: 0×0
block checksum disabled
Tue Jul 05 16:32:23 2011
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p003_2164.trc:
ORA-00600: internal error code, arguments: [ksfdrmms1], [0x4EA4B98C], [], [], [], []

Tue Jul 05 16:53:14 2011
Hex dump of (file 2, block 321) in trace file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p003_2192.trc
Corrupt block relative dba: 0×00800141 (file 2, block 321)
Fractured block found during crash/instance recovery
Data in bad block:
type: 5 format: 2 rdba: 0x0141a226
last change scn: 0x8ad2.8ad20141 seq: 0×0 flg: 0×00
spare1: 0×26 spare2: 0xa2 spare3: 0x893e
consistency value in tail: 0×26020000
check value in block header: 0×0
block checksum disabled
Reread of rdba: 0×00800141 (file 2, block 321) found different data
Hex dump of (file 2, block 321) in trace file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p003_2192.trc
Corrupt block relative dba: 0×00800141 (file 2, block 321)
Fractured block found during reread
Data in bad block:
type: 38 format: 2 rdba: 0×00800141
last change scn: 0×0000.0e568ad2 seq: 0×2 flg: 0×04
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0xc1c42602
check value in block header: 0x893e
computed block checksum: 0xea14
Tue Jul 05 16:53:15 2011
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p002_2188.trc:
ORA-00600: internal error code, arguments: [ksfdchkfob1], [0x4EA4CF64], [0xB424CF94], []
Hex dump of (file 1, block 166657) in trace file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p004_3696.trc
Corrupt block relative dba: 0x00428b01 (file 1, block 166657)
Fractured block found during media recovery
Data in bad block:
type: 6 format: 2 rdba: 0x00428b01
last change scn: 0×0000.0e53ba17 seq: 0×1 flg: 0×06
spare1: 0×0 spare2: 0×0 spare3: 0×0
consistency value in tail: 0xbb9b0601
check value in block header: 0xc268
computed block checksum: 0xb19e
Reread of rdba: 0x00428b01 (file 1, block 166657) found same corrupted data

第一次恢复是7月5号下午6点左右,当时恢复以后,最后发现system仍然有坏块,如下:

Tue Jul 05 18:20:14 2011
Completed: ALTER DATABASE OPEN
Tue Jul 05 18:20:15 2011
ORA-1578 encountered when generating server alert SMG-4120
ORA-1578 encountered when generating server alert SMG-4121
ORA-1578 encountered when generating server alert SMG-4121
Tue Jul 05 18:23:11 2011
ORA-1578 encountered when generating server alert SMG-3503
Tue Jul 05 18:24:53 2011
alter database datafile ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS2.DBF’ resize 2048M
Tue Jul 05 18:25:10 2011
ORA-1578 encountered when generating server alert SMG-4121
ORA-1578 encountered when generating server alert SMG-4121

Tue Jul 05 18:25:41 2011
Completed: alter database datafile ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS2.DBF’ resize 2048M
Tue Jul 05 18:25:41 2011
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_3656.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4554)
ORA-01110: data file 1: ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF’

换句话说,从5号到6号下午6点左右,我们再次进行恢复的时候,alert 一直在报错上面的坏块。

关于这个file 1 block 4554,其实都是一样的,10gR2的库,查询都一样,如下:

SQL> SET LINES 150
SQL> col segment_name FOR a30
SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2    FROM dba_extents
  3   WHERE file_id = 1
  4     AND 4554 BETWEEN block_id AND block_id + blocks - 1;

TABLESPACE_NAME    SEGMENT_TYPE       OWNER     SEGMENT_NAME
------------------ ------------------ --------- ------------------------------
SYSTEM             CLUSTER            SYS       SMON_SCN_TO_TIME

SQL> SELECT owner,index_name FROM dba_indexes WHERE TABLE_NAME='SMON_SCN_TO_TIME';

OWNER                          INDEX_NAME                                       
------------------------------ ------------------------------                   
SYS                            SMON_SCN_TO_TIME_IDX

在6号下午6点进行的恢复时,用dbv检查system datafile,发现居然有2个坏块

Y:\expdp>dbv file=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF blocksize=8192 logfile=sys_dbv.log

DBVERIFY: Release 10.2.0.1.0 - Production on 星期三 7月 6 17:46:38 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBV-00200: 块 dba 4198858 已标记为损坏
DBV-00200: 块 dba 4360961 已标记为损坏


SQL> SELECT dbms_utility.data_block_address_file(TO_NUMBER('4011F2', 'XXXXXXXX')) file_id,
  2         dbms_utility.data_block_address_block(TO_NUMBER('4011F2', 'XXXXXXXX')) block_id
  3  FROM dual;


   FILE_ID   BLOCK_ID
---------- ----------
         1       4594

SQL> SELECT dbms_utility.data_block_address_file(TO_NUMBER('428B01', 'XXXXXXXX')) file_id,
  2         dbms_utility.data_block_address_block(TO_NUMBER('428B01', 'XXXXXXXX')) block_id
  3  FROM dual;


   FILE_ID   BLOCK_ID
---------- ----------
         1     166657

SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2    FROM dba_extents
  3   WHERE file_id = 1
  4     AND 4594 BETWEEN block_id AND block_id + blocks - 1;


TABLESPACE_NAME      SEGMENT_TYPE       OWNER    SEGMENT_NAME
-------------------- ------------------ ------  ------------------------
SYSTEM               INDEX              SYS     AW_IND$

SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2    FROM dba_extents
  3   WHERE file_id = 1
  4     AND 166657 BETWEEN block_id AND block_id + blocks - 1;


TABLESPACE_NAME   SEGMENT_TYPE       OWNER  SEGMENT_NAME
----------------- ------------------ ------ ------------------------
SYSTEM            CLUSTER            SYS    SMON_SCN_TO_TIME

SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2    FROM dba_extents
  3   WHERE file_id = 1
  4     AND 4554 BETWEEN block_id AND block_id + blocks - 1;


TABLESPACE_NAME   SEGMENT_TYPE       OWNER  SEGMENT_NAME
----------------- ------------------ ------ ------------------------
SYSTEM            INDEX              SYS    SMON_SCN_TO_TIME_IDX

windows环境下,检测的结果是十进制,首先需要转换为16进制然后再转换为file id和 block number。

对于dbv检测到的第1个坏块,通过直接rebuild index就解决了,如下:

SQL> ALTER INDEX aw_ind$ rebuild;

索引已更改。
对于第2个坏块,尝试了多种方式,均无法删除,如下:

SQL> DROP TABLE  SMON_SCN_TIME purge;
DROP TABLE  SMON_SCN_TIME purge
            *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 166657)
ORA-01110: 数据文件 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'

SQL> RENAME smon_scn_time TO smon_scn_time_bak;
表已重命名。

SQL> DROP INDEX SMON_SCN_TIME_TIM_IDX;
索引已删除。

SQL> DROP INDEX SMON_SCN_TIME_SCN_IDX;
索引已删除。

SQL> CREATE TABLE "SYS"."SMON_SCN_TIME" (
  2    "THREAD" NUMBER,
  3    "TIME_MP" NUMBER,
  4    "TIME_DP" DATE,
  5    "SCN_WRP" NUMBER,
  6    "SCN_BAS" NUMBER,
  7    "NUM_MAPPINGS" NUMBER,
  8    "TIM_SCN_MAP" RAW(1200),
  9    "SCN" NUMBER DEFAULT 0,
10    "ORIG_THREAD" NUMBER DEFAULT 0           /* for downgrade */
11   ) CLUSTER "SYS"."SMON_SCN_TO_TIME" ("THREAD");
表已创建。

SQL> SELECT COUNT(*) FROM smon_scn_time_bak;
SELECT COUNT(*) FROM smon_scn_time_bak
                     *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 166657)
ORA-01110: 数据文件 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'

开始的尝试都不行,最后我将该表的结构以及相关的几个index通过
dbms_metadata.get_ddl获取出来,然后使用dbms_repair进行跳过坏块,才完成的。

1. 创建表

SQL> exec DBMS_REPAIR.ADMIN_TABLES('REPAIR_TABLE',1,1,'SYSTEM');

PL/SQL 过程已成功完成。

SQL> exec DBMS_REPAIR.ADMIN_TABLES('ORPHAN_TABLE',2,1,'SYSTEM');

PL/SQL 过程已成功完成。

2. 标记坏块

SQL> SET serveroutput ON
SQL> DECLARE
  2     cc NUMBER;
  3   BEGIN
  4      DBMS_REPAIR.check_object(schema_name => 'SYS',object_name =>'SMON_SCN_TIME',corrupt_count => cc);
  5      DBMS_OUTPUT.put_line(a => TO_CHAR(cc));
  6   END;
  7  /


PL/SQL 过程已成功完成。

3. 检查确认坏块

SQL> SELECT * FROM REPAIR_TABLE;

4. 跳过坏块

SQL> exec OracleоDBMS_REPAIR.skip_corrupt_blocks(schema_name => 'SYS',object_name => 'SMON_SCN_TIME',flags => 1);

PL/SQL 过程已成功完成。
做完上面的工作以后,才能删除smon_scn_time,我狂汗,最后使用同样的方式删除smon_scn_time_bak。
最后再创建表和相关的cluster以及index,如下:

SQL> EXEC dbms_repair.skip_corrupt_blocks(schema_name => 'SYS',object_name => 'SMON_SCN_TIME_BAK',flags => 1);
PL/SQL 过程已成功完成。

SQL> DROP TABLE smon_scn_time_bak purge;
DROP TABLE smon_scn_time_bak purge
           *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 166657)
ORA-01110: 数据文件 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'

SQL> RENAME smon_scn_time_bak TO smon_scn_time_old;
表已重命名。

SQL> DROP TABLE smon_scn_time_old purge;
DROP TABLE smon_scn_time_old purge
           *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 166657)
ORA-01110: 数据文件 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORA-00600: 内部错误代码, 参数: [729], [71888], [SPACE leak], [], [], [], [], []

SQL> conn /AS sysdba
ERROR:
ORA-00600: internal error code, arguments: [17182], [0x9B6E6F0], [], [], [],[], [], []
ORA-00600: internal error code, arguments: [17018], [0], [], [], [], [], [], []
这里的2个600错误,直接忽略了不用管。

SQL> DROP cluster smon_scn_to_time including TABLES;
簇已删除。

SQL> CREATE TABLE "SYS"."SMON_SCN_TIME"
  2     (    "THREAD" NUMBER,
  3          "TIME_MP" NUMBER,
  4          "TIME_DP" DATE,
  5          "SCN_WRP" NUMBER,
  6          "SCN_BAS" NUMBER,
  7          "NUM_MAPPINGS" NUMBER,
  8          "TIM_SCN_MAP" RAW(1200),
  9          "SCN" NUMBER DEFAULT 0,
10          "ORIG_THREAD" NUMBER DEFAULT 0           /* for downgrade */
11     ) CLUSTER "SYS"."SMON_SCN_TO_TIME" ("THREAD");
   ) CLUSTER "SYS"."SMON_SCN_TO_TIME" ("THREAD")
                   *
第 11 行出现错误:
ORA-00943: 簇不存在

SQL> CREATE CLUSTER "SYS"."SMON_SCN_TO_TIME"  ("THREAD" NUMBER )
  2    PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  5    TABLESPACE "SYSTEM"
  6    PARALLEL (DEGREE 1 INSTANCES 1);
簇已创建。

SQL> CREATE TABLE "SYS"."SMON_SCN_TIME"
  2     (    "THREAD" NUMBER,
  3          "TIME_MP" NUMBER,
  4          "TIME_DP" DATE,
  5          "SCN_WRP" NUMBER,
  6          "SCN_BAS" NUMBER,
  7          "NUM_MAPPINGS" NUMBER,
  8          "TIM_SCN_MAP" RAW(1200),
  9          "SCN" NUMBER DEFAULT 0,
10          "ORIG_THREAD" NUMBER DEFAULT 0           /* for downgrade */
11     ) CLUSTER "SYS"."SMON_SCN_TO_TIME" ("THREAD");
表已创建。

SQL> CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_TIM_IDX" ON "SYS"."SMON_SCN_TIME" ("TIME_MP")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  5    TABLESPACE "SYSTEM";
索引已创建。

SQL> CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_SCN_IDX" ON "SYS"."SMON_SCN_TIME" ("SCN")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  5    TABLESPACE "SYSTEM";
索引已创建。

SQL> CREATE INDEX "SYS"."SMON_SCN_TO_TIME_IDX" ON CLUSTER "SYS"."SMON_SCN_TO_TIME"
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  5    TABLESPACE "SYSTEM";
索引已创建。

这里要补充一下的是关于smon_scn_time以及smon_scn_to_time,其实对于一个没启动闪回的库来说,这2个表根本不关紧要,smon进程多长时间写一次smon_scn_time表,网上似乎说法不一致,9iR2版本
都认为是5分钟更新一次,可是10gR2呢,我的linux x86 10204版本,我发现是5分钟多一点。

我们rename 原来的smon_scn_time,然后新建该表,查询结果如下:
SQL> SELECT * FROM smon_scn_time;
未选定行

SQL> SELECT COUNT(*) FROM smon_scn_time;

  COUNT(*)
----------
         4

-- 当然 前后查询间隔了一段时间有一点让我很不解的是,做过recover database以后,file 1中居然还有坏块?

按理说,即使有,那么也应该是逻辑坏块,不会再出现物理坏块,但是dbv缺检测到了。

在恢复完以后,今天那边的人把alert log传过来,我一看,发现居然仍然有坏块。
Wed Jul 06 22:11:08 2011
Thread 1 advanced to log sequence 12905
Current log# 1 seq# 12905 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
Wed Jul 06 22:15:10 2011
ORA-1578 encountered when generating server alert SMG-4120
ORA-1578 encountered when generating server alert SMG-4121
ORA-1578 encountered when generating server alert SMG-4121

Wed Jul 06 22:20:11 2011       -- 这里的ora-1578是因为block 166657被标记为坏块了,无法清除。
Wed Jul 06 22:35:16 2011
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_m000_5896.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01578: ORACLE data block corrupted (file # 3, block # 2674)    <== 注意这里.
ORA-01110: data file 3: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF'
ORA-01578: ORACLE data block corrupted (file # 3, block # 2674)
ORA-01110: data file 3: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF'
ORA-01578: ORACLE data block corrupted (file # 3, block # 2674)
ORA-01110: data file 3: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF'

Wed Jul 06 22:36:18 2011
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_988.trc:
ORA-01578: ORACLE data block corrupted (file # 3, block # 2674)
ORA-01110: data file 3: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF'

Wed Jul 06 22:36:19 2011
ORACLE Instance orcl (pid = 8) - Error 1578 encountered while recovering transaction (39, 23) on object 8781.
Wed Jul 06 22:36:19 2011
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_2260.trc:
ORA-01578: ORACLE data block corrupted (file # 3, block # 2674)
ORA-01110: data file 3: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF'

ORACLE Instance orcl (pid = 8) - Error 1578 encountered while recovering transaction (39, 23) on object 8781.
Wed Jul 06 22:36:21 2011
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_2260.trc:
ORA-01578: ORACLE data block corrupted (file # 3, block # 2674)  <== 注意这里.
ORA-01110: data file 3: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF'

Wed Jul 06 22:40:14 2011
ORA-1578 encountered when generating server alert SMG-4121
ORA-1578 encountered when generating server alert SMG-4121
Wed Jul 06 22:45:14 2011

从上看,file 3 block 2647又出问题了。
关于后面为什么再次出现坏块,这个我没信息了,客户那边也打算将数据导出新建库,他们以后这个库不会用了。

值得庆幸的是,昨天我在recover的时候,最后将那个坏块file 1 block 166657 dump了出来,现在就让咋们来
好好分析一下这个block的情况。

*** SERVICE NAME:(SYS$USERS) 2011-07-06 20:35:03.000
*** SESSION ID:(159.3) 2011-07-06 20:35:03.000
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0×1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0×1)
*** 2011-07-06 20:36:48.484
Start dump data blocks tsn: 0 file#: 1 minblk 166657 maxblk 166657
buffer tsn: 0 rdba: 0x00428b01 (1/166657)
scn: 0×0000.00000000 seq: 0xff flg: 0×04 tail: 0x000006ff
frmt: 0×02 chkval: 0x7e29 type: 0×06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04CC2200 to 0x04CC4200

先不说block dump的内容,就前面的那两句,我感觉可能就不正常,或者说可能是bug,虽然可能不是致命的。
如下:

tkcrrsarc: (WARN) Failed to find ARCH for message (message:0×1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0×1)


经常mos,发现果然是bug,再次狂汗。。。。 详见如下:

BUG 4704334 – ”TKCRRSARC: (WARN) FAILED TO FIND ARCH FOR MESSAGE (MESSAGE:0X1)”

These messages are warning messages and are a consequence of the internal archive process spawning and releasing.

There can be different reasons for this message to appear, but it is in fact related to internal work.

The BUG 4704334 has been closed as an duplicate of BUG 6074620 – fixed in 10.2.0.4.1, 10.2.0.5, 11.1.0.7 and 11.2 releases.

这个意义不大,咋们继续看上面的block dump信息。

我们就来看看怪异的事情:

scn: 0×0000.00000000 seq: 0xff flg: 0×04 tail: 0x000006ff
frmt: 0×02 chkval: 0x7e29 type: 0×06=trans data
Hex dump of block: st=0, typ_found=1

今天查看昨天的操作记录,发现了如下信息:

SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2    FROM dba_extents
  3   WHERE file_id = 1
  4     AND 166657 BETWEEN block_id AND block_id + blocks - 1;

未选定行

SQL> ALTER system dump datafile 1 block 166657;

系统已更改。
难道说是因为我们rename了该表,导致该block的scn被清空了 ?
下午自己做了下实验,发现rename table的过程实际上不会该表block的位置,换句话说,
原来这个表分布在a,b两个block上,那么rename以后仍然分布在a和b 两个block上。

关于block scn为0的情况,问了老白,他的说法是除非是刚格式化的block或空块,不然不可能为0.
但是事实上我这里确实是遇到了,太诡异了。
最近遇到了好几个怪异的问题,这是其中之一,还有前不久的临时段的问题,看来需要花点功夫再研究研究。

最后补充一下,关于那几个字典表,其实可以查询sql.bsq如下:

CREATE cluster smon_scn_to_time (
  thread NUMBER                         /* thread, compatibility */
)
/
CREATE INDEX smon_scn_to_time_idx ON cluster smon_scn_to_time
/
CREATE TABLE smon_scn_time (
  thread NUMBER,                         /* thread, compatibility */
  time_mp NUMBER,                        /* time this recent scn represents */
  time_dp DATE,                          /* time as date, compatibility */
  scn_wrp NUMBER,                        /* scn.wrp, compatibility */
  scn_bas NUMBER,                        /* scn.bas, compatibility */
  num_mappings NUMBER,
  tim_scn_map raw(1200),
  scn NUMBER DEFAULT 0,                  /* scn */
  orig_thread NUMBER DEFAULT 0           /* for downgrade */
) cluster smon_scn_to_time (thread)
/

CREATE UNIQUE INDEX smon_scn_time_tim_idx ON smon_scn_time(time_mp)
/

CREATE UNIQUE INDEX smon_scn_time_scn_idx ON smon_scn_time(scn)
/

CREATE TABLE aw$
(awname varchar2("M_IDEN"),              /* name of AW */
owner#  NUMBER NOT NULL,                /* owner of AW */
awseq#  NUMBER NOT NULL,                /* aw sequence number */
version NUMBER DEFAULT NULL,            /* aw storage version */
oids    NUMBER(10) DEFAULT NULL,        /* object id page space */
objs    NUMBER(10) DEFAULT NULL,        /* object storage page space */
dict    raw(8) DEFAULT NULL)            /* aw dictionary object */
/
CREATE UNIQUE INDEX aw_ind$ ON aw$(awname, owner#)

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-00600 [ksfdrmms1]遇到坏块的解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1273.html

Oracle研究中心

关键词:

ORA-00600

ORA-01578

ORA-00600 [ksfdrmms1]

Oracle system遇到坏块的解决笔记