Disabling LOGGING on a LOB

In Oracle 10.2.0.4, we were doing a large data migration of archive records. Initially, we could recreate the data from the source, so our plan was to:

copy rows nologging to destination database

backup destination database

truncate tables in the source database

The tables have LOB columns in them, and we found that with an append hint and the table defined as NOLOGGING, redo was still generated.

Using five threads to copy the data into the target database, the following redo in bytes is generated per thread for a small subset of rows…

esbdb01pxdu:oracle:dledb01:/home/oracle>./copyJournals.py 5
/-- 5872 ---/
/-- 432 ---/
/-- 432 ---/
/-- 383260 ---/
/-- 476 ---/

…and we see the LOB is set to LOGGING…

esbdb01pxdu:oracle:dledb01:/home/oracle>./dynColumns.py "select * from dba_lobs where table_name = upper('xwcmd_xml_dat_jrn_09_2010')"
-----------------------------------------------
OWNER                                   XWC
TABLE_NAME                              XWCMD_XML_DAT_JRN_09_2010
COLUMN_NAME                             SYS_NC00008$
SEGMENT_NAME                            SYS_LOB0000103173C00008$$
TABLESPACE_NAME                         XWCD_ARLB_09_10_4K
INDEX_NAME                              SYS_IL0000103173C00008$$
CHUNK                                   4096
PCTVERSION                              2
RETENTION                               None
FREEPOOLS                               None
CACHE                                   NO
LOGGING                                 YES
IN_ROW                                  YES
FORMAT                                  ENDIAN NEUTRAL
PARTITIONED                             NO
-----------------------------------------------

…so we disable caching (required for nologging on LOB’s) and logging…

SQL> alter table xwc.xwcmd_xml_dat_jrn_09_2010 modify lob(mdata_xml.xmldata) (nocache nologging);

Table altered.

SQL>

…and see our redo is decreased ten-fold when we insert the exact same rows as we did initially…

esbdb01pxdu:oracle:dledb01:/home/oracle>./copyJournals.py 5
/-- 1452 ---/
/-- 1112 ---/
/-- 1452 ---/
/-- 39444 ---/
/-- 1452 ---/
esbdb01pxdu:oracle:dledb01:/home/oracle>

Some redo is still generated, as always for the UNDO on the DML statement. You can’t “redo” the SQL statement on the table, but you have to be able to “redo” the undo blocks that were changed as a result of the DML on the table.