{"id":9081,"date":"2016-10-11T09:29:57","date_gmt":"2016-10-11T07:29:57","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/"},"modified":"2016-10-11T09:29:57","modified_gmt":"2016-10-11T07:29:57","slug":"oracle-12c-managing-rman-persistent-settings-via-sql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/","title":{"rendered":"Oracle 12c &#8211; Managing RMAN persistent settings via SQL"},"content":{"rendered":"<h2>By William Sescu<\/h2>\n<p>RMAN persistent settings can be managed in two different ways.<\/p>\n<ul>\n<li>Via the RMAN interface<br \/>\n&#8211; e.g. RMAN&gt; CONFIGURE BACKUP OPTIMIZATION ON;<\/li>\n<li>Via SQL<br \/>\n&#8211; e.g. VARIABLE RECNO NUMBER;<br \/>\nEXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(&#8216;BACKUP OPTIMIZATION&#8217;,&#8217;ON&#8217;);<\/li>\n<\/ul>\n<p>There are several scenarios when it might be helpful to use the SQL way. I will show 3 of them:<\/p>\n<ul>\n<li>Automation<\/li>\n<li>Reset to default<\/li>\n<li>Rebuilding the RMAN persistent settings after losing all controlfiles (no catalog)<\/li>\n<\/ul>\n<p>Let&#8217;s take a look at the first scenario. For example, when\u00a0you have an automated way to run SQL&#8217;s against all of your databases and you want to change\u00a0the RMAN retention from 3 days to 4 days for all of your databases. Then you could\u00a0run the following.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">SQL&gt; select conf#, name, value from v$rman_configuration where name = 'RETENTION POLICY';\n\nCONF# NAME                             VALUE\n----- -------------------------------- ----------------------------------------------------------------------------------------\n    1 RETENTION POLICY                 TO RECOVERY WINDOW OF 3 DAYS\n\n\nSQL&gt; EXECUTE DBMS_BACKUP_RESTORE.DELETECONFIG(CONF# =&gt; 1);\n\nPL\/SQL procedure successfully completed.\n\nSQL&gt; VARIABLE RECNO NUMBER;\nSQL&gt; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 4 DAYS');\n\nPL\/SQL procedure successfully completed.\n\n\nSQL&gt; select conf#, name, value from v$rman_configuration where name = 'RETENTION POLICY';\n\nCONF# NAME                             VALUE\n----- -------------------------------- ----------------------------------------------------------------------------------------\n    1 RETENTION POLICY                 TO RECOVERY WINDOW OF 4 DAYS\n\n\t\n-- The new value is, of course, immediately reflected via the RMAN interface as well\n\nRMAN&gt; SHOW RETENTION POLICY;\n\nRMAN configuration parameters for database with db_unique_name OCM121 are:\nCONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;<\/pre>\n<p>&nbsp;<\/p>\n<p>The second useful scenario might be, to reset the whole RMAN config with one shot, instead of\u00a0running several clear commands like the following, &#8220;RMAN&gt; CONFIGURE BACKUP OPTIMIZATION CLEAR;&#8221; ,\u00a0simply run the RESETCONFIG.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">SQL&gt; EXECUTE DBMS_BACKUP_RESTORE.RESETCONFIG;\n\nPL\/SQL procedure successfully completed.\n\n-- After executing this command, the v$rman_configuration view is empty, which means that all\n-- RMAN persistent settings are default.\n\nSQL&gt; select conf#, name, value from v$rman_configuration;\n\nno rows selected<\/pre>\n<p>&nbsp;<\/p>\n<p>And last but not least, to restore the RMAN persistent settings via SQL, in case you have lost all of your controlfiles and no RMAN catalog is in place.<\/p>\n<p>One little side note, in case you have a RMAN catalog. The RMAN sync from the controlfile to the catalog is usually unidirectional, meaning that the controlfile is always the master and it syncs the information to the catalog. However, there are exceptions were it\u00a0is bidirectional. One of it is, when you recreate the controlfile manually, then RMAN is able to get the last\u00a0RMAN persistent settings from the catalog and applies it to the controlfile.<\/p>\n<p>However, if you don&#8217;t have a catalog, dump out the RMAN persistent settings into SQL, simply by backing up the controlfile to trace.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">SQL&gt; alter database backup controlfile to trace as '\/tmp\/cntrl.trc';\n\nDatabase altered.\n\n-- Configure RMAN configuration record 1\nVARIABLE RECNO NUMBER;\nEXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 4 DAYS');\n-- Configure RMAN configuration record 2\nVARIABLE RECNO NUMBER;\nEXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');\n-- Configure RMAN configuration record 3\nVARIABLE RECNO NUMBER;\nEXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');\n-- Configure RMAN configuration record 4\nVARIABLE RECNO NUMBER;\nEXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RMAN OUTPUT','TO KEEP FOR 14 DAYS');\n-- Configure RMAN configuration record 5\nVARIABLE RECNO NUMBER;\nEXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET');\n-- Configure RMAN configuration record 6\nVARIABLE RECNO NUMBER;\nEXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');\n-- Configure RMAN configuration record 7\nVARIABLE RECNO NUMBER;\nEXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE ''SBT_TAPE'' PARMS  ''SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=\/nfs\/OCM121)''');\n-- Configure RMAN configuration record 8\nVARIABLE RECNO NUMBER;\nEXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET');\n-- Configure RMAN configuration record 9\nVARIABLE RECNO NUMBER;\nEXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO APPLIED ON ALL STANDBY');<\/pre>\n<p>And if you run into the severe situation of losing all controlfiles, you can restore the RMAN persistent settings quite quickly. Especially useful, when you have configured complex Media Manager settings.<br \/>\nCheers,<br \/>\nWilliam<\/p>\n<p>P.S. Managing RMAN persistent settings via SQL is not a 12c feature. It exists for quite a long time.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By William Sescu RMAN persistent settings can be managed in two different ways. Via the RMAN interface &#8211; e.g. RMAN&gt; CONFIGURE BACKUP OPTIMIZATION ON; Via SQL &#8211; e.g. VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(&#8216;BACKUP OPTIMIZATION&#8217;,&#8217;ON&#8217;); There are several scenarios when it might be helpful to use the SQL way. I will show 3 of [&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-9081","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 - Managing RMAN persistent settings via SQL - 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-managing-rman-persistent-settings-via-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12c - Managing RMAN persistent settings via SQL\" \/>\n<meta property=\"og:description\" content=\"By William Sescu RMAN persistent settings can be managed in two different ways. Via the RMAN interface &#8211; e.g. RMAN&gt; CONFIGURE BACKUP OPTIMIZATION ON; Via SQL &#8211; e.g. VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(&#8216;BACKUP OPTIMIZATION&#8217;,&#8217;ON&#8217;); There are several scenarios when it might be helpful to use the SQL way. I will show 3 of [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-10-11T07:29:57+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\/oracle-12c-managing-rman-persistent-settings-via-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12c &#8211; Managing RMAN persistent settings via SQL\",\"datePublished\":\"2016-10-11T07:29:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/\"},\"wordCount\":355,\"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-managing-rman-persistent-settings-via-sql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/\",\"name\":\"Oracle 12c - Managing RMAN persistent settings via SQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-10-11T07:29:57+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12c &#8211; Managing RMAN persistent settings via SQL\"}]},{\"@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 - Managing RMAN persistent settings via SQL - 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-managing-rman-persistent-settings-via-sql\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12c - Managing RMAN persistent settings via SQL","og_description":"By William Sescu RMAN persistent settings can be managed in two different ways. Via the RMAN interface &#8211; e.g. RMAN&gt; CONFIGURE BACKUP OPTIMIZATION ON; Via SQL &#8211; e.g. VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(&#8216;BACKUP OPTIMIZATION&#8217;,&#8217;ON&#8217;); There are several scenarios when it might be helpful to use the SQL way. I will show 3 of [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/","og_site_name":"dbi Blog","article_published_time":"2016-10-11T07:29:57+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\/oracle-12c-managing-rman-persistent-settings-via-sql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12c &#8211; Managing RMAN persistent settings via SQL","datePublished":"2016-10-11T07:29:57+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/"},"wordCount":355,"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-managing-rman-persistent-settings-via-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/","name":"Oracle 12c - Managing RMAN persistent settings via SQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-10-11T07:29:57+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-managing-rman-persistent-settings-via-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12c &#8211; Managing RMAN persistent settings via SQL"}]},{"@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\/9081","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=9081"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9081\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9081"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9081"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9081"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9081"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}