Shulou(Shulou.com)06/01 Report--

Http://blog.csdn.net/leshami/article/details/10500997

For physically damaged blocks, we can complete the recovery of damaged blocks through the RMAN Block Media recovery (BLOCK MEDIA RECOVERY) feature, without the need to restore the entire database or all files to repair these small amounts of damaged blocks. Recover the entire database or data file. It's not a cannon to shoot mosquitoes. It's not worth it! But the prerequisite is that you have an available RMAN backup, so whenever a backup is everything. This article demonstrates the whole process of bad block recovery even though RMAN is used to generate bad block.

1. Create a data file for demonstration

SQL > create tablespace tbs datafile'/ u01 size size 10m autoextend on

Tablespace created.

-- create object tb based on the new data file

SQL > create table tb tablespace tbs as select * from dba_objects

Table created.

SQL > col file_name format A60

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

FILE_ID FILE_NAME

7 / u01/app/oracle/oradata/DBdb/tbs.dbf

SQL > COL SEGMENT_NAME FOR A15

SQL > select segment_name, header_file, header_block,blocks from dba_segments where segment_name = 'TB' and owner='SYS'

SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS

--

TB 7 130 1280

-- first use rman to back up the corresponding data files

[oracle@wang ~] $rman target /

Recovery Manager: Release 11.2.0.4.0-Production on Mon Nov 27 22:39:49 2017

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

Connected to target database: DBDB (DBID=3282897732)

RMAN > backup datafile 7

Starting backup at 27-NOV-17

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=44 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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf

Channel ORA_DISK_1: starting piece 1 at 27-NOV-17

Channel ORA_DISK_1: finished piece 1 at 27-NOV-17

Piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014 comment=NONE

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

Finished backup at 27-NOV-17

RMAN >

2. Recovery treatment of single data block damage

-- the following uses the dd command included with linux to damage a single block of data

[oracle@wang ~] $dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=130 EOF

031 records in

031 records out

17 bytes (17 B) copied, 0.000124852 s, 136 kB/s

[oracle@wang ~] $

-- clear buffer cache

SQL > alter system flush buffer_cache

System altered.

-- query table tb and receive ORA-01578

SQL > select count (*) from tb

Select count (*) from tb

*

ERROR at line 1:

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

ORA-01110: data file 7:'/ u01 *

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

V$DATABASE_BLOCK_CORRUPTION:

V$DATABASE_BLOCK_CORRUPTION displays information about database blocks that were corrupted after the last backup.

SQL > select * from v$database_block_corruption

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

--

7 130 1 0 CORRUPT

-- now use blockrecover to recover bad blocks

RMAN > blockrecover datafile 7 block 130

Starting recover at 27-NOV-17

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=50 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 00007

Channel ORA_DISK_1: reading from backup piece / u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp

Channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014

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:03

Finished recover at 27-NOV-17

RMAN >

-- query the table again. Tb is normal.

SQL > show user

USER is "SYS"

SQL >

SQL > select count (*) from tb

COUNT (*)

-

87046

3. Recovery treatment of multi-block damage

[oracle@wang ~] $dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=133 EOF

031 records in

031 records out

21 bytes (21 B) copied, 4.6398e-05 s, 453kB/s

[oracle@wang ~] $

[oracle@wang ~] $dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=143 EOF

031 records in

031 records out

23 bytes (23 B) copied, 6.3948e-05 s, 360 kB/s

[oracle@wang ~] $

[oracle@wang ~] $dd of=/u01/app/oracle/oradata/DBdb/tbs.dbf bs=8192 conv=notrunc seek=153 EOF

031 records in

031 records out

23 bytes (23 B) copied, 6.5705e-05 s, 350 kB/s

[oracle@wang ~] $

Refresh the shared pool

SQL > alter system flush buffer_cache

System altered.

The following hint shows that block 133 is damaged. Note that we have damaged multiple blocks, but when querying, start with the lowest block number. If there are bad blocks after 133 is repaired, we will continue to prompt the bad blocks after 133.

SQL > select count (*) from tb

Select count (*) from tb

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 7, block # 133)

ORA-01110: data file 7:'/ u01 *

-- query View v$database_block_corruption has no record

SQL > select * from v$database_block_corruption

No rows selected

-- use backup validate to verify the data file below

RMAN > backup validate datafile 7

Starting backup at 27-NOV-17

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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf

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

List of Datafiles

=

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

-

7 FAILED 0 138 1536 3821836

File Name: / u01/app/oracle/oradata/DBdb/tbs.dbf

Block Type Blocks Failing Blocks Processed

Data 0 1240

Index 0 0

Other 3 158-there are 3 Blocks Failing

Validate found one or more corrupt blocks

See trace file / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_17497.trc for details

Finished backup at 27-NOV-17

RMAN >

-- query v$database_block_corruption again, indicating that there are 3 damaged blocks

SQL > select * from v$database_block_corruption

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

--

7 133 1 0 CORRUPT

7 143 1 0 CORRUPT

7 153 1 0 CORRUPT

-- use blockrecover corruption list to restore directly, and all the following bad blocks that have just been verified will be restored:

Blockrecover corruption list

Or the following command

Run {

Allocate channel ch2 device type disk

Blockrecover datafile 7 block 133

Blockrecover datafile 7 block 143

Blockrecover datafile 7 block 153

Release channel ch2;}

Perform as follows:

RMAN > run {

2 > allocate channel ch2 device type disk

3 > blockrecover datafile 7 block 133

4 > blockrecover datafile 7 block 143

5 > blockrecover datafile 7 block 153

6 > release channel ch2;}

Released channel: ORA_DISK_1

Allocated channel: ch2

Channel ch2: SID=50 device type=DISK

Starting recover at 27-NOV-17

