Creating Redo Log Groups and Members

Creating Redo Log Groups

SQL>ALTER DATABASE
  ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K;
SQL>ALTER DATABASE 
ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', /oracle/dbs/log2c.rdo')
SIZE 500K;

Creating Redo Log Members

SQL>ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;
SQL>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.

Relocating and Renaming Redo Log Members

Shut down the database.

SQL>SHUTDOWN Immediate

Copy the redo log files to the new location.

Note:

You can execute an operating system command to copy a file (or perform other operating system commands) without exiting SQL*Plus by using the HOST command. Some operating systems allow you to use a character in place of the word HOST. For example, you can use an exclamation point (!) in UNIX.

The following example uses operating system commands (UNIX) to move the redo log members to a new location:

mv /diska/logs/log1a.rdo /diskc/logs/log1c.rdo
mv /diska/logs/log2a.rdo /diskc/logs/log2c.rdo

Startup the database, mount, but do not open it.

SQL>CONNECT / as SYSDBA
SQL>STARTUP MOUNT

Rename the redo log members.

SQL>ALTER DATABASE 
RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';

Open the database

SQL>ALTER DATABASE OPEN; 

Dropping Log Groups

Make sure a redo log group is archived (if archiving is enabled) before dropping it. To see whether this has happened, use the V$LOG view.

SQL>SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

Drop a redo log group

SQL>ALTER DATABASE DROP LOGFILE GROUP 3;

Dropping Redo Log Members

SQL>ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo';

Forcing Log Switches

SQL>ALTER SYSTEM SWITCH LOGFILE;

Clearing a Redo Log File

SQl>ALTER DATABASE CLEAR LOGFILE GROUP 3;

Viewing Redo Log Information

The following views provide information on redo logs.

View	Description
V$LOG	Displays the redo log file information from the control file
V$LOGFILE	Identifies redo log groups and members and member status
V$LOG_HISTORY	Contains log history information