Shulou(Shulou.com)05/31 Report--

Editor to share with you how to achieve bad block media recovery based on RMAN in Oracle, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

For physically damaged data blocks, we can recover the damaged blocks through the RMAN Block Media recovery (BLOCK MEDIA RECOVERY) function when there is a backup.

There is no need to restore the entire database or all files to repair these small amounts of damaged data blocks. But only if you have an available RMAN backup.

Therefore, whenever a backup is everything. In this article, we will simulate the generation of a bad block, and then use RMAN to achieve bad block recovery.

Description:

When a bad block occurs, the following error is reported when the business accesses the bad block:

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 18, block # 130)

ORA-01110: data file 18:'/ ora11gSource/ora11g/tbs_tmp.dbf'

Action:

1 create a tablespace for presentation

Create tablespace tbs_tmp datafile'/ ora11gSource/ora11g/tbs_tmp.dbf' size 10m autoextend on

2 create object tb_tmp based on the new data file

Conn scott/tiger

Create table tb_tmp tablespace tbs_tmp as select * from dba_objects

SQL > col file_name format A60

SQL > select file_id,file_name from dba_data_files where tablespace_name='TBS_TMP'

FILE_ID FILE_NAME

18 / ora11gSource/ora11g/tbs_tmp.dbf

3 Information on the table object tb_tmp, corresponding file information, header block, total number of blocks

SQL > select segment_name, header_file, header_block,blocks

2 from dba_segments

3 where segment_name = 'TB_TMP' and owner='SCOTT'

SEGMENT_N HEADER_FILE HEADER_BLOCK BLOCKS

--

TB_TMP 18 130 1280

4 use rman to make a backup of the data file

$$ORACLE_HOME/bin/rman target /

RMAN > backup datafile 18 tag=health

Starting backup at 12-JUN-18

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=37 device type=DISK

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Input datafile file number=00018 name=/ora11gSource/ora11g/tbs_tmp.dbf

Channel ORA_DISK_1: starting piece 1 at 12-JUN-18

Channel ORA_DISK_1: finished piece 1 at 12-JUN-18

Piece handle=/ora11gSource/zhida/ORA11G/backupset/2018_06_12/o1_mf_nnndf_HEALTH_fkz35h7r_.bkp tag=HEALTH comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 12-JUN-18

5 artificial damage to a single block of data

Use the dd command included with linux to damage a single block of data

Ora11g [Redora] / home/ora11g > dd of=/ora11gSource/ora11g/tbs_tmp.dbf bs=8192 conv=notrunc seek=130 EOF

031 records in

031 records out

17 bytes (17 B) copied, 5.9505e-05 seconds, 286 kB/s

6 trigger the object where the bad block is located

Clear buffer cache

Conn scott/tiger

Alter system flush buffer_cache

Query table object tb_tmp, received ORA-01578

SQL > select count (*) from tb_tmp

Select count (*) from tb_tmp

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 18, block # 130)

ORA-01110: data file 18:'/ ora11gSource/ora11g/tbs_tmp.dbf'

Query view v$database_block_corruption, indicating bad blocks. Note that the view may not return any data. If no data is returned, execute backup validate first.

SQL > select * from v$database_block_corruption

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

--

18 130 1 0 CORRUPT

7 use the dbv tool to check bad blocks

Ora11g [Redora] / home/ora11g > dbv file=/ora11gSource/ora11g/tbs_tmp.dbf feedback=1000

DBVERIFY: Release 11.2.0.4.0-Production on Wed Jun 13 10:25:13 2018

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

DBVERIFY-Verification starting: FILE = / ora11gSource/ora11g/tbs_tmp.dbf

Page 130 is marked corrupt

Corrupt block relative dba: 0x04800082 (file 18, block 130)

Bad header found during dbv:

Data in bad block:

Type: 67 format: 7 rdba: 0x65747075

Last change scn: 0x636f.6c622064 seq: 0x6b flg: 0x21

Spare1: 0x72 spare2: 0x72 spare3: 0x0

Consistency value in tail: 0x8d8d2301

Check value in block header: 0xc50a

Block checksum disabled

..

DBVERIFY-Verification complete

Total Pages Examined: 1536

Total Pages Processed (Data): 1196

Total Pages Failing (Data): 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 154

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 185

Total Pages Marked Corrupt: 1

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 939342345 (3332.939342345)

8 use blockrecover below to recover bad blocks

RMAN > blockrecover datafile 18 block 130

Starting recover at 13-JUN-18

Using channel ORA_DISK_1

Searching flashback logs for block images until SCN 14311770329033

Finished flashback log search, restored 1 blocks

Starting media recovery

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

Finished recover at 13-JUN-18

9 verify the effect of repair

Query the table again. Tb_emp is normal.

SQL > select count (*) from tb_tmp

COUNT (*)

-

72449

Ora11g [Redora] / home/ora11g > dbv file=/ora11gSource/ora11g/tbs_tmp.dbf feedback=1000

DBVERIFY: Release 11.2.0.4.0-Production on Wed Jun 13 10:26:31 2018

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

DBVERIFY-Verification starting: FILE = / ora11gSource/ora11g/tbs_tmp.dbf

..

DBVERIFY-Verification complete

Total Pages Examined: 1536

Total Pages Processed (Data): 1196

Total Pages Failing (Data): 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 155

Total Pages Processed (Seg): 0

Total Pages Failing (Seg): 0

Total Pages Empty: 185

Total Pages Marked Corrupt: 0

Total Pages Influx: 0

Total Pages Encrypted: 0

Highest block SCN: 939342345 (3332.939342345)

These are all the contents of the article "how to recover bad blocks of media based on RMAN in Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.