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 of adding a second controlfile to a database using OMF on a file system?
I think it’s the following procedure. Although, I’m gladly proven wrong with a even more elegant solution.
The command output has been stripped to the relevant information.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | $ . oraenvORACLE_SID = [DUMMY] ? DEV1$ rman target /-- see, there is only one online log destinationRMAN> select name, value from v$parameter where name like 'db_create_online_log_dest_%';NAME VALUE-------------------------------------- --------------------------------------db_create_online_log_dest_1 /u03/app/oracle/redo/db_create_online_log_dest_2db_create_online_log_dest_3db_create_online_log_dest_4db_create_online_log_dest_5-- set a second online log destinationRMAN> alter system set db_create_online_log_dest_2 = '/u02/app/oracle/oradata/DEV1' scope=both;-- get the current controlfile path for later reference in commandsRMAN> select value from v$parameter where name = 'control_files';VALUE--------------------------------------------------------------------------------/u03/app/oracle/redo/DEV1/controlfile/o1_mf_cwxjo46k_.ctl-- this will make RMAN "restore" command create new controlfiles in all online log destinationsRMAN> alter system reset control_files;-- restart instance so parameter changes will take effectRMAN> shutdown immediate-- only start in NOMOUNT as to be able to restore controlfileRMAN> startup nomount-- this will create two new OMF controlfiles based on existing one-- remember, we have reset control_files parameterRMAN> restore controlfile from '/u03/app/oracle/redo/DEV1/controlfile/o1_mf_cwxjo46k_.ctl';...channel ORA_DISK_1: copied control file copyoutput file name=/u03/app/oracle/redo/DEV1/controlfile/o1_mf_cxf4q747_.ctloutput file name=/u02/app/oracle/oradata/DEV1/DEV1/controlfile/o1_mf_cxf4q74v_.ctlFinished restore at 12-SEP-16-- use the two newly created controlfiles at next startupRMAN> 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;-- the old controlfile is not needed anymoreRMAN> host "rm /u03/app/oracle/redo/DEV1/controlfile/o1_mf_cwxjo46k_.ctl";RMAN> startup force |
Since I’m using Oracle 12c RMAN is capable of running SQL just like this…no need to change between SQL*Plus and RMAN anymore.
(Please don’t debate the file system structure, it’s from an engineered system. I think it’s horrible!)