ora-01578: oracle data block corrupted Fix and Solution

Hi DBA-Mates,

We faced this below issue which is very critical and tricky also.

After cloning issue:

Self service url was not working https://ora-data.blogspot.com/OA_HTML/AppsLocalLogin.jsp

The only visible message was:

"You have encountered an unexpected error. Please contact the System Administrator for assistance."

Work-around:

1. Then we changed user’s password but still not working.

2. Checked db alert log, but didn’t get any no clue.

3. Checked apache error log, access log and jserv log but nothing found.

Error:

The error message is below. : file # 368, block # 29907

------------------------------------------------------------------------------------------------------------------------------------

Exception:oracle.apps.fnd.cache.CacheException at oracle.apps.fnd.cache.AppsCache.get(AppsCache.java:339) at oracle.apps.fnd.functionSecurity.User.getUser(User.java:447) at oracle.apps.fnd.functionSecurity.FunctionSecurity.getUser(FunctionSecurity.java:638) at oracle.apps.fnd.functionSecurity.FunctionSecurity.init(FunctionSecurity.java:2731) at oracle.apps.fnd.functionSecurity.FunctionSecurity.(FunctionSecurity.java:299) at oracle.apps.fnd.sso.SSOUtil.getStartPageUrl(SSOUtil.java:249) at oracle.apps.fnd.sso.SessionMgr.createAppsSession(SessionMgr.java:2808) at _oa__html._fndvald._jspService(_fndvald.java:222) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:220) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:496) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:360) at oracle.jsp.JspServlet.internalService(JspServlet.java:289) at oracle.jsp.JspServlet.service(JspServlet.java:259) at javax.servlet.http.HttpServlet.service(HttpServlet.java:699) at org.apache.jserv.JServConnection.processRequest(JServConnection.java:567) at org.apache.jserv.JServConnection.run(JServConnection.java:205) at java.lang.Thread.run(Thread.java:645) Caused by: oracle.apps.jtf.base.resources.FrameworkException: Failure in CacheLoader: oracle.apps.jtf.base.resources.FrameworkException at oracle.apps.jtf.cache.IASCacheProvider.get(IASCacheProvider.java:933) at oracle.apps.jtf.cache.CacheManager.getInternal(CacheManager.java:5805) at oracle.apps.jtf.cache.CacheManager.get(CacheManager.java:5728) at oracle.apps.fnd.cache.AppsCache.get(AppsCache.java:327) ... 16 more Caused by: oracle.apps.jtf.base.resources.FrameworkException at oracle.apps.fnd.cache.GenericCacheLoader.load(GenericCacheLoader.java:343) at oracle.apps.fnd.cache.GenericCacheLoader.load(GenericCacheLoader.java:200) at oracle.apps.fnd.cache.GenericCacheLoader.load(GenericCacheLoader.java:285) at oracle.apps.fnd.cache.GenericCacheLoader.load(GenericCacheLoader.java:250) at oracle.apps.jtf.cache.GenericCacheLoader.load(GenericCacheLoader.java:98) at oracle.ias.cache.CacheLoader.callLoad(Unknown Source) at oracle.ias.cache.CacheHandle.findObject(Unknown Source) at oracle.ias.cache.CacheHandle.locateObject(Unknown Source) at oracle.ias.cache.CacheAccess.get(Unknown Source) at oracle.apps.jtf.cache.IASCacheProvider.get(IASCacheProvider.java:874) ... 19 more Caused by: oracle.apps.jtf.base.resources.FrameworkException: ORA-01578: ORACLE data block corrupted (file # 368, block # 29907) ORA-01110: data file 368: '/ora-data/sku/data2/applsysd09.dbf' ORA-26040: Data block was loaded using the NOLOGGING option   at oracle.apps.jtf.base.resources.FrameworkException.convertException(FrameworkException.java:718) at oracle.apps.jtf.base.resources.FrameworkException.addException(FrameworkException.java:696) at oracle.apps.jtf.base.resources.FrameworkException.(FrameworkException.java:77) at oracle.apps.jtf.base.resources.FrameworkException.(FrameworkException.java:99) at oracle.apps.jtf.base.resources.FrameworkException.(FrameworkException.java:313) at oracle.apps.jtf.base.resources.FrameworkException.(FrameworkException.java:329) at oracle.apps.jtf.base.resources.FrameworkException.(FrameworkException.java:350) ... 29 more

Then checked for WF related tables

The below error comes if we query the below tables

SQL> select count(1) from WF_LOCAL_USER_ROLES;

select count(1) from WF_LOCAL_USER_ROLES

                    *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 151, block # 82209)

ORA-01110: data file 2197: '/oradata/sku/data20/applsysd47.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

Solution and Work-around:

Check details of the block which table it belongs.

select SEGMENT_NAME,SEGMENT_TYPE,BLOCK_ID,OWNER,TABLESPACE_NAME from dba_extents where BLOCK_ID like '29907' and BLOCK_ID like '82209';

select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,LOGGING,NUM_ROWS,LAST_ANALYZED from dba_tab_partitions where TABLE_NAME like 'WF_LOCAL_USER_ROLES';

select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 from dba_segments where SEGMENT_NAME like 'WF_LOCAL_USER_ROLES';

Run the dbv utility from OS prompt

$ dbv blocksize=8192  file=/oradata/sku/data2/applsysd09.dbf feedback=100

$ dbv blocksize=8192  file=/oradata/sku/data20/applsysd47.dbf feedback=100

Partition of the table WF_LOCAL_USER_ROLES will have problem. This Table partion will be having "LOGGING" column value "NO" which should be actually "YES".

Change the same.

SQL> ALTER TABLE WF_LOCAL_USER_ROLES MODIFY PARTITION <partition_name> LOGGING;

Table altered.

Create the necessary auxiliary tables for identifying corrupt blocks in the table in question:

SQL> exec DBMS_REPAIR.ADMIN_TABLES (TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');

PL/SQL procedure successfully completed.

SQL> desc repair_table

SQL> var c number
 

SQL> exec dbms_repair.check_object(schema_name=>'APPLSYS',
object_name=>'WF_LOCAL_USER_ROLES',
corrupt_count=>:c);

PL/SQL procedure successfully completed.

SQL> select :c from dual;

:C
----------
1934

SQL> select distinct SCHEMA_NAME, OBJECT_NAME, CORRUPT_TYPE, PARTITION_NAME, repair_DESCRIPTION, MARKED_CORRUPT from repair_table;

SQL> exec DBMS_REPAIR.FIX_CORRUPT_BLOCKS (SCHEMA_NAME => 'APPLSYS', OBJECT_NAME=> 'WF_LOCAL_USER_ROLES', OBJECT_TYPE => dbms_repair.table_object, REPAIR_TABLE_NAME => 'REPAIR_TABLE', FIX_COUNT=> :c);

PL/SQL procedure successfully completed.

SQL> exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ( SCHEMA_NAME => 'APPLSYS', OBJECT_NAME => 'WF_LOCAL_USER_ROLES', OBJECT_TYPE => dbms_repair.table_object, FLAGS => dbms_repair.skip_flag);

PL/SQL procedure successfully completed.

SQL> select count(1) from applsys.WF_LOCAL_USER_ROLES;

Check front end login for the affected users.

Some more useful link:

12c Database Installation Steps

Oracle Data Guard Concepts

RAC Concepts and Details

Jobs Tips

Regards,