Skip to content

Alter Database Add Logfile Correctly

  • by

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.

Leave a Reply

Your email address will not be published.