ORA-01578: ORACLE data block corrupted (file # 377, block #
4205)
ORA-01110: data file 392: '/u01/app/oracle/db/apps_st/data/a_tx_data01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
For Refer :
&AFN is : 392 (from the ORA-1110 portion of the error)
&RFN is : 377 (from the "file #" in the ORA-1578)
&BL is : 4205 (from the "block #" in the ORA-1578)
&FILENAME is : '/d01/app/oracle/db/apps_st/data/a_tx_data01.dbf'
Step 1 .The corrupted blocks are listed in the view
v$database_block_corruption:
SQL> select * from v$database_block_corruption;
Step 2: The
corrupted blocks are listed with Tablsspace
SQL> select b.tablespace_name, count(BLOCK#) from
v$database_block_corruption a, dba_data_files b where a.FILE#=b.FILE_ID group
by b.tablespace_name
Step 3: Identify the corrupt segments It will map each
block from v$database_block_corruption to either a segment or if the block is
free.
SQL> set pagesize 2000
set linesize
250
SELECT e.owner,
e.segment_type, e.segment_name, e.partition_name, c.file#
,
greatest(e.block_id, c.block#) corr_start_block#
,
least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
,
least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
-
greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null
description
FROM dba_extents e,
v$database_block_corruption c
WHERE e.file_id =
c.file#
AND e.block_id
<= c.block# + c.blocks - 1
AND e.block_id +
e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name,
s.partition_name, c.file#
, header_block
corr_start_block#
, header_block
corr_end_block#
, 1
blocks_corrupted
, 'Segment
Header' description
FROM dba_segments
s, v$database_block_corruption c
WHERE s.header_file
= c.file#
AND s.header_block
between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name,
null partition_name, c.file#
,
greatest(f.block_id, c.block#) corr_start_block#
,
least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
,
least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
-
greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block'
description
FROM dba_free_space
f, v$database_block_corruption c
WHERE f.file_id =
c.file#
AND f.block_id
<= c.block# + c.blocks - 1
AND f.block_id +
f.blocks - 1 >= c.block#
order by file#, corr_start_block#;
An output example is:
OWNER
SEGMENT_TYPE SEGMENT_NAME PARTITION_ FILE#
CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----- ------------------ ------------ ---------- ----- -----------------
--------------- ---------------- -------------
SCOTT TABLE
EMP
6
10
10
1
SCOTT TABLE PARTITION
ORDER
ORDER_JAN
6
26
28
3
6
29
29
1 Free Block
Step 4:To find the Corrupt Tablespace Name and details ..
SQL> select
b.tablespace_name,count(BLOCK#) from v$database_block_corruption a,
dba_data_files b where a.FILE#=b.FILE_ID group by b.tablespace_name;
An output example is:
TABLESPACE_NAME FILE# BLOCK#
BLOCKS CORRUPTIO
------------------------------ ---------- ----------
---------- ---------
APPS_TS_TX_DATA 06 57997 3 CORRUPT
APPS_TS_TX_DATA 31 16067 1 CORRUPT
APPS_TS_TX_DATA 34 173076 4 CORRUPT
APPS_TS_TX_DATA 392 620
13 CORRUPT
APPS_TS_NOLOGGING 401 2090 5 CORRUPT
Step 5: For each file# need to fire below query and find
the objects that are corrupt & make a note of them. (Replace 06
with file number)
SELECT e.owner,
e.segment_type, e.segment_name, e.partition_name, c.file#
,
greatest(e.block_id, c.block#) corr_start_block#
,
least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
,
least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
-
greatest(e.block_id, c.block#) + 1 blocks_corrupted
,
null description
FROM dba_extents e, v$database_block_corruption
c
WHERE e.file_id = c.file#
AND e.block_id
<= c.block# + c.blocks - 1
AND e.block_id +
e.blocks - 1 >= c.block#
and c.file#=06
UNION
SELECT s.owner,
s.segment_type, s.segment_name, s.partition_name, c.file#
,
header_block corr_start_block#
,
header_block corr_end_block#
, 1
blocks_corrupted
,
'Segment Header' description
FROM dba_segments s,
v$database_block_corruption c
WHERE s.header_file = c.file#
AND
s.header_block between c.block# and c.block# + c.blocks - 1
and c.file#=06
UNION
SELECT null owner, null
segment_type, null segment_name, null partition_name, c.file#
,
greatest(f.block_id, c.block#) corr_start_block#
,
least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
,
least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
-
greatest(f.block_id, c.block#) + 1 blocks_corrupted
,
'Free Block' description
FROM dba_free_space f,
v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id
<= c.block# + c.blocks - 1
AND f.block_id +
f.blocks - 1 >= c.block#
and c.file#=06
order by file#,
corr_start_block#;
Solution :
Import the identified tables & rebuilt the
indexes:
Drop the Tablespace ,Import
from Source to Target:
Source :
expdp system/manager@PRD
tablespaces=APPS_TS_TX_DATA directory=txdata dumpfile=appstxdt.dmp
logfile=appstxdt.log
Target :
impdp system/manager@DEV tablespaces=APPS_TS_TX_DATA directory=txdata
dumpfile=appstxdt.dmp logfile=appstxdt.log
Step 6: Retest the issue
To find the details …. Please check bellow :
1) An absolute FILE NUMBER (&AFN) of the file
containing the corrupt block
SQL> SELECT name FROM v$datafile WHERE file#=&AFN;
Note : The file name of the file containing the corrupt
block (Ex : file # 377)
The
BLOCK NUMBER of the corrupt block in that file (Ex : block # 4205)
2). The tablespace number and name containing the affected
block.
SQL> SELECT ts#
"TSN" FROM v$datafile WHERE file#=&AFN;
SQL> SELECT tablespace_name FROM dba_data_files WHERE
file_id=&AFN;
3). The block size of the tablespace where the corruption
lies
SQL> SELECT block_size FROM dba_tablespaces
WHERE
tablespace_name =
(SELECT tablespace_name FROM
dba_data_files WHERE file_id=&AFN);
4).Identify the Corrupt Object
The database must be open in order to use this query:
The following query will tell you the object TYPE , OWNER and NAME of a segment
SQL> SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents WHERE file_id = &AFN
and &BL between block_id AND block_id + blocks – 1;
SQL> select * from dba_extents where file_id = &AFN
and &BL between block_id AND block_id + blocks - 1;
Note : If the block is in a TEMPFILE the above query will return no data
5). If the above query does not return rows, it can also be that the corrupted block is a segment header
in a Locally Managed Tablespace (LMT). When the corrupted block is a segment
header block in a LMT, the above query produces a corruption message in the alert.log
but the query does not not fail. In that case use this query:
SQL> SELECT owner, segment_name, segment_type, partition_name
FROM dba_segments WHERE header_file = &AFN and header_block = &BL;
Reference Note 819533.1(case 5)
6). If the block belongs to a free extent it should appear in DBA_FREE_SPACE:
SQL> select * from dba_free_space
where file_id = &AFN and &BL between block_id AND block_id + blocks - 1;
7). If the segment is a CLUSTER determine which tables it contains.
SQL> SELECT owner, table_name FROM dba_tables
WHERE owner='&OWNER' AND cluster_name='&SEGMENT_NAME';
8). If the segment is an INDEX PARTITION
SQL> SELECT partition_name FROM dba_extents WHERE file_id = &AFN
AND &BL BETWEEN block_id AND block_id + blocks – 1;
Note : then continue below as if the segment was an INDEX segment.
Options:Index partitions can be rebuilt using:
EX: ALTER INDEX xxx REBUILD PARTITION ppp;
INDEX :
- If the segment is an INDEX then if the OWNER is "SYS" contact
Oracle support with all details.
Note: For a non-dictionary INDEX or INDEX PARTITIONs find out which table
the INDEX is on:
Eg:SQL>SELECT table_owner, table_name FROM dba_indexes
WHERE owner='&OWNER' AND index_name='&SEGMENT_NAME';
Note: and determine if the index supports a CONSTRAINT:
Eg:SQL>SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints WHERE owner='&TABLE_OWNER'
AND constraint_name='&INDEX_NAME';
Note: Possible values for CONSTRAINT_TYPE are:
P The index supports a primary key constraint.
U The index supports a unique constraint.
Note : If the INDEX supports a PRIMARY KEY constraint (type "P") then
check if the primary key is referenced by any foreign key constraints:
Eg:
SQL> SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints WHERE r_owner='&TABLE_OWNER'
AND r_constraint_name='&INDEX_NAME';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ID 28814.,472231.1,336133.1,819533.1 & 369076.1
+++++++++++++++++++++++++++++++++++++++++++++++++++++
Step 1:
SQL> select * from v$database_block_corruption;
SQL> select * from v$datafile where file#='&file#no';
SQL> select distinct file# from v$database_block_corruption;
SQL> select distinct file#,CORRUPTION_TYPE from V_$DATABASE_BLOCK_CORRUPTION order by file#;
SQL> select file_id,file_name from dba_data_files where file_id in (no1,n02,no3,no4);
SQL> select owner, segment_name,tablespace_name from dba_extents where FILE_ID=&id and <Block> between BLOCK_ID and BLOCK_ID+BLOCKS;
Step 2: Check whether block is part of any object
Query dba_extents and cross check the block doesnot belong to any object.
SQL> select segment_name,segment_type, owner from dba_extents
where file_id = <Absolute filenumber> and <corrupted block number>
between block_id and block_id + blocks -1;
Note : If it doesn't belong to an object, double check if itdoes exists in dba_free_space to check if the block belongs to file space usage bitmap.
SQL> Select * from dba_free_space where file_id= <Absolute file number>
and <corrupted block number> between block_id and block_id + blocks -1;
Step 3: We cannot run a query on dba_extents to find out what the object is on the standby, so we ran the following query on the Primary to find the corrupted block:
SQL> SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents WHERE file_id = &no-start and &no-end between block_id AND block_id + blocks - 1;
Step 4: The repair data block of table in a given tablespace:
SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => '&tablespace_name');
END;
/
Step 5: Identify corrupted blocks for schema.object:
SQL> set serveroutput on
SQL> DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
Step 6: Optionally display any corrupted block identified by check_object:
SQL> select BLOCK_ID,CORRUPT_TYPE, CORRUPT_DESCRIPTION from REPAIR_TABLE;
Step 7: Mark the identified blocks as corrupted
SQL> DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME=> '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
END;
/
Step 8 : To find the corruption objects
SQL> select count(*) from v$database_block_corruption;
SQL> select 'SELECT e.file_id,e.block_id,e.owner,e.segment_name,
e.segment_type FROM dba_extents e WHERE
file_id='|| file#||' and '||
block#||' BETWEEN block_id AND block_id + blocks - 1; '
from v$database_block_corruption order by file#;
Note :Allow future DML statements to skip the corrupted blocks
Note : If it is needed to stop skipping corrupt blocks in
the object -- ID 556733.1:
SQL> BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/
Note : if not required :
SQL> execute
dbms_backup_restore.resetCfileSection(file#); /** clear
v$backup_corruption
To verify :
SQL> SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE,
MARKED_CORRUPT,CORRUPT_DESCRIPTION,EPAIR_DESCRIPTION FROM REPAIR_TABLE;
SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;
RMAN Verify
To identify both Physical and Logical Block Corruptions use
the "CHECK LOGICAL" option. The next command checks the complete
database for both corruptions without actually doing a backup:
$ rman target /
RMAN> backup check logical validate database;
Note : The next command checks the complete database for both
corruptions in a backup:
$ rman target /
RMAN> backup check logical
database
DBVerify - Identify Datafile Block Corruptions
DBVERIFY identify Physical and Logical Intra Block
Corruptions by default. Dbverify cannot be run for the whole database in a
single command. It does not need a database connection either:
dbv file=<datafile name>
blocksize=<datafile Block size>
SQL> connect internal
Connected.
SQL> show parameter db_block_size
SQL>select file#,name,bytes/2048 from v$datafile;
SQL> EXIT
$dbv file=/u02/oradata/R815/oradata/R815/users01.dbf blocksize=2048 logfile=users01_dbv.log feedback=100
++++++++++++++++++++++++++++++++++++++++++++++
