[20160722]对象C_OBJ#_INTCOL#有坏块.txt
--前几天看到的帖子，一直没时间测试，链接如下：
http://www.itpub.net/thread-2063836-1-1.html
--我以前按照eygle的链接http://www.eygle.com/archives/2012/05/event_38003_c_obj_intcol.html做过测试，测试在11.2.0.2下做的。
--通过设置alter system set event='38003 trace name context forever, level 10' scope=spfile;
--来Truncate该对象。当时做完了，直方图没建立，导致登录后运行sql很慢，或者报错。
--设置optimizer_mode=rule,重新分析系统表以及应用表以后才正常。
--今天在11.2.0.4下再测试看看：
1.环境与介绍C_OBJ#_INTCOL#：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
CREATE CLUSTER SYS.C_OBJ#_INTCOL#
(
OBJ# NUMBER,
INTCOL# NUMBER
)
TABLESPACE SYSTEM
PCTUSED 40
PCTFREE 5
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 2M
NEXT 200K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
INDEX
NOROWDEPENDENCIES
NOPARALLEL;
--
-- I_OBJ#_INTCOL# (Index)
--
CREATE INDEX SYS.I_OBJ#_INTCOL# ON CLUSTER SYS.C_OBJ#_INTCOL#
TABLESPACE SYSTEM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
--
-- HISTGRM$ (Table)
--
CREATE TABLE SYS.HISTGRM$
(
OBJ# NUMBER,
COL# NUMBER,
ROW# NUMBER,
BUCKET NUMBER,
ENDPOINT NUMBER,
INTCOL# NUMBER,
EPVALUE VARCHAR2(1000 BYTE),
SPARE1 NUMBER,
SPARE2 NUMBER
)
CLUSTER SYS.C_OBJ#_INTCOL#(OBJ#, INTCOL#);
--
-- I_H_OBJ#_COL# (Index)
--
-- Dependencies:
-- HISTGRM$ (Table)
--
CREATE INDEX SYS.I_H_OBJ#_COL# ON SYS.HISTGRM$
(OBJ#, COL#)
TABLESPACE SYSTEM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
--从对象看SYS.C_OBJ#_INTCOL#是一个cluster，里面仅仅一个表SYS.HISTGRM$。SYS.HISTGRM$里面实际上就是字段直方图的信息。
2.建立测试表：
create table emp3 as select * from emp;
alter table emp3 move tablespace users;
execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 'emp3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false);
SCOTT@book> select * from dba_objects where object_name='EMP3';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ ----------- ---------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT EMP3 95323 95371 TABLE 2016-07-20 10:00:44 2016-07-22 09:26:31 2016-07-20:10:01:00 VALID N N N 1
SYS@book> select * from SYS.HISTGRM$ where obj# in ( 95323, 95371) and col#=1;
OBJ# COL# ROW# BUCKET ENDPOINT INTCOL# EPVALUE SPARE1 SPARE2
---------- ---------- ---------- ---------- ---------- ---------- ------- ------ -------
95323 1 0 1 7369 1
95323 1 0 2 7499 1
95323 1 0 3 7521 1
95323 1 0 4 7566 1
95323 1 0 5 7654 1
95323 1 0 6 7698 1
95323 1 0 7 7782 1
95323 1 0 8 7788 1
95323 1 0 9 7839 1
95323 1 0 10 7844 1
95323 1 0 11 7876 1
95323 1 0 12 7900 1
95323 1 0 13 7902 1
95323 1 0 14 7934 1
14 rows selected.
--说明OBJ#=dba_objects.object_id.
3.破坏块看看：
SYS@book> select rowid,a.* from SYS.HISTGRM$ a where obj#=95323 and col#=1 and rownum<=3;
ROWID OBJ# COL# ROW# BUCKET ENDPOINT INTCOL# EPVALUE SPARE1 SPARE2
------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------- ------ ------
AAAAG8AABAAASuVAAO 95323 1 0 1 7369 1
AAAAG8AABAAASuVAAP 95323 1 0 2 7499 1
AAAAG8AABAAASuVAAQ 95323 1 0 3 7521 1
SYS@book> @ &r/rowid AAAAG8AABAAASuVAAO
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
444 1 76693 14 1,76693 alter system dump datafile 1 block 76693
--//通过bbed破坏。
BBED> set dba 1,76693
DBA 0x00412b95 (4270997 1,76693)
BBED> corrupt
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Block marked media corrupt.
SYS@book> alter system flush buffer_cache;
System altered.
SYS@book> select rowid,a.* from SYS.HISTGRM$ a where obj#=95323 and col#=1 and rownum<=3;
select rowid,a.* from SYS.HISTGRM$ a where obj#=95323 and col#=1 and rownum<=3
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 76693)
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
--我的测试可能直方图信息已经加载到共享池，无法清除，重启数据看看：
SCOTT@book> select * from emp3 where empno=7369;
select * from emp3 where empno=7369
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 76693)
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
--可以看出sql语句分析，递归调用直方图信息，结果因为存在坏块1,76693，报错。
SCOTT@book> select /*+ rule */ * from emp3 where empno=7369;
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
--加入提示rule，可以执行。
4.首先看看这个块的内容，如果已经乱了，就无法看到里面的信息：
BBED> set dba 1,76693
DBA 0x00412b95 (4270997 1,76693)
BBED> p kdbt
struct kdbt[0], 4 bytes @106
sb2 kdbtoffs @106 0
sb2 kdbtnrow @108 1
struct kdbt[1], 4 bytes @110
sb2 kdbtoffs @110 1
sb2 kdbtnrow @112 28
--//看kdbt可以确定有两张表。可以从kdbtoffs确定记录的范围。
BBED> p kdbr
sb2 kdbr[0] @114 7871
sb2 kdbr[1] @116 7855
sb2 kdbr[2] @118 7839
sb2 kdbr[3] @120 7823
sb2 kdbr[4] @122 7807
sb2 kdbr[5] @124 7791
sb2 kdbr[6] @126 7775
sb2 kdbr[7] @128 7759
sb2 kdbr[8] @130 7743
sb2 kdbr[9] @132 7727
sb2 kdbr[10] @134 7711
sb2 kdbr[11] @136 7695
sb2 kdbr[12] @138 7680
sb2 kdbr[13] @140 7664
sb2 kdbr[14] @142 7648
sb2 kdbr[15] @144 7632
sb2 kdbr[16] @146 7616
sb2 kdbr[17] @148 7600
sb2 kdbr[18] @150 7584
sb2 kdbr[19] @152 7568
sb2 kdbr[20] @154 7552
sb2 kdbr[21] @156 7536
sb2 kdbr[22] @158 7520
sb2 kdbr[23] @160 7504
sb2 kdbr[24] @162 7488
sb2 kdbr[25] @164 7472
sb2 kdbr[26] @166 7457
sb2 kdbr[27] @168 7441
sb2 kdbr[28] @170 7425
BBED> x /rnn *kdbr[0]
rowdata[446] @7963
------------
flag@7963: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@7964: 0x00
cols@7965: 2
kref@7966: 28
mref@7968: 14
hrid@7970:0x00412b95.0
nrid@7976:0x00412b95.0
col 0[4] @7982: 95323
col 1[2] @7987: 1
--//对应的就是OBJ#，INTCOL#。也就是OBJ#对应dba_objects.objects_id,在这里就是scott.emp3表。
BBED> x /rnnnncnn *kdbr[15]
rowdata[207] @7724
------------
flag@7724: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@7725: 0x02
cols@7726: 4
col 0[2] @7728: 1
col 1[1] @7731: 0
col 2[2] @7733: 1
col 3[3] @7736: 7369
SYS@book> select obj#,col#,segcol#,name,intcol# from sys.col$ where obj# in (select object_id from dba_objects where object_name='HISTGRM$');
OBJ# COL# SEGCOL# NAME INTCOL#
---------- ---------- ---------- -------------------- ----------
446 1 1 OBJ# 1
446 2 3 COL# 2
446 3 4 ROW# 3
446 4 5 BUCKET 4
446 5 6 ENDPOINT 5
446 6 2 INTCOL# 6
446 7 7 EPVALUE 7
446 8 8 SPARE1 8
446 9 9 SPARE2 9
9 rows selected.
--//注意列定义的顺序，cluster的定义是OBJ#，INTCOL#，也就是表HISTGRM$的第1,6字段。它实际在段中是第1，2字段。
--//COL# 不等于0的列显示。select * from t;
--//SEGCOL# 表示在段内存储的顺序。
--//INTCOL# 表示创建表时各列的定义顺序的顺序。
--重新分析报错。
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 'emp3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false)
BEGIN sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 'emp3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false); END;
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 76693)
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 1
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 'emp3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
BEGIN sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 'emp3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false); END;
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 76693)
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 1
--可以测试事件绕过坏块扫描。alter session set events '10231 trace name context forever,level 10' ;
SCOTT@book> alter session set events '10231 trace name context forever,level 10' ;
Session altered.
SCOTT@book> select * from emp3 where empno=7369;
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 'emp3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
--可以删除直方图。但是重新建立依旧报错。
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 'emp3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false)
BEGIN sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 'emp3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 76693)
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 1
SYS@book> select /*+ full(a) */ count(*) from SYS.HISTGRM$ a;
select /*+ full(a) */ count(*) from SYS.HISTGRM$ a
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 76693)
ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'
--依旧要访问这个块。
5.首先建立这个表的备份：
SYS@book> alter session set events '10231 trace name context forever,level 10' ;
Session altered.
SYS@book> select /*+ full(a) */ count(*) from SYS.HISTGRM$ a;
COUNT(*)
----------
55096
SYS@book> create table scott.HISTGRM tablespace users as select * from SYS.HISTGRM$;
Table created.
SYS@book> select /*+ full(a) */ count(*) from Scott.HISTGRM a;
COUNT(*)
----------
55096
SYS@book> truncate cluster SYS.C_OBJ#_INTCOL#;
truncate cluster SYS.C_OBJ#_INTCOL#
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SYS@book> truncate table SYS.HISTGRM$;
truncate table SYS.HISTGRM$
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
--可以发现这个对象无法truncate。
$ oerr ora 701
00701, 00000, "object necessary for warmstarting database cannot be altered"
// *Cause: Attempt to alter or drop a database object (table, cluster, or
// index) which are needed for warmstarting the database.
// *Action: None.
6.设置 38003 事件，可以将对象 C_OBJ#_INTCOL# 从启动依赖中暂时剥离出来：
alter system set event='38003 trace name context forever, level 10' scope=spfile;
$ oerr ora 38003
38003, 00000, "CBO Disable column stats for the dictionary objects in recursive SQL"
// *Cause:
// *Action:
--//从提示看实际上就是关闭CBO递归执行。
SYS@book> alter system set event='38003 trace name context forever, level 10' scope=spfile;
System altered.
--//重启数据库。
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
SYS@book> startup RESTRICT
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
--//alter system enable restricted session;
SYS@book> show parameter event
NAME TYPE VALUE
------------- ------ ------------------------------------------
event string 38003 trace name context forever, level 10
xml_db_events string enable
SYS@book> truncate table SYS.HISTGRM$;
truncate table SYS.HISTGRM$
*
ERROR at line 1:
ORA-03292: Table to be truncated is part of a cluster
--//在cluster的表不能执行truncate。
$ oerr ora 3292
03292, 00000, "Table to be truncated is part of a cluster"
// *Cause: The table being truncated is a member of a cluster.
// *Action: Either use TRUNCATE CLUSTER or DROP TABLE
SYS@book> truncate cluster SYS.C_OBJ#_INTCOL#;
Cluster truncated.
SYS@book> insert into SYS.HISTGRM$ select * from scott.HISTGRM order by OBJ#, INTCOL# ;
55096 rows created.
SYS@book> commit ;
Commit complete.
SYS@book> alter system reset event scope=spfile sid='*';
System altered.
--//再次重启看看。
$ dbv file=/mnt/ramdisk/book/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Fri Jul 22 10:41:05 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 99840
Total Pages Processed (Data) : 66606
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 14368
Total Pages Failing (Index): 0
Total Pages Processed (Other): 3592
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 15274
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 359730318 (3.359730318)
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 'emp3',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SYS@book> select rowid,a.* from SYS.HISTGRM$ a where obj#=95323 and col#=1 and rownum<=3;
ROWID OBJ# COL# ROW# BUCKET ENDPOINT INTCOL# EPVALUE SPARE1 SPARE2
------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------- ------- -------
AAAXSVAABAAAXgkAAA 95323 1 0 1 7369 1
AAAXSVAABAAAXgkAAB 95323 1 0 2 7499 1
AAAXSVAABAAAXgkAAC 95323 1 0 3 7521 1
--//上次我的truncate时没有备份，导致toad无法登录，或者报错，再或者很慢。几乎无法使用，必须设置optimizer_mode=rule,分析系统
--//以及应用表才正常。
SYS@book> select * from dba_objects where object_name in ('HISTGRM$','C_OBJ#_INTCOL#') and owner='SYS';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SYS C_OBJ#_INTCOL# 444 95381 CLUSTER 2013-08-24 11:37:43 2013-08-24 11:37:43 2013-08-24:11:37:43 VALID N N N 5
SYS HISTGRM$ 446 95381 TABLE 2013-08-24 11:37:43 2013-08-24 11:37:43 2013-08-24:11:37:43 VALID N N N 1
--DATA_OBJECT_ID已经变化。
SYS@book> show parameter event
NAME TYPE VALUE
------------- ------- --------
event string
xml_db_events string enable
--简单总结一下：
1.确定坏块内容，尽可能备份SYS.HISTGRM$，通过10231事件。alter session set events '10231 trace name context forever,level 10' ;
2.设置38003 事件，truncate cluster，然后插入好的记录，重置event。检查测试数据库是否正常。