Correct Ways to Add Logfiles

  • 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 is optional, if you omit it, the database provides a sequential group number automatically.

Leave a Reply

Your email address will not be published. Required fields are marked *