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;
8 Comments. Leave new
Yury, good that you raise this alarm, I think it’s a mandatory thing for each apps dba to verify logging settings and be aware of what might happen if recovery is needed. I’ve ran into these things several times, luckily all of them were discovered after cloning production to a test environment from a backup (whew, not the production recovery situations).
I had checked some of my environemnts and there’s a quite a lot of segments in NOLOGGING mode, most likely the data in those segments can be rebuilt using some concurrent programs, but I haven’t seen any MOS document compiling all the information about tables in nologging mode. And it’s too hard to read all the user/setup guides for each module to find details about this. Luckily I had chance to put databases in FORCE_LOGGING mode, but I don’t know how I would have dealt with it if that was not an option.
Could you share your experience with this? Have you seen any good documentation containing recommendations/procedures for rebuilding the data in those segments?
Hello Maris and thank you for your questions and comments.
>> Could you share your experience with this? Have you seen any good documentation containing recommendations/procedures for rebuilding the data in those segments?
The direct answer is – No! I have not seen any EBS specific document with a list of objects (segments) that could be lost without any significant business impact.
In fact I think that this is one of those areas there very tight cooperation in between Development and DBAs are necessary to come with such a list. You and me as anyone else know that it almost impossible to archive :) Especially in that context there developers doesn’t care that much about recoverability. I am sure most of them don’t know about the impact at all and who knows sure that it isn’t their problem (DBA should cover it). DBAs in their turn do perfectly understand what the possible impact is but do not have any understanding on the business value of the data in one or another table.
To make the long answer short I think that the list doesn’t exist and it will not be created in the nearest few years (I wish to be wrong).
However there is a good news as well. Have a look on the post. There is APPS_TS_NOLOGGING tablespace in EBS now. IMHO this is much easier to explain to Developers that if you do not need data in certain object to be recoverable or it is recoverable in an easier way in case of disaster put those objects in that tablespace. In that way there is a single easy controllable “basket”. I see that this might be the way to go.
>> I had checked some of my environments and there’s a quite a lot of segments in NOLOGGING mode
Hey! Be alarmed my friend here! NOLOGGING object attribute doesn’t mean anything. There might not be any single NOLOGGING (direct write) operation against an object with NOLOGGING attribute during it whole life. On the other hand there might be objects in with LOGGING = YES that would give you FALSE safety feeling however some of the application code may set LOGGING = NO, do the damage, set LOGGING = YES.
My point is LOGGING attribute give us some idea on the objects at risk however it isn’t 100 reliable.
>> luckily all of them were discovered after cloning production to a test environment from a backup (whew, not the production recovery situations).
This VERY good point you are making here. I glad to see that there are people using Best practices in backup and recovery area. I am sure that this is must to have thing in any environment:
– We should test out backups on regular basis
– Production Cloning over Dev/Test is the a perfect opportunity for recovery testing
– I would run DB VERIFY utility at very end of cloning process as the only reliable way of checking unrecoverable objects.
Thank you Maris once again and stay tuned :)
Yury
Yury,
I have a slightly different case..
In my case, data tablespaces are in LOGGING mode and index tablespaces are in NOLOGGING mode.
What is your take on this ?
From what I understand, all DML’s which affect single or multiple rows will LOG both table and index in this case even though index tablespace is in NOLOGGING mode . I was told that this – INDEX tablespaces in NOLOGGING mode – was done to reduce the redo generation, but in this case, I don’t think we are reducing any REDO generation since Oracle generates redo for both table and index, inspite of indexes being in NOLOGGING mode.
SJois,
your comment made me interested on how redo is affected by indexes with nologging setting, therefore I carried out a small test case, the sesults are available here https://appsdbalife.wordpress.com/2010/09/11/is-redo-size-affected-by-nologging-indexes
The short answer is, you are right, NOLOGGING index still causees redo generation during DML operations even if the table is in NOLOGGING mode and is loaded using insert +append.
Hello SJois,
Thank you for following my blog posts.
You are complicity right talking about DML and NOLOGGING impact on INDEXes. All DMLs are LOGGING redo for INDEX operations. E.g in case of INSERT /*+APPEND */… AS SELECT … operation direct load part will avoid REDO stream (if te table have LOGGING=NO attribute set) but all index rebuild operations will generate REDO entries.
LOGGING=NO on INDEXES have an effect during INDEXes maintenance operations (ALTER INDEX … REBUILD). In case of recovery you will need to rebuild the effected indexes if no proper backup have been taken after the maintenance.
Please stay alarmed that LOGGING tablespace attribute even more meaningless than an object attribute. The tablespace’s attribute just sets default value for any objects created in that tablespace. This attribute could be changed for any object any time changing the behaviour.
Beer in mind NOLOGGING is SEGMENT level attribute (even different partitions of the same table can have different values).
FORCE LOGGING however is either TABLESPACE or DATABASE level parameter. It have immediate effect on all objects located in the tablespace to whole DB.
BTW: I am not sure if TABLESPACE level FORCE logging overwrites DB level value :). You should test it if you are interested to know.
Regards,
Yury
[…] by nologging indexes? September 11, 2010 Maris Elsins Leave a comment Go to comments Yury recently raised a topic about nologging segments in e-Business Suite database. The discussion later […]
Hi all,
This is a Good Discussion about Nologging operations. I have never used EBS before, but i accidentally came across a Support Note which describes these Objects:
See MOS:
Nologging in the E-Business Suite [ID 216211.1]
Generally, when the Nologging Objects are known beforehand, then these could be dropped and recreated after performing a DB Restore.
Most of these Objects are used for temporary Operations anyway, so this would be ok.
Another Solution would be to schedule an incremental Backup of all affected datafiles by checking for unrecoverable Changes in the dynamic View v$datafile.
select * from v$datafile df
where df.unrecoverable_change# is not null
Regards,
Pascal
Thanks for the information ! I believe you may also be interested to read and share your feedback for the top reasons on why you should not use Clonetab (An Automated Cloning Appliance). You can check it from here : https://app.snapapp.com/leadrev-BkRxu15Se