Today we had a major issue in Production, and the DBAs ended up restoring everything from RMAN backup. After the Database was restored, we could not access the AppsLogin page. After turning on the debug mode, we saw these errors in the jserv.log:
ORA-01578: ORACLE data block corrupted (file # 377, block # 4205)
ORA-01110: data file 377: '/dev/rac_data2/rapplsysd03.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
On seeing this error, Akhilesh said that it is a known issue and ran the WF_LOCAL_SYNCH procedure:
SQL> begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL',
3 P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE,
After running the above procedure, we were able to see the AppsLogin page and login to the instance.
Metalink Notes 433280.1 and 435523.1 describe this problem in detail and provide this cause:
During its execution, the Synchronize WF LOCAL tables concurrent program
executes the following statements:
ALTER TABLE WF_LOCAL_ROLES
WITH TABLE WF_LOCAL_ROLES_STAGE INCLUDING INDEXES WITHOUT VALIDATION;
ALTER TABLE WF_LOCAL_USER_ROLES
WITH TABLE WF_LOCAL_USER_ROLES_STAGE INCLUDING INDEXES WITHOUT VALIDATION;
ALTER TABLE WF_USER_ROLE_ASSIGNMENTS
WITH TABLE WF_UR_ASSIGNMENTS_STAGE INCLUDING INDEXES WITHOUT VALIDATION;
When an alter table exchange partition takes place, all the segment attributes
of the two objects (including tablespace and
logging) are also exchanged.
So if one of the stage tables is set to logging = 'NO' before the Synchronize WF
concurrent program is launched, after its completion a partition of
the corresponding table will be set to LOGGING = 'NO'.
1. WF_LOCAL_ROLES_STAGE are set to LOGGING = 'NO'
all partitions of WF_LOCAL_ROLES are set to LOGGING = 'YES'
Synchronize WF LOCAL tables concurrent program for originating system = ENG_LIST.
3. After completion,
WF_LOCAL_ROLES_STAGE is set to LOGGING = 'NO'
ENG_LIST partition of WF_LOCAL_ROLES is set to LOGGING = 'YES'
This problem is described in Bug 5942254 RUNNING SYNCHRONIZE WF LOCAL TABLES CHANGES PARTITION TO NOLOGGING.
I also found this solution on astijf wiki here.