Oracle人为制造物理坏块的方法（ORA-01578）

电玩女神 2022-06-02 11:53 195阅读 0赞

有的时候为了测试，我们不得不人为制造物理坏块，下面介绍如何制造坏块：

10:06:02 SQL> create table test (id int,times date);
    
    Table created.
    
    10:07:05 SQL> insert into test values(1,sysdate);
    
    1 row created.
    
    10:07:15 SQL> commit;
    
    Commit complete.
    
    10:07:19 SQL> insert into test values(2,sysdate);
    
    1 row created.
    
    10:07:30 SQL> commit;
    
    Commit complete.
    
    10:07:35 SQL> select * from test;
    
    	ID TIMES
    ---------- ---------
    	 1 12-JAN-18
    	 2 12-JAN-18

10:13:53 SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from test where rownum<10;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
    ------------------------------------ ------------------------------------
    				 132					5
    				 132					5

11:52:14 SQL> alter system checkpoint;

cebpm:/data/backup/cebpm/fullback@cebpm>rman target /
    
    Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 12 11:52:49 2018
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: CEBPM (DBID=3677012495)
    
    RMAN> recover datafile 5 block 132 clear;
    
    Starting recover at 2018/01/12 11:54:10
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=56 device type=DISK
    Finished recover at 2018/01/12 11:54:14

11:53:10 SQL> alter system flush buffer_cache;
    11:54:27 SQL> select count(*) from test;
    select count(*) from test
                         *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 5, block # 132)
    ORA-01110: data file 5: '/data/CEBPM/datafile/test01.dbf'