|
How to add and drop
online redo log members and groups?
Redo log Files: The Oracle server maintains online
redo log files to minimize the loss of data in the database. The redo log
files record all changes made to data in the database buffer cache with
some exceptions; for example, in the case of direct writes.
Redo log files are used in a situation such as an instance failure to
recover committed data that has not been written to the data files. The
redo log files are only used for recovery.
What are online Redo Log Groups?
A set of identical copies of online redo log files is called an online
redo log group.
The background process LGWR concurrently writes the
same information to all online redo log files in a group.
The Oracle
server needs a minimum of two online redo log file groups for the normal
operation of a database. Oracle suggests keeping three groups.
What are online Redo Log Members?
Each online redo log file in a group is called a member.
Each
member in a group has identical log sequence number and the same size. The
log sequence number is assigned each time the server starts writing to a
log group to identify each redo log file uniquely. The current log
sequence number is stored in the control file and in the header of all
data files.
How to obtain Information about Groups and Members?
The following query returns information about the online redo log file
from the control file:
SQL> select group#, sequence#, bytes, members, status from v$log;
| GROUP# |
SEQUENCE# |
BYTES |
MEMBERS |
STATUS |
|
1 |
215 |
104857600 |
1 |
INACTIVE |
|
2 |
216 |
104857600 |
1 |
CURRENT |
|
3 |
214 |
104857600 |
1 |
INACTIVE |
3 rows selected.
The following query returns information about all members of a group:
SVRMGR> select * from v$logfile;
GROUP# STATUS TYPE MEMBER ---------- ------- -------
---------------------------------------- 3 ONLINE
/u02/ORADATA/VTEST/REDO03.LOG 2 ONLINE /u03/ORADATA/VTEST/REDO02.LOG 1
ONLINE /u03/ORADATA/VTEST/REDO01.LOG
3 rows selected.
Adding Online Redo Log Groups
In some cases you might need to create additional log file groups. For
example, adding groups can solve availability problems. To create a new
group of online redo log files use the following command:
ALTER DATABASE ADD
LOGFILE
('/DISK1/log3a.rdo','/DISK2/log3b.rdo') size 1M;
Adding Online Redo Log Members
You can add new member to an existing redo log file group using the
following command:
ALTER DATABASE ADD LOGFILE MEMBER
/DISK2/log1b.rdo' TO GROUP 1,
'/DISK2/log2b.rdo' TO GROUP 2;
Dropping Online Redo Log Groups
To drop a group of online redo log files use the following command:
ALTER DATABASE DROP LOGFILE GROUP 3;
Dropping Online Redo Log Members
To drop a member of an online redo log group use the following command:
ALTER DATABASE DROP LOGFILE MEMBER
'/DISK2/log2b.dbf';
Please note when dropping redo log groups and redo log
files, there must be at least two redo log groups and each redo log group
must have at least one log member.
| 
