ODA 12.1.X.X.X – add a multiplexed control file under ACFS

Since version 12, ODA stores databases on ACFS volumes instead of ASM directly. This slightly changed the way the files are managed and administer. This articles presents how to multiplex your control files on ACFS.

At first database creation, ODA set up 3 ACFS volumes to store all non-CDB databases (CDB are managed on dedicated volumes):

/dev/asm/datastore-386   62G  5.2G   57G   9% /u01/app/oracle/oradata/datastore
/dev/asm/datastore-286   1.7T  871G  836G  52% /u02/app/oracle/oradata/datastore
/dev/asm/datastore-360   2.2T  5.6G  2.2T   1% /u01/app/oracle/fast_recovery_area/datastore

These reside on the ASM disk group:

• /u01 datastore on +REDO
• /u02 on +DATA
• Fast Recovery Area on +RECO

All files stored on that volumes are OMF based and have therefore Oracle generated file names.

By default any new database has the initialisation parameters db_create_file_dest and db_create_online_log_dest_1 configured as following:

SQL> show parameter create

NAME                                       TYPE     VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size            integer     8388608
db_create_file_dest                    string     /u02/app/oracle/oradata/datastore/.ACFS/snaps/DBPROD1
db_create_online_log_dest_1      string     /u01/app/oracle/oradata/datastore/DBPROD1
db_create_online_log_dest_2      string

As consequence all databases are created with a single control file located in the same ACFS volume than the redo logs.

SQL> show parameter control_files
NAME              TYPE     VALUE
---------------- --------- ------------------------------
control_files     string     /u01/app/oracle/oradata/datastore/DBPROD1/DBPROD1/controlfile/o1_mf_bj2r0f4n_.ctl

This volume is based on a triple mirrored ASM disk group, which already safeguard us from most possible issues except of course from user errors 🙄 or logical corruptions. Therefore if you still want to multiplex your control file, you will have manually to go through following steps:

1. Generate a trace file from your control file
2. Edit the trace file to keep only the NORESETLOGS version
3. Configure the db_create_online_log_dest_2 initialisation parameter
4. Reset the control_files parameter
5. Shutdown your database
6. Run the control file creation script

Preparate control file creation script

First of all generate the trace file

SQL> alter database backup controlfile to trace as '/tmp/ctl_DBPROD1.sql';

Database altered.

Then edit the generated file to keep only the NORESETLOGS version. It should look like something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DBPROD1" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/datastore/DBPROD1/DBPROD1/onlinelog/o1_mf_1_bj2xmhs9_.log',
    '/u01/app/oracle/oradata/datastore/DBPROD1/DBPROD1/onlinelog/o1_mf_1_bj2xmjlt_.log'
  ) SIZE 500M BLOCKSIZE 512,
 ...
 ...DATAFILE
  '/u02/app/oracle/oradata/datastore/.ACFS/snaps/DBPROD1/DBPROD1/datafile/o1_mf_system_bj2r0ptp_.dbf',
  '/u02/app/oracle/oradata/datastore/.ACFS/snaps/DBPROD1/DBPROD1/datafile/o1_mf_sysaux_bj2r0t8o_.dbf',
 ...
 ...
 ...
CHARACTER SET WE8ISO8859P15
;
EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('/u01/app/oracle/fast_recovery_area/datastore/DBPROD1/snapcf_DBPROD1.f');

VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO ''/u01/app/oracle/fast_recovery_area/datastore/DBPROD1/snapcf_DBPROD1.f''');

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

Prepare database

Configure the parameter db_create_online_log_dest_2 in order to Oracle to generate a second control file are re-creation

SQL> alter system set db_create_online_log_dest_2='/u01/app/oracle/oradata/datastore/DBPROD1' scope=both;

System altered.

Then remove the parameter control_files from the spfile. If you forget that point, Oracle will ignore the db_create_online_log_dest_X parameter and still re-create only one control file.

SQL> alter system reset control_files scope=spfile;

System altered.

Re-create the control files

At that point the system is ready to re-create the control file and multiplexing it. Simply shutdown the database and run the created SQL script:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> @ctl_DBPROD1.sql
ORACLE instance started.Total System Global Area 1.7180E+10 bytes
Fixed Size            3731384 bytes
Variable Size         2550136904 bytes
Database Buffers     1.4563E+10 bytes
Redo Buffers           63377408 bytesControl file created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery requiredSystem altered.
Database altered.

The database is now OPEN READ WRITE again and has two control files:

SQL> select name from v$controlfile;NAME
-------------------------------------------------------------------------------------
/u01/app/oracle/oradata/datastore/DBPROD1/DBPROD1/controlfile/o1_mf_bj3526gr_.ctl
/u01/app/oracle/oradata/datastore/DBPROD1/DBPROD1/controlfile/o1_mf_bj3526hy_.ctl

Enjoy! 😀