How to multiplex redo log members and groups on ASM with sqlplus and EM

Tweet

This post will show you how to add new redo log members and groups when the storage engine is ASM; they will be added using both sqlplus console and Enterprise Manager Console.
The following picture is a screenshot taken from the Enterprise Manager after I finished to configure a default Oracle RAC installation formed by two nodes. As you can see there are four groups with just one redo log member.

From sqlplus console you can see how this redo log groups and redo log member are accessible by both nodes.
If you want to see which redo log member is accessible by one node, simply run the following query:
SQL> select * from v$logfile order by GROUP#;

Otherwise if you want to see which redo log member is accessible by both nodes, run this other query:
SQL> select * from gv$logfile order by INST_ID, GROUP#;


Now we will add a new redo log group from sqlplus console, using the following command:
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 SIZE 51200K;


As you can see from the EM console the new redo log group is created and magically two redo log members are added to this redo log group. Now let's create another redo log group, the sixth, but using the EM console. Click on the CREATE button on the right.


Complete the group name, the file size and choose to which thread assign this new redo log group. Then click on SHOW SQL button to see what command will be executed.

As you can see the command is the same we executed previously from sqlplus console. Click RETURN button. On the previuos screen you can simply click OK button to execute the command.


Again the EM console will show our new configuration and the new redo log group will be formed by two redo log members.

Now we want to add another redo log member to redo log groups 1, 2, 3 and 4. For 1, 2 and 3 I will add them by sqlplus console; the fourth will be added by EM console.

From sqlplus console I simply used the following commands as you can see from picture. My storage engine is based on ASM (Automatic Storage Management), so the syntax is very easy.

ALTER DATABASE ADD LOGFILE MEMBER '+DATA_RAC3' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA_RAC3' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA_RAC3' TO GROUP 3;

From EM console, select redo log group number 4 and then click EDIT button.

The following screenshot will be showed. To add a new redo log member, click ADD button.

Use default suggestion and the click on CONTINUE.

The following screenshot will be showed. Now select SHOW SQL button to see the command line.

As you can see it's the same we executed by sqlplus console. Click RETURN.

Click APPLY to add the new redo log member to redo log group number 4.

Finally... every redo log group is formed now by two redo log members.