EBS DBAs Be Warned! NOLOGGING Can Hit You Badly …

Hello Apps DBA,

I’ve just come across an old known issue again today. Was working in the greatest and latest 12.1.2 Oracle e-Business Suite environment. Surprisingly, I face the old issue when WF_LOCAL_ROLES and WF_LOCAL_USER_ROLES objects (among other objects) are set to NOLOGGING mode. Strictly speaking, the LOGGING attribute was set to YES at the table level, but several partitions of that table were set to LOGGING = NO! The SQL’s below will help you check if your environments are effected. Keep in mind that you should sound the alarm at your business if any other modules’ objects are in the list.

The easiest solution is to switch the whole database to FORCE_LOGGING mode:

ALTER DATABASE FORCE LOGGING;

If you don’t have that luxury because of huge REDOLOG volumes I would suggest you to put all your stage/temp/interface – unrecoverable objects in the APPS_TS_NOLOGGING tablespace. It looks like someone at Oracle thought about the issue and designed the new tablespace model specifically for that purpose. Just put all your high volume unrecoverable objects in that tablespace and switch FORCE_LOGGING for all other tablespaces.

ALTER TABLESPACE < TBS name > FORCE LOGGING;

Protect yourself and your EBS environments,

Don’t say that Pythian didn’t warn you :)

Yury

SQLs to be used:

select tablespace_name, owner, table_name from dba_tables
where logging = 'NO'and partitioned = 'NO' and temporary = 'N'
and IOT_TYPE is null order bytablespace_name, owner,table_name;
select tablespace_name, table_owner,table_name, partition_name
from dba_tab_partitions where logging = 'NO' and (table_owner, table_name) not in
(select owner,table_name from dba_tables where temporary = 'Y' and IOT_TYPE is null)
order by tablespace_name, table_owner,table_name,partition_name;
select tablespace_name, force_logging from dba_tablespaces
where contents = 'PERMANENT'
order by force_logging desc, tablespace_name;
select tablespace_name, owner, index_name
from dba_indexes where logging = 'NO'
and temporary = 'N' and partitioned = 'YES'
order by tablespace_name, owner,table_name;
select tablespace_name, index_owner,index_name, partition_name
from dba_ind_partitions where logging = 'NO' and (index_owner, index_name) not in
(select owner,index_name from dba_indexes where temporary = 'Y' )
order by tablespace_name, index_owner,index_name,partition_name;
select owner,segment_name,partition_name,segment_type
from dba_segments where tablespace_name='APPS_TS_NOLOGGING'
order by segment_type desc, owner, segment_name;