|
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.
| 
