.
\nWhen you load data in direct-path and have the NOLOGGING attribute set, you minimize redo generation, but you take the risk, in case of media recovery, to loose the data in the blocks that you’ve loaded. So you probably run a backup as soon as the load is done. But what happens if you have a crash, with media failure, before the backup is finish?<\/p>\n
I encountered recently the situation but – probably because of a bug – the result was not exactly what I expected. Of course, before saying that it’s a bug I need to clear any doubt about what I think is the normal situation. So I’ve reproduced the normal situation and I’m sharing it here in case someone wants to see how to handle it.<\/p>\n
First, let me emphasize something that is very important. I didn’t say that you can loose the data that you’ve loaded. You loose the data which were in the blocks that have been allocated by your load. It may concern conventional DML happening long time after the nologging load. And anyway, you probably loose the whole table (or partition) because as you will see the proper way to recover from nologging recovery is to truncate the table (or partition).<\/p>\n
I’m in 12c so I can run my SQL statements from RMAN. I create a DEMO tablespace and a 1000 rows table in it:<\/p>\n
RMAN> echo set on\n\nRMAN> create tablespace DEMO datafile '\/tmp\/demo.dbf' size 10M;\nStatement processed\n\nRMAN> create table DEMO.DEMO pctfree 99 tablespace DEMO nologging as select * from dual connect by level commit;\nStatement processed\n<\/code><\/pre>\nImagine that I’ve a media failure and I have to restore my tablespace:<\/p>\n
RMAN> alter tablespace DEMO offline;\nStatement processed\n\n\nRMAN> restore tablespace DEMO;\nStarting restore at 04-SEP-14\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=8 device type=DISK\n\ncreating datafile file number=2 name=\/tmp\/demo.dbf\nrestore not done; all files read only, offline, or already restored\nFinished restore at 04-SEP-14\n<\/code><\/pre>\nand recover up to the point of failure:<\/p>\n
RMAN> recover tablespace DEMO;\nStarting recover at 04-SEP-14\nusing channel ORA_DISK_1\n\nstarting media recovery\nmedia recovery complete, elapsed time: 00:00:01\n\nFinished recover at 04-SEP-14\n\nRMAN> alter tablespace DEMO online;\nStatement processed\n<\/code><\/pre>\nThen here is what happen when I want to query the table where I’ve loaded data without logging:<\/p>\n
RMAN> select count(*) from DEMO.DEMO;\nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03002: failure of sql statement command at 09\/04\/2014 16:21:27\nORA-01578: ORACLE data block corrupted (file # 2, block # 131)\nORA-01110: data file 2: '\/tmp\/demo.dbf'\nORA-26040: Data block was loaded using the NOLOGGING option\n<\/code><\/pre>\nLet’s see that:<\/p>\n
RMAN> validate tablespace DEMO;\nStarting validate at 04-SEP-14\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting validation of datafile\nchannel ORA_DISK_1: specifying datafile(s) for validation\ninput datafile file number=00002 name=\/tmp\/demo.dbf\nchannel ORA_DISK_1: validation complete, elapsed time: 00:00:01\nList of Datafiles\n=================\nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n2 OK 167 974 1280 6324214\n File Name: \/tmp\/demo.dbf\n Block Type Blocks Failing Blocks Processed\n ---------- -------------- ----------------\n Data 0 0\n Index 0 0\n Other 0 306\n\nFinished validate at 04-SEP-14\n<\/code><\/pre>\n167 blocks have been marked as corrupt.<\/p>\n
The solution is to truncate the concerned table.<\/p>\n
And if you don’t know what are the tables that are concerned then you need to check v$database_block_corruption and dba_extents. So, my advise is that the tables loaded in NOLOGGING should be documented in the recovery plan, with the way to reload the data. Of course, that’s not an easy task because NOLOGGING is usually done by developers and recovery is done by the DBA. The other alternative is to prevent any NOLOGGING operation and put the database in FORCE LOGGING. In a Data Guard configuration, you should do that anyway.<\/p>\n
So I truncate my table:<\/p>\n
RMAN> truncate table DEMO.DEMO;\nStatement processed\n<\/code><\/pre>\nand if I check my tablespace, I still see the blocks as ‘Marked Corrupt’:<\/p>\n
RMAN> validate tablespace DEMO;\nStarting validate at 04-SEP-14\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting validation of datafile\nchannel ORA_DISK_1: specifying datafile(s) for validation\ninput datafile file number=00002 name=\/tmp\/demo.dbf\nchannel ORA_DISK_1: validation complete, elapsed time: 00:00:01\nList of Datafiles\n=================\nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n2 OK 167 974 1280 6324383\n File Name: \/tmp\/demo.dbf\n Block Type Blocks Failing Blocks Processed\n ---------- -------------- ----------------\n Data 0 0\n Index 0 0\n Other 0 306\n\nFinished validate at 04-SEP-14\n<\/code><\/pre>\nThis is the normal behaviour. The blocks are still marked as corrupt until they are formatted again.<\/p>\n
I put back my data;<\/p>\n
RMAN> insert \/*+ append *\/ into DEMO.DEMO select * from dual connect by level commit;\nStatement processed\n<\/code><\/pre>\nAnd check my tablespace again:<\/p>\n
RMAN> validate tablespace DEMO;\nStarting validate at 04-SEP-14\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting validation of datafile\nchannel ORA_DISK_1: specifying datafile(s) for validation\ninput datafile file number=00002 name=\/tmp\/demo.dbf\nchannel ORA_DISK_1: validation complete, elapsed time: 00:00:01\nList of Datafiles\n=================\nFile Status Marked Corrupt Empty Blocks Blocks Examined High SCN\n---- ------ -------------- ------------ --------------- ----------\n2 OK 0 974 1280 6324438\n File Name: \/tmp\/demo.dbf\n Block Type Blocks Failing Blocks Processed\n ---------- -------------- ----------------\n Data 0 167\n Index 0 0\n Other 0 139\n\nFinished validate at 04-SEP-14\n<\/code><\/pre>\nThe 167 corrupted blocks have been reused, now being safe and containing my newly loaded data.<\/p>\n
This is the point I wanted to validate because I’ve seen a production database where the blocks remained marked as corrupted. The load has allocated exents containing those blocks but, fortunately, has avoided to put rows in it. However, monitoring is still reporting corrupt blocks and we have to fix that as soon as we can move the tables to another tablespace.<\/p>\n
Last point. If you want to see if some tablespace had NOLOGGING operations since the last backup, run:<\/p>\n
RMAN> report unrecoverable;\nReport of files that need backup due to unrecoverable operations\nFile Type of Backup Required Name\n---- ----------------------- -----------------------------------\n2 full \/tmp\/demo.dbf\n<\/code><\/pre>\nThis is an indication that you should backup that datafile now. Knowing the objects concerned if a lot more complex…<\/p>\n
I’ll not open a SR as I can’t reproduce the issue I encountered (corrupt flag remaining after reallocating blocks) but if anyone had that kind of issue, please share.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . When you load data in direct-path and have the NOLOGGING attribute set, you minimize redo generation, but you take the risk, in case of media recovery, to loose the data in the blocks that you’ve loaded. So you probably run a backup as soon as the load is done. But what […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,59],"tags":[221,485,96,226],"type_dbi":[],"class_list":["post-3995","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-data-guard","tag-nologging","tag-oracle","tag-recovery"],"acf":[],"yoast_head":"\n
What's the consequence of NOLOGGING loads? - dbi Blog<\/title>\n<meta name=\"description\" content=\"You restored the database and have following errors. What do you do? ORA-01578: ORACLE data block corrupted ORA-26040: Data block was loaded using the NOLOGGING option\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What's the consequence of NOLOGGING loads?\" \/>\n<meta property=\"og:description\" content=\"You restored the database and have following errors. What do you do? ORA-01578: ORACLE data block corrupted ORA-26040: Data block was loaded using the NOLOGGING option\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-09-04T11:25:04+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"What’s the consequence of NOLOGGING loads?\",\"datePublished\":\"2014-09-04T11:25:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/\"},\"wordCount\":597,\"commentCount\":0,\"keywords\":[\"Data Guard\",\"nologging\",\"Oracle\",\"Recovery\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/\",\"name\":\"What's the consequence of NOLOGGING loads? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2014-09-04T11:25:04+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"You restored the database and have following errors. What do you do? ORA-01578: ORACLE data block corrupted ORA-26040: Data block was loaded using the NOLOGGING option\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What’s the consequence of NOLOGGING loads?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"What's the consequence of NOLOGGING loads? - dbi Blog","description":"You restored the database and have following errors. What do you do? ORA-01578: ORACLE data block corrupted ORA-26040: Data block was loaded using the NOLOGGING option","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/","og_locale":"en_US","og_type":"article","og_title":"What's the consequence of NOLOGGING loads?","og_description":"You restored the database and have following errors. What do you do? ORA-01578: ORACLE data block corrupted ORA-26040: Data block was loaded using the NOLOGGING option","og_url":"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/","og_site_name":"dbi Blog","article_published_time":"2014-09-04T11:25:04+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"What’s the consequence of NOLOGGING loads?","datePublished":"2014-09-04T11:25:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/"},"wordCount":597,"commentCount":0,"keywords":["Data Guard","nologging","Oracle","Recovery"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/","url":"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/","name":"What's the consequence of NOLOGGING loads? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-09-04T11:25:04+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"You restored the database and have following errors. What do you do? ORA-01578: ORACLE data block corrupted ORA-26040: Data block was loaded using the NOLOGGING option","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/whats-the-consequence-of-nologging-loads\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"What’s the consequence of NOLOGGING loads?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3995","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=3995"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3995\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=3995"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=3995"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=3995"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=3995"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}