Redo Log

MANAGING REDO LOG GROUPS & MEMBERS

• ADDING NEW REDO LOG MEMEBERS

First display the name & location of present redo log members.

SQL>select member from v$logfile;

It is preferable to create all the members of all the group together in a separate location i.e all the first members of suppose group 1, group 2 in one location and all the second members of group 1 and group 2 in another location.

SQL>alter database add logfile member

‘d:\oracle\ora92\oradata\db2\log01b.rdo’ to group 1,

‘d:\oracle\ora92\oradata\db2\log02b.rdo’ to group 2;

• DROPING ONLINE REDO LOG MEMBER

SQL>alter database drop logfile member

‘d:\oracle\ora92\oradata\db2\log3a.rdo’;

Restrictions :

If the member you want to drop is the last valid member of the group, you cannot drop that member.

If a group is current you have to force a log file switch, to drop its member

Command to know whether the group is active or not

SQL>select group#, status from v$log;

Command to force a log file switch

SQL>Alter system switch logfile;

If the database is in archivelog mode and the log file group is not archived yet you cannot drop its member.

When an online redo log member is dropped, the operating system files are not deleted.

• ADDING REDOLOG GROUP

If already 2 groups are their with two members each.

SQL> alter database add logfile group 3

(‘d:\oracle\ora92\oradata\db2\log3a.rdo’,

‘e:\oracle\oradata\db2\log3b.rdo’) size 1m;

• DROPING REDO LOG GROUP

SQL>alter database drop logfile group 3;

Restrictions :

An instance requires at least two groups of online redo log files.

An active or current group cannot be dropped.

When an online redo log group is dropped, the operating system files are not deleted.

• CLEARING ONLINE REDO LOGFILE

Need arises when the redo log file is corrupted, dba can solve the problem by reinitializing these log files , the command is equivalent to adding and droping an online redo log file.

SQL>alter database clear logfile

‘d:\oracle\ora92\oradata\db2\log2a.rdo’;

• RELOCATING AND RENAMING REDO LOG FILES

The location can be changed or files can be renamed by drop the old log files and creating a new file.

Even though one more method is available i.e.

SQL>alter database rename file ……

But for this the database requires to be in mount mode.