LOBs Can Break Your Standby

When you create a table in logging mode and the table has LOBs, the LOB columns will take the logging mode of the TABLESPACE. That is right, not the table but the TABLESPACE. So if you have a tablespace that is NOLOGGING and you create the LOB segments of a table in the NOLOGGING tablespace, the LOBs will be created NOLOGGING.

The changes to LOBs will not get recorded in your redo stream. But what is even worse is that you will be unaware of this until you attempt to use your standby and find the data out of sync. Normally, the database keeps track of NOLOGGING operations and you can query this. However, LOBs seem to be handled differently.

I have created the following test case :

1. Make sure the database is NOT force logging:

   select force_logging from v$database;

2. Create two tablespaces, one LOGGING, the other NOLOGGING:

   create tablespace nolog datafile '/opt/db/oracle/product/10.2.0/dbs/nologtest3.dbf' size 50M NOLOGGING;
   create tablespace yeslog datafile '/opt/db/oracle/product/10.2.0/dbs/yeslogtest3.dbf' size 50M LOGGING;

3. Create a table with a LOB segment in the NOLOGGING tablespace and add data:

   create table testlob
   ( col1 number, col2 varchar2(80),col3 clob)
   lob (col3)  store as ( disable storage in row tablespace nolog)
   tablespace yeslog;
   insert into testlob values ( 1, 'Test 1', 'SOME VALUE');
   insert into testlob values ( 2, 'Test 2', 'SOME VALUE');
   update testlob set col3 = lpad('X',  4000, 'X');
   commit;

4. Confirm segments in proper tablespaces:

   select table_name, column_name, segment_name, tablespace_name, index_name from user_lobs;
   select segment_name, segment_type, tablespace_name from user_segments;

5. And see if the database recognizes any NOLOGGING operations. (It does not.)

   select * from v$datafile;
   create table testnolog NOLOGGING tablespace yeslog as select * from dba_objects where 1=0;
   insert /*+ append */ into testnolog select * from dba_objects;
   drop table testnolog;
   select * from testlob;
   select * from dba_objects where object_name like '%REC%';

6. Check to see if NOLOGGING was recorded for the tablespace:

   select unrecoverable_change# unreccha
       , unrecoverable_time unrectime
       , first_nonlogged_scn firstscn
       , first_nonlogged_time firsttime 
   from v$datafile;

   UNRECCHA	UNRECTIME	FIRSTSCN	FIRSTTIME
   0		0
   0		0
   0		0
   0		0
   0		0

   And this indicates that nothing has been identified as unrecoverable or NOLOGGING.

Typically, we monitor the v$datafile to ensure there are no NOLOGGING operations that will break our standby database. So it is important that we confirm that this mechanism actually works. We do this by creating a NOLOGGING table in a LOGGING tablespace as follows :

create table testnolog NOLOGGING tablespace yeslog as select * from dba_objects where 1=0;
insert /*+ append */ into testnolog select * from dba_objects;

But for some reason, it is showing nothing under v$datafile for the YESLOG tablespace. I know this operation will break the standby database (because of a break in archivelog activity). I am just not sure how to confirm in the data dictionary.