{"id":9187,"date":"2016-10-29T10:29:44","date_gmt":"2016-10-29T08:29:44","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/"},"modified":"2016-10-29T10:29:44","modified_gmt":"2016-10-29T08:29:44","slug":"oracle-12c-automatic-control-file-backups","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/","title":{"rendered":"Oracle 12c &#8211; Automatic Control File Backups"},"content":{"rendered":"<h2>By William Sescu<\/h2>\n<p>By default, automatic control file backups are disabled (even with 12c), maybe for performance reasons.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">RMAN&gt; SHOW CONTROLFILE AUTOBACKUP;\n\nRMAN configuration parameters for database with db_unique_name OCM121 are:\nCONFIGURE CONTROLFILE AUTOBACKUP OFF; # default<\/pre>\n<p>And also good to know, the autobackup after structural changes does not occur for databases in NOARCHIVELOG mode. So, if your database is running in NOARCHIVELOG mode, you will never see any impact, independent of controlfile autobackup is on or off.<br \/>\nHowever, my database is running in ARCHIVELOG mode and at the moment the controlfile autobackup feature is disabled.<br \/>\nBut even when the auto backup feature is disabled, RMAN will still back up the current controlfile and the server parameter file whenever any backup command includes datafile 1 from the data files that belong to the target database. In an Oracle database, data file 1 is always part of the system tablespace, which contains the data dictionary.<\/p>\n<p>But I have heard that it is highly recommended to enable automatic controlfile backups. It will ensure that the critical controlfile is backed up regularly following a backup or structural change to the database. Once you configure automatic controlfile backup, RMAN will automatically back up your target database controlfile, as well as the current server parameter file, when any of the following events occurs:<\/p>\n<ul>\n<li>Successful completion of either a backup or the copy command<\/li>\n<li>After a create catalog command from the RMAN prompt is successfully completed<\/li>\n<li>Any structural changes to the database modify the contents of the control file<\/li>\n<\/ul>\n<p>Any changes to the physical structure of your database, even if they are made through SQL*Plus, will trigger a controlfile auto backup, e.g.<\/p>\n<ul>\n<li>adding a tablespace or data file<\/li>\n<li>dropping a data file<\/li>\n<li>placing a tablespace offline or online<\/li>\n<li>adding an online redo log, and renaming a data file<\/li>\n<\/ul>\n<p>So, to follow the recommendation, I will enable the automatic backup of the controlfile. At the moment I have no backup of the controlfile, which is not good at all.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">RMAN&gt;  list backup of controlfile;\n\nspecification does not match any backup in the repository\n\nRMAN&gt; CONFIGURE CONTROLFILE AUTOBACKUP ON;\n\nnew RMAN configuration parameters:\nCONFIGURE CONTROLFILE AUTOBACKUP ON;\nnew RMAN configuration parameters are successfully stored<\/pre>\n<p>But nothing happens. What is going on here. Is it a bug, a feature or something else.<br \/>\nHold on. Then &#8230; all of a sudden, the backup of the controlfile popps up.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">RMAN&gt; list backup of controlfile;\n\nList of Backup Sets\n===================\n\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\n------- ---- -- ---------- ----------- ------------ --------------------\n131     Full    9.89M      DISK        00:00:04     28-OCT-2016 15:42:23\n        BP Key: 314   Status: AVAILABLE  Compressed: NO  Tag: TAG20161028T154219\n        Piece Name: +FRA\/OCM121\/AUTOBACKUP\/2016_10_28\/s_926437339.526.926437343\n  Control File Included: Ckp SCN: 10102765     Ckp time: 28-OCT-2016 15:42:19<\/pre>\n<p>&nbsp;<\/p>\n<p>To understand what is happening here, the answer is like always. It depends. \ud83d\ude42 In case you are running an Oracle database with 11gR2 or higher, then it is a feature. Anything below would be a bug.<\/p>\n<p>Beginning with Oracle 11gR2, the controlfile autobackup deferral feature has been implemented. In order to increase performance, the controlfile autobackup creation after structural changes, has been deferred. In previous releases, one controlfile autobackup is created with each DDL command that makes structural changes in the database and we can see in the alert.log a message about controlfile autobackup creation after each DDL command executed.<br \/>\nThis can provoke serious performance problems when multiple structural changes are made together. Starting from Oracle Database Release 11g Release 2, RMAN takes only one control file autobackup when multiple structural changes contained in a script have been applied (for example, adding tablespaces, altering the state of a tablespace or datafile,<br \/>\nadding a new online redo log, renaming a file, and so on) during a specified time.<\/p>\n<p>But what does time mean here exactly. It is 1 minute, 1 hour, or 1 day?<\/p>\n<p>The deferral time is controlled by an underscore parameter that defaults to 300 seconds (5 minutes). The parameter is the following:<\/p>\n<p>_controlfile_autobackup_delay=300<\/p>\n<p>The minimum value for that parameter is 0, which simulates the behavior before 11gR2. The maximum value in 12c is (1024*1024*1024*2)-1, which is 2147483647 seconds. However, I don&#8217;t see any practical value for setting the value that high.<\/p>\n<p>In 11gR2 or higher, the controlfile autobackups are created by MMON slaves after few minutes (5 minutes per default) of the structural changes, which increases performance. It is also expected that no message about controlfile autobackup creation will appear in the alert.log.<br \/>\nHowever, there will be one MMON slave trace file with the controlfile creation information, that will be a file named: SID__m000_&lt;OS_PID&gt;.trc<br \/>\nOk. Let\u2019s try to simulate the old behavior by setting the autobackup delay to 0, and to create another tablespace afterwards.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">SQL&gt; alter system set \"_controlfile_autobackup_delay\"=0;\n\nSystem altered.\n\nSQL&gt; create tablespace unpatient2 datafile size 16M;\n\nTablespace created.<\/pre>\n<p>And now the controlfile autobackup is created immediately.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">RMAN&gt; list backup of controlfile;\n\nusing target database control file instead of recovery catalog\n\nList of Backup Sets\n===================\n\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\n------- ---- -- ---------- ----------- ------------ --------------------\n131     Full    9.89M      DISK        00:00:04     28-OCT-2016 15:42:23\n        BP Key: 314   Status: AVAILABLE  Compressed: NO  Tag: TAG20161028T154219\n        Piece Name: +FRA\/OCM121\/AUTOBACKUP\/2016_10_28\/s_926437339.526.926437343\n  Control File Included: Ckp SCN: 10102765     Ckp time: 28-OCT-2016 15:42:19\n\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\n------- ---- -- ---------- ----------- ------------ --------------------\n132     Full    9.89M      DISK        00:00:05     28-OCT-2016 17:41:16\n        BP Key: 315   Status: AVAILABLE  Compressed: NO  Tag: TAG20161029T113939\n        Piece Name: +FRA\/OCM121\/AUTOBACKUP\/2016_10_29\/s_926509179.514.926509183\n  Control File Included: Ckp SCN: 10271426     Ckp time: 28-OCT-2016 17:41:11<\/pre>\n<p>So, do I recommend to set the RMAN controlfile autobackup to ON. Yes absolutely. And do I recommend to set the controlfile autobackup\u00a0delay to 0. No, probably not. I think, that the 5-minute interval is a quite good compromise. You just need to be aware, that it exists.<\/p>\n<p>Another hint is, that you should not rely too much on the view V$RMAN_BACKUP_JOB_DETAILS. In this view, the autobackups should be populated, whenever a autobackup happend.\u00a0But in my case the AUTOBACKUP_DONE is always set to NO.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">SQL&gt; select start_time,end_time,status,autobackup_done, AUTOBACKUP_COUNT from\n2 V$RMAN_BACKUP_JOB_DETAILS where autobackup_done = 'YES';\n\nno rows selected<\/pre>\n<p>There is a patch available from Oracle: &#8220;Patch 18074513: V$RMAN_BACKUP_JOB_DETAILS VIEWS COLUMN AUTOBACKUP_DONE DOESNOT GET POPULATED&#8221;, but it is not available for every platform and every version.<\/p>\n<p>Better use the RMAN &#8220;list backup of controlfile;&#8221; command. That one is much more reliable.<\/p>\n<p>Cheers,<br \/>\nWilliam<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By William Sescu By default, automatic control file backups are disabled (even with 12c), maybe for performance reasons. RMAN&gt; SHOW CONTROLFILE AUTOBACKUP; RMAN configuration parameters for database with db_unique_name OCM121 are: CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default And also good to know, the autobackup after structural changes does not occur for databases in NOARCHIVELOG mode. [&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":[209,270],"type_dbi":[],"class_list":["post-9187","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-oracle-12c","tag-rman"],"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>Oracle 12c - Automatic Control File Backups - 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\/oracle-12c-automatic-control-file-backups\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12c - Automatic Control File Backups\" \/>\n<meta property=\"og:description\" content=\"By William Sescu By default, automatic control file backups are disabled (even with 12c), maybe for performance reasons. RMAN&gt; SHOW CONTROLFILE AUTOBACKUP; RMAN configuration parameters for database with db_unique_name OCM121 are: CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default And also good to know, the autobackup after structural changes does not occur for databases in NOARCHIVELOG mode. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-10-29T08:29:44+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\/oracle-12c-automatic-control-file-backups\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12c &#8211; Automatic Control File Backups\",\"datePublished\":\"2016-10-29T08:29:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/\"},\"wordCount\":832,\"commentCount\":0,\"keywords\":[\"Oracle 12c\",\"RMAN\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/\",\"name\":\"Oracle 12c - Automatic Control File Backups - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-10-29T08:29:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12c &#8211; Automatic Control File Backups\"}]},{\"@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":"Oracle 12c - Automatic Control File Backups - 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\/oracle-12c-automatic-control-file-backups\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12c - Automatic Control File Backups","og_description":"By William Sescu By default, automatic control file backups are disabled (even with 12c), maybe for performance reasons. RMAN&gt; SHOW CONTROLFILE AUTOBACKUP; RMAN configuration parameters for database with db_unique_name OCM121 are: CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default And also good to know, the autobackup after structural changes does not occur for databases in NOARCHIVELOG mode. [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/","og_site_name":"dbi Blog","article_published_time":"2016-10-29T08:29:44+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\/oracle-12c-automatic-control-file-backups\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12c &#8211; Automatic Control File Backups","datePublished":"2016-10-29T08:29:44+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/"},"wordCount":832,"commentCount":0,"keywords":["Oracle 12c","RMAN"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/","name":"Oracle 12c - Automatic Control File Backups - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-10-29T08:29:44+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-automatic-control-file-backups\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12c &#8211; Automatic Control File Backups"}]},{"@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\/9187","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=9187"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9187\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9187"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}