Tracking down NOLOGGING objects

Fellow Listers,

 I've got a database that shows nologging operations in one tablespace as
evident by this query :

  1  SELECT NAME, UNRECOVERABLE_CHANGE#,TO_CHAR
(UNRECOVERABLE_TIME,'DD-MON-YYYY HH:MI:SS')  UNRECOVERABLE_TIME
 2  FROM V$DATAFILE
 3* where UNRECOVERABLE_CHANGE# != 0

 NAME                           UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
------------------------------ --------------------- --------------------
/SVCREQRP01/siebeld01.dbf                 8.3601E+12 29-AUG-2008 02:47:20

I have new "unrecoverable" operations in that tablespace every morning at
the same time. I want to get rid of the nologging, but I can't figure out
which object is causing the nologging operation (I know I can set force
logging at the tablespace/database, but I want to find out what's causing
this).

Here's what I've looked at so far, and I'm looking for ideas to what else I
can look for.

I've checked every object in the tablespace in question and they all have
LOGGING='YES'.

As you can see from the below SQL using the AWR tables, there are 3 objects
that receive physical writes and at the right time (the 2-3am snapshot). As
you can see all these objects have logging turned on and last had a DDL
statement execute on them in July (i.e. it's not a job that alters them to
nologging and then back to logging).

  1  select
snap.begin_interval_time,o.owner,o.object_name,physical_writes_direct_delta,
t.logging, o.last_ddl_time
  2  from wrh$_seg_stat s, dba_hist_snapshot snap, v$tablespace ts,
dba_objects o, dba_tables t
  3  where s.snap_id = snap.snap_id
  4  and s.dbid = snap.dbid
  5  and o.object_id = s.obj#
  6  and ts.ts# = s.ts#
  7  and ts.name = 'SIEBELD'
  8  and s.physical_writes_direct_delta != 0 and
s.physical_writes_direct_delta is not null
  9  and t.owner = o.owner
 10  and t.table_name = o.object_name
 11* order by 2,3,1

BEGIN_INTERVAL_TIME         OWNER
OBJECT_NAME               PHYSICAL_WRITES_DIRECT_DELTA LOG LAST_DDL_
--------------------------- ------------------------------
------------------------- ---------------------------- --- ---------
22-AUG-08 02.00.59.698 AM   SIEBEL
STG_CCV_SERVICE_REQUEST                            912 YES 24-JUL-08
23-AUG-08 02.00.47.427 AM   SIEBEL
STG_CCV_SERVICE_REQUEST                            921 YES 24-JUL-08
24-AUG-08 02.00.34.775 AM   SIEBEL
STG_CCV_SERVICE_REQUEST                            921 YES 24-JUL-08
25-AUG-08 02.00.22.106 AM   SIEBEL
STG_CCV_SERVICE_REQUEST                            920 YES 24-JUL-08
26-AUG-08 02.00.10.019 AM   SIEBEL
STG_CCV_SERVICE_REQUEST                            934 YES 24-JUL-08
27-AUG-08 02.00.57.546 AM   SIEBEL
STG_CCV_SERVICE_REQUEST                            948 YES 24-JUL-08
28-AUG-08 02.00.46.124 AM   SIEBEL
STG_CCV_SERVICE_REQUEST                            958 YES 24-JUL-08
29-AUG-08 02.00.33.595 AM   SIEBEL
STG_CCV_SERVICE_REQUEST                            972 YES 24-JUL-08
22-AUG-08 02.00.59.698 AM   SIEBEL
STG_SERVICE_REQUEST                               1302 YES 24-JUL-08
23-AUG-08 02.00.47.427 AM   SIEBEL
STG_SERVICE_REQUEST                               1314 YES 24-JUL-08
24-AUG-08 02.00.34.775 AM   SIEBEL
STG_SERVICE_REQUEST                               1314 YES 24-JUL-08
25-AUG-08 02.00.22.106 AM   SIEBEL
STG_SERVICE_REQUEST                               1314 YES 24-JUL-08
26-AUG-08 02.00.10.019 AM   SIEBEL
STG_SERVICE_REQUEST                               1330 YES 24-JUL-08
27-AUG-08 02.00.57.546 AM   SIEBEL
STG_SERVICE_REQUEST                               1350 YES 24-JUL-08
28-AUG-08 02.00.46.124 AM   SIEBEL
STG_SERVICE_REQUEST                               1367 YES 24-JUL-08
29-AUG-08 02.00.33.595 AM   SIEBEL
STG_SERVICE_REQUEST                               1379 YES 24-JUL-08
22-AUG-08 02.00.59.698 AM   SIEBEL
STG_TOUCH                                          439 YES 22-JUL-08
23-AUG-08 02.00.47.427 AM   SIEBEL
STG_TOUCH                                          443 YES 22-JUL-08
24-AUG-08 02.00.34.775 AM   SIEBEL
STG_TOUCH                                          443 YES 22-JUL-08
25-AUG-08 02.00.22.106 AM   SIEBEL
STG_TOUCH                                          443 YES 22-JUL-08
26-AUG-08 02.00.10.019 AM   SIEBEL
STG_TOUCH                                          449 YES 22-JUL-08
27-AUG-08 02.00.57.546 AM   SIEBEL
STG_TOUCH                                          454 YES 22-JUL-08
28-AUG-08 02.00.46.124 AM   SIEBEL
STG_TOUCH                                          460 YES 22-JUL-08
29-AUG-08 02.00.33.595 AM   SIEBEL
STG_TOUCH                                          465 YES 22-JUL-08

I have turned on auditing and audited for create/alter table and
create/alter index and there's nothing there. The recycle bin is empty as
well.

I'm running out of ideas for tracking down why the first query show
unrecoverable data has been written to the tablespace.

Would a sqlloader direct/unrecoverable load cause this even if logging is
set to YES on the table level?

Any input greatly appreciated.

Thanks,
Finn

Other related posts: