Oracle Backup and
Restore Case Scenarios - Case Study 7 (Block Corruption)
Case 7: Recover a
block corruption
Corrupted blocks can be recovered in many ways. I am
showing you 2 methods. First using RMAN BLOCKRECOVER and next using
Export/Import.
Steps Outlined:
Method
1
1.
Create a test tablespace for the practical
2.
Create a table in tablespace and populate it
with some rows
3.
Take a consistent RMAN backup of database
4.
Find the block numbers for ROWIDs in the table
5.
Corrupt few blocks using OS dd command.
6.
Flush buffer cache to force Oracle to read rows
from disk.
7.
Read from table to see corruption
8.
Corruption can be seen in alert log as well
9.
Repair corrupted blocks using BLOCKRECOVER
command of RMAN
10.
Check whether corruption has been recovered
Method 2
1.
Take datapump export of test table
2.
Corrupt few blocks again for the table
3.
Drop the table
4.
Import the dump back to table
5.
Check whether corruption has been recovered.
Create a test tablespace
SQL>
create tablespace tbs_test datafile
'/u01/app/oracle/oradata/ORADB1/tbs_test.df' size 20m autoextend on next 10m
extent management local segment space management auto;
Tablespace
created.
SQL>
create user aj identified by aj default tablespace tbs_test;
User
created.
SQL>
grant connect, resource to aj;
Grant
succeeded.
SQL>
grant dba to aj;
Grant
succeeded.
SQL>
conn aj/aj
Connected.
Create test table in tablespace and populate it with
some rows.
SQL>
create table testaj tablespace tbs_test as select * from dba_objects;
Table
created.
SQL>
select count(*) from testaj;
COUNT(*)
----------
86977
Get the block numbers of rows using procedure
dbms_rowid.rowid_block_number.
SQL>
select * from (select distinct dbms_rowid.rowid_block_number(rowid) bn from
testaj order by bn )
where
rownum <10;
BN
----------
131
132
133
134
135
136
137
138
139
9
rows selected.
Take a good consistent backup of database to rely
upon.
RMAN>
backup database plus archivelog;
Starting
backup at 26-SEP-2017 16:54:06
.
. . .
.
. . .
Finished
backup at 26-SEP-2017 16:55:20
Starting
Control File and SPFILE Autobackup at 26-SEP-2017 16:55:20
piece
handle=/u01/backup/rman/ctl_c-2294695355-20170926-01 comment=NONE
Finished
Control File and SPFILE Autobackup at 26-SEP-2017 16:55:21
RMAN>
Corrupt few blocks using OS dd command.
[oracle@localhost
trace]$ dd
of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=131 conv=notrunc
count=1 if=/dev/zero
1+0
records in
1+0
records out
8192
bytes (8.2 kB) copied, 0.0129064 s, 635 kB/s
[oracle@localhost
trace]$ dd
of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=132 conv=notrunc
count=1 if=/dev/zero
1+0
records in
1+0
records out
8192
bytes (8.2 kB) copied, 0.00177635 s, 4.6 MB/s
[oracle@localhost
trace]$ dd of=/u01/app/oracle/oradata/ORADB1/tbs_test.df
bs=8192 seek=133 conv=notrunc count=1 if=/dev/zero
1+0
records in
1+0
records out
8192
bytes (8.2 kB) copied, 0.000128699 s, 63.7 MB/s
[oracle@localhost
trace]$ dd
of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=134 conv=notrunc
count=1 if=/dev/zero
1+0
records in
1+0
records out
8192
bytes (8.2 kB) copied, 0.000116262 s, 70.5 MB/s
[oracle@localhost
trace]$ dd
of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=135 conv=notrunc
count=1 if=/dev/zero
1+0
records in
1+0
records out
8192
bytes (8.2 kB) copied, 0.000150806 s, 54.3 MB/s
[oracle@localhost
trace]$ dd
of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=136 conv=notrunc
count=1 if=/dev/zero
1+0
records in
1+0
records out
8192
bytes (8.2 kB) copied, 0.000117566 s, 69.7 MB/s
[oracle@localhost
trace]$ dd
of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=137 conv=notrunc
count=1 if=/dev/zero
1+0
records in
1+0
records out
8192
bytes (8.2 kB) copied, 0.000116422 s, 70.4 MB/s
[oracle@localhost
trace]$ dd
of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=138 conv=notrunc
count=1 if=/dev/zero
1+0
records in
1+0
records out
8192
bytes (8.2 kB) copied, 0.000115229 s, 71.1 MB/s
[oracle@localhost
trace]$
Flush buffer cache to force Oracle to read from disk.
SQL>
alter system flush buffer_cache;
System
altered.
Try to read the table which will fail. If it does not
fail, flush the buffer cache couple more times.
SQL>
select count(*) from testaj;
select
count(*) from testaj
*
ERROR
at line 1:
ORA-01578:
ORACLE data block corrupted (file # 6, block # 131)
ORA-01110:
data file 6: '/u01/app/oracle/oradata/ORADB1/tbs_test.df'
SQL>
select * from v$database_block_corruption;
FILE#
BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----------
---------- ---------- ------------------ ---------
6 131 8
0 ALL ZERO
Check the alert log for list of corrupted blocks.
[oracle@localhost
trace]$ cat alert_ORADB1.log |grep -i "corrupt block"
Corrupt
block relative dba: 0x01800083 (file 6, block 131)
Corrupt
block relative dba: 0x01800084 (file 6, block 132)
Corrupt
block relative dba: 0x01800085 (file 6, block 133)
Corrupt
block relative dba: 0x01800086 (file 6, block 134)
Corrupt
block relative dba: 0x01800087 (file 6, block 135)
Corrupt
block relative dba: 0x01800088 (file 6, block 136)
Corrupt
block relative dba: 0x01800089 (file 6, block 137)
Corrupt
block relative dba: 0x0180008a (file 6, block 138)
You can either recover one block at a time using
DATAFILE and BLOCK number. Or you can recover entire list using clause
CORRUPTION LIST.
BLOCKRECOVER
DATAFILE 6, BLOCK 131
BLOCKRECOVER
DATAFILE 6, BLOCK 132
BLOCKRECOVER
DATAFILE 6, BLOCK 133
BLOCKRECOVER
DATAFILE 6, BLOCK 134
BLOCKRECOVER
DATAFILE 6, BLOCK 135
BLOCKRECOVER
DATAFILE 6, BLOCK 136
BLOCKRECOVER
DATAFILE 6, BLOCK 137
BLOCKRECOVER
DATAFILE 6, BLOCK 138
RMAN>
blockrecover corruption list;
Starting
recover at 26-SEP-2017 16:58:27
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=34 device type=DISK
allocated
channel: ORA_DISK_2
channel
ORA_DISK_2: SID=29 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 00006
channel
ORA_DISK_1: reading from backup piece
/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_26/o1_mf_nnndf_TAG20170926T165423_dwng78bv_.bkp
channel
ORA_DISK_1: piece
handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_26/o1_mf_nnndf_TAG20170926T165423_dwng78bv_.bkp
tag=TAG20170926T165423
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 26-SEP-2017 16:58:32
Check whether corruption is still present.
SQL>
select * from v$database_block_corruption;
no
rows selected
Read from table to check whether all rows have been
recovered.
SQL>
select count(*) from testaj;
COUNT(*)
----------
86977
2nd Method: Repair corruption using Export / Import
We can recover the block using export/import as well.
Take a good export of the table which we are going to
corrupt.
[oracle@localhost
trace]$ expdp aj/aj tables=testaj dumpfile=testaj.dmp logfile=expdp_testaj.log
Export:
Release 11.2.0.4.0 - Production on Tue Sep 26 17:01:01 2017
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
Connected
to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting
"AJ"."SYS_EXPORT_TABLE_01":
aj/******** tables=testaj dumpfile=testaj.dmp logfile=expdp_testaj.log
Estimate
in progress using BLOCKS method...
Processing
object type TABLE_EXPORT/TABLE/TABLE_DATA
Total
estimation using BLOCKS method: 10 MB
Processing
object type TABLE_EXPORT/TABLE/TABLE
.
. exported "AJ"."TESTAJ" 8.433 MB 86977 rows
Master
table "AJ"."SYS_EXPORT_TABLE_01" successfully
loaded/unloaded
******************************************************************************
Dump
file set for AJ.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/ORADB1/dpdump/testaj.dmp
Job
"AJ"."SYS_EXPORT_TABLE_01" successfully completed at Tue
Sep 26 17:01:13 2017 elapsed 0 00:00:09
Corrupt few blocks again using dd command.
[oracle@localhost
trace]$ dd of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=131
conv=notrunc count=1 if=/dev/zero
[oracle@localhost
trace]$ dd of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=132
conv=notrunc count=1 if=/dev/zero
[oracle@localhost
trace]$ dd of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=133
conv=notrunc count=1 if=/dev/zero
[oracle@localhost
trace]$ dd of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=134
conv=notrunc count=1 if=/dev/zero
[oracle@localhost
trace]$ dd of=/u01/app/oracle/oradata/ORADB1/tbs_test.df bs=8192 seek=135
conv=notrunc count=1 if=/dev/zero
Check whether database got affected with corruption.
SQL>
alter system flush buffer_cache;
System
altered.
SQL>
/
System
altered.
SQL>
select count(*) from testaj;
select
count(*) from testaj
*
ERROR
at line 1:
ORA-01578:
ORACLE data block corrupted (file # 6, block # 131)
ORA-01110:
data file 6: '/u01/app/oracle/oradata/ORADB1/tbs_test.df'
Drop the table which will remove corruption.
SQL>
drop table testaj;
Table
dropped.
SQL>
exit
Disconnected
from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
Import the table from dump to get back the rows.
[oracle@localhost
trace]$ impdp aj/aj tables=testaj dumpfile=testaj.dmp logfile=impdp_testaj.log
Import:
Release 11.2.0.4.0 - Production on Tue Sep 26 17:07:15 2017
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
Connected
to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
Master
table "AJ"."SYS_IMPORT_TABLE_01" successfully
loaded/unloaded
Starting
"AJ"."SYS_IMPORT_TABLE_01":
aj/******** tables=testaj dumpfile=testaj.dmp logfile=impdp_testaj.log
Processing
object type TABLE_EXPORT/TABLE/TABLE
Processing
object type TABLE_EXPORT/TABLE/TABLE_DATA
.
. imported "AJ"."TESTAJ" 8.433 MB 86977 rows
Job
"AJ"."SYS_IMPORT_TABLE_01" successfully completed at Tue
Sep 26 17:07:19 2017 elapsed 0 00:00:03
[oracle@localhost
trace]$ sqlplus aj/aj
SQL*Plus:
Release 11.2.0.4.0 Production on Tue Sep 26 17:07:23 2017
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
You have got all the rows back.
SQL>
select count(*) from testaj;
COUNT(*)
----------
86977
You can find more information at:
http://docs.oracle.com/cd/E25054_01/backup.1111/e10642/rcmblock.htm
