1. Create a tablespace for the experiment and create the createtablespaceblocktestdatafileu01oradatabys1blocktest. dbfsize1m; colnamefora40selectfile #, namefromv $ datafile; FILE # NAME Partition --------------------------------------------------
1. create tablespace blocktest datafile '/u01/oradata/bys1/blocktest. dbf 'size 1 m; col name for a40 select file #, name from v $ datafile; FILE # NAME --------------------------------------------------
1. Create a tablespace for the experiment and create a table on the tablespace.
Create tablespace blocktest datafile '/u01/oradata/bys1/blocktest. dbf' size 1 m;
Col name for a40
Select file #, name from v $ datafile;
FILE # NAME
--------------------------------------------------
1/u01/oradata/bys1/system01.dbf
2/u01/oradata/bys1/sysaux01.dbf
3/u01/oradata/bys1/undotbs01.dbf
4/u01/oradata/bys1/users01.dbf
5/u01/oradata/bys1/example01.dbf
6/u01/oradata/bys1/blocktest. dbf
BYS @ bys1> create table test9 tablespace blocktest as select * from scott. emp;
Table created.
BYS @ bys1> select count (*) from test9;
COUNT (*)
----------
14
2. Use RMAN to back up the database. Here I only backed up the data files that are prepared to create bad blocks for quick experiments.
[Oracle @ bys001 ~] $ Rman target/
Recovery Manager: Release 11.2.0.1.0-Production on Fri Sep 13 13:28:36 2013
Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.
Connected to target database: BYS1 (DBID = 3957527513)
RMAN> backup datafile 6;
Starting backup at 13-SEP-13
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting full datafile backup set
Channel ORA_DISK_1: specifying datafile (s) in backup set
Input datafile file number = 00006 name =/u01/oradata/bys1/blocktest. dbf
Channel ORA_DISK_1: starting piece 1 at 13-SEP-13
Channel ORA_DISK_1: finished piece 1 at 13-SEP-13
Piece handle =/u01/flash_recovery_area/BYS1/backupset/2013_09_13/partition _. bkp tag = TAG20130913T150457 comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-SEP-13
RMAN> list backupset 7;
List of Backup Sets
==============================
BS Key Type LV Size Device Type Elapsed Time Completion Time
-------------------------------------------------------------
7 Full 1.02 m disk 00:00:00 13-SEP-13
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20130913T150457
Piece Name:/u01/flash_recovery_area/BYS1/backupset/2013_09_13/o1_mf_nnndf_TAG20130913T150457_935g8s7w _. bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---------------------------------
6 Full 1910363 13-SEP-13/u01/oradata/bys1/blocktest. dbf
3. Simulate the generation of a bad block: I used VI to delete some of the content methods in the data file.
[Oracle @ bys001 bys1] $ vi blocktest. dbf is garbled after it is opened. You can simply delete the data in the middle of a point. Sometimes, you need to delete the data multiple times before it can cause bad blocks. After my first experiment, I made a bad block. Later I tried it multiple times. Then go to the database to query the TEST9 table created on the blocktest. tablespace. An error is reported and a prompt is displayed: the file number is 6, and the bad block is 10:
[Oracle @ bys001 bys1] $ sqlplus bys/bys
BYS @ bys1> select * from test9;
Select * from test9
*
ERROR at line 1:
ORA-01578: ORACLE data block upload upted (file #6, block #10)
ORA-01110: data file 6: '/u01/oradata/bys1/blocktest. dbf'
4. Go to RMAN to fix Bad blocks.
Optional: -- I have not done
RMAN> backup validate datafile 6; this command is used to verify the file, you can also find bad blocks.
Then query from SQLPLUS: select * from v $ database_block_corruption;
Directly use RMAN to specify the recovered file and BLOCK number"
Run the following command to repair Bad blocks:
RMAN> blockrecover datafile 6 block 10 from backupset;
RMAN> exit
6. query the table in SQLPLUS
BYS @ bys1> select count (*) from test9;
COUNT (*)
----------
14