Even on the latest Oracle Engineered Systems like ODA X6-2S\/M there’s only one single controlfile created by default. Despite the storage redundancy there’s always a risk of someone accidentally deleting a file or some logical corruption of the file.
\nTherefore, we always multiplex the controlfiles and online redo logs. But, what is the easiest way of adding a second controlfile to a database using OMF on a file system?<\/p>\n
I think it’s the following procedure. Although, I’m gladly proven wrong with a even more elegant solution.<\/p>\n
The command output has been stripped to the relevant information.<\/p>\n
\r\n$ . oraenv\r\nORACLE_SID = [DUMMY] ? DEV1\r\n\r\n$ rman target \/\r\n\r\n\r\n-- see, there is only one online log destination\r\nRMAN> select name, value from v$parameter where name like 'db_create_online_log_dest_%';\r\n\r\nNAME VALUE\r\n-------------------------------------- --------------------------------------\r\ndb_create_online_log_dest_1 \/u03\/app\/oracle\/redo\/\r\ndb_create_online_log_dest_2\r\ndb_create_online_log_dest_3\r\ndb_create_online_log_dest_4\r\ndb_create_online_log_dest_5\r\n\r\n\r\n-- set a second online log destination\r\nRMAN> alter system set db_create_online_log_dest_2 = '\/u02\/app\/oracle\/oradata\/DEV1' scope=both;\r\n\r\n\r\n-- get the current controlfile path for later reference in commands\r\nRMAN> select value from v$parameter where name = 'control_files';\r\n\r\nVALUE\r\n--------------------------------------------------------------------------------\r\n\/u03\/app\/oracle\/redo\/DEV1\/controlfile\/o1_mf_cwxjo46k_.ctl\r\n\r\n\r\n-- this will make RMAN "restore" command create new controlfiles in all online log destinations\r\nRMAN> alter system reset control_files;\r\n\r\n\r\n-- restart instance so parameter changes will take effect\r\nRMAN> shutdown immediate\r\n\r\n\r\n-- only start in NOMOUNT as to be able to restore controlfile\r\nRMAN> startup nomount\r\n\r\n\r\n-- this will create two new OMF controlfiles based on existing one\r\n-- remember, we have reset control_files parameter\r\nRMAN> restore controlfile from '\/u03\/app\/oracle\/redo\/DEV1\/controlfile\/o1_mf_cwxjo46k_.ctl';\r\n...\r\nchannel ORA_DISK_1: copied control file copy\r\noutput file name=\/u03\/app\/oracle\/redo\/DEV1\/controlfile\/o1_mf_cxf4q747_.ctl\r\noutput file name=\/u02\/app\/oracle\/oradata\/DEV1\/DEV1\/controlfile\/o1_mf_cxf4q74v_.ctl\r\nFinished restore at 12-SEP-16\r\n\r\n\r\n-- use the two newly created controlfiles at next startup\r\nRMAN> alter system set control_files = '\/u03\/app\/oracle\/redo\/DEV1\/controlfile\/o1_mf_cxf4q747_.ctl','\/u02\/app\/oracle\/oradata\/DEV1\/DEV1\/controlfile\/o1_mf_cxf4q74v_.ctl' scope=spfile;\r\n\r\n\r\n-- the old controlfile is not needed anymore\r\nRMAN> host "rm \/u03\/app\/oracle\/redo\/DEV1\/controlfile\/o1_mf_cwxjo46k_.ctl";\r\n\r\nRMAN> startup force\r\n<\/pre>\nSince I’m using Oracle 12c RMAN is capable of running SQL just like this…no need to change between SQL*Plus and RMAN anymore.<\/p>\n
(Please don’t debate the file system structure, it’s from an engineered system. I think it’s horrible!)<\/p>\n","protected":false},"excerpt":{"rendered":"
Even on the latest Oracle Engineered Systems like ODA X6-2S\/M there’s only one single controlfile created by default. Despite the storage redundancy there’s always a risk of someone accidentally deleting a file or some logical corruption of the file. Therefore, we always multiplex the controlfiles and online redo logs. But, what is the easiest way […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,10],"tags":[],"class_list":["post-139","post","type-post","status-publish","format-standard","hentry","category-general","category-rman"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/139","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=139"}],"version-history":[{"count":10,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/139\/revisions"}],"predecessor-version":[{"id":149,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/139\/revisions\/149"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=139"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=139"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=139"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}