ORA-01578 on lob segment

Background:
No force logging was enabled on this database. The database was moved to a new box and as part of this move the database name was also changed but a hot backup was used to restore this database on the new box. This caused ORA-01578 errors on objects that were created using the nologgin option.

At the time, the old database existed on the old box. Missing data was identified and was re-applied with developers's help and using MOS note ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors [ID 293515.1]

Everything was ok for an year.

Issue:
Application reported the ORA-1578 error again. The corrupt segment was a lob segment. This error also was reported in alert.log whenever health monitor ran for db integrity check. This was not reported all these days. Possibly because the bad blocks were never touched by the application. The answer was also in the note 293515.1
Setting the corrupt lob to empty lob will add the blocks formerly mapped to this lob to the freelist.  Eventually when PCTVERSION or RETENTION criteria causes the space to be salvaged and reused for new data, error ORA-1578/ORA-26040 can be seen again in the same LOB blocks. Empty lob means that the pointer to the corrupt lob referenced in that column is cleared. The corrupt block itself is not touched/repaired; it is just marked as free in the freelist metadata for the lob segment. If the lob segment continues growing using more space, the corrupt block can be attempted to be reused (as the block is free) and corruption error will be produced again for an INSERT or an UPDATE of the lob segment requesting more space.   

Solution:

Good thing about this object was there are no parent-child relationship with other tables and there was a composite index on couple of tables.


--expdp

expdp userid=system/xxxxx DIRECTORY=IMP_DIR DUMPFILE=ptl_docs_hist.dmp TABLES=hats_admin.ptl_docs_hist
- impdp with sqlfile option

impdp userid=system/xxxxxx DIRECTORY=IMP_DIR REMAP_TABLE=HATS_ADMIN.PTL_DOCS_HIST:PTL_DOC_HIST_IMP DUMPFILE=ptl_docs_hist.dmp SQLFILE=ptl_docs_hist.sql

- impdp with exclude index option and remap table option

impdp userid=system/xxxxx  DIRECTORY=IMP_DIR DUMPFILE=ptl_docs_hist.dmp EXCLUDE=INDEX REMAP_TABLE=HATS_ADMIN.PTL_DOCS_HIST:PTL_DOC_HIST_IMP


- rename ptl_Docs_hist to ptl_docs_hist_old


alter table HATS_aDMIN.PTL_DOCS_HIST rename to PTL_DOCS_HIST_BAD;

- rename the table created above to original table


alter table HATS_aDMIN.PTL_DOC_HIST_IMP rename to PTL_DOCS_HIST;

-- Change index name and create index

ALTER INDEX HATS_ADMIN.PTL_DOCS_HIST_FK_INDX1 RENAME TO PTL_DOCS_HIST_BAD_FK_INDX1;

  CREATE INDEX "HATS_ADMIN"."PTL_DOCS_HIST_FK_INDX1" ON "HATS_ADMIN"."PTL_DOCS_HIST" ("PROJ_ID", "PTL_DTLS_ID")
     PCTFREE 10 INITRANS 2 MAXTRANS 255
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "HATS_HS_TABLES" PARALLEL 1 ;