Lets simulates the test case to recover the corrupted data block through RMAN utility.
SQL> conn scott/tiger
Connected.
SQL> insert into test
2 select * from test;
2814 rows created.
SQL> conn scott/tiger
Connected.
SQL> insert into test
2 select * from test;
2814 rows created.
Through DBMS_ROWID package using ROWID_BLOCK_NUMBER function we can find out the block number of any table rows.
SQL> select count(*),DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)
2 from scott.test
3 GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)
4 ORDER BY 2;
COUNT(*) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------
14 171
170 172
170 173
170 174
170 175
170 176
170 177
170 178
170 179
171 180
170 181
170 182
170 183
170 185
170 186
170 187
170 188
170 189
171 190
171 191
170 192
170 193
170 194
170 195
170 196
171 197
170 198
170 199
170 201
171 202
170 204
171 205
167 206
171 207
34 rows selected.
I'm going to corrupt 8 blocks of table TEST.
[oracle@oracle dbs]$ dd if=/dev/zero of=/u01/app/oracle/oradata/db1/users01.dbf bs=8192 conv=notrunc seek=172 count=8
8+0 records in
8+0 records out
65536 bytes (66 kB) copied, 7.5908e-05 seconds, 863 MB/s
Flush the database buffer cache so that there is no result data in buffer cache.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from scott.test;
select count(*) from scott.test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 172)
ORA-01110: data file 4: '/u01/app/oracle/oradata/db1/users01.dbf'
Verify the corrupted blocks through DBV utility.
[oracle@oracle dbs]$ dbv file=/u01/app/oracle/oradata/db1/users01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Mon Jul 1 18:31:57 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/db1/users01.dbf
Page 172 is marked corrupt
Corrupt block relative dba: 0x010000ac (file 4, block 172)
Completely zero block found during dbv:
Page 173 is marked corrupt
Corrupt block relative dba: 0x010000ad (file 4, block 173)
Completely zero block found during dbv:
Page 174 is marked corrupt
Corrupt block relative dba: 0x010000ae (file 4, block 174)
Completely zero block found during dbv:
Page 175 is marked corrupt
Corrupt block relative dba: 0x010000af (file 4, block 175)
Completely zero block found during dbv:
Page 176 is marked corrupt
Corrupt block relative dba: 0x010000b0 (file 4, block 176)
Completely zero block found during dbv:
Page 177 is marked corrupt
Corrupt block relative dba: 0x010000b1 (file 4, block 177)
Completely zero block found during dbv:
Page 178 is marked corrupt
Corrupt block relative dba: 0x010000b2 (file 4, block 178)
Completely zero block found during dbv:
Page 179 is marked corrupt
Corrupt block relative dba: 0x010000b3 (file 4, block 179)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 42
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing (Index): 0
Total Pages Processed (Other): 579
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 9
Total Pages Marked Corrupt : 8
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 839839 (0.839839)
RMAN> blockrecover datafile 4 block 172,173,174,175,176,177,178,179;
Starting recover at 01-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
finished standby search, restored 8 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-JUL-13
SQL> select count(*) from scott.test;
COUNT(*)
----------
5628
Here we done with Block recovery. enjoy :)
No comments:
Post a Comment