DB_CREATE_ONLINE_LOG_DEST_n is a set of server parameters which specify the default destination where new Oracle-managed redo log files (OMF) should be placed.
More precisely, DB_CREATE_ONLINE_LOG_DEST_n contains 5 related parameters. They are meant to multiplex a new redo log group into multiple members named by Oracle.
- DB_CREATE_ONLINE_LOG_DEST_1
- DB_CREATE_ONLINE_LOG_DEST_2
- DB_CREATE_ONLINE_LOG_DEST_3
- DB_CREATE_ONLINE_LOG_DEST_4
- DB_CREATE_ONLINE_LOG_DEST_5
Usually, we set DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2 to multiplex 2 members for each log group, that's pretty enough for securing the redo.
In other words, if you want only one member each group, then you can specify only the first parameter with valid destination, then there's no multiplexing.
In this post, we'll talk about the following topics.
How to Set DB_CREATE_ONLINE_LOG_DEST_n
In this case, we'd like to have 2 members for each group, so we set both DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2.
Since the server parameter can be changed online, so we can set it at system-level or session-level.
Alter System Set DB_CREATE_ONLINE_LOG_DEST_n
RAC DB
For RAC databases, we should only specify the diskgroup name for the value, ASM knows where to categorize new data files.
SQL> alter system set db_create_online_log_dest_1='+DATA1' sid='*' scope=both;
System altered.
SQL> alter system set db_create_online_log_dest_2='+DATA2' sid='*' scope=both;
System altered.
Single Instance DB
In contrast, we should specify the base directory for single-instance databases, because Oracle doesn't categorize new files in file system.
SQL> alter system set db_create_online_log_dest_1='/oradata1/ORCL/ONLINE' scope=both;
System altered.
SQL> alter system set db_create_online_log_dest_2='/oradata2/ORCL/ONLINE' scope=both;
System altered.
It just follows the base directory to create data files.
Alter Session Set DB_CREATE_ONLINE_LOG_DEST_n
For changing the value temporarily, you can set it in your session.
SQL> alter session set db_create_online_log_dest_1='+DATA1';
Session altered.
SQL> alter session set db_create_online_log_dest_2='+DATA2';
Session altered.
This could be useful when we want to allocate new logfiles to destinations other than the default.
How to Use DB_CREATE_ONLINE_LOG_DEST_n
To use DB_CREATE_ONLINE_LOG_DEST_n properly after setting new places, we can create a new logfile group for sure.
Alter Database Add Logfile
Adding new log groups to ease the burden of log switching is a very common operation for a database in use.
SQL> alter database add logfile thread 1 group 5 size 1g;
Database altered.
SQL> alter database add logfile thread 2 group 6 size 1g;
Database altered.
In which, group 5 is for the first instance (thread 1), whereas group 6 is for the second instance (thread 2).
With DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2 specified, we don't have to specify the absolute path of files.
Let's see the result.
SQL> set linesize 100;
SQL> column status format a10;
SQL> column member format a50;
SQL> select a.group#, a.thread#, a.status, a.bytes/1024/1024 "Size (MB)", b.member from v$log a, v$logfile b where a.group# = b.group# and group# in (5,6) order by 1,2,3,4;
GROUP# THREAD# STATUS Size (MB) MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
5 1 UNUSED 1024 +DATA1/ORCL/ONLINELOG/group_5.327.1232015933
5 1 UNUSED 1024 +DATA2/ORCL/ONLINELOG/group_5.326.1232015927
6 2 UNUSED 1024 +DATA1/ORCL/ONLINELOG/group_6.328.1232015979
6 2 UNUSED 1024 +DATA2/ORCL/ONLINELOG/group_6.329.1232015985
Similarly, you can move current redo logfiles to the new places like this.