How many control files should I have

oracle-11g-r2oracle-12coracle-asmoracle-database-appliance

On the Oracle Database Appliance, the default deployment only gives you a single control file.

I find this a little puzzling. The single control file causes a policy violation in the automatically configured Enterprise Manager DB Console and Oracle's recommendation is still, as far as I can tell, that you should always have at least two control files on separate drives and file systems. Personally, I've always had three copies, just to be on the safe side.

The ODA is configured with ASM and does have a good bit of storage redundancy using triple mirrored drives. Is it OK to run with a single control file in this configuration?

Adding a second control file to the same disk group might not make much sense, would multiplexing the control files to the SSD disk group or perhaps the OS drives of each node make more sense?

Best Answer

KISS principle: Do you have the storage that itself offers you the required availability level (i.e. disk array)? And you do have RMAN backups with a recovery catalog (you need it anyway, for DUPLICATE), don't you? If yes and yes, my version is: no secondary control file at all, one member per each redo group, as there are no secondary datafiles (quite obviously, even for system01.dbf) or no secondary archivelogs.

Otherwise your database is unnecessarily doing stuff that belongs on the storage level. I would just leave storage things on the storage level - the mirror is done there, and it is done effectively. Or, if you want cross-site mirror, do it on ASM level. Database is occupied with database-related processing. The software mirroring on database level only makes sense if you use bare hard disks.

Most people conservatively stick to having three control files, though. Probably Oracle support would also recommend you to use three.

Not only I've never practically benefited from having a second or a third control file; I'm unable to think of a scenario when secondary copy would be justified (i.e. it provides some benefit for the lack of simplicity). Logical corruption in control file would be propagated to a second control file, just as in case of hardware mirroring. Physical corruption wouldn't be propagated in either case. If storage fails, you lose redo or data - you need to restore via RMAN anyway. You lose control file, you can restore it with RESTORE CONTROLFILE, which only costs extra minute at most. If you've lost redo, the recovery is incomplete. If you didn't lost redo (but lost all control files), the recovery is complete de facto, but Oracle insists on OPEN RESETLOGS (loss-less in such case).