[20150513]人為破壞資料塊.txt

[20150513]人為破壞資料塊.txt

--演示的目的,參考連結:

--不要在生產系統測試!!!!!

1.建立測試環境:

SCOTT@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SCOTT@test> create table depty as select * from dept ;
Table created.

SCOTT@test> select rowid,depty.* from depty ;
ROWID                    DEPTNO DNAME          LOC
------------------ ------------ -------------- -------------
AAAPbxAAEAAAAUkAAA           10 ACCOUNTING     NEW YORK
AAAPbxAAEAAAAUkAAB           20 RESEARCH       DALLAS
AAAPbxAAEAAAAUkAAC           30 SALES          CHICAGO
AAAPbxAAEAAAAUkAAD           40 OPERATIONS     BOSTON

SCOTT@test> @ &r/lookup_rowid AAAPbxAAEAAAAUkAAA
      OBJECT         FILE        BLOCK          ROW DBA                  TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
       63217            4         1316            0 4,1316               alter system dump datafile 4 block 1316

--保證髒塊寫盤.
SCOTT@test> alter system checkpoint;
System altered.

--安全需要備份資料檔案.
RMAN> backup datafile 4 format '/tmp/users_%u' ;
Starting backup at 2015-05-13 12:02:58
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/mnt/ramdisk/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-05-13 12:02:59
channel ORA_DISK_1: finished piece 1 at 2015-05-13 12:03:00
piece handle=/tmp/users_01q6r3rj tag=TAG20150513T120259 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-05-13 12:03:00

2.開始測試:

SCOTT@test> alter system flush buffer_cache;
System altered.

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 1316 clear;

Starting blockrecover at 2015-05-13 12:07:46
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
Finished blockrecover at 2015-05-13 12:07:46

SCOTT@test> select rowid,depty.* from depty ;
select rowid,depty.* from depty
                          *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1316)
ORA-01110: data file 4: '/mnt/ramdisk/test/users01.dbf'

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 1316 ;

Starting blockrecover at 2015-05-13 12:08:51
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /tmp/users_01q6r3rj
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/tmp/users_01q6r3rj tag=TAG20150513T120259
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished blockrecover at 2015-05-13 12:08:54

==補充說明,10g不支援如下格式:
RMAN> recover datafile 4 block 1316 ;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "block": expecting one of: "archivelog, auxiliary, allow, check, comma, delete, from, high, noredo, noparallel, parallel, ;, skip, test, until, undo"
RMAN-01007: at line 1 column 20 file: standard input

RMAN> recover datafile 4 block 1316 clear;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "block": expecting one of: "archivelog, auxiliary, allow, check, comma, delete, from, high, noredo, noparallel, parallel, ;, skip, test, until, undo"
RMAN-01007: at line 1 column 20 file: standard input
================

SCOTT@test> select rowid,depty.* from depty ;

ROWID                    DEPTNO DNAME          LOC
------------------ ------------ -------------- -------------
AAAPbxAAEAAAAUkAAA           10 ACCOUNTING     NEW YORK
AAAPbxAAEAAAAUkAAB           20 RESEARCH       DALLAS
AAAPbxAAEAAAAUkAAC           30 SALES          CHICAGO
AAAPbxAAEAAAAUkAAD           40 OPERATIONS     BOSTON

--資料恢復.

== >
補充一點:11g才可以使用如下,10g不行!在11g下做的測試,省略許多步驟:

RMAN> RECOVER DATAFILE 4 BLOCK 1523 clear;

Starting recover at 2015-05-13 15:14:09
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Finished recover at 2015-05-13 15:14:10

 

RMAN> RECOVER corruption list;

Starting recover at 2015-05-13 15:15:52
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /tmp/users_3gq6rf0g
channel ORA_DISK_1: piece handle=/tmp/users_3gq6rf0g tag=TAG20150513T151319
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2015-05-13 15:16:00

SCOTT@test> select rowid,depty.* from depty ;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AABKCeAAEAAAAXzAAA         10 ACCOUNTING     NEW YORK
AABKCeAAEAAAAXzAAB         20 RESEARCH       DALLAS1
AABKCeAAEAAAAXzAAC         30 SALES          CHICAGO
AABKCeAAEAAAAXzAAD         40 OPERATIONS     BOSTON
AABKCeAAEAAAAXzAAE         50 MARKETING      LONDON