[Oracle DataBase Server 12.1 Installation on Oracle Linux 6.7]: Multiplexing Redologs

$ mkdir -p /u02/oracle/oradata/12.1/${ORACLE_SID}/REDOLOGS
$ mkdir -p /u03/oracle/oradata/12.1/${ORACLE_SID}/REDOLOGS



$ sqlplus / as sysdba

A set of commands to make it easier to display query results on the screen.

SQL> set linesize 250;
SQL> set pagesize 0;
SQL> col  GROUP# format 99;
SQL> col  MEMBER format a50;
SQL> col  STATUS format a10;
SQL> col  MB format 999;



SQL> select a.group#, member, a.status, bytes/1024/1024 as "MB"
from v$log a, v$logfile b
where a.group# = b.group#
order by 1;



1 /u02/oracle/oradata/12.1/orcl12/redo01.log	  INACTIVE     50
2 /u02/oracle/oradata/12.1/orcl12/redo02.log	  INACTIVE     50
3 /u02/oracle/oradata/12.1/orcl12/redo03.log	  CURRENT      50

Only files of an inactive group can be deleted. Groups can be switched, which will be shown below.
Delete files of the INACTIVE group

  1. Need to recreate group 1 and its files.

Delete files of group 1

SQL> alter database drop logfile group 1;

SQL> quit



$ rm /u02/oracle/oradata/12.1/orcl12/redo01.log



$ sqlplus / as sysdba

Add a new group, list the files of the new group and specify their size.

SQL> alter database add logfile group 1 ('/u02/oracle/oradata/12.1/orcl12/REDOLOGS/redo01.log' , '/u03/oracle/oradata/12.1/orcl12/REDOLOGS/redo01.log') size 100M;


  1. Need to recreate group 2 and its files.
    Delete files of group 2

    SQL> alter database drop logfile group 2;

    SQL> quit


$ rm /u02/oracle/oradata/12.1/orcl12/redo02.log



$ sqlplus / as sysdba



SQL> alter database add logfile group 2 ('/u02/oracle/oradata/12.1/orcl12/REDOLOGS/redo02.log' , '/u03/oracle/oradata/12.1/orcl12/REDOLOGS/redo02.log') size 100M;
  1. Need to recreate group 3 and its files.
    Since the group is active, you need to switch to the next group of files, making group 2 INACTIVE.

To switch, simply run the commands:

SQL> alter system checkpoint;
SQL> alter system switch logfile;

Delete files of group 3

SQL> alter database drop logfile group 3;

SQL> quit



$ rm /u02/oracle/oradata/12.1/orcl12/redo03.log



$ sqlplus / as sysdba



SQL> alter database add logfile group 3 ('/u02/oracle/oradata/12.1/orcl12/REDOLOGS/redo03.log' , '/u03/oracle/oradata/12.1/orcl12/REDOLOGS/redo03.log') size 100M;



SQL> set linesize 250;
SQL> set pagesize 0;
SQL> col  GROUP# format 99;
SQL> col  MEMBER format a55;
SQL> col  STATUS format a10;
SQL> col  MB format 999;



SQL> select a.group#, member, a.status, bytes/1024/1024 as "MB"
from v$log a, v$logfile b
where a.group# = b.group#
order by 1,2;



1 /u02/oracle/oradata/12.1/orcl12/REDOLOGS/redo01.log     CURRENT	   100
1 /u03/oracle/oradata/12.1/orcl12/REDOLOGS/redo01.log     CURRENT	   100
2 /u02/oracle/oradata/12.1/orcl12/REDOLOGS/redo02.log     UNUSED	   100
2 /u03/oracle/oradata/12.1/orcl12/REDOLOGS/redo02.log     UNUSED	   100
3 /u02/oracle/oradata/12.1/orcl12/REDOLOGS/redo03.log     UNUSED	   100
3 /u03/oracle/oradata/12.1/orcl12/REDOLOGS/redo03.log     UNUSED	   100

6 rows selected.



SQL> quit