Use the below Steps as reference for Identifying Block Corruption and Fixing the Same:
SQL> select name from v$database;
NAME
---------
NEWDB
SQL> select file_id,block_id from dba_extents
where segment_name ='DEPARTMENTS';
FILE_ID BLOCK_ID
---------- ----------
5 49
SQL> select file_name from dba_data_files where
file_id=5;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/newdb/example01.dbf
SQL> host rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on
Fri Sep 17 11:03:39 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: NEWDB (DBID=2968639662)
RMAN> report schema;
using target database control file instead of
recovery catalog
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- -------
------------------------
1 490 SYSTEM *** /u01/app/oracle/oradata/newdb/system01.dbf
2 155 UNDOTBS1 ***
/u01/app/oracle/oradata/newdb/undotbs01.dbf
3 270 SYSAUX *** /u01/app/oracle/oradata/newdb/sysaux01.dbf
4 96 USERS *** /u01/app/oracle/oradata/newdb/users01.dbf
5 100 EXAMPLE ***
/u01/app/oracle/oradata/newdb/example01.dbf
6 50 TBSADDM *** /u01/app/oracle/oradata/newdb/addm1.dbf
7 50 OLD *** /u01/app/oracle/oradata/newdb/old1.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- -----------
--------------------
1 50 TEMP 50
/u01/app/oracle/oradata/newdb/temp01.dbf
2 50 TEMP
50
/u01/app/oracle/oradata/newdb/temp02.dbf
RMAN> backup datafile 5;
Starting backup at 17-SEP-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=137 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in
backupset
input datafile fno=00005
name=/u01/app/oracle/oradata/newdb/example01.dbf
channel ORA_DISK_1: starting piece 1 at 17-SEP-10
channel ORA_DISK_1: finished piece 1 at 17-SEP-10
piece handle=/u01/app/oracle/flash_recovery_area/NEWDB/backupset/2010_09_17/o1_mf_nnndf_TAG20100917T110405_695zgfrw_.bkp
tag=TAG20100917T110405 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed
time: 00:00:25
Finished backup at 17-SEP-10
Starting Control File and SPFILE Autobackup at
17-SEP-10
piece
handle=/u01/app/oracle/flash_recovery_area/NEWDB/autobackup/2010_09_17/o1_mf_s_729947071_695zh9rr_.bkp
comment=NONE
Finished Control File and SPFILE Autobackup at
17-SEP-10
SQL> host
[oracle@localhost ~]$ cd /home/oracle/Desktop/labs2/
--------------à
[oracle@localhost labs2]$ ./lab_07_02.sh
/u01/app/oracle/oradata/newdb/example01.dbf 49 8192
0+1 records in
0+1 records out
8 bytes (8 B) copied, 0.0271975 seconds, 0.3 kB/s
0+1 records in
0+1 records out
8 bytes (8 B) copied, 0.000938639 seconds, 8.5 kB/s
0+1 records in
0+1 records out
8 bytes (8 B) copied, 0.00046544 seconds, 17.2 kB/s
0+1 records in
0+1 records out
8 bytes (8 B) copied, 0.0202623 seconds, 0.4 kB/s
[oracle@localhost labs2]$ exit
exit
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from hr.departments;
select * from hr.departments
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5,
block # 51)
ORA-01110: data file 5:
'/u01/app/oracle/oradata/newdb/example01.dbf'
SQL> host
[oracle@localhost ~]$ dbv
/u01/app/oracle/oradata/newdb/example01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Sep
17 11:14:01 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
LRM-00108: invalid positional parameter value '/u01/app/oracle/oradata/newdb/example01.dbf'
DBV-00001: Illegal command line syntax - parse error
= (108)
[oracle@localhost ~]$ dbv
file=/u01/app/oracle/oradata/newdb/example01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Sep
17 11:14:12 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE =
/u01/app/oracle/oradata/newdb/example01.dbf
Page 49 is marked corrupt
Corrupt block relative dba: 0x01400031 (file 5, block
49)
Bad header found during dbv:
Data in bad block:
type: 67
format: 7 rdba: 0x0a545055
last change
scn: 0x0000.0006d161 seq: 0x2 flg: 0x04
spare1: 0x52
spare2: 0x52 spare3: 0x0
consistency
value in tail: 0xd1612002
check value in
block header: 0xe5e
computed block
checksum: 0xe441
Page 50 is marked corrupt
Corrupt block relative dba: 0x01400032 (file 5, block
50)
Bad header found during dbv:
Data in bad block:
type: 67
format: 7 rdba: 0x0a545055
last change
scn: 0x0000.0006d085 seq: 0x1 flg: 0x04
spare1: 0x52
spare2: 0x52 spare3: 0x0
consistency
value in tail: 0xd0852101
check value in
block header: 0x4e60
computed block
checksum: 0xe443
Page 51 is marked corrupt
Corrupt block relative dba: 0x01400033 (file 5, block
51)
Bad header found during dbv:
Data in bad block:
type: 67
format: 7 rdba: 0x0a545055
last change
scn: 0x0000.0006d161 seq: 0x2 flg: 0x04
spare1: 0x52
spare2: 0x52 spare3: 0x0
consistency
value in tail: 0xd1612302
check value in
block header: 0x63bb
computed block
checksum: 0xe440
Page 52 is marked corrupt
Corrupt block relative dba: 0x01400034 (file 5, block
52)
Bad header found during dbv:
Data in bad block:
type: 67
format: 7 rdba: 0x0a545055
last change
scn: 0x0000.0006d161 seq: 0x2 flg: 0x04
spare1: 0x52
spare2: 0x52 spare3: 0x0
consistency
value in tail: 0xd1610602
check value in
block header: 0x59cc
computed block
checksum: 0xe462
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 4408
Total Pages Failing
(Data) : 0
Total Pages Processed (Index): 1264
Total Pages Failing
(Index): 0
Total Pages Processed (Other): 1536
Total Pages Processed (Seg) : 0
Total Pages Failing
(Seg) : 0
Total Pages Empty : 5588
Total Pages Marked Corrupt : 4
Total Pages Influx : 0
Highest block SCN : 997842 (0.997842)
[oracle@localhost ~]$ tail -50
/u01/app/oracle/admin/newdb/bdump/alert_newdb.log
Completed: create tablespace tbs1
datafile '/u01/app/oracle/oradata/tbs101.dbf' size
50m
Sat Sep 4
12:46:58 2010
drop tablespace tbs1 including contents
Sat Sep 4
12:46:59 2010
Starting control autobackup
Sat Sep 4
12:47:01 2010
Errors in file
/u01/app/oracle/admin/newdb/udump/newdb_ora_20773.trc:
Sat Sep 4
12:47:01 2010
Errors in file
/u01/app/oracle/admin/newdb/udump/newdb_ora_20773.trc:
Sat Sep 4
12:47:01 2010
Errors in file
/u01/app/oracle/admin/newdb/udump/newdb_ora_20773.trc:
Control autobackup written to DISK device
handle
'/u01/app/oracle/flash_recovery_area/NEWDB/autobackup/2010_09_04/o1_mf_s_728830019_683wmdjm_.bkp'
Completed: drop tablespace tbs1 including contents
Fri Sep 17 10:55:17 2010
MMNL absent for 1106557 secs; Foregrounds taking over
Fri Sep 17 10:59:05 2010
Thread 1 advanced to log sequence 37
Current log#
3 seq# 37 mem# 0: /u01/app/oracle/oradata/newdb/redo03.log
Fri Sep 17 11:04:31 2010
Starting control autobackup
Fri Sep 17 11:04:36 2010
Errors in file
/u01/app/oracle/admin/newdb/udump/newdb_ora_2484.trc:
Fri Sep 17 11:04:36 2010
Errors in file
/u01/app/oracle/admin/newdb/udump/newdb_ora_2484.trc:
Fri Sep 17 11:04:36 2010
Errors in file
/u01/app/oracle/admin/newdb/udump/newdb_ora_2484.trc:
Control autobackup written to DISK device
handle
'/u01/app/oracle/flash_recovery_area/NEWDB/autobackup/2010_09_17/o1_mf_s_729947071_695zh9rr_.bkp'
Fri Sep 17 11:11:44 2010
ALTER SYSTEM: Flushing buffer cache
Fri Sep 17 11:12:01 2010
Hex dump of (file 5, block 51) in trace file
/u01/app/oracle/admin/newdb/udump/newdb_ora_1251.trc
Corrupt block relative dba: 0x01400033 (file 5, block
51)
Bad header found during buffer read
Data in bad block:
type: 67
format: 7 rdba: 0x0a545055
last change
scn: 0x0000.0006d161 seq: 0x2 flg: 0x04
spare1: 0x52
spare2: 0x52 spare3: 0x0
consistency
value in tail: 0xd1612302
check value in
block header: 0x63bb
computed block
checksum: 0xe440
Reread of rdba: 0x01400033 (file 5, block 51) found
same corrupted data
Fri Sep 17 11:12:09 2010
Corrupt Block Found
TSN =
6, TSNAME = EXAMPLE
RFN =
5, BLK = 51, RDBA = 20971571
OBJN =
51852, OBJD = 51250, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
[oracle@localhost ~]$ exit
exit
SQL> host rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on
Fri Sep 17 11:19:47 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: NEWDB (DBID=2968639662)
RMAN> blockrecover datafile 5 block 49,50,51,52;
Starting blockrecover at 17-SEP-10
using target database control file instead of
recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=129 devtype=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
/u01/app/oracle/flash_recovery_area/NEWDB/backupset/2010_09_17/o1_mf_nnndf_TAG20100917T110405_695zgfrw_.bkp
channel ORA_DISK_1: restored block(s) from backup
piece 1
piece
handle=/u01/app/oracle/flash_recovery_area/NEWDB/backupset/2010_09_17/o1_mf_nnndf_TAG20100917T110405_695zgfrw_.bkp
tag=TAG20100917T110405
channel ORA_DISK_1: block restore complete, elapsed
time: 00:00:08
starting media recovery
media recovery complete, elapsed time: 00:00:08
Finished blockrecover at 17-SEP-10
RMAN> exit
Recovery Manager complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from hr.departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------
---------- -----------
10
Administration
200 1700
20
Marketing
201 1800
30
Purchasing
114 1700
40
Human Resources
203 2400
50
Shipping 121
1500
60
IT
103 1400
70
Public Relations
204 2700
80
Sales
145 2500
90
Executive 100 1700
100
Finance
108 1700
110
Accounting
205 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------
---------- -----------
120
Treasury
1700
130
Corporate Tax
1700
140
Control And Credit
1700
150
Shareholder Services
1700
160
Benefits
1700
170
Manufacturing
1700
180
Construction 1700
190
Contracting BLOCK CORRUPTION
No comments:
Post a Comment