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