一、損壞分類
1.1、物理壞塊
-
塊斷裂
主要指數據塊頭部和尾部的SCN不一致導致
塊頭SCNBase 和seq --bas_kcbh偏移量8，seq_kcbh偏移量14
塊尾SCNBase和seq --tailchk 偏移量8188 由SCNBase的低位兩個字節加上塊類型加上SCN序列號組成，報錯信息如下：Corrupt block relative dba: 0x0380e573 (file 14, block 58739) Fractured block found during buffer read --檢測到塊斷裂 Data in bad block - type: 6 format: 2 rdba: 0x0380e573 last change scn: 0x0288.8e5a2f78 seq: 0x1 flg: 0x04 consistency value in tail: 0x00780601 --塊尾計算值 check value in block header: 0x8739, computed block checksum: 0x2f00 --塊頭0x8739，校驗錯誤 spare1: 0x0, spare2: 0x0, spare3: 0x0 *** Reread of rdba: 0x0380e573 (file 14, block 58739) found same corrupted data -
錯誤校驗不一致
塊校驗用于確定最后一次由Oracle寫入之后，塊是否由Oracle外部的東西更改。校驗在將塊寫入磁盤之前由DBWR或direct load計算，并存儲在塊頭中。
每次讀取塊時，如果參數db_block_checksum參數不為false，Oracle都會計算一個校驗和，并將其與存儲在塊頭中的校驗和進行比較。常見報錯：
Corrupt block relative dba: 0x0380a58f (file 14, block 42383)
Bad check value found during buffer read --校驗錯誤
Data in bad block -
type: 6 format: 2 rdba: 0x0380a58f
last change scn: 0x0288.7784c5ee seq: 0x1 flg: 0x06
consistency value in tail: 0xc5ee0601
check value in block header: 0x68a7, computed block checksum: 0x2f00 --校驗值不一致
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380a58f (file 14, block 42383) found same corrupted data
- 塊錯位
當 Oracle 檢測到正在讀取的塊的內容屬于不同的塊并且校驗有效時,實例錯誤如下
Corrupt block relative dba: 0x0d805a89 (file 54, block 23177)
Bad header found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0d805b08 ----> 與頭部0x0d805a89不一致
last change scn: 0x0692.86dc08e3 seq: 0x1 flg: 0x04
consistency value in tail: 0x08e30601
check value in block header: 0x2a6e, computed block checksum: 0x0 --但是校驗值一直
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
物理損壞的塊也稱為媒體損壞塊。媒體損壞塊不是軟損壞塊
1.2、邏輯塊損壞
當包含校驗有效校驗，但是塊開頭下方的結構已損壞（塊內容已損壞）時。它可能會導致不同的 ORA-600 錯誤。邏輯損壞的詳細損壞描述通常不會打印在 alert.log 中。DBVerify 檢查會報告塊中邏輯損壞的內容。常見邏輯損壞如下：
-
行被不存在的事務鎖定 - ORA-600 [4512] 等
-
使用的空間量不等于塊大小
-
壞塊
當啟用 db_block_checking 時，它可能會產生內部錯誤 ORA-600 [kddummy_blkchk] 或 ORA-600 [kdBlkCheckError]。如果啟用了 db_block_checking 并且該塊在磁盤上已經邏輯損壞，則下一次塊更新會將塊標記為，并且以后對該塊的讀取將產生錯誤 ORA-1578。在這種情況下，DBVerify 會報告此損壞并顯示如下錯誤
DBV-200: Block, dba <rdba>, already marked corrupted
1.3、軟損壞塊
- 什么是軟損壞
- 由邏輯損壞標記為損壞
- 試圖在緩沖區緩存中恢復但無法恢復的塊。下一條消息指示將塊標記為內存中的軟損壞，因為自動塊恢復無法恢復該塊：
"Block recovery logically corrupted file .. block ..."
-
標記軟損壞
-
啟用db_block_checking后，Oracle在修改已損壞的塊時會對其進行軟損壞（映像之前的塊已損壞，這意味著該塊可能已在DISK上損壞）。
-
當塊修改期間進程失敗后自動塊恢復無法恢復塊時Block recovery logically corrupted file … block出現在alert日志中。
-
dbms_repair.FIX_CORRUPT_BLOCKS 也可用于將邏輯損壞塊標記為軟損壞
-
如果塊已經物理損壞，則介質恢復也可以軟損壞塊
-
隨后的塊讀取會產生 ORA-1578 而不是內部錯誤
-
為什么一個塊被標記為軟損壞
-
塊被標記為軟損壞以防止其他內部錯誤并防止數據庫中的進一步損壞。當一個塊在邏輯上損壞時，它可能會導致 ORA-600 / ORA-7445 錯誤，并且當損壞的塊被修改時，可能會對 Oracle 緩沖區緩存內存中的其他塊產生進一步的損壞。將塊標記為軟損壞可防止 DML SQL 語句修改塊。當塊被標記為軟損壞時，SQL 語句在讀取塊時會失敗并出現錯誤 ORA-1578。
-
塊也被標記為軟損壞時，可以使用事件 10231 或使用過程 dbms_repair.SKIP_CORRUPT_BLOCKS可以跳過它
-
使用 TDE（透明數據加密 - 表空間加密）時，如果使用了不正確或無效的錢包，塊可能會被標記為軟損壞。請注意，對于這種情況，只要使用了無效的錢包，該塊在內存中就只是軟損壞（dbverify 不會產生錯誤，因為該塊在磁盤上不是軟損壞）。
-
-
標記為軟損壞的行為
-
當一個塊被標記為軟損壞時，除非使用dbms_repair.SKIP_CORRUPT_BLOCKS或事件10231/10233，否則SQL語句在讀取該塊時會失敗并出現錯誤RA-1578。
-
RMAN 備份不會因軟損壞塊而失敗。RMAN 在備份期間不設置 MAXCORRUPT 子句將忽略“軟損壞”塊。
-
介質恢復（前滾）忽略軟損壞塊。一般來說，介質恢復會忽略物理損壞塊和軟損壞塊。當塊在邏輯上損壞時，介質恢復可能會因意外錯誤而失敗。當塊是物理損壞時，介質恢復將塊標記為軟損壞，恢復繼續進行，沒有錯誤（跳過損壞的塊）。
-
RMAN 驗證不報告跟蹤文件中的軟損壞塊，而是在v$database_block_corruption中報告軟損壞塊。
SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO 1 59744 1 0 CORRUPT- dbv驗證時報錯DBV-200錯誤
DBV-00200: Block, dba <rdba>, already marked corrupted -
二、如何識別數據庫中的損壞
2.1、識別損壞的數據塊
backup validate check logical database;
RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;
- CHECK LOGICAL 選項檢查 PHYSICAL 和 LOGICAL 塊損壞。
- 當發現邏輯損壞時，警報日志會更新為 Error backing up file <file#>, block <block#>: logical corruption
- 當發現物理損壞時，警報日志也會更新損壞描述：
RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}
backup validate check logical datafile 1, 2
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
6 10 1 8183236781662 LOGICAL
6 42 1 0 FRACTURED
6 34 2 0 CHECKSUM
6 50 1 8183236781952 LOGICAL
6 26 4 0 FRACTURED
5 rows selected.
Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found during validation
Data in bad block:
type: 16 format: 2 rdba: 0x01000009
last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x000010ff
check value in block header: 0xb4e0
computed block checksum: 0xa800 -------->這個地方不是0x0,物理壞塊 校驗不通過
Reread of blocknum=9, file=/<path>/<datafilename>.dbf found same corrupt data
Block Checking: DBA = 25165834, Block Type = KTB-managed data block
data header at 0x2b2deb49e07c
kdbchk: fsbo(144) wrong, (hsz 78)
Error backing up file 6, block 10: logical corruption -->邏輯壞塊
Corrupt block relative dba: 0x01000009 (file 4, block 9)
Bad check value found during validation -->發現
Data in bad block:
type: 16 format: 2 rdba: 0x01000009
last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x000010ff
check value in block header: 0xb4e0
computed block checksum: 0xa800 --》校驗不對
Reread of blocknum=9, file=/<path>/<datafilename>.dbf found same corrupt data
監視校驗進度
select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and totalwork != 0
and sofar <> totalwork;
- 對于10g及以下版本的NOARCHIVELOG模式數據庫，數據庫必須處于MOUNT狀態；否則會產生錯誤 ORA-19602。如果無法關閉數據庫，請改用 dbverify。這個限制在11g 中取消了。
- 從 11g 開始：單個數據文件的驗證可以通過使用 section 子句并行進行。RMAN 將文件分成多個部分并并行處理每個文件部分
backup validate check logical datafile 5 SECTION SIZE 1024M;
- 從 11g 開始，可以使用 BLOCK TO 子句在數據文件中檢查一系列塊。下一個命令檢查數據文件 1 的第 5 到 20 個塊：
validate check logical datafile 1 BLOCK 5 TO 20;
- V$DATABASE_BLOCK_CORRUPTION 中報告的損壞會隨著每次 RMAN 備份驗證運行而更新
2.2、識別損壞的數據段
The query can be run to map each block to a segment in the database. It will map each block from v$database_block_corruption to either a segment or if the block is free.
set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, corruption_type description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, corruption_type||' Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_ FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----- ------------------ ------------ ---------- ----- ----------------- --------------- ---------------- -------------
SCOTT TABLE EMP 6 10 10 1
SCOTT TABLE PARTITION ORDER ORDER_JAN 6 26 28 3
6 29 29 1 Free Block
SCOTT TABLE BONUS 6 34 34 1
6 35 35 1 Free Block
SCOTT TABLE DEPT 6 42 42 1 Segment Header
SCOTT TABLE INVOICE 6 50 50 1
- 如果損壞的塊在字典管理的表空間中并且段頭塊損壞，則上述查詢可能會顯示同一塊兩次。
- 如果 ASSM 表空間中的段頭塊損壞，上述查詢會顯示段頭塊，但可能不會顯示同一對象的后續損壞塊。
2.3、識別 12c 中損壞的 NOLOGGING 塊
由 rman validate 標識的 NOLOGGING 塊位于新視圖 v$nonlogged_block 中：
set echo on
select systimestamp from dual;
select FILE#, BLOCK#, BLOCKS, to_char(NONLOGGED_START_CHANGE#, '999999999999999') NONLOGGED_START_CHANGE#
from v$nonlogged_block;
set pagesize 2000
set linesize 250
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$nonlogged_block c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$nonlogged_block c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
三、如何格式化不屬于任何段的損壞塊
不屬于任何段的壞塊損壞表現如下
1.Rman備份失敗，出現ORA-19566錯誤，并且報告損壞的塊不屬于任何對象
2.Dbverify將塊顯示為已損壞
3.損壞的塊不屬于任何對象
RMAN和DBV仍然會報告損壞的塊，直到它被重新使用和重新格式化。
Step 1 - 識別損壞的數據文件
RMAN-03009: failure of backup command on <channel_name> channel at 04/29/2005 09:44:41
ORA-19566: exceeded limit of 0 corrupt blocks for file E:\xxxx\<datafilename>.ORA.
Step 2 對受影響的數據文件運行DBV/Rman驗證，并檢查損壞的數據塊
dbv userid={system/<password>} file={full path filename} logfile={output filename}
DBVERIFY - Verification starting : FILE = E:\xxxx\<datafilename>.ORA
樣本輸出
Page 48740 is marked corrupt ***
Corrupt block relative dba: 0x01c0be64 (file 7, block 48740)
Bad check value found during dbv:
Data in bad block -
type: 0 format: 2 rdba: 0x0000be64
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
consistency value in tail: 0x00000001
check value in block header: 0xb964, computed block checksum: 0x2a5a
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
DBVERIFY - Verification complete
Total Pages Examined : 64000
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1751
Total Pages Failing (Index): 0
Total Pages Processed (Other): 45
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 62203
Total Pages Marked Corrupt : 1
rman檢查
Rman> backup validate check logical database ;
For specific datafile
Rman> backup validate check logical datafile <fileno> ;
Once done query
SQL>Select * from v$database_block_corruption ;
Step 3 -檢查塊是否是任何對象的一部分-對于少量損壞的塊
檢查是否屬于任何對象
SQL> select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupted block number> between block_id
and block_id + blocks -1;
檢查是否不屬于任何對象
Select * from dba_free_space where file_id= <Absolute file number>
and <corrupted block number> between block_id and block_id + blocks -1;
Step 4 -檢查塊是否是任何對象的一部分-對于大量損壞的塊
$ rman target / nocatalog
or
$ rman target sys/ nocatalog
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
------------------------------------------------------------------------
-- multiple channels may be allocated for parallelizing purposes
-- depends: RMAN - Min ( MAXOPENFILES , FILESPERSET )
-- Defaults: MAXOPENFILES =8, FILESPERSET =64
------------------------------------------------------------------------
allocate channel dn type disk;
backup check logical validate database;
}
select * from v＄database_block_corruption;
檢查該塊是被使用還是空閑區
set lines 200 pages 10000
col segment_name format a30
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
ORDER BY file#, corr_start_block#;
Step 5 -以SYS和SYSTEM之外的用戶身份創建一個虛擬表
SQL> create table s (
n number,
c varchar2(4000)
) nologging tablespace <tablespace name having the corrupt block> pctfree 99;
SQL> select segment_name,tablespace_name from user_segments where segment_name='S' ;
Step 6 -在虛擬表上創建觸發器，一旦損壞的塊被重用，該觸發器將拋出異常
CREATE OR REPLACE TRIGGER corrupt_trigger
AFTER INSERT ON <username>.s
REFERENCING OLD AS p_old NEW AS new_p
FOR EACH ROW
DECLARE
corrupt EXCEPTION;
BEGIN
IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
RAISE corrupt;
END IF;
EXCEPTION
WHEN corrupt THEN
RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
END;
/
當提示輸入塊號時，輸入報告損壞的塊。
當提示輸入文件號時，輸入損壞數據文件的相對文件號(v$datafile中的rfile#值)。
Step 7-從受影響的數據文件中為表分配空間
-
如果這是一個ASSM表空間，您可能需要重復此步驟幾次。也就是說，創建多個表并分配多個區。并定期查看dba_extents以確保空閑空間現在被分配給了一個虛擬表。這是因為ASSM將自動確定下一個擴展區的大小
-
建議確保關閉數據文件的自動擴展，以防止其增長
首先，通過查詢dba_free_space找到extent大小
SQL> Select BYTES from dba_free_space where file_id=<file no> and <corrupt block no> between block_id and block_id + blocks -1;
BYTES
---------------- ---------- ---------- ---------- ---------- ------------
65536
在本例中是64K，因此按如下方式分配擴展區:
SQL> alter table <username>.s
allocate extent (DATAFILE 'E:\xxxx\<datafilename>.ORA' SIZE 64K);
如果該數據文件中有多個64K的空閑區段，您可能需要使用以下循環
BEGIN
for i in 1..1000000 loop
EXECUTE IMMEDIATE 'alter table <username>.s allocate extent (DATAFILE '||'''E:\xxxx\<datafilename>.ORA''' ||'SIZE 64K) ';
end loop;
end ;
/
繼續分配，直到損壞的塊成為<用戶名>的一部分。使用以下查詢進行確認:
SQL> select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupt block number> between block_id
and block_id + blocks -1 ;
Step 8 -將數據插入虛擬表以格式化塊
--Sample code (depending on the size of the tablespace it may vary):
BEGIN
FOR i IN 1..1000000000 LOOP
INSERT /*+ APPEND */ INTO <username>.s select i, lpad('REFORMAT',3092, 'R') from dual;
commit ;
END LOOP;
END;
Or
BEGIN
FOR i IN 1..1000000000 LOOP
INSERT INTO <username>.s VALUES(i,'x');
END LOOP;
END;
/
Or use the below code which includes 2 loops:
Begin
FOR i IN 1..1000000000 loop
for j IN 1..1000 loop
Insert into <username>.s VALUES(i,'x');
end loop;
commit;
END LOOP;
END;
對于插入到表中的每一行都將觸發觸發器，并且一旦將第一行插入到損壞的塊中，就會產生ORA-20000異常。
Step 9 -確認塊現在沒有損壞
再次對損壞的數據文件(或整個數據庫)運行dbverify或RMAN驗證。它不會將塊顯示為已損壞。
確保為要寫入磁盤的內存中的信息進行了幾次手動日志切換或檢查點操作。
RMAN備份不會報告該塊上的任何錯誤。
Rman> Backup validate check logical datafile <fileno> ;
Or
Rman> validate datafile <fileno> block <blockno reported corrupt>, <blockno reported corrupt> ;
SQL>Select * from v$database_block_corruption ;
Step 10:-刪除在步驟4中創建的虛擬表
SQL> DROP TABLE <username>.s ;
Step 11:-執行手動日志切換和檢查點
執行日志切換和檢查點操作，以便將內存中格式化的塊寫入磁盤，并且dbverify不再報告錯誤
SQL>Alter system switch logfile ;
alter system checkpoint;
Step 12:刪除在步驟6中創建的觸發器
SQL> DROP trigger corrupt_trigger ;
四、控制文件損壞
4.1、報錯
- Error: ORA 227
Text: corrupt block detected in controlfile: (block %s, # blocks %s)
---------------------------------------------------------------------------
Cause: A block header corruption or checksum error was detected on reading
the controlfile.
Action: Use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUP
CONTROLFILE command.
控制文件已損壞。這可能是由 Oracle 外部問題引起的，例如硬件/操作系統問題或 Oracle 缺陷。
4.2、確定是否由Bug 20324049導致錯誤
set numwidth 15
select max(FHCSQ)
, case trunc(max(FHCSQ)/4294967295,1)
when 0.9 then 'WARNING: Reference Bug 20324049'
else 'NO Warning' end "Bug 20324049"
from x$kcvfh;
4.3、處理
step 1、備份控制文件的當前狀態
使用常規副本 (cp) 或任何其他機制備份現有的控制文件。這可用于將來的診斷，以防需要它們進行額外的恢復。
step 2、確定哪個控制文件出現故障
ORA-00227 通常伴隨 ORA-202，它打印受影響的控制文件名。查看警報日志以獲取更多詳細信息。
step 3、對所有控制文件執行DBVERIFY
對所有控制文件執行 DBVERIFY
dbfsize /<path>/<controlfilename>.ctl
Database file: /<path>/<controlfilename>.ctl
Database file type: file system
Database file size: 614 16384 byte blocks
dbv file=/<path>/<controlfilename>.ctl blocksize=16384
4.4 解決辦法
- 方法1、復制另一個
- 方法2、重建控制文件
- 方法3、恢復控制文件，并介質恢復。
alter database backup controlfile to trace;
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/opt/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_17822.trc
SQL> startup nomount;
SQL>@control.sql
http://m.sunline.cc/db/392807
五、undo壞塊
5.1、錯誤
屬于撤消段的塊損壞ORA-01578
ORA-00600 [4193]，ORA-00600 [4194]，ORA-00600 [4037]等...
這些錯誤通常與以下錯誤一起出現:
Error <Error code> encountered while recovering transaction (<undo_segment>,<slot>) on object <object_id>.
5.2、三種場景
- 場景1:沒有待定事務處理。
- 場景2:數據庫運行時有掛起的事務
- 場景3:存在數據庫崩潰的掛起事務
5.3、檢查是否有未決交易
Select u.inst# instid ,
u.name useg ,
u.status$ status ,
x.ktuxeusn usn ,
x.ktuxeslt slt ,
x.ktuxesqn wrp ,
x.ktuxesiz undoblocks
From x$ktuxe x,
undo$ u
Where x.ktuxeusn = u.us#
And x.ktuxesta = 'ACTIVE'
And x.ktuxecfl like 'DEAD%' ;
5.4、解決方案將取決于我們的備份情況，以及是否有掛起的事務。
解決方案將取決于我們的備份情況，以及是否有掛起的事務
5.4.1、塊損壞
rman恢復
Recover tablespace testing dba 29360329 ;
5.4.2、沒有分布式事務
刪除undo表空間的前提是沒有活動事務
show parameter undo_tablespace
select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name='UNDOTBS1';
create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\UNDOTBS02.DBF' size 5000M;
alter system set undo_tablespace = undotbs2 scope=both;
select tablespace_name , status , count(*) from dba_rollback_segs group by tablespace_name , status;
5.4.3、有未決事務
報錯伴隨
Error <Error code> encountered while recovering transaction (<undo_segment>,<slot>) on object <object_id>.
There are different cases:
Error 376 encountered while recovering transaction (A, B) on object YYYY.
(A, B) shows the UNDO segment id and slot number (USN , SLOT)
YYYY shows the object id (NOT the data object id)
Error 600 encountered while recovering transaction (A, B) on object YYYY.
(A, B) shows the UNDO segment id and slot number (USN , SLOT)
YYYY shows the object id (NOT the data object id)
This error can raise without reference to an object_id, but affecting directly to the UNDO segment.
Error XXXX encountered while recovering transaction (A, B).
Note this error has no "on object ...." clause in the error.
This is reported to the alert log when error XXXX is encountered on a UNDO SEGMENT Block.
XXXX is the ORA-XXXX error encountered
(A, B) shows the rollback segment id and slot number (USN , SLOT) of the transaction being recovered.
在這種情況下，最佳選擇是恢復-恢復或RMAN數據塊恢復
如果數據庫關閉且無法啟動
startup mount；
alter system set "_smu_debug_mode" = 1024
這是一個臨時操作，因此在完成嘗試修復問題的過程后必須取消設置
alter database opne;
如果alert.log中報告了任何object_id，請盡可能將其重新創建
如果沒有報告任何對象，請嘗試重新創建還原表空間(如果有待定事務，ORACLE將不允許重新創建它)
alter system set "_smu_debug_mode" = ''
4.4、ORA-00600 [4194]/[4193]
Short Description of ORA-00600[4194]
---------------------------------------
A mismatch has been detected between Redo records and rollback (Undo)
records.
ARGUMENTS:
Arg [a] Maximum Undo record number in Undo block
Arg [b] Undo record number from Redo block
方法一、刪除undo表空間
Step 1
SQL> Startup nomount ; --> using spfile
SQL> Create pfile='/temp/<corrupt_pfile>.ora' from spfile ;
SQL> Shutdown immediate;
Step 2
修改參數文件set Undo_management=Manual
SQL> Startup mount pfile='/temp/<corrupt_pfile>.ora'
SQL> Show parameter undo
SQL> Alter database open ;
SQL> Create rollback segment r01 ;
SQL> Alter rollback segment r01 online ;
Create undo tablespace undotbs_new datafile '<>' size <> M ;
請注意:-您可以延遲刪除舊的還原表空間，這只是為了允許對死事務進行塊清除。
因此，在數據庫啟動并使用新的還原表空間運行幾個小時后，可以執行下面的步驟。
另請注意，如果您的數據庫已使用任何不支持的方法被強制打開(數據文件不同步，歸檔日志丟失)，請不要放棄舊的撤消。
SQL> Drop tablespace <undo tablespace name> including contents and datafiles
Step 3
-------
SQL> Shutdown immediate;
SQL> Startup nomount ; ---> Using spfile
SQL>Alter system set undo_tablespace=<new Undo tablespace created> scope=spfile;
SQL> Shutdown immediate ;
SQL> Startup
Check if error is
對于Rac實例(如果一個實例關閉，另一個啟動并運行)
如果一個節點啟動并運行，而另一個節點出現ORA-00600[4194]/[4193]故障，則從啟動并運行的實例創建一個新的還原表空間，
并將其作為因錯誤而關閉的另一個實例的默認表空間。使用以下命令啟動失敗的實例新的還原表空間。
從啟動并運行的實例
Create undo tablespace undo_new datafile '<filename>' size <> m ;
Alter system set undo_tablespace=<New undo tablespace name> sid=<instance which has corrupt undo tablespace and is down> scope=spfile ;
SQL>Startup mount
SQL>Show parameter undo
SQL>Alter database open ;
SQL>Drop tablespace <Old undo tablespace of the failing instance> including contents and datafiles
五、數據字典不一致
hcheck.sql - Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c and Above (Doc ID 136697.1)
它指的是數據字典表之間的不一致。這些表歸用戶SYS所有，存儲在SYSTEM表空間中，用于跟蹤數據庫中創建的用戶、表、索引等。
提供一個匿名PL/SQL塊來檢查常見的數據字典問題。
該腳本檢查所選字典關系的一致性，并尋找某些已知問題——一些報告的“問題”將是正常的和預期的。
該腳本可用于Oracle版及更高版本。hcheck8i.sql包含8.1。
這是一個輕量級的“只讀”腳本，沒有任何影響。
該腳本主要在Oracle技術支持的指導下使用。
該腳本報告了各種與字典相關的問題，這些問題可能是問題，也可能不是問題。任何報告的問題都應由Oracle支持分析師審查，因為一些報告的“問題”可能是正常的和預期的。
HCKE錯誤被認為是一個潛在的問題。
HCKW錯誤被視為警告。
SQL> spool hcheck.log
SQL> @hcheck
SQL> spool off
$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on ...
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter username: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> spool hcheck.log
SQL> @hcheck
HCheck Version 07MAY18 on .....
----------------------------------------------
Catalog Version 12.2.0.1.0 (1202000100)
db_name:
Is CDB?: YES CON_ID: 3 Container: CDB1_PDB1
Catalog Fixed
Procedure Name Version Vs Release Timestamp Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- LobNotInObj ... 1202000100 <= *All Rel* ..... 09:49:01 PASS
.- MissingOIDOnObjCol ... 1202000100 <= *All Rel* ..... 09:49:01 PASS
...
.- OrphanedTable ... 1202000100 <= *All Rel* ..... 09:49:02 FAIL
HCKE-0019: Orphaned TAB$ (no SEG$) (Doc ID 1360889.1)
ORPHAN TAB$: OBJ#=105785 DOBJ#=105785 TS=7 RFILE/BLOCK=7/113 TABLE=SCOTT.SALES_REP BOBJ#=
.- MaxControlfSeq ... 1202000100 <= *All Rel* ..... 09:49:02 PASS
.- SegNotInDeferredStg ... 1202000100 > 1102000000 ..... 09:49:02 PASS
---------------------------------------
..................... Elapsed: 1 secs
---------------------------------------
Found 1 potential problem(s) and 0 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
PL/SQL procedure successfully completed.
Statement processed.
Complete output is in trace file:
/<path>/diag/rdbms/<db_name>/<oracle_sid>/trace/<oracle_sid>_<ora>_<pid>_HCHECK.trc
SQL> spool off
六、redo損壞
ORA-354 ORA-353 and ORA-312 on Redo Log Group members (Doc ID 332672.1)
6.1、報錯
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 1892904 change 878787 time 12/05/2014 13:17:10
ORA-00312: online log 3 thread 1: '/oracle/dbs/log3_A.dbf'
ORA-00312: online log 3 thread 1: '/oracle/dbs/log3_B.dbf'
如果所有重做成員確實損壞，并且數據庫處于archivelog模式，則預警日志也可能包含下一條消息:
ARCn: All Archive destinations made inactive due to error 354
....
CORRUPTION DETECTED: thread %d sequence %d log %d at block %d. Arch found corrupt blocks
Example:
ARC1: All Archive destinations made inactive due to error 354
....
CORRUPTION DETECTED: thread 1 sequence 28 log 3 at block 1892904. Arch found corrupt blocks
6.2、原因
重做日志組中的在線重做日志文件成員可能已損壞。
如果Oracle在歸檔日志成員時檢測到損壞，它會嘗試從該組的第二個成員讀取相同的重做塊。如果在重做日志組的所有成員中發現該塊已損壞，則歸檔不會繼續進行。
原因通常是由于操作系統故障或硬件故障造成的覆蓋/遺漏。
6.3、解決
驗證哪些重做日志文件成員已損壞
alter session set tracefile_identifier='VALIDATEREDO';
alter system dump logfile '&name' VALIDATE;
oradebug setmypid
oradebug tracefile_name
SQL> alter system dump logfile '/oracle/dbs/log3_A.dbf' validate;
alter system dump logfile '/oracle/dbs/log3_A.dbf' validate
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 1892904 change 869569 time 12/05/2014 13:17:10
ORA-00334: archived log: '/oracle/dbs/log3_A.dbf'
對每個redo驗證
select member
from v$logfile
where group# = &group_number;
在應用以下任何解決方案之前，請備份損壞的重做日志文件，以備進一步分析根本原因時使用。
解決方法一、
如果重做日志成員之一未損壞，則執行下一個命令幾次，直到相應的重做日志組號在視圖v$log中處于status=CURRENT狀態。
alter system switch logfile;
select status
from v$log
where group#=&log_group_number;
可以選擇等待，直到重做日志被重用，并且塊將被修復。
解決方案2
如果所有重做日志成員都已損壞
ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
清除重做日志組后，預警日志可能會更新為下一條消息:
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE [Timestamp] (CHANGE [scn]) CANNOT BE USED FOR RECOVERY.
請確保在清除重做日志組后進行新的備份。
還要注意，如果重做日志組的單個成員編號不能被清除；產生錯誤ORA-1514:
SQL> alter database clear unarchived logfile '/oracle/dbs/log3_A.dbf';
alter database clear unarchived logfile '/oracle/dbs/log3_A.dbf'
*
ERROR at line 1:
ORA-01514: error in log specification: no such log
ORA-01517: log member: '/oracle/dbs/log3_A.dbf'
這是正常現象
七、行和列損壞
這意味著列不包含與其聲明相對應的有效值；示例:列被聲明為日期，但存儲的日期無效。如果塊結構沒問題；例如，帶有check logical選項的dbverify和rman都
沒有檢測到任何問題，則這不是塊損壞，但為了完整起見，此處包含了這一問題。
日期/時間戳列的驗證步驟由以下步驟組成，如果所討論的表有多個日期/時間戳列，可以多次重復驗證步驟:
1)創建一個存儲可疑行的表
2)驗證日期/時間戳列
3)檢查找到的行
4)手動更正日期/時間戳值
7.1、創建一個存儲可疑行的表(假設在表所有者模式中):
SQL>
connect &user/&password
undefine table_name
set escape \
create table &&table_name\_invalid\_date
(row_id rowid, col_name varchar2(31), col_value varchar2(50));
7.2、驗證時間戳列
SQL>
connect &user/&password
undefine table_name
set escape \
create table &&table_name\_invalid\_date
(row_id rowid, col_name varchar2(31), col_value varchar2(50));
set escape \
undefine table_name
undefine column_name
set serveroutput on
declare
cursor c_cur is select rowid,substr(dump(&&column_name),instr(dump(&&column_name),':')+2) dump_col from &&table_name where &&column_name is not null; -- no need to check null values
i integer;
j integer;
k integer:=0;
bc boolean; -- a date can be BC or AD, dump values of century/year will/might be negative in case of BC
bc_ad varchar2(2);
century varchar(3);
year varchar(3);
month number(2);
day number(2);
hour number(2);
minute number(2);
second number(2);
chck_date date;
begin
for c in c_cur loop
j:=instr(c.dump_col,',');
century:=substr(c.dump_col,1,j-1)-100;
if century < 0 /* if century is negative then date is BC */
then
bc:=true;
century:=abs(century);
end if;
if length(century)=1
then century:='0'||century;
end if;
i:=j+1;
j:=instr(c.dump_col,',',1,2);
year:=substr(c.dump_col,i,j-i)-100;
if year < 0 /* if year is negative then date is BC */
then
bc:=true;
year:=abs(year);
end if;
if length(year)=1
then year:='0'||year;
end if;
i:=j+1;
j:=instr(c.dump_col,',',1,3);
month:=substr(c.dump_col,i,j-i);
i:=j+1;
j:=instr(c.dump_col,',',1,4);
day:=substr(c.dump_col,i,j-i);
i:=j+1;
j:=instr(c.dump_col,',',1,5);
hour:=substr(c.dump_col,i,j-i)-1;
i:=j+1;
j:=instr(c.dump_col,',',1,6);
minute:=substr(c.dump_col,i,j-i)-1;
/* timestamp might not contain a full format and end direct after seconds */
i:=j+1;
j:=instr(c.dump_col,',',1,7);
if j=0
then j:=length(c.dump_col)+1;
end if;
second:=substr(c.dump_col,i,j-i)-1;
/* checking/setting date BC or AD */
if bc
then
bc_ad:='bc';
else
bc_ad:='ad';
end if;
/* usage of a separate PL/SQL block to trap the exception locally in order to continue with for in ... loop */
begin
/* checking if the timestamp value can be recomposed to a date */
chck_date:=to_date(day||'-'||month||'-'||century||year||' '||hour||':'||minute||':'||second||' '||bc_ad,'dd-mm-yyyy hh24:mi:ss ad');
exception
when others then
k:=k+1;
insert into &&table_name\_invalid\_date values (c.rowid,'&&column_name',c.dump_col);
end;
end loop;
commit;
/* showing how many columns with invalid date were found */
dbms_output.put_line('No of incorrect date values as found: '||k);
end;
/
7.3、檢查找到的行
set escape \
undefine table_name
column col_name format a20;
column col_value format a40;
set pagesize 9999
select * from &&table_name\_invalid\_date;
ROW_ID COL_NAME COL_VALUE
------------------ -------------------- ----------------------------------------
AAAdtXAAEAAAKIqAAR column1 255,100,0,114,0,0,0,1,16,211,192
AAAdtXAAEAAAKIqAAS column1 255,100,0,114,0,0,0,1,16,211,192
...
AAAdtXAAEAAAKIrAAT column1 18,11,45,192,0,2,0
AAAdtXAAEAAAKIrAAU column1 18,11,45,192,0,2,0
...
AAAdtXAAEAAAKIrAAK column2 255,100,0,114,0,0,0,1,16,211,192
AAAdtXAAEAAAKIrAAT column2 18,11,41,80,0,2,0
AAAdtXAAEAAAKIrAAU column2 18,11,41,80,0,2,0
AAAdtXAAEAAAKIrAAa column2 18,11,35,112,0,0,0,1,16,211,192
AAAdtXAAEAAAKIrAAb column2 18,11,35,112,0,0,0,1,16,211,192
AAAdtXAAEAAAKIrAAe column2 5,74,56,236,18,205,0,17,16,209,248
A correct column value would look like the following:
create table test (t timestamp);
insert into test values (sysdate);
select dump(t) from test;
DUMP(T)
--------------------------------------------------------------------------------
Typ=180 Len=7: 120,109,12,11,10,58,49
120 - 100 = 20 = century -1 (20 implies 21st century)
109 - 100 = 09 = year
12 = month
11 = day
10 - 1 = 9 hour
58 -1 = 57 = min
49 - 1 = 48 = second
檢查找到的每一行，以確認該列的值不正確:
SQL> select <column_name> from <table_name> where rowid='&rowid';
如果列值正確，您可以將其從<表名> _無效日期中刪除，這樣您就可以使用<表名> _無效日期的內容來更正該表。
7.4、手動更正日期/時間戳值
SQL>
set escape
undefine table_name
undefine column_name
update &&table_name set &&column_name=...
where rowid in (select row_id from &&table_name_invalid_date where col_name='&&column_name');
如果您能夠從其他列值中導出日期/時間戳列的正確值，您可能需要在單個基礎上更正行。
八、表/索引不一致
表/索引不一致是指表中的條目在索引中不存在，反之亦然。常見的錯誤有ORA-8102，ORA-600 [kdsgrp1]，ORA-1499
有幾種類型的損壞，如:
數據文件塊損壞-物理/邏輯
表/索引不匹配
范圍不一致
數據字典不一致
8.1、數據文件塊損壞-塊內損壞
它是指可能導致不同錯誤(如ORA-1578、ORA-8103、ORA-1410、ORA-600等)的塊內損壞。
RMAN -識別數據文件塊損壞
backup check logical validate database;
backup check logical database
檢查視圖V＄DATABASE _ BLOCK _ CORRUPTION以確定RMAN檢測到的塊損壞。
DBVerify -識別數據文件塊損壞
dbv file=<datafile name> blocksize=<datafile Block size>
RMAN Vs DBVerify -數據文件塊內損壞
在識別塊內損壞時，使用什么工具是一個兩難的問題。以下是RMAN和DBV的一些比較:
- 當RMAN使用邏輯選項時，它對塊內損壞進行與DBV完全相同的檢查。
- RMAN可以使用多個通道并行運行，這使得它比DBV更快，后者不能在單個命令中并行運行。
- DBV檢查空塊。在10g中，當使用本地管理的表空間時，RMAN可能不會檢查空閑區中的塊。在11g中，RMAN會檢查空閑區和已用區。
- DBV和RMAN (11g)都可以檢查一系列數據塊。RMAN: VALIDATE DATAFILE 1 BLOCK 10 to 100;. DBV: start=10 end=100
- RMAN將損壞信息保存在控制文件中(v＄database _ block _ corruption，v＄backup _ corruption)。DBV沒有。
- RMAN可能不會像報告為邏輯損壞塊的塊中究竟損壞了什么那樣報告損壞細節。DBV在屏幕或日志文件中報告損壞的詳細信息。
- DBV可以掃描SCN高于給定SCN的塊(HIGH_SCN子句)。
- DBV不需要連接到數據庫。
- RMAN不會檢測Doc ID7517208.8中描述的邏輯損壞(DBV有)。11.2之前bug
8.2、識別表/索引不匹配
表/索引不一致是指表中的條目在索引中不存在，反之亦然。常見的錯誤有ORA-8102，ORA-600 [kdsgrp1]，ORA-1499 by "analyze validate structure cascade
-analyze發現不一致命令
analyze table <table name> validate structure cascade <ONLINE>;
ORA-1499. Table/Index row count mismatch (Doc ID 563070.1)
8.3、確定本地管理的表空間中的范圍不一致
它指的是本地管理的表空間(LMT)用來保存已用區和空閑區信息的部分中的不一致。
- 重疊范圍(兩個段可能錯誤地使用同一個塊)。常見的錯誤是ORA-8103、ORA-1410、ORA-600 [kdddgb2]
- 報告為已用的空閑區
- dba_tablespaces.SEGMENT_SPACE_MANAGEMENT='MANUAL'
alter session set tracefile_identifier='TABLESPACE_VERIFY';
execute dbms_space_admin.tablespace_verify('&tablespace_name')
oradebug setmypid
oradebug tracefile_name
--實例輸出
Extent Map Entry Overlaps with Another Extent Map Entry
SegDBA: 0x0fc3e832 : ExtNo 3 UetDBA 0x2ec00091: ExtNbk: 32
SegDBA: 0x1000c8d0 : ExtNo 212 UetDBA 0x2ec00011: ExtNbk: 1280
dba_tablespaces.SEGMENT_SPACE_MANAGEMENT='AUTO'
alter session set tracefile_identifier='ASSM_TABLESPACE_VERIFY';
execute dbms_space_admin.assm_tablespace_verify('&tablespace_name',dbms_space_admin.TS_VERIFY_BITMAPS)
oradebug setmypid
oradebug tracefile_name
如果存在不一致，dbms_space_admin會生成一個跟蹤文件。請注意，如果檢測到不一致，運行dbms_space_admin的會話可能不會在屏幕中報告。
8.4、識別由寫入丟失導致的損壞
當I/O子系統確認數據塊寫入完成時，發生數據塊丟失寫入，而事實上寫入并未發生在永久存儲器中。結果是數據庫中的塊是陳舊/舊的拷貝，它不是邏輯或物理損壞的；塊內部結構是正確的
當與類似ORA-600 kdsgrp1、ORA-8103(舊對象id)等的另一個上下文比較時，具有丟失的改變的塊可能產生幾個錯誤；或者介質恢復期間的下一個錯誤(像在物理備用中):ORA-600 [3020]，ORA-752(如果啟用了db_lost_write_protect)。
-
DBV/RMAN打算識別由丟失寫入引起的不一致:
由于dbverify/rman會運行塊內檢查(塊不會與另一個上下文進行比較)，因此通過丟失IO來識別損壞擴展并不簡單。塊本身是健康的，因為結構是有效的(不是垃圾)。但是，在極少數情況下，數據塊可能會間接暴露于邏輯損壞，尤其是在空間管理區域，如果存在寫入丟失。例如，數據塊在元數據中被標記為已滿，但更改已丟失。后續插入可能會在邏輯上損壞該塊。 -
介質恢復/物理待機
最佳選擇是像備用數據庫一樣進行介質恢復，或者在另一個系統中還原/恢復數據庫。媒體恢復執行檢查以識別塊內容是否是預期的內容，因為重做結構跟蹤塊先前版本(預期的scn ),并將其與當前塊scn進行比較。如果存在不匹配，則生成ORA-600 [3020]或ORA-752。
如果配置了物理備用數據庫，那么現在在主數據庫和備用數據庫上將DB_LOST_WRITE_PROTECT設置為TYPICAL。通過這樣做，主數據庫將為讀取創建重做條目(select語句)，這有助于更快地檢測備用數據庫中丟失的寫入。
有一個運行診斷試驗恢復的選項，這將掃描重做以查找問題，但實際上不會對恢復的數據庫進行任何更改。試用恢復會在alert.log中報告任何其他損壞。恢復…測試語句可用于調用試驗恢復。涉及文件編號283262.1有關試用恢復的更多詳細信息。
- 重疊范圍
由于元數據中的空間管理不一致，丟失的寫入可能會導致致命問題，如數據段相互覆蓋；重疊范圍。
- analyze
運行“analyze table validate structure cascade ”,在將索引內容與相應的表內容進行比較時，可能會檢測到由丟失的寫入導致的不一致。如果不匹配，則報告ORA-1499。它還可以僅在舊塊版本具有不同的數據對象id時識別舊塊版本，這可能導致ORA-8103、ORA-1410等。使用cascade選項分析整個數據庫中的所有表可能需要很長時間。
九、ASM磁盤組損壞信息收集
1、首先，如果受影響的磁盤組已裝載且數據庫已打開，則從與受影響的磁盤組相關聯的所有數據庫運行完整數據庫備份(例如，使用RMAN ),并驗證備份狀態良好。然后繼續下面的步驟。
2、詳細描述腐敗產生時正在進行的工作，或者自上次工作以來發生了什么變化(請非常詳細)？
3、對受影響的磁盤組運行下一次運行狀況檢查，如下所示:
SQL > alter disk group < disk group name > check all no repair；
4、提供ASM alert.log(它將報告上一個命令的結果)。 如果這是一個ASM集群配置，則從所有節點提供ASM alert.log。
5、從受影響的磁盤組獲取AMDU轉儲，如下所示(以網格操作系統用戶身份執行):
<ASM Oracle Home>/bin/amdu -diskstring '<ASM disks location>/*' -dump '<diskgroup>'
$> <ASM Oracle Home>/bin/amdu -diskstring '/dev/rhdisk*' -dump 'DATA'
$> <ASM Oracle Home>/bin/amdu -diskstring '/dev/oracleasm/disks/*' -dump 'DATA'
$> <ASM Oracle Home>/bin/amdu -diskstring '/dev/rdsk/*' -dump 'DATA'
6、此外，為了確認或丟棄物理磁盤上的任何磁盤I/O問題，收集并提供所有節點的操作系統日志(至少包含3個月前到現在的條目),如以下文檔所述:
7、另外，請提供每個受影響和損壞的磁盤的第一個50MB轉儲，如下所示:
$> dd if=<full path affected disk name> of=/tmp/<affected disk name>.dump bs=1048576 count=50
dd if=/dev/oracleasm/disks/DB0054 of=/tmp/DB0054.dump bs=1048576 count=50
十、表/索引行數不匹配
10.1、檢測
SQL> analyze table test validate structure cascade;
analyze table test validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
Table/Index row count mismatch
table 6559 : index 10000, 0
Index root = tsn: 6 rdba: 0x01400091
這意味著:表掃描返回6559行，索引掃描返回10000行。
rdba: 0x01400091是索引段頭相對數據塊地址。它是十進制20971665，即Rfile#=5(相對文件號)Block#=145:
prompt Enter tsn:
accept tsn
prompt Enter rdba (in hex, no '0x' prefix):
accept hex_rdba
set verify off
select file#,
dbms_utility.data_block_address_block(
to_number('&hex_rdba','XXXXXXXXX')) block#
from sys.v$datafile
where ts# = &tsn
and rfile# = dbms_utility.data_block_address_file(
to_number('&hex_rdba','XXXXXXXXX'));
FILE# BLOCK#
---------- ----------
7 145
從dba_segments運行下一個查詢可以識別相關的索引(它需要header_file的絕對文件號):
QUERY 1:
SQL> select owner, segment_name, segment_type
2 from dba_segments
3 where header_file = 7
4 and header_block = 145;
OWNER SEGMENT_NAME SEGMENT_TYPE
-------- --------------- ------------------
SCOTT I_TEST INDEX
10g+中的ORA-600 [kdsgrp1]或較低版本中的ORA-600 [12700]也可能表現出這種邏輯不一致。
10.2原因
表及其索引之間存在邏輯不一致。這種邏輯不一致通常是由于表中的高水位線(HWM)問題造成的，在這種情況下，全表掃描返回的行數可能比索引掃描少。
不一致可能是由Oracle缺陷或操作系統/硬件問題導致的，這些問題會導致IO丟失。
10.3、解決方案
通過運行以下查詢，可以識別通過索引檢索的行，這些行不是通過全表掃描檢索的:
select /*+ INDEX_FFS(<tablename> <indexname identified in 2.1>) */ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from <tablename>
where <indexed column> is not null
minus
select /*+ FULL(<tablename>)*/ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from <tablename>;
Example:
select /*+ INDEX_FFS(TEST I_TEST) */ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from test
where c2 is not null
minus
select /*+ FULL(TEST)*/ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from test;
全表掃描中丟失的值可以使用下一個plsql(腳本1)的索引存儲在另一個表中:
drop table test_copy;
create table test_copy as select * from test where 1=2;
declare
cursor missing_rows is
select /*+ INDEX_FFS(TEST I_TEST) */ rowid rid
from test
where c2 is not null
minus
select /*+ FULL(TEST)*/ rowid rid
from test;
begin
for i in missing_rows loop
insert into TEST_COPY
select /*+ ROWID(TEST) */ * from TEST where rowid = i.rid;
end loop;
end;
/
10.4解決辦法
-當索引的行數少于表的行數時，重新創建索引可能會解決這個問題。
對于索引的行數多于表中的行數，并且表中確實不存在該行的情況，使用本節中描述的第一個查詢中索引返回的rowid，通過“select * from table where rowid = & rowid”進行確認，則解決方案可以是重新創建索引，注意索引可能具有預期的數據，并且可以通過訪問索引列值來保存數據，如下所示:
select /*+ INDEX_FFS(<tablename> <indexname identified in 2.1>) */ rowid, <indexed column 1>, <indexed column 2>...
from <tablename>
where <indexed column> is not null
minus
select /*+ FULL(<tablename>)*/ rowid, <indexed column 1>, <indexed column 2>...
from <tablename>;
十一、提取索引定義
select 'select dbms_metadata.get_ddl ("INDEX", "'||index_name||'", "'||owner||'") from dual;' from dba_indexes where tablespace_name = 'EXAMPLE' and index_name like 'E%';
十二、檢測lob損壞
12.1、嘗試導出包含lob的表時，會出現以下錯誤:
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-31693: Table data object "OWNER"."TABLEABC" failed to load/unload and is being
skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old、
這些錯誤的原因可能是LOB損壞。維護lob將解決Exp/Expdp的問題。
12.2、檢查lob損壞
set serverout on
exec dbms_output.enable(100000);
declare
page number;
len number;
c varchar2(10);
charpp number := 8132/2;
begin
for r in (select rowid rid, dbms_lob.getlength (<your_clob_column>) len
from <your_table_with_clcob_column>) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (<your_clob_column>, 1, 1+ (page * charpp))
into c
from <your_table_with_clcob_column>
where rowid = r.rid;
exception
when others then
dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
dbms_output.put_line (sqlerrm);
end;
end loop;
end if;
end loop;
end;
/
如果沒有錯誤，LOB是OK的。
否則，如果發生錯誤(即ORA-1403、ORA-1578、ORA-1555、ORA-22922 ),則意味著LOB已損壞。
12.3、解決
-使用物理備份還原和恢復LOB段。
-清空lob
2.如果連續兩次運行顯示相同的行，那么這些LOB記錄就有問題。最簡單的方法是刪除帶有上述ROWIDs的lob，并重新構建它們
12.4、如何清空lob
1.使用物理備份還原和恢復LOB段。
2.使用UPDATE語句清空受影響的lob、
3.執行導出，排除損壞的rowids
注意:如果沒有損壞的lob數據，并且問題仍然存在，則重建整個表以解決問題。
1. Create a new temporary table for storing all rowids of the corrupted LOBs. Let's call it "corrupt_lobs"
SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);
2. Make a desc on the table containing the LOB column:
DESC <TABLE_NAME>
Name Null? Type
---------- --------- ------------
<COL1> NOT NULL NUMBER
<LOB_COLUMN> BLOB
-- Run the following PLSQL block:
declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
for cursor_lob in (select rowid r, <LOB_COLUMN> from <TABLE_NAME>) loop
begin
n:=dbms_lob.instr(cursor_lob.<LOB_COLUMN>,hextoraw('889911'));
exception
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/
-- In the end all rowids of the corrupted LOBs will be inserted into the corrupt_lobs newly created table.
-- A possible solution would then be to empty the affected LOBs using a statement like:
SQL> update <TABLE_NAME> set <LOB_COLUMN> = empty_blob() where rowid in (select corrupt_rowid from corrupt_lobs);
(注意:對于BLOB和BFILE列，請使用EMPTY _ BLOB對于CLOB和NCLOB列，使用EMPTY_CLOB )
-- Or export the table without the corrupted row, like:
% expdp system/<PASSWORD> DIRECTORY=my_dir DUMPFILE=<dump_name>.dmp LOGFILE=<logfile_name>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> QUERY=\"WHERE rowid NOT IN \(\'<corrupt_rowid>\'\)\"
十三、索引組織表
讀取表時跳過ORA-8103 ORA-1410 ORA-1578 ORA-600[kdsgrp 1]的腳本
13.1、跳過ORA-1578 ORA-8103 ORA-1410
REM Create a new table based on the table that is producing errors with no rows: --基于產生錯誤且沒有行的表創建一個新表:
create table <new table name>
as
select *
from <original table name>
where 1=2;
REM Create the table to keep track of ROWIDs pointing to affected rows: --創建表來跟蹤指向受影響行的ROWIDs:
create table bad_rows (row_id rowid, oracle_error_code number);
set serveroutput on
DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
CURSOR c1 IS select /*+ index_ffs(tab1 <index name>) parallel(tab1) */ rowid
from <original table name> tab1
where <indexed column> is NOT NULL
order by rowid;
r RowIDTab;
rows NATURAL := 20000;
bad_rows number := 0 ;
errors number;
error_code number;
myrowid rowid;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO r LIMIT rows;
EXIT WHEN r.count=0;
BEGIN
FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
insert into <new table name>
select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)>
from <original table name> A where rowid = r(i);
EXCEPTION
when OTHERS then
BEGIN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR err1 IN 1..errors LOOP
error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
if error_code in (1410, 8103, 1578) then
myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
bad_rows := bad_rows + 1;
insert into bad_rows values(myrowid, error_code);
else
raise;
end if;
END LOOP;
END;
END;
commit;
END LOOP;
commit;
CLOSE c1;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/
如果“壞行總數:”顯示0，并且確定磁盤上存在導致ORA-8103的不正確塊，則意味著該塊是空的(沒有行)，并且沒有數據丟失。
13.2、跳過表中的ORA-600
當ORA-600由不存在的鏈接行(無效的nrid)生成時，如ORA-600 [kdsgrp1]和事件10231不起作用時，這很有用。
create table bad_rows (row_id ROWID
,oracle_error_code number);
rem Create the new empty table:
create table &&new_table
as select *
from &&affected_table
where 1=2;
set serveroutput on
declare
n number:=0;
bad_rows number := 0;
error_code number;
ora600 EXCEPTION;
PRAGMA EXCEPTION_INIT(ora600, -600);
begin
for i in (select rowid rid from &&affected_table) loop
begin
insert into &&new_table
select *
from &&affected_table
where rowid=i.rid;
n:=n+1;
exception
when ora600 then
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,600);
commit;
when others then
error_code:=SQLCODE;
bad_rows := bad_rows + 1;
insert into bad_rows values(i.rid,error_code);
commit;
end;
end loop;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
dbms_output.put_line('Total Good rows: '||n);
end;
/
十四、臨時段損壞
Example 1: Alert Log may show ORA-1578 followed by the corrupt object information with "SEGMENT TYPE = Temporary Segment":
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_8086.trc (incident=2446468):
ORA-01578: ORACLE data block corrupted (file # 1707, block # 233066810)
ORA-01110: data file 1707: '/oracle/dbs/tools.dbf'
Wed Sep 24 16:33:09 2014
Corrupt Block Found
TSN = 73, TSNAME = TOOLS
RFN = 1024, BLK = 2646152, RDBA = 2646152
OBJN = 0, OBJD = 124839000, OBJECT = TOOLS, SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE = Temporary Segment
in this example tablespace TOOLS is a PERMANENT tablespace (dba_tablespaces.contents='PERMANENT').
Example 2: Alert Log may not show any errors but message:
Corrupt Block Found
TSN = 23, TSNAME = TEMP
RFN = 1, BLK = 4608, RDBA = 4198912
OBJN = 298432, OBJD = 4198912, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
select segment_name, segment_type
from dba_segments
where tablespace_name = '&TSNAME'
and header_file = &FILE_NUMBER
and header_block = &BLK;
In the Example 1 above it is:
select segment_name, segment_type
from dba_segments
where tablespace_name = 'TOOLS'
and header_file = 1707
and header_block = 233066810;
SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
1707.233066810 TEMPORARY
If the above query returns a row then fix the corruption by using dbms_space_admin:
exec dbms_space_admin.segment_corrupt('&TSNAME', &RFN, &BLK)
exec dbms_space_admin.segment_drop_corrupt('&TSNAME', &RFN, &BLK)
exec dbms_space_admin.tablespace_rebuild_bitmaps('&TSNAME')
In our example it is:
select relative_fno
from dba_data_files
where tablespace_name = 'TOOLS'
and file_id = 1707;
RELATIVE_FNO
------------
1024
Then remove the temporary Segment and rebuild the tablespace bitmap:
exec dbms_space_admin.segment_corrupt('TOOLS', 1024, 233066810)
exec dbms_space_admin.segment_drop_corrupt('TOOLS', 1024, 233066810)
exec dbms_space_admin.tablespace_rebuild_bitmaps('TOOLS')
十五 、處理Oracle塊損壞
Handling Oracle Block Corruptions (Doc ID 28814.1)
十六、常見報錯
ORA-1578
ORA-1578 The data block indicated was corrupt. This was a physical corruption, also called a media corruption. The cause is unknown but is most likely external to the database. If ORA-26040 is also signaled, the corruption is due to NOLOGGING or UNRECOVERABLE operations.
ORA-1410
This error is raised when an operation refers to a ROWID in a table for which there is no such row.
The reference to a ROWID may be implicit from a WHERE CURRENT OF clause or directly from a WHERE ROWID=... clause.
ORA-1410 indicates the ROWID is for a BLOCK that is not part of this table.
ORA-8103
The object has been deleted by another user since the operation began; example: another session truncated or dropped the segment while the SQL statement was still active.
If the error is reproducible, following may be the reasons:
a.) The header block has an invalid block type.
b.) The data_object_id (seg/obj) stored in the block is different than the data_object_id stored in the segment header. See dba_objects.data_object_id and compare it to the decimal value stored in the block (field seg/obj).
ORA-8102
An ORA-08102 indicates that there is a mismatch between the key(s) stored in the index and the values stored in the table. What typically happens is the index is built and at some future time, some type of corruption occurs, either in the table or index, to cause the mismatch.
ORA-1498
Generally this is a result of an ANALYZE ... VALIDATE ... command.
This error generally manifests itself when there is inconsistency in the data/Index block. Some of the block check errors that may be found:-
a.) Row locked by a non-existent transaction
b.) The amount of space used is not equal to block size
c.) Transaction header lock count mismatch.
While support are processing the tracefile it may be worth the re-running the ANALYZE after restarting the database to help show if the corruption is consistent or if it 'moves'.
Send the tracefile to support for analysis.
If the ANALYZE was against an index you should check the whole object. Eg: Find the tablename and execute:
ANALYZE TABLE xxx VALIDATE STRUCTURE CASCADE;
ORA-1499
An error occurred when validating an index or a table using the ANALYZE command.
One or more entries does not point to the appropriate cross-reference.
ORA-752 or ORA-600 [3020]
Media recovery detected a lost write of a data block. A data block write to storage was lost during normal database operation on the primary database.
This is reporting a lost write during media recovery.
Reference the next article:
Note 1265884.1 : Resolving ORA-00752 or ORA-600 [3020] During Standby Recovery
ORA-26040
Trying to access data in block that was loaded without redo generation using the NOLOGGING/UNRECOVERABLE option.
This Error raises always together with ORA-1578
ORA-600 [12700]
Oracle is trying to access a row using its ROWID, which has been obtained from an index.
A mismatch was found between the index rowid and the data block it is pointing to. The rowid points to a non-existent row in the data block. The corruption can be in data and/or index blocks.
ORA-600 [12700] can also be reported due to a consistent read (CR) problem.
ORA-600 [3020]
This is called a 'STUCK RECOVERY'.
There is an inconsistency between the information stored in the redo and the information stored in a database block being recovered.
This error indicates a lost write or a lost change in the database
ORA-600 [4194]
A mismatch has been detected between Redo records and rollback (Undo) records.
Oracle is validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block.
This error is reported when the validation fails.
ORA-600 [4193]
A mismatch has been detected between Redo records and Rollback (Undo) records.
Oracle is validating the Undo block sequence number in the undo block against the Redo block sequence number relating to the change being applied.
This error is reported when this validation fails.
ORA-600 [4137]
While backing out an undo record (i.e. at the time of rollback) Oracle found a transaction id mismatch indicating either a corruption in the rollback segment or corruption in an object which the rollback segment is trying to apply undo records on.
This would indicate a corrupted rollback segment.
ORA-600 [6101]
Not enough free space was found when inserting a row into an index leaf block during the application of undo.
ORA-600 [2103]
Oracle is attempting to read or update a generic entry in the control file.
If the entry number is invalid, ORA-600 [2130] is logged.
ORA-600 [4512]
Oracle is checking the status of transaction locks within a block.
If the lock number is greater than the number of lock entries, ORA-600 [4512] is reported followed by a stack trace, process state and block dump.
This error possibly indicates a block corruption.
ORA-600 [2662]
A data block SCN is ahead of the current SCN.
The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN identified by the process that is normally close to the database scn.
If the SCN is less than the dependent SCN then ORA-600 [2662] is signaled.
ORA-600 [4097]
Oracle is accessing a rollback segment header to review if a transaction has been committed.
However, the xid given is in the future of the transaction table.
This could be due to a rollback segment corruption issue.
ORA-600 [4000]
It means that Oracle has tried to find an undo segment number in the data dictionary and this undo segment number was not found.
ORA-600 [6006]
Oracle is undoing an index leaf key operation. If the key is not found, ORA-00600 [6006] is logged.
ORA-600[6006] is usually caused by a media corruption problem related to either a lost write to disk or a corruption on disk.
ORA-600 [4552]
This assertion is raised because Oracle is trying to unlock the rows in a block, but receive an incorrect block type.
The second argument is the block type received.
ORA-600[6856]
Oracle is checking that the row slot that is about to be freed is not already on the free list.
This internal error is raised when this check fails.
ORA-600[13011]
During a delete operation Oracle is deleting from a view via an instead-of trigger or an Index organized table and have exceeded a 5000 pass count
ORA-600[13013]
During the execution of an UPDATE statement, after several attempts (Arg [a] passcount) Oracle is unable to get a stable set of rows that conform to the WHERE clause.
Note 816784.1 : How to resolve ORA-00600 [13013], [5001]
ORA-600[13030]
ORA-600[25012]
Oracle is trying to generate the absolute file number given a tablespace number and relative file number and cannot find a matching file number or the file number is zero.
ORA-600[25026]
Looking up/checking a tablespace invalid tablespace ID and/or rdba found
ORA-600[25027]
Invalid tsn and/or relative file number found
ORA-600 [kcbz_check_objd_typ_3]
An object block buffer in memory is checked and is found to have the wrong object id. This is most likely due to corruption.
ORA-600 [kdsgrp1]
Error may be caused by:
Case 1. A row referenced in an index that does not exist in the table
ORA-1499 may be produced by analyze:
analyze table <table name> validate structure cascade online;
Case 2. An non-existent rowid pointed to by a chained row
Run an export (exp) or Full Table Scan to identify if there is a permanent invalid chained row.
ORA-600[kddummy_blkchk] ORA-600[kdblkcheckerror]
ORA-600 [kdbBlkCheckError]
ORA-600 [ktfBlkCheckError]
ORA-600 [ktfBlkCheckError]
ORA-600 [ktsBlkChekError]
ORA-600 [ktspBlkCheckError]
ORA-600 [ktfbnBlkCheckError]
ORA-600 [ktuBlkCheckError]
ORA-600 [kdliBlkCheckError]
ORA-600 [kdxdBlkCheckError]
ORA-600 [kdiBlkCheckError]
ORA-600 [kddummy_blkchk] is for 10g and ORA-600[kdblkcheckerror] for 11g onward.
These errors report a Logical Block Corruption
If the error is raised in a data guard physical standby database, follow the next article:
Note 2821699.1 : Resolving Logical Block Corruption Errors in a Physical Standby Database
ORA-600[ktadrprc-1]
Orphan segment or invalid rdba in Index,Table,Partition etc. Example: An entry in sys.ind$ does not exist in sys.seg$
Note 136697.1 : "hcheck.sql" Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c
ORA-600[ktsircinfo_num1]
This exception occurs when there are problems obtaining the row cache information correctly from sys.seg$. In most cases there is no information in sys.seg$.
Note 136697.1 : "hcheck.sql" Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c
ORA-600[qertbfetchbyrowid]
This error might be that a row was not found in an Index. Perform the check in section "Identify TABLE / INDEX Mismatch" in:
Note 836658.1 : Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes
ORA-600[ktbdchk1-bad dscn]
This exception is raised when Oracle is performing a sanity check on the dependent SCN and fail.
The dependent scn is greater than the current scn.
十七、參考文檔
Primary Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1)
Note 840978.1 : Physical and Logical block corruption
Note 472231.1 : How to identify all the Corrupted Objects in the Database reported by RMAN
Note 819533.1 : How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY
Note 48808.1 : OERR: ORA-00227 corrupt block detected in controlfile: (block %s, # blocks %s) Primary Note / Troubleshooting, Diagnostic and Solution
Note 1950230.1 : Solving UNDO Corruption
Note 281429.1 : Basic Steps to be Followed While Solving ORA-00600 [4194]/[4193] Errors Without Using Unsupported parameter
Note 39283.1 : ORA-600 [4194] "Undo Record Number Mismatch While Adding Undo Record"
Note 431652.1 : How to Change the Existing Undo Tablespace to a New Undo Tablespace
Note 136697.1 : "hcheck.sql" Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c
Note 332672.1 : ORA-354 ORA-353 and ORA-312 on Redo Log Group members
Note 1031381.6 : How to Dump Redo Log File Information.
Note 428526.1 : Baddata Script To Check Database For Corrupt column data
Note 976591.1 : How To validate a date/timestamp column
Note 869305.1 : How To identify a 'corrupt' row when error is raised but no row information provided
Note 136620.1 : Sanity Check of Oracle NUMBERS, How to Find and Patch
Note 836658.1 : Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes
Note 438143.1 : Use of dbms_metadata.get_ddl() to extract Index DDL
Note 394143.1 : How Could I Format The Output From Dbms_metadata.Get_ddl Utility?
Note 28814.1 : Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g
Note 452341.1 : How to detect Lob Corruption
Note 293515.1 : ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors
Note 1527738.1 : SCRIPT to skip ORA-8103 ORA-1410 ORA-1578 ORA-600 [kdsgrp1] when reading a TABLE
Note 1332088.1 : How to clear a block corruption in a TEMPORARY segment
Note 422039.1 : Steps to drop a temporary segment while SMON is not able
Note 556733.1 : DBMS_REPAIR script
Note 68013.1: DBMS_REPAIR example
最后修改時間：2022-12-05 09:32:11
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
