Working with Redo Logs

Script to View Online RedoLog Information :

SQL> SELECT * FROM V$LOGFILE;

OR

SQL > SELECT a.group#

,a.thread# ,a.status grp_status

,b.member member ,b.status mem_status

,a.bytes/1024/1024 mbytes FROM v$log a, v$logfile b

WHERE a.group# = b.group#

ORDER BY a.group#, b.member;

Status for Online Redo Log of V$LOG View  :

CURRENT The log group is currently being written to by the log writer.

ACTIVE The log group is required for crash recovery and may or may not have been archived.

CLEARING The log group is being cleared out by an ALTER DATABASE CLEAR LOGFILE command.

CLEARING_CURRENT The current log group is being cleared of a closed thread.

INACTIVE The log group isn’t required for crash recovery and may or may not have been archived.

UNUSED The log group has never been written to; it was recently created.

Status of Online Redo Log File Members in V$LOGFILE View

INVALID The log file member is inaccessible or has been recently created.

DELETED The log file member is no longer in use.

STALE The log file member’s contents aren’t complete.

NULL The log file member is being used by the database.

How to Rename the Redolog files:

1 . Shut down the DB

2. Rename the redolog files on OS level (using ‘mv’ command in UNIX)

3. Startup mount

SQL> alter database rename file ‘<loc with prev file name> to <loc with current file name>;

Do the same for all renamed redolog files

SQL> alter database open;

How to Multiplex the redolog files:

To protect against a failure involving the redo log itself, Oracle Database allows a multiplexed redo log, meaning that two or more identical copies of the redo log can be automatically maintained in separate locations. For the most benefit, these locations should be on separate disks. Even if all copies of the redo log are on the same disk, however, the redundancy can help protect against I/O errors, file corruption, and so on. When redo log files are multiplexed, LGWR concurrently writes the same redo log information to multiple identical redo log files, thereby eliminating a single point of redo log failure.

Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. Each redo log group is defined by a number, such as group 1, group 2, and so on.

FOR EXAMPLE:

In group 1 all memebers will have same type of data, if one member is corrupted then another member is user for recovery

SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP# = ‘1’;

SQL>ALTER DATABASE ADD LOGFILE MEMBER ‘<>loc with redolog file name> TO GROUP <group_num>;