测试AIX从linux导入数据时 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS阶段停留了很久：

ORA-39082: Object type ALTER_PROCEDURE:”USER_DATA2″.”PRO_MOBILE_CHARGE_REFOUND” created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:”USER_DATA2″.”PRO_USER_FINISH_GRF_BATCH” created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:”USER_DATA2″.”PRO_USER_FINISH_GRF” created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:”USER_DATA2″.”SERIES_COUPON_CANCELORDERZERO2″ created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:”USER_DATA2″.”SERIES_COUPON_CANCELNOTZERO2″ created with compilation warnings
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:”USER_DATA2″.”TRIG_DML_AREA” created with compilation warnings
ORA-39082: Object type TRIGGER:”USER_DATA2″.”TRIG_DML_CITY” created with compilation warnings
ORA-39082: Object type TRIGGER:”USER_DATA2″.”TRIG_DML_CITY” created with compilation warnings
ORA-39082: Object type TRIGGER:”USER_DATA2″.”TRIG_DML_COUNTY” created with compilation warnings
ORA-39082: Object type TRIGGER:”USER_DATA2″.”TRIG_DML_COUNTY” created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

发现坏块 来记录一下操作过程：

Import> status

Job: SYS_IMPORT_FULL_01
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 154,504,859,144
Percent Done: 99
Current Parallelism: 1
Job Error Count: 0
Dump File: /data1/full.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Type: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Completed Objects: 80
Worker Parallelism: 1

Import> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

数据已经导入完成 只是在imp TABLE_STATISTICS时候hang住 直接kill掉该job

SQL> select count(*) from MON_MODS$;
select count(*) from MON_MODS$
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 3186)
ORA-01110: data file 1: ‘/data1/oracle/oradata/testdb/system01.dbf’

block 3186 标记为坏块：

sys@testdb> select
2 object_id||’-‘||segment_name||’/’||partition_name segment_name,
segment_type,
RELATIVE_FNO
from dba_extents a
,dba_objects b
where file_id = ‘1’
and 3186 between block_id and block_id + blocks – 1
and a.SEGMENT_NAME = b.OBJECT_NAME
and (a.PARTITION_NAME = b.SUBOBJECT_NAME or a.PARTITION_NAME is null) 3 4 5 6 7 8 9 10 ;

SEGMENT_NAME SEGMENT_TYPE RELATIVE_FNO
——————————————————————————————————————————————————— —————— ————
461-MON_MODS$/ TABLE 1

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
1 3186 1 0 FRACTURED

确定segment为 MON_MODS$ 对于这个DB来说 无备份 无法recover 这个block块

对于这个表的解释为统计信息收集记录,关闭自动统计信息收集这个表也失去了功效

— Create table
create table MON_MODS$
(
obj# NUMBER,
inserts NUMBER,
updates NUMBER,
deletes NUMBER,
timestamp DATE,
flags NUMBER,
drop_segments NUMBER
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 200K
next 104K
minextents 1
maxextents unlimited
);
— Create/Recreate indexes
create unique index I_MON_MODS$_OBJ on MON_MODS$ (OBJ#)
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

OTN 对于这个case 也给出过解决方法 eg:

rename to ; (preferently log in with owner)
where: sys.mon_mods$ sys.mon_mods$_corrupted

–> In the same session:
begin
SYS.DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘SYS’,’MON_MOD$_CORRUPTED’);
end;

–> after finish, try (remember that run in the same session):

create table SYS.MON_MODS$ as
select * from SYS.MON_MODS$_CORRUPTED

对于这个case 我们直接跳过这个block

SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’MON_MODS$’);

PL/SQL procedure successfully completed.

sys@testdb> select count(*) from MON_MODS$;

COUNT(*)
———-
684

dd了这个block 期待惜分飞同学的repair测试