Error :
ORA-01578: ORACLE data block
corrupted (file # 14, block # 406221)
ORA-01110: data file 14:
'/u02/oracle/SVRQAT/db/apps_st/data/a_txn_data04.dbf'
ORA-26040: Data block was loaded
using the NOLOGGING option
( OR)
REP-0069: Internal error
REP-57054: In-process job
terminated:Terminated with error:
REP-300: ORACLE data block corrupted
(file # 14, block # 406221)
ORA-01110: data file 14:
'/u02/oracle/SVRQAT/db/apps_st/data/a_txn_data04.dbf'
ORA-26040: Data block was loaded
using the NOLOGGING option
Solution :
STEP 1: TO FIND THE CORRUPTION SEGMENT NAME/TYPE
Connect / as sysdba
SQL> SELECT tablespace_name,
segment_type, owner, segment_name FROM dba_extents WHERE file_id = 14 and
406221 between block_id AND block_id + blocks - 1;
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#;
SQL> select
TABLESPACE_NAME,RELATIVE_FNO,BYTES from DBA_FREE_SPACE where FILE_ID=14 and
406221 between BLOCK_ID AND BLOCK_ID + BLOCKS -1;
SQL> SELECT owner, segment_name,
segment_type FROM dba_extents WHERE file_id = 14 AND 406221 BETWEEN block_id
AND block_id + blocks - 1;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------ ------------------ --------------------
APPLSYS WF_LOCAL_USER_ROLES TABLE PARTITION
STEP2:
ALTER TABLE WF_LOCAL_USER_ROLES
ENABLE ROW MOVEMENT;
ALTER TABLE
APPLSYS.WF_LOCAL_USER_ROLES SHRINK SPACE COMPACT;
ALTER TABLE
APPLSYS.WF_LOCAL_USER_ROLES SHRINK SPACE;
ALTER TABLE
APPLSYS.WF_LOCAL_USER_ROLES SHRINK SPACE
CASCADE;
Error :
SQL> ALTER TABLE
APPLSYS.WF_LOCAL_USER_ROLES SHRINK SPACE COMPACT;
ALTER TABLE
APPLSYS.WF_LOCAL_USER_ROLES SHRINK SPACE COMPACT
*
ERROR at line 1:
ORA-01578: ORACLE data block
corrupted (file # 14, block # 421578)
ORA-01110: data file 14:
'/u02/oracle/SVRQAT/db/apps_st/data/a_txn_data04.dbf'
ORA-26040: Data block was loaded
using the NOLOGGING option
STEP 3:EXPORT TABLE FROM SOURCE<
PROD >
SQL> select directory_name, directory_path
from dba_directories where directory_name=' WF_LOCAL_USER' ;
expdp \"/ as
sysdba\" directory=WF_LOCAL_USER dumpfile=WF_LOCAL_USER_ROLES.dmp TABLES=APPLSYS.WF_LOCAL_USER_ROLES logfile=WF_LOCAL_USER.log
STEP 4: DROP TEH TABLE FROM
TARGET
SQL> drop TABLE
APPLSYS.WF_LOCAL_USER_ROLES;
SQL> exit
STEP 5: COPY THE EXPORT
DUMPFILE FROM PROD TO TARGET
STEP6: IMPORT THE TABLE FORM TARGET
SQL> select directory_name, directory_path
from dba_directories where directory_name=' WF_LOCAL_USER' ;
impdp \"/ as sysdba\" directory=WF_LOCAL_USER dumpfile=WF_LOCAL_USER_ROLES.dmp TABLES=APPLSYS.WF_LOCAL_USER_ROLES logfile=WF_LOCAL_USER.log
STEP 6:
SQL> SELECT owner, segment_name,
segment_type FROM dba_extents WHERE file_id = 14 AND 406221 BETWEEN block_id
AND block_id + blocks - 1;
no rows selected
++++++++++++++Manjunatha++++++++++++++
Use RMAN to format corrupt data block which is not part of
any object (Doc ID 1459778.1)
Doc ID 214369.1,472231.1,28814.1
Master Note (Doc ID 1578.1)
