Manage Redo Log Groups and Members

Below are the examples how to add redo log groups, how to add redo log members,how to drop redo log group,how to drop redo log member,how to clear redo log

Adding Online Redo Log Groups
ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/orcl/data/redo05a.log','/u01/orcl/data/redo05b.log') SIZE 500M;


Adding Online Redo Log Members
ALTER DATABASE ADD LOGFILE MEMBER
'/u01/orcl/data/redo05c.log' TO GROUP 1,
'/u01/orcl/data/redo05c.log' TO GROUP 2,
'/u01/orcl/data/redo05c.log' TO GROUP 3;


Dropping Online Redo Log Groups
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.

ALTER DATABASE DROP LOGFILE GROUP 5;


Dropping Online Redo Log Members
Restrictions
• If the member you want to drop is the last valid member of the group, you cannot drop
that member.
• If the group is current, you must force a log file switch before you can drop the
member.
• If the database is running in ARCHIVELOG mode and the log file group to which the
member belongs is not archived, then the member cannot be dropped.
• When an online redo log member is dropped, the operating system file is not deleted.

ALTER DATABASE DROP LOGFILE MEMBER '/u01/orcl/data/redo05c.log';



Clearing online redo log files:
Restrictions
You can clear an online redo log file whether it is archived or not. However, when it is not
archived, you must include the keyword UNARCHIVED. This makes backups unusable if the
online redo log file is needed for recovery.

ALTER DATABASE CLEAR LOGFILE '/u01/orcl/data/redo05c.log';


Information about group and members can be obtained by querying the data dictionary.
• V$LOG
• V$LOGFILE