Table LIQY.T1 is the only VERY FIRST table in tablepsace USERS, and I was wrong to use dba_extents to find the block address due to rush time in evening .
SYS@DG> select file_id, file_name from dba_data_files;
FILE_ID
----------
FILE_NAME
------------------------------------------------------------------------------------------------------------------- -----------------
1
/u01/oradata/DG/system01.dbf
2
/u01/oradata/DG/sysaux01.dbf
3
/u01/oradata/DG/undotbs01.dbf
4
/u01/oradata/DG/users01.dbf
5
/u01/oradata/DG/dg1_tbs01.dbf
SYS@DG> select owner, segment_name from dba_segments where tablespace_name='USERS';
OWNER SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------------------
LIQY T1
SYS@DG> select file_id, block_id from dba_extents where SEGMENT_NAME='T1';
FILE_ID BLOCK_ID
---------- ----------
4 128
And I followed below command in "Active data guard hands on lab.pdf" .
"dd if=/u01/app/oradata/SFO/example01.dbf of=/u01/app/oradata/SFO/example01.dbf count=1 bs=8192 skip=141 seek=140 conv=notrunc"
I thought the (seek value) = (skip value) - 1 , and come out my command.
dd if=/u01/oradata/DG/users01.dbf of=/u01/oradata/DG/users01.dbf count=1 bs=8192 skip=130 seek=129 conv=notrunc
Today, I spend some to "man dd" and thanks to "
Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR"
which tells me to query dba_segments . SYS@DG> select segment_name , header_file , header_block
2 from dba_segments
3 where segment_name = 'T1'
4 and owner = 'LIQY';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
--------------------------------------------------------------------------------- ----------- ------------
T1 4 130
Thus, I realized that :
1. I forgot that two blocks are used as data file header. So the table's header block should be 128 +2 =130. The first block of table LIQY.T1 is 131.
2. The value passed to the command in "Active data guard hands on lab.pdf" is incorrect, reason being :
seek=BLOCKS
skip BLOCKS obs-sized blocks at start of output
skip=BLOCKS
skip BLOCKS ibs-sized blocks at start of input
so , we should skip130 blocks in input file and seek for 131st block in output file as target block.
The correct command should be :
orarac1poc:DG:/u01/oradata/DG> dd if=/u01/oradata/DG/users01.dbf of=/u01/oradata/DG/users01.dbf count=1 bs=8192 skip=130 seek=131 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 8.6e-05 seconds, 95.3 MB/s
With the help of active data guard and ABMR , the corrupt block is repaired transparently to front-end.
SYS@DG> alter system flush buffer_cache;
System altered.
SYS@DG> set time on
16:59:48 SYS@DG> select * from liqy.t1;
F1
----------
1
16:59:55 SYS@DG> select file_id, block_id from dba_extents where SEGMENT_NAME='T1';
FILE_ID BLOCK_ID
---------- ----------
4 128
Here are the corresponding alert.log .
Thu Jan 24 16:59:42 2013
ALTER SYSTEM: Flushing buffer cache
Thu Jan 24 16:59:54 2013
Hex dump of (file 4, block 131) in trace file /u01/app/oracle/diag/rdbms/dg/DG/trace/DG_ora_10205.trc
Corrupt block relative dba: 0x01000083 (file 4, block 131)
Bad header found during multiblock buffer read
Data in bad block:
type: 35 format: 2 rdba: 0x01000082
last change scn: 0x0000.000f3818 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x38182301
check value in block header: 0x442e
computed block checksum: 0x0
Reading datafile '/u01/oradata/DG/users01.dbf' for corruption at rdba: 0x01000083 (file 4, block 131)
Reread (file 4, block 131) found same corrupt data (no logical check)
Starting background process ABMR
Thu Jan 24 16:59:54 2013
ABMR started with pid=36, OS id=10244
Automatic block media recovery service is active.
Automatic block media recovery requested for (file# 4, block# 131)
Thu Jan 24 16:59:55 2013
Automatic block media recovery successful for (file# 4, block# 131)
Automatic block media recovery successful for (file# 4, block# 131)
WARNING: AutoBMR fixed mismatched on-disk block 1000082 with in-mem rdba 1000083.
