[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20140424]oracle的邏輯壞塊.txtOracle
- [20150513]人為破壞資料塊.txt
- C_OBJ#_INTCOL#OBJ
- Oracle塊損壞恢復（有rman備份）Oracle
- ORACLE資料庫壞塊的處理 (處理無物件壞快的方法)Oracle資料庫物件
- [20150811]模擬壞塊處理.txt
- Oracle資料庫壞塊(corruption)-物理壞塊Oracle資料庫
- 【故障分析】通過壞塊提示資訊確定損壞的資料庫物件資訊資料庫物件
- 磁碟損壞造成RMAN備份檔案有壞塊的恢復案例
- Oracle上的邏輯壞塊和物理壞塊Oracle
- oracle壞塊(二)Oracle
- oracle檢查資料庫是否有壞塊的命令Oracle資料庫
- [20190311]關於oracle物理與邏輯壞塊.txtOracle
- [20121016]壞塊處理以及資料恢復.txt資料恢復
- ORA-01578(資料塊損壞)跳過壞塊
- 資料塊損壞ORA-1578(發現損壞塊)
- Oracle壞塊處理Oracle
- oracle壞塊Block CorruptionsOracleBloC
- rootvg壞塊處理
- 檢查 oracle 壞塊Oracle
- ORACLE 壞塊處理Oracle
- 處理塊損壞
- RMAN修復壞塊
- [20190718]12c壞塊處理一例.txt
- 【dbv】使用dbv工具檢驗資料檔案是否有壞塊
- Oracle如何進行塊介質的恢復？（有邏輯壞塊是如何處理）Oracle
- ORACLE壞塊總結（轉）Oracle
- ORACLE壞塊總結2Oracle
- oracle 壞塊問題急救Oracle
- 壞塊標記並skip
- 如何查詢損壞塊
- 壞塊的處理思維（用程式製作壞塊不如用系統）
- 使用blockrecover 對有壞塊的資料檔案進行恢復BloC
- ORA-01578(資料塊損壞)跳過壞塊處理辦法
- oracle壞塊修復例項Oracle
- Oracle 11.1 邏輯壞塊Oracle
- Oracle壞塊問題總結Oracle
- 壞塊問題(摘抄總結)