Channel ch2: restoring block (s)

Channel ch2: specifying block (s) to restore from backup set

Restoring blocks of datafile 00007

Channel ch2: reading from backup piece / u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp

Channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014

Channel ch2: restored block (s) from backup piece 1

Channel ch2: block restore complete, elapsed time: 00:00:01

Starting media recovery

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

Finished recover at 27-NOV-17

Starting recover at 27-NOV-17

Channel ch2: restoring block (s)

Channel ch2: specifying block (s) to restore from backup set

Restoring blocks of datafile 00007

Channel ch2: reading from backup piece / u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp

Channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014

Channel ch2: restored block (s) from backup piece 1

Channel ch2: block restore complete, elapsed time: 00:00:01

Starting media recovery

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

Finished recover at 27-NOV-17

Starting recover at 27-NOV-17

Channel ch2: restoring block (s)

Channel ch2: specifying block (s) to restore from backup set

Restoring blocks of datafile 00007

Channel ch2: reading from backup piece / u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp

Channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_TAG20171127T224014_f1r8ygdx_.bkp tag=TAG20171127T224014

Channel ch2: restored block (s) from backup piece 1

Channel ch2: block restore complete, elapsed time: 00:00:01

Starting media recovery

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

Finished recover at 27-NOV-17

Released channel: ch2

-- verify, check:

SQL > select * from v$database_block_corruption

No rows selected.

SQL >

SQL > select count (*) from tb

COUNT (*)

-

87046

4. Object location and influence of bad blocks.

SQL > col object_name for A25

SQL > select dbms_rowid.rowid_object (rowid) object_id,dbms_rowid.rowid_relative_fno (rowid) file_id,dbms_rowid.rowid_block_number (rowid) block_id,owner,object_name,object_id from tb where dbms_rowid.rowid_block_number (rowid) = 163and rownum select count (*) from tb

Select count (*) from tb

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 7, block # 163)

ORA-01110: data file 7:'/ u01 *

B. Records on bad blocks cannot be queried

-- We use the OBJECT_ID we queried earlier to query

SQL > select owner,object_name,object_id from tb where object_id in (2364 and 2365)

Select owner,object_name,object_id from tb where object_id in (2364 and 2365)

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 7, block # 163)

ORA-01110: data file 7:'/ u01 *

-- such as the following query, located on the damaged block (163 of the data cannot be queried, but can still be queried for the uncorrupted. The object on block 161 in the following query

SQL > select owner,object_name,object_id from tb where dbms_rowid.rowid_block_number (rowid) = 161and rownum SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = & file_id AND & block_id BETWEEN block_id AND block_id + blocks-1

Enter value for file_id: 7

Enter value for block_id: 163

Old 1: SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = & file_id AND & block_id BETWEEN block_id AND block_id + blocks-1

New 1: SELECT tablespace_name,segment_type,owner,segment_name,partition_name FROM dba_extents WHERE file_id = 7 AND 163BETWEEN block_id AND block_id + blocks-1

TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME

TBS TABLE SYS TB

D. Corrupted data files cannot be backed up by default, as follows

RMAN > backup datafile 7 tag='corruption'

Starting backup at 27-NOV-17

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=44 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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf

Channel ORA_DISK_1: starting piece 1 at 27-NOV-17

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/27/2017 23:23:49

ORA-19566: exceeded limit of 0 corrupt blocks for file / u01/app/oracle/oradata/DBdb/tbs.dbf

RMAN >

-- query the number of bad blocks in the corresponding data file:

SQL > select * from v$database_block_corruption

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

--

7 163 1 0 CORRUPT

7 173 1 0 CORRUPT

-- you need to set the number of allowed damaged blocks before you can make a backup

Run {

Set maxcorrupt for datafile 7 to 2

Backup datafile 7 tag='corruption'

}

Perform as follows:

RMAN > run {

2 > set maxcorrupt for datafile 7 to 2

3 > backup datafile 7 tag='corruption'

4 >}

Executing command: SET MAX CORRUPT

Starting backup at 27-NOV-17

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=00007 name=/u01/app/oracle/oradata/DBdb/tbs.dbf

Channel ORA_DISK_1: starting piece 1 at 27-NOV-17

Channel ORA_DISK_1: finished piece 1 at 27-NOV-17

Piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_27/o1_mf_nnndf_CORRUPTION_f1rcshjk_.bkp tag=CORRUPTION comment=NONE

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

Finished backup at 27-NOV-17

RMAN >

-- check the backup information as follows. You should restore the backup after repairing the bad block to prevent previously available backups from being aged out due to the retention policy.

RMAN > list backup summary

List of Backups

=

Key TY LV S Device Type Completion Time # Pieces # Copies Compressed Tag

1 B F A DISK 27-NOV-17 11 NO TAG20171127T224014

2 B F A DISK 27-NOV-17 1 1 NO CORRUPTION

RMAN >

5. Postscript

A, for damaged data blocks, only the data on the bad blocks can not be queried or read, and the data of other normal blocks can still be used.

B. receive an error prompt when doing related operations such as aggregating damaged table objects, because the data on the bad blocks cannot be counted. If you aggregate index columns, you can return normally if the index is not corrupted.

C. Block media recovery can be realized based on the backup files available in RMAN. The data files do not need offline, and the overhead and impact are the least.

D. For the damage of multiple data blocks, first execute backup validate to check the database or corresponding data files in order to mark the damaged blocks, record them to the view v$database_block_corruption, and then restore them later.

E. For the case after using backup validate check, when the bad blocks are recovered, you can directly use blockrecover corruption list to recover all the bad blocks at one time.

F. By default, data files with bad blocks cannot be backed up successfully, which will also cause the automatic backup script to fail.

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.