Oracle data block corruption errors have been found in the alert log.

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)