{"id":9825,"date":"2017-02-26T15:32:25","date_gmt":"2017-02-26T14:32:25","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/"},"modified":"2017-02-26T15:32:25","modified_gmt":"2017-02-26T14:32:25","slug":"12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/","title":{"rendered":"12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nYou can accept to do NOLOGGING operations on bulk loads or index build according that you do a backup just after, and that your recovery plan mentions how to load the data again in case of media recovery. With a standby database, we usually force logging because we want redo to be generated for all operations in order to ship it and apply it on standby database. 12.2 brings a new solution: do nologging operations, without generating redo, and then ship the blocks to the standby. This is done on the standby by RMAN.<\/p>\n<h3>On primary ORCLA<\/h3>\n<p>I create the demo table<\/p>\n<pre><code>SQL&gt; create table DEMO tablespace users pctfree 99 as select rownum n from xmltable('1 to 1000');\nTable created.<\/code><\/pre>\n<p>put it in NOLOGGING<\/p>\n<pre><code>SQL&gt; alter table DEMO nologging;\nTable altered.\n<\/code><\/pre>\n<p>The database is not in force logging:<\/p>\n<pre><code>SQL&gt; select force_logging from v$database;\nFORCE_LOGGING\n---------------------------------------\nNO\n<\/code><\/pre>\n<p>Here is a direct-path insert<\/p>\n<pre><code>SQL&gt; insert \/*+ append *\/ into DEMO select rownum n from xmltable('1 to 100000');\n100000 rows created.\n&nbsp;\nSQL&gt; commit;\nCommit complete.<\/code><\/pre>\n<p>My rows are here:<\/p>\n<pre><code>SQL&gt; select count(*) from DEMO;\n  COUNT(*)\n----------\n    200000<\/code><\/pre>\n<p>This is a nologging operation. Media recovery is not possible. The datafile needs backup:<\/p>\n<pre><code>RMAN&gt; report unrecoverable;\nusing target database control file instead of recovery catalog\nReport of files that need backup due to unrecoverable operations\nFile Type of Backup Required Name\n---- ----------------------- -----------------------------------\n7    full or incremental     \/u01\/oradata\/ORCLA\/users01.dbf\n<\/code><\/pre>\n<h3>On ADG standby ORCLB<\/h3>\n<p>In Active Data Guard, I can query the table, but:<\/p>\n<pre><code>SQL&gt; select count(*) from DEMO\n       *\nERROR at line 1:\nORA-01578: ORACLE data block corrupted (file # 7, block # 16966)\nORA-01110: data file 7: '\/u01\/oradata\/ORCLB\/datafile\/o1_mf_users_dbvmwdqc_.dbf'\nORA-26040: Data block was loaded using the NOLOGGING option<\/code><\/pre>\n<p>The blocks were not replicated because redo was not generated by the primary and then not shipped and applied on the standby.<\/p>\n<p>Note that this is not identifed by RMAN on the standby:<\/p>\n<pre><code>RMAN&gt; report unrecoverable;\nusing target database control file instead of recovery catalog\nReport of files that need backup due to unrecoverable operations\nFile Type of Backup Required Name\n---- ----------------------- -----------------------------------\n&nbsp;\nRMAN&gt;<\/code><\/pre>\n<h3>recover nonlogged blocks<\/h3>\n<p>If I try some recovery here, I can&#8217;t because I&#8217;m still is apply mode, here is the message I get if I try:<\/p>\n<pre><code>ORA-01153: an incompatible media recovery is active<\/code><\/pre>\n<p>Let&#8217;s stop the apply:<\/p>\n<pre><code>DGMGRL&gt; edit database orclb set state=apply-off;\nSucceeded.<\/code><\/pre>\n<p>In 12.1 I can recover the datafile from the primary with &#8216;recover from service&#8217; but in 12.2 there is no need to ship the whole datafile. The non-logged block list has been shipped to the standby, recorded in the standby controlfile, and we can list them from v$nonlogged_block.<\/p>\n<p>And we can recover them with a simple command: RECOVER DATABASE NONLOGGED BLOCK<\/p>\n<pre><code>RMAN&gt; recover database nonlogged block;\nStarting recover at 25-FEB-17\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=58 device type=DISK\n&nbsp;\nstarting recovery of nonlogged blocks\nList of Datafiles\n=================\nFile Status Nonlogged Blocks Blocks Examined Blocks Skipped\n---- ------ ---------------- --------------- --------------\n1    OK     0                0               104959\n2    OK     0                0               18783\n3    OK     0                0               62719\n4    OK     0                0               8959\n7    OK     0                16731           18948\n&nbsp;\nDetails of nonlogged blocks can be queried from v$nonlogged_block view\n&nbsp;\nrecovery of nonlogged blocks complete, elapsed time: 00:00:03<\/code><\/pre>\n<p>Here it is, I can query the table now<\/p>\n<pre><code>SQL&gt; select count(*) from DEMO;\n  COUNT(*)\n----------\n    200000<\/code><\/pre>\n<p>I re-enable real-time apply<\/p>\n<pre><code>DGMGRL&gt; edit database orclb set state=apply-on;\nSucceeded.<\/code><\/pre>\n<h3>Switchover<\/h3>\n<p>Now, what would happen if I do a switchover of failover between the nologging operation and the nonlogged recovery?<br \/>\nI did the same on primary and then:<\/p>\n<pre><code>DGMGRL&gt; switchover to orclb;\nPerforming switchover NOW, please wait...\nOperation requires a connection to database \"orclb\"\nConnecting ...\nConnected to \"ORCLB\"\nConnected as SYSDBA.\nNew primary database \"orclb\" is opening...\nOperation requires start up of instance \"ORCLA\" on database \"orcla\"\nStarting instance \"ORCLA\"...\nORACLE instance started.\nDatabase mounted.\nDatabase opened.\nConnected to \"ORCLA\"\nSwitchover succeeded, new primary is \"orclb\"<\/code><\/pre>\n<p>I can query the list of nonlogged blocks that was shipped to standby:<\/p>\n<pre><code>SQL&gt; select * from v$nonlogged_block;\n&nbsp;\n     FILE#     BLOCK#     BLOCKS NONLOGGED_START_CHANGE# NONLOGGED\n---------- ---------- ---------- ----------------------- ---------\nNONLOGGED_END_CHANGE# NONLOGGED RESETLOGS_CHANGE# RESETLOGS\n--------------------- --------- ----------------- ---------\nOBJECT#                                  REASON      CON_ID\n---------------------------------------- ------- ----------\n         7        307      16826                 2197748\n              2197825                     1396169 22-FEB-17\n74006                                    UNKNOWN          0<\/code><\/pre>\n<p>But I cannot recover because the database (the old standby that became primary) is opened:<\/p>\n<pre><code>ORA-01126: database must be mounted in this instance and not open in any instance\n<\/code><\/pre>\n<h3>So what?<\/h3>\n<p>This new feature is acceptable if you recover the nonlogged blocks on the standby just after the nologging operation on the primary. This can be used automatically for datawarehouse load, but also manually when doing a reorganization or an application release that touches the data. Just don&#8217;t forget the recover on the standby to avoid surprises later. It will not reduce the amount of data that is shipped to the standby, because shipping the blocks is roughly the same as shipping the redo for the direct-path writes. But one the primary you have the performance benefit of nologging operations.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . You can accept to do NOLOGGING operations on bulk loads or index build according that you do a backup just after, and that your recovery plan mentions how to load the data again in case of media recovery. With a standby database, we usually force logging because we want redo to [&hellip;]<\/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":[229],"tags":[656,221,96,209],"type_dbi":[],"class_list":["post-9825","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-12-2","tag-data-guard","tag-oracle","tag-oracle-12c"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard - dbi Blog<\/title>\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\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . You can accept to do NOLOGGING operations on bulk loads or index build according that you do a backup just after, and that your recovery plan mentions how to load the data again in case of media recovery. With a standby database, we usually force logging because we want redo to [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-02-26T14:32:25+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=\"4 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\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard\",\"datePublished\":\"2017-02-26T14:32:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/\"},\"wordCount\":459,\"commentCount\":0,\"keywords\":[\"12.2\",\"Data Guard\",\"Oracle\",\"Oracle 12c\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/\",\"name\":\"12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-02-26T14:32:25+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard\"}]},{\"@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":"12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard - dbi Blog","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\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/","og_locale":"en_US","og_type":"article","og_title":"12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard","og_description":"By Franck Pachot . You can accept to do NOLOGGING operations on bulk loads or index build according that you do a backup just after, and that your recovery plan mentions how to load the data again in case of media recovery. With a standby database, we usually force logging because we want redo to [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/","og_site_name":"dbi Blog","article_published_time":"2017-02-26T14:32:25+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard","datePublished":"2017-02-26T14:32:25+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/"},"wordCount":459,"commentCount":0,"keywords":["12.2","Data Guard","Oracle","Oracle 12c"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/","url":"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/","name":"12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-02-26T14:32:25+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"12cR2: Recover nonlogged blocks after NOLOGGING in Data Guard"}]},{"@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\/9825","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=9825"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9825\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9825"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9825"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9825"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9825"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}