Oracleデータベースのブロック破損を修復する

ブロックが解放されたら、次のコマンドを使用して、破損したブロックを強制的にクリアできます。これは、セグメントの一部ではない破損したブロックのフォーマットとも呼ばれます。

1。ユーザーを作成し、ユーザーにデータベースへのアクセスを許可します。

create user Scott identified by password default tablespace SYSTEM;
grant resource, connect, create table, create trigger to Scott;

2。データファイルに対して破損したブロックを特定します

[Thu Nov 17 11:59:19 orbdev@samusxxxxxxxx:~ ] $ dbv file='/mnt/apps_st/samusxxxxxxxx_data2/system09.dbf' userid=sys/xxxxx

DBVERIFY: Release 11.2.0.4.0 - Production on Thu Nov 17 11:59:21 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting: FILE = /mnt/apps_st/samusxxxxxxxx_data2/system09.dbf
Page 173191 is marked corrupt
Corrupt block relative dba: 0x5802a487 (file 352, block 173191)
Completely zero block found during dbv:

Page 173192 is marked corrupt
Corrupt block relative dba: 0x5802a488 (file 352, block 173192)
Completely zero block found during dbv:

Page 173193 is marked corrupt
Corrupt block relative dba: 0x5802a489 (file 352, block 173193)
Completely zero block found during dbv:

Page 173194 is marked corrupt
Corrupt block relative dba: 0x5802a48a (file 352, block 173194)
Completely zero block found during dbv:

Page 173195 is marked corrupt
Corrupt block relative dba: 0x5802a48b (file 352, block 173195)
Completely zero block found during dbv:

Page 173196 is marked corrupt
Corrupt block relative dba: 0x5802a48c (file 352, block 173196)
Completely zero block found during dbv:

Page 173197 is marked corrupt
Corrupt block relative dba: 0x5802a48d (file 352, block 173197)
Completely zero block found during dbv:

Page 173198 is marked corrupt
Corrupt block relative dba: 0x5802a48e (file 352, block 173198)
Completely zero block found during dbv:

Page 173199 is marked corrupt
Corrupt block relative dba: 0x5802a48f (file 352, block 173199)
Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined         : 917504
Total Pages Processed (Data): 253735
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 335744
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 375
Total Pages Processed (Seg)  : 17
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 327624
Total Pages Marked Corrupt   : 9
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3412538527 (1421.3412538527)

[Thu Nov 17 12:00:13 orbdev@samusxxxxxxxx:~ ] $

3。空き容量を探す

Select * from dba_free_space where file_id= <Absolute file number> and <corrupted block number> between block_id and block_id + blocks -1;


SQL> Select * from dba_free_space where file_id=352 and 173191 between block_id and block_id + blocks -1;

TABLESPACE_NAME   FILE_ID   BLOCK_ID     BYTES    BLOCKS   RELATIVE_FNO
--------------- ---------- ---------- ---------- --------- ------------
SYSTEM              352     173196      73728        9          352

4。最初に破損したブロックを再フォーマットします

破損したすべてのブロックが再フォーマットされるまで、手順4を繰り返します。

create table scott.s (n number,c varchar2(4000)) nologging tablespace SYSTEM;

select owner,table_name,tablespace_name from dba_tables where table_name='S';

SQL> CREATE OR REPLACE TRIGGER corrupt_trigger
   AFTER INSERT ON scott.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;
 /

Enter value for blocknumber: 173191
old   8:   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
new   8:   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=173191)

Enter value for filenumber: 352
old   9:  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
new   9:  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=352) THEN

Trigger created.

Select BYTES/1024/1024 from dba_free_space where file_id=352 and 173191 between block_id and block_id + blocks -1;

72K

SQL> BEGIN
   FOR i IN 1..100000 LOOP
      EXECUTE IMMEDIATE 'alter table scott.s allocate extent (DATAFILE '||'''/mnt/apps_st/samusxxxxxxxx_data2/system09.dbf''' ||'SIZE 72K)';
   END LOOP;
END;
/

SQL> BEGIN
  FOR i IN 1..1000000 LOOP
     INSERT /*+ APPEND */ INTO scott.s select i, lpad('REFORMAT',3092, 'R') from dual;
  commit ;
  END LOOP;
END;
/

SQL> Select * from v$database_block_corruption;

no rows selected

DROP TABLE scott.s;

Alter system switch logfile;

Alter system checkpoint;

DROP trigger corrupt_trigger;

5。破損したブロックが修正されていることを確認します

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

no rows selected

[Wed Nov 16 08:12:37 orbdev@samusxxxxxx:~ ] $ dbv file='/mnt/apps_st/samusxxxxxx_data2/system09.dbf' userid=sys/****

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Nov 16 08:12:41 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

DBVERIFY - Verification starting : FILE = /mnt/apps_st/samusxxxxxx_data2/system09.dbf

DBVERIFY - Verification complete

Total Pages Examined     : 655359
Total Pages Processed (Data) : 260868
Total Pages Failing  (Data) : 0
Total Pages Processed (Index): 340482
Total Pages Failing  (Index): 0
Total Pages Processed (Other): 278
Total Pages Processed (Seg): 15
Total Pages Failing (Seg): 0
Total Pages Empty     : 53716
Total Pages Marked Corrupt : 0
Total Pages Influx     : 0
Total Pages Encrypted   : 0
Highest block SCN     : 136830000 (1422.136830000)

[Wed Nov 16 08:14:50 orbldev2@samusxxxxxx:~ ] $