ALTER DATABASE ADD LOGFILE clause has two different functions, one is to add a new member to an existing group, the other is to create a new logfile group.
Add a New Logfile Member
Let's see some wrong syntaxes.
SQL> alter database add logfile '+DATA/ORCLCDB/redo01c.log' to group 1;
alter database add logfile '+DATA/ORCLCDB/redo01c.log' to group 1
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
As we can see, SQL parser thought we were trying to create a new logfile group. This is because we missed MEMBER keyword.
SQL> alter database add logfile member ('+DATA/ORCLCDB/redo01c.log','+DATA/ORCLCDB/redo01d.log') to group 1;
alter database add logfile member ('+DATA/ORCLCDB/redo01c.log','+DATA/ORCLCDB/redo01d.log') to group 1
*
ERROR at line 1:
ORA-02236: invalid file name
We have specified MEMBER, but SQL parser recognized parentheses as an invalid character. We should remove parentheses.
SQL> alter database add logfile member '+DATA/ORCLCDB/redo01c.log','+DATA/ORCLCDB/redo01d.log' to group 1;
Database altered.
Multiple logfiles are allowable to be added in one statement as long as you use comma to delimit them.
Create a New Logfile Group
Let's see some incorrect syntaxes of adding logfile groups to a RAC database.
SQL> alter database add logfile ('+DATA/ORCLCDB/redo05a.log','+DATA/ORCLCDB/redo05b.log') thread 1 group 5 size 10m;
alter database add logfile ('+DATA/ORCLCDB/redo05a.log','+DATA/ORCLCDB/redo05b.log') thread 1 group 5 size 10m
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
As you can see, we put THREAD and GROUP after LOGFILE, it failed with ORA-00933.
SQL> alter database add logfile group 5 thread 1 ('+DATA/ORCLCDB/redo05a.log','+DATA/ORCLCDB/redo05b.log') size 10m;
alter database add logfile group 5 thread 1 ('+DATA/ORCLCDB/redo05a.log','+DATA/ORCLCDB/redo05b.log') size 10m
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
As you can see, we specify THREAD after GROUP, it failed with ORA-00933, too.
Eventually, we should specify THREAD then GROUP, logfile member follows.
SQL> alter database add logfile thread 1 group 5 ('+DATA/ORCLCDB/redo05a.log','+DATA/ORCLCDB/redo05b.log') size 10m;
Database altered.
SQL> alter database add logfile thread 2 group 6 ('+DATA/ORCLCDB/redo06a.log','+DATA/ORCLCDB/redo06b.log') size 10m;
Database altered.
Please note that, GROUP clause is optional, if you omit it, the database provides the next group number automatically.
Create a Standby Redo Log
Let's see some incorrect syntaxes of adding a standby redo log to a RAC database.
SQL> alter database add logfile standby thread 1 group 101 '+DATA/ORCLCDB/redo101a.log' size 2048m;
alter database add logfile standby thread 1 group 101 '+DATA/ORCLCDB/redo101a.log' size 2048m
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
We cannot use ALTER DATABASE ADD LOGFILE clause any longer, instead, we should use ALTER DATABASE ADD STANDBY LOGFILE clause.
SQL> alter database add standby logfile thread 1 group 101 '+DATA/ORCLCDB/redo101a.log' size 2048m;
Database altered.
We should put STANDBY keyword before LOGFILE.