Creating Redo Log Groups and Members

Create all required groups and members of redo log files during database creation. However, there are situations where you might want to create additional groups or members.

Creating Redo Log Groups

To create new redo log groups and members, you must have the ALTER DATABASE system privilege.

To create a new group of redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause.

The following statement adds a new group of redo logs to the database:

 ALTER DATABASE

  ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K;

You can also specify the number that identifies the group using the GROUP clause:

ALTER DATABASE

  ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')

      SIZE 500K;

Using group numbers can make administering redo log groups easier. However, the group number must be between 1 and MAXLOGFILES.

In some cases, it might not be necessary to create a complete group of redo log files. A group could already exist, but not be complete because one or more members of the group were dropped (for example, because of a disk failure). In this case, you can add new members to an existing group.

Creating Redo Log Members

To create new redo log members for an existing group, use the SQL statement ALTER DATABASE with the ADD LOGFILE MEMBER clause.

The following statement adds a new redo log member to redo log group number 2:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;

Notice that filenames must be specified, but sizes need not be. The size of the new members is determined from the size of the existing members of the group.

When using the ALTER DATABASE statement, you can alternatively identify the target group by specifying all of the other members of the group in the TO clause, as shown in the following example:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo'

    TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

Note:

Fully specify the filenames of new log members to indicate where the operating system files should be created. Otherwise, the files will be created in either the default or current directory of the database server, depending upon your operating system. You may also note that the status of the new log member is shown as INVALID. This is normal and it will change to active (blank) when it is first used.