### 塊管理

#### 檢視壞塊
``` sql
--檢查資料檔案是否正常
dbv file=F:\oracle\product\10.2.0\oradata\movo\BLOCK.DBF blocksize=8192

--rman驗證
validate datafile 1; --or validate database; 可以並行

--檢視壞塊
select * from v$database_block_corruption;

--檢視壞塊物件
select tablespace_name,segment_type,owner,segment_name 
from dba_extents 
where file_id=4 and 35 between block_id and block_id+blocks-1;

--or 具體資訊,檢查哪個物件
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#;
```

#### 壞塊處理
``` sql
--可透過rman 備份 修復壞塊，或者填充為空塊
blockrecover datafile 5 block 19;

--跳過壞塊
BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(SCHEMA_NAME => 'HR',
                  OBJECT_NAME => 'EMP2',
                  OBJECT_TYPE => dbms_repair.table_object,
                  FLAGS  => dbms_repair.skip_flag);
END;
/
--取消跳過壞塊
execute dbms_repair.skip_corrupt_block(username,tablename,flags=>dbms_repair.noskip_flag);
```

#### rowid掃描方法
``` sql
--定位壞塊
select dmbs_rowid.rowid_create(1,<data_object_id>,<rfn>,<bl>,0) low_rid from dual;
select dmbs_rowid.rowid_create(1,<data_object_id>,<rfn>,<bl>+1,0) low_rid from dual;

--cts
create table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid < 
'<low_rid>';
create table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid >= '<high_rid>';

--檢查壞塊是否處於表段頭,如果extent_id 等於0，表示段頭
select file_id,block_id,blocks,extent_id from dba_extents where owner='' and segment_name='' and segment_type='TABLE' order by extent_id;

--非空，從索引搶救資料 Fast Full Scan 訪問方式
select /*+ index_ffs (x <index_name>) */ index_col1,index_col2 from tablename x where rowid >= 'low_rid' and row ) */ index_col1,index_col2 from tablename x where rowid >= 'low_rid' and row = <min_col1_value>;;

--物件所佔用的塊
 select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from BOOTSTRAP$;
```
#### UNDO壞塊
``` sql
--跳過
alter system set "_corrupted_rollback_segments"=(r1,r2,r3) scope=spfile;

--設定offline
alter system set "_offline_rollback_segments"=() scope=spfile;
```
**詳細資訊查詢**
``` sql
--檢視錶空間datafile資訊
select status, name, file# from v$datafile where ts# in (Select ts# from v$tablespace where name='UNDOTBS1')

--加入隱含引數
set long 9999
select '*._corrupted_rollback_segments=''' || replace(wm_concat(segment_name), ',', ''',''') || '''' from DBA_ROLLBACK_SEGS where tablespace_name = 'UNDOTBS1';

--檢視undo 段資訊
select segment_name,status from dba_rollback_segs where tablespace_name = 'UNDOTBS1' and status != 'OFFLINE';

--檢視事務
SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE';

col GLOBAL_TRAN_ID for a30
SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING;

SELECT LOCAL_TRAN_ID,IN_OUT,DATABASE,INTERFACE FROM dba_2pc_neighbors;

set serveroutput on
alter system disable distributed recovery;

begin
    for rec in (SELECT KTUXEUSN ||'.'|| KTUXESLT ||'.'|| KTUXESQN as local_trx_id /* Transaction ID */
                  FROM x$ktuxe
                 WHERE ktuxesta!='INACTIVE'
                )
    loop
        
        set transaction use rollback segment SYSTEM;
        delete from sys.pending_trans$ where local_tran_id = rec.local_trx_id;
        delete from sys.pending_sessions$ where local_tran_id = rec.local_trx_id;
        delete from sys.pending_sub_sessions$ where local_tran_id = rec.local_trx_id;
        --commit;
        
        insert into pending_trans$ (
        LOCAL_TRAN_ID, GLOBAL_TRAN_FMT, GLOBAL_ORACLE_ID, STATE, STATUS, SESSION_VECTOR,RECO_VECTOR, TYPE#, FAIL_TIME,RECO_TIME)
        values( rec.local_trx_id, 306206, /* */
        'XXXXXXX.12345.1.2.3', 'prepared','P', hextoraw( '00000001' ), hextoraw( '00000000' ),0, sysdate, sysdate );

insert into pending_sessions$
        values( rec.local_trx_id, 1, hextoraw('05004F003A1500000104'), 'C', 0, 30258592, '', 146);

commit;

EXECUTE IMMEDIATE 'commit force '''|| rec.local_trx_id ||'''';

DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(rec.local_trx_id);

commit;
    end loop;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
end;
/

select segment_name,status from dba_rollback_segs where tablespace_name = 'UNDOTB2' and status != 'OFFLINE';
```

#### LOB壞塊
``` sql
create table corrupt_lobs (corrupt_rowid rowid, err_num number);

--分析壞塊
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_owner.&table_with_lob) 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;
/

--檢視損壞的lob資訊
select * from corrupt_lobs;

--清空損壞的lob行
update EMP
set EMP_XML = empty_blob()
where rowid in (select corrupted_rowid
from corrupt_lobs);
commit;

--匯出
expdp scott/tiger directory=data_pump_dir dumpfile=test.dmp logfile=test.log tables=EMP 
query=\"where rowid not in $\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'$\"
```
**參考**
- 壞塊參考 mos:1526911.1</min_col1_value></index_name></high_rid></low_rid></bl></rfn></data_object_id></bl></rfn></data_object_id>

【BLOCK】Oracle壞塊處理命令參考

相關文章