ORA-00357
Tried to add more redo log member to an existing group, but it failed with ORA-00357.
SQL> alter database add logfile member '+DATA/ORCLCDB/redo01g.log' to group 1;
alter database add logfile member '+DATA/ORCLCDB/redo01g.log' to group 1
*
ERROR at line 1:
ORA-00357: too many members specified for log file, the maximum is 5
ORA-00357 means that you can't add more logfiles to the group, because the number of members in that group has already reached the maximum.
According to Database Limits of 19c Database Administrator's Reference for Linux and UNIX System-Based Operating Systems, the upper limit of MAXLOGMEMBERS is 5, no more.
Here I list the database limits in controlfile.
Parameter | Default | Maximum |
---|---|---|
MAXLOGFILES | 16 | 4220 |
MAXLOGMEMBERS | 2 | 5 |
MAXLOGHISTORY | 100 | 65534 |
MAXDATAFILES | 30 | 65534 |
MAXINSTANCES | 1 | 1055 |
Solution
Since MAXLOGMEMBERS is the hard limit to a redo log group, the only solution is to drop some members before adding it. A similar technique can be found in How to move redo log location.
In some databases, multiplexing more redo log members could cause undesirable IO bottlenecks. To balance IO resource and media loss, having 2 members at most in a group is pretty safe in practice. Multiplexing member of a redo log group to 5 is really unnecessary in my opinion.