{"id":1591,"date":"2011-10-01T07:52:47","date_gmt":"2011-10-01T12:52:47","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=1591"},"modified":"2011-10-01T07:54:34","modified_gmt":"2011-10-01T12:54:34","slug":"disabling-logging-on-a-lob","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2011\/10\/01\/disabling-logging-on-a-lob\/","title":{"rendered":"Disabling LOGGING on a LOB"},"content":{"rendered":"<p>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:<\/p>\n<li>copy rows nologging to destination database<\/li>\n<li>backup destination database<\/li>\n<li>truncate tables in the source database<\/li>\n<p>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.<\/p>\n<p>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&#8230;<\/p>\n<pre lang=\"text\">\r\nesbdb01pxdu:oracle:dledb01:\/home\/oracle>.\/copyJournals.py 5\r\n\/-- 5872 ---\/\r\n\/-- 432 ---\/\r\n\/-- 432 ---\/\r\n\/-- 383260 ---\/\r\n\/-- 476 ---\/\r\n<\/pre>\n<p>\u2026and we see the LOB is set to LOGGING&#8230;<\/p>\n<pre lang=\"text\">\r\nesbdb01pxdu:oracle:dledb01:\/home\/oracle>.\/dynColumns.py \"select * from dba_lobs where table_name = upper('xwcmd_xml_dat_jrn_09_2010')\"\r\n-----------------------------------------------\r\nOWNER                                   XWC\r\nTABLE_NAME                              XWCMD_XML_DAT_JRN_09_2010\r\nCOLUMN_NAME                             SYS_NC00008$\r\nSEGMENT_NAME                            SYS_LOB0000103173C00008$$\r\nTABLESPACE_NAME                         XWCD_ARLB_09_10_4K\r\nINDEX_NAME                              SYS_IL0000103173C00008$$\r\nCHUNK                                   4096\r\nPCTVERSION                              2\r\nRETENTION                               None\r\nFREEPOOLS                               None\r\nCACHE                                   NO\r\nLOGGING                                 YES\r\nIN_ROW                                  YES\r\nFORMAT                                  ENDIAN NEUTRAL\r\nPARTITIONED                             NO\r\n-----------------------------------------------\r\n<\/pre>\n<p>\u2026so we disable caching (required for nologging on LOB\u2019s) and logging\u2026<\/p>\n<pre lang=\"text\">\r\nSQL> alter table xwc.xwcmd_xml_dat_jrn_09_2010 modify lob(mdata_xml.xmldata) (nocache nologging);\r\n\r\nTable altered.\r\n\r\nSQL>\r\n<\/pre>\n<p>\u2026and see our redo is decreased ten-fold when we insert the exact same rows as we did initially\u2026<\/p>\n<pre lang=\"text\">\r\nesbdb01pxdu:oracle:dledb01:\/home\/oracle>.\/copyJournals.py 5\r\n\/-- 1452 ---\/\r\n\/-- 1112 ---\/\r\n\/-- 1452 ---\/\r\n\/-- 39444 ---\/\r\n\/-- 1452 ---\/\r\nesbdb01pxdu:oracle:dledb01:\/home\/oracle>\r\n<\/pre>\n<p>Some redo is still generated, as always for the UNDO on the DML statement.  You can\u2019t \u201credo\u201d the SQL statement on the table, but you have to be able to \u201credo\u201d the undo blocks that were changed as a result of the DML on the table.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/appcrawler.com\/wordpress\/2011\/10\/01\/disabling-logging-on-a-lob\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[19,22],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/1591"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=1591"}],"version-history":[{"count":11,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/1591\/revisions"}],"predecessor-version":[{"id":1610,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/1591\/revisions\/1610"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=1591"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=1591"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=1591"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}