一次远程协助的恢复遇到异灵事件

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

最近2天远程帮忙恢复了一个数据库，虽然过程不难，但是却遇到了非常奇怪的事情。
某个客户的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: 0x0000.0e5676f3 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
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: 0x00800141 (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: 0x0 flg: 0x00
spare1: 0x26 spare2: 0xa2 spare3: 0x893e
consistency value in tail: 0x26020000
check value in block header: 0x0
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: 0x00800141 (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: 0x0 flg: 0x00
spare1: 0x26 spare2: 0xa2 spare3: 0x893e
consistency value in tail: 0x26020000
check value in block header: 0x0
block checksum disabled
Reread of rdba: 0x00800141 (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: 0x00800141 (file 2, block 321)
Fractured block found during reread
Data in bad block:
type: 38 format: 2 rdba: 0x00800141
last change scn: 0x0000.0e568ad2 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
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: 0x0000.0e53ba17 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
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>
SQL> select owner,index_name from dba_indexes where table_name='SMON_SCN_TO_TIME';
OWNER                          INDEX_NAME                                        
------------------------------ ------------------------------                    
SYS                            SMON_SCN_TO_TIME_IDX

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>

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=8
192 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 已标记为损坏

Y:\expdp>dbv file=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF blocksize=8

192 logfile=sys_dbv.log

DBVERIFY: Release 10.2.0.1.0 - Production on 星期三 7月 6 17:46:38 2011

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

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;
索引已更改。

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'

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 dbms_repair.skip_corrupt_blocks(schema_name => 'SYS',object_name => 'SMON_SCN_TIME',flags => 1);
PL/SQL 过程已成功完成。

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 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> startup
ORA-01031: 权限不足
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";
索引已创建。

100

101

102

103

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> startup

ORA-01031: 权限不足

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，然后新建该表，查询结果如下：

这里要补充一下的是关于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
-- 当然 前后查询间隔了一段时间

SQL> select * from smon_scn_time;

未选定行

SQL> select count(*) from smon_scn_time;

COUNT(*)

----------

-- 当然前后查询间隔了一段时间

有一点让我很不解的是，做过recover database以后，file 1中居然还有坏块？
按理说，即使有，那么也应该是逻辑坏块，不会再出现物理坏块，但是dbv缺检测到了。
这让我百思不得其解。
在恢复完以后，今天那边的人把alert log传过来，我一看，发现居然仍然有坏块。

有一点让我很不解的是，做过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

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

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) <== 注意这里.