Partitions in Workflow Local Tables are Automatically Switched to NOLOGGING (Doc ID 433280.1)

Last updated on OCTOBER 06, 2016

Applies to:

Oracle Workflow - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.
Checked for relevance on 06-OCT-2012


Symptoms

After running the synchronize WF LOCAL tables concurrent program with logging mode parameter = 'LOGGING', partitions of the following tables are switched to logging = 'NO' :

- WF_LOCAL_ROLES
- WF_LOCAL_USER_ROLES
- WF_USER_ROLE_ASSIGNMENTS

This can cause the following failures when using archive logs for recovery due to the fact that a table partition that had been archived as being logging = 'YES' is now set to logging = 'NO':

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

The impacted partitions can be checked by running the following SQL:

select table_name, partition_name 
from dba_tab_partitions 
where table_name in 
('WF_LOCAL_ROLES' , 
'WF_LOCAL_USER_ROLES' , 
'WF_USER_ROLE_ASSIGNMENTS') 
and logging = 'NO' ; 


example of output:

TABLE_NAME                                     PARTITION_NAME
------------------------------                 ------------------------------

WF_LOCAL_ROLES                           ENG_LIST
WF_LOCAL_USER_ROLES               ENG_LIST
WF_USER_ROLE_ASSIGNMENTS   ENG_LIST

(the  name of the partition being set to nologging may vary depending of the originating system).

Partitions set to nologging may result in data block corruption in case the database needs to be restored 
(ORA-01578: Oracle data block corrupted).

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms