[20150513]人为破坏数据块.txt
--演示的目的,参考链接:
http://www.askmaclean.com/archives/oracle-make-block-physical-corruption.html
--不要在生产系统测试!!!!!
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