Oracle repair of Bad blocks 1

Repair of oracle11g Bad blocks
I. a brief introduction to the bbed command. Later, we will use this tool to construct the block checksum and do not express it to simulate Bad blocks.

Show all current configuration options
Info: list the files that can be processed by the current bbed.
Set dba fileid, block: set the id and block number of the data file to be processed.
Set dba fileid, block can also be replaced by set file fileid and set block blockno
Set offset xxx offset uses the set block number setting as the baseline offset for the number of bytes of the current block number
Dump/v displays the content of the current data block. By default, the content is displayed starting from the number of offset bytes set for the current data block. The/v parameter shows the detailed content, which is detailed to what realm, and will be known later.
Find/c search content TOP/c specify search content as character TOP specify from the data block header offset is 0 start the entire search to find a display, if you want to find the next character, specify f.
The content to be modified in modify/c is modified by default from the number of file, block number, and offset bytes specified in set.

Ii. Create a test table
SQL> create table jiujian (des varchar (30) tablespace pos;

Table created.

SQL> insert into jiujian values ('zhangxu love oracle ');

1 row created.

SQL> select * from jiujian;

DES
------------------------------
Zhangxu love oracle

2. query the block where the jiujian table is located.
Statement: select rowid,
Dbms_rowid.rowid_relative_fno (rowid) rel_fno,
Dbms_rowid.rowid_block_number (rowid) blockno,
Dbms_rowid.rowid_row_number (rowid) rowno
From jiujian;
SQL> select rowid,
2 dbms_rowid.rowid_relative_fno (rowid) rel_fno,
3 dbms_rowid.rowid_block_number (rowid) blockno,
4 dbms_rowid.rowid_row_number (rowid) rowno
5 from jiujian;

ROWID REL_FNO BLOCKNO ROWNO
------------------------------------------------
AAATqzAAFAAAACHAAA 5 135 0
BLOCKNO 135 is the block where the table jiujian is located.

3. Create a configuration file for bbed.
[Oracle @ oracle ~] $ Cat bbed. par
Blocksize = 8192
Listfile =/oracle/file
Mode = edit
Text file Content
[Oracle @ oracle ~] $ Cat file
1/oracle/CRM/system01.db 786432000
2/oracle/CRM/sysaux01.dbf 566231040
3/oracle/CRM/undotbs01.dbf 104857600
4/backup/users01.dbf 1827143680
5/oracle/CRM/pos. DB 524288000
6/oracle/CRM/erp. dbf 104857600
7/oracle/CRM/user01.dbf 5242880
10/oracle/CRM/undotbs02.dbf 104857600
The file content is obtained by the following statement:
SQL> select file # | ''| name |'' | bytes from v $ datafile;

FILE # | ''| NAME |'' | BYTES
--------------------------------------------------------------------------------
1/oracle/CRM/system01.db 786432000
2/oracle/CRM/sysaux01.dbf 566231040
3/oracle/CRM/undotbs01.dbf 104857600
4/backup/users01.dbf 1827143680
5/oracle/CRM/pos. DB 524288000
6/oracle/CRM/erp. dbf 104857600
7/oracle/CRM/user01.dbf 5242880
10/oracle/CRM/undotbs02.dbf 104857600

4. Construct Bad blocks with bbed

[Oracle @ oracle ~] $ Bbed parfile = bbed. par
Password: blockedit

BBED> show display Configuration
FILE #1
BLOCK #1
OFFSET 0
DBA 0x00400001 (4194305)
FILENAME/oracle/CRM/system01.dbf
BIFILE bifile. bbd
LISTFILE/oracle/file
Blocsize 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 1, 512
LOGFILE log. bbd
SPOOL No

BBED> info: displays the numbers and locations of data files processed by bbed.
File # Name Size (blks)
-------------------
1/oracle/CRM/system01.db 96000
2/oracle/CRM/sysaux01.dbf 69120
3/oracle/CRM/undotbs01.dbf 12800
4/backup/users01.dbf 223040
5/oracle/CRM/pos. DB 64000
6/oracle/CRM/erp. dbf 12800
7/oracle/CRM/user01.dbf 640
10/oracle/CRM/undotbs02.dbf 12800

BBED> set dba 5,135 set the current data file number and block number
DBA 0x01400087 (20971655 5,135)

Run the show command to confirm the settings of BBED> show.
FILE #5
BLOCK #135.
OFFSET 0
DBA 0x01400087 (20971655 5,135)
FILENAME/oracle/CRM/pos. dbf
BIFILE bifile. bbd
LISTFILE/oracle/file
Blocsize 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 1, 512
LOGFILE log. bbd
SPOOL No

BBED> find/c zhangxu TOP: locate the first character zhangxu in the current data block
File:/oracle/CRM/pos. dbf (5)
Block: 135 Offsets: 8169 to 8191 Dba: 0x01400087
------------------------------------------------------------------------
7a68616e 67787520 6c6f7665 206f3161 636c6501 066c05

<32 bytes per line>
The preceding output shows that the current string zhangxu is at the offset of 8169.
 
BBED> set offset 8169 change the current offset to 8169
OFFSET 8169

BBED> dump/v displays the data block content from the specified offset.
File:/oracle/CRM/pos. dbf (5)
Block: 135 Offsets: 8169 to 8191 Dba: 0x01400087
-------------------------------------------------------
7a68616e 67787520 6c6f7665 206f3161 l zhangxu love ora
636c6501 066c05 l cle... l.

<16 bytes per line>
From the above and below sentences, we can see that dump/v is more detailed than dump. We can see the corresponding hexadecimal content.
BBED> dump
File:/oracle/CRM/pos. dbf (5)
Block: 135 Offsets: 8169 to 8191 Dba: 0x01400087
------------------------------------------------------------------------
7a68616e 67787520 6c6f7665 206f3161 636c6501 066c05 note that there is no hair connection here

<32 bytes per line>

BBED> modify/c jiujian is replaced with the character jiujian starting from offset 8169.
File:/oracle/CRM/pos. dbf (5)
Block: 135 Offsets: 8169 to 8191 Dba: 0x01400087
------------------------------------------------------------------------
6a69756a 69616e20 6c6f7665 206f3161 636c6501 06b7f5

<32 bytes per line>

BBED> dump/v indicates whether the replacement is successful.
File:/oracle/CRM/pos. dbf (5)
Block: 135 Offsets: 8169 to 8191 Dba: 0x01400087
-------------------------------------------------------
6a69756a 69616e20 6c6f7665 206f3161 l jiujian love ora
636c6501 06b7f5 l cle... incubator

<16 bytes per line>
Set the tablespace offline and then go online or restart the database to see the bad block effect.
SQL> select * from jiujian;
Select * from jiujian
*
ERROR at line 1:
ORA-01578: ORACLE data block upted (file #5, block #135)
ORA-01110: data file 5: '/oracle/CRM/pos. dbf'

5-to-5 bad block repair
1. query bad block information
SQL> select * from v $ database_block_corruption;

FILE # BLOCK # BLOCKS upload uption_change # upload uptio
---------------------------------------------------------
5 135 1 0 CHECKSUM
2. The following two methods are provided to fix the block.
Run recover .... Block command to restore the specified Block
[Oracle @ oracle ~] $ Rman target/

Recovery Manager: Release 11.2.0.2.0-Production on Sun Sep 23 11:09:32 2012

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

Connected to target database: CRM (DBID = 3599153036)

RMAN> recover datafile 5 block 135;

Starting recover at 11:10:32
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 199 device type = DISK
Allocated channel: ORA_DISK_2
Channel ORA_DISK_2: SID = 72 device type = DISK

Channel ORA_DISK_1: restoring block (s)
Channel ORA_DISK_1: specifying block (s) to restore from backup set
Restoring blocks of datafile 00005
Channel ORA_DISK_1: reading from backup piece/backup/20120913_ebnl4bil_1_1
Channel ORA_DISK_1: piece handle =/backup/20120913_ebnl4bil_1_1 tag = TAG20120913T195604
Channel ORA_DISK_1: restored block (s) from backup piece 1
Channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35

Starting media recovery

Archived log for thread 1 with sequence 82 is already on disk as file/oracle/archive/10982_791488634.dbf
Archived log for thread 1 with sequence 83 is already on disk as file/oracle/archive/route 83_791488634.dbf
Archived log for thread 1 with sequence 84 is already on disk as file/oracle/archive/1_84_791488634.dbf
Archived log for thread 1 with sequence 85 is already on disk as file/oracle/archive/1_85_791488634.dbf
Archived log for thread 1 with sequence 86 is already on disk as file/oracle/archive/10986_791488634.dbf
Archived log for thread 1 with sequence 87 is already on disk as file/oracle/archive/10987_791488634.dbf
Archived log for thread 1 with sequence 88 is already on disk as file/oracle/archive/10988_791488634.dbf
Archived log for thread 1 with sequence 89 is already on disk as file/oracle/archive/10989_791488634.dbf
Archived log for thread 1 with sequence 90 is already on disk as file/oracle/archive/1_90_791488634.dbf
Archived log for thread 1 with sequence 91 is already on disk as file/oracle/archive/41091_791488634.dbf
Archived log for thread 1 with sequence 92 is already on disk as file/oracle/archive/10992_791488634.dbf
Archived log for thread 1 with sequence 93 is already on disk as file/oracle/archive/10993_791488634.dbf
Archived log for thread 1 with sequence 94 is already on disk as file/oracle/archive/1_94_791488634.dbf
Archived log for thread 1 with sequence 95 is already on disk as file/oracle/archive/10995_791488634.dbf
Archived log for thread 1 with sequence 96 is already on disk as file/oracle/archive/10996_791488634.dbf
Archived log for thread 1 with sequence 97 is already on disk as file/oracle/archive/10997_791488634.dbf
Channel ORA_DISK_1: starting archived log restore to default destination
Channel ORA_DISK_1: restoring archived log
Archived log thread = 1 sequence = 81
Channel ORA_DISK_1: reading from backup piece/backup/20120913_ednl4bp0_1_1
Channel ORA_DISK_1: piece handle =/backup/20120913_ednl4bp0_1_1 tag = TAG20120913T195928
Channel ORA_DISK_1: restored backup piece 1
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Media recovery complete, elapsed time: 00:00:03
Finished recover at 11:11:19

SQL> select * from jiujian;

DES
------------------------------
Zhangxu love oracle

SQL> select * from v $ database_block_corruption;

No rows selected
You can see that running recover .... Block command to restore the specified Block and clear the bad Block content in view v $ database_block_corrutption

2. Fix all the bad blocks in the view V $ database_block_partition uption and Recover the partition uption list. Here, the bad blocks have been re-constructed)
[Oracle @ oracle ~] $ Rman target/

Recovery Manager: Release 11.2.0.2.0-Production on Mon Sep 24 21:41:42 2012

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

Connected to target database: CRM (DBID = 3599153036)

RMAN> recover upload uption list;

Starting recover at 21:42:01
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 70 device type = DISK
Allocated channel: ORA_DISK_2
Channel ORA_DISK_2: SID = 131 device type = DISK

Channel ORA_DISK_1: restoring block (s)
Channel ORA_DISK_1: specifying block (s) to restore from backup set
Restoring blocks of datafile 00005
Channel ORA_DISK_1: reading from backup piece/backup/20120913_ebnl4bil_1_1
Channel ORA_DISK_1: piece handle =/backup/20120913_ebnl4bil_1_1 tag = TAG20120913T195604
Channel ORA_DISK_1: restored block (s) from backup piece 1
Channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35

Starting media recovery
Media recovery complete, elapsed time: 00:00:07

Finished recover at 21:42:47

SQL> select * from v $ database_block_corruption;

No rows selected

Summary: both the data block recovery command recover datafile xxx block xxx and the command recover partition uption list can restore Bad blocks and automatically clear records about bad blocks in view v $ database_block_partition uption.

This article is from the "myblog" blog, please be sure to keep this source http://jiujian.blog.51cto.com/444665/1006025