[20160722]物件C_OBJ#_INTCOL#有壞塊.txt
[20160722]物件C_OBJ#_INTCOL#有壞塊.txt
--前幾天看到的帖子，一直沒時間測試，連結如下：
http://www.itpub.net/thread-2063836-1-1.html
--我以前按照eygle的連結做過測試，測試在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。檢查測試資料庫是否正常。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2122380/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190311]關於oracle物理與邏輯壞塊.txtOracle
- [20190718]12c壞塊處理一例.txt
- C_OBJ#_INTCOL#OBJ
- oracle壞塊(二)Oracle
- Oracle壞塊處理Oracle
- RMAN修復壞塊
- [20180702]物件名重用.txt物件
- [20190101]塊內重整.txt
- Oracle日常問題-壞塊修復Oracle
- RMAN備份中發現壞塊
- truncate操作消除ORACLE SEG壞塊解析Oracle
- [20210128]拼接資料塊.txt
- Oracle資料庫壞塊典型案例分析Oracle資料庫
- 【BLOCK】Oracle壞塊處理命令參考BloCOracle
- [20190129]塊內重整3.txt
- [20190102]塊內重整2.txt
- 程式碼壞味道之濫用物件導向物件
- Oracle資料庫壞塊典型案例擴充Oracle資料庫
- [20180306]資料塊檢查和.txt
- [20210318]bbed讀取資料塊.txt
- OceanBase 原始碼解讀（十二）：宏塊的垃圾回收和壞塊檢查原始碼
- oracle 普通表空間資料檔案壞塊Oracle
- 一次壞塊的處理過程(一)
- 一次壞塊的處理過程(二)
- 你有把依賴注入玩壞？依賴注入
- [20211105]索引分裂 塊清除 日誌增加.txt索引
- [20210423]dump sga映像的對應塊.txt
- [20210317]如何知道索引塊地址2.txt索引
- laravel chunk 分塊後 第二塊資料是物件Laravel物件
- win10快速啟動有什麼壞處_win10快速啟動壞處有哪些Win10
- 一次ORACLE資料庫undo壞塊處理Oracle資料庫
- 深入解析：段頭塊損壞bbed異常恢復
- [20180328]不要在sys建立使用者物件.txt物件
- [20210902]library_cache物件級別轉儲.txt物件
- [20180626]延遲塊清除與只讀表.txt
- [20180306]資料塊檢查和2.txt
- [20210831]bbed讀取資料塊6.txt
- [20210323]bbed讀取資料塊5.txt
- [20210429]檔案頭塊不會快取.txt快取