Oracle REDO Management (LOG File)

• The database maintains online redo log files to protect against data loss.
• Specifically, after an instance failure, the online redo log files enable Oracle Database to recover committed data that it has not yet written to the data files.
• Server processes write every transaction synchronously to the redo log buffer, which the LGWR process then writes to the online redo log.
• Oracle Database uses the online redo log only for recovery. Redo log files are a useful source of historical information about database activity.

When will LGWR write to Redo Log File:

• On transaction commit (COMMIT).
• Every 3 seconds (timeout).
• When the Redo Log Buffer is one-third full.
• Before DBWR writes dirty buffers to data files.
• During a log switch.
• During a checkpoint.
• When the Redo Log Buffer is full.
• When a background process requires redo data.

The redo records have all relevant metadata for the change, including the following:

• SCN and time stamp of the change
• Transaction ID of the transaction that generated the change.
• SCN and time stamp when the transaction committed (if it committed)
• Type of operation that made the change.
• Name and type of the modified data segment

It Can be organized into groups. At least minimum 2 groups required for a single database.

• Logical – Group
•  Physical- Member (Minimum 1 to Maximum 8 can set for a single DB)

Log File – Logical

Logical unit of logfile know as Group. There are minimum 2 group to maximum 32 group can set for a single database.

Log File Group: status

Group Status	Description
Unused	Its newly added redolog group.
Current	Its active to redogroup lgwr enabled.
Active	Its active to arwr enabled to offline backup
Clearing	Its recreated after clearing the group.
Clearing_current	Its being clearing.
Inactive	The group already archived, ready to reuse.

select sequence#, group#,members,status from v$log;

select member from v$logfile where group#=1;

alter database add logfile group 4;

alter database add logfile group 4 'u01/app/oracle/oradata/PEARL/onlinelog/log4a.log' size 200m;

ALTER DATABASE DROP LOGFILE GROUP 4;

Clearing a Log File Group:

alter database clear logfile group 1;

alter database clear unarchived logfile group 4;

alter database clear logfile group 4;

ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/PEARL/onlinelog/log4a.log' TO GROUP 4;

select member from v$logfile where group#=4;

Switch the Log File:

alter system switch logfile;

ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/PEARL/onlinelog/log4b.log';

select a.group#, a.status, b.status from v$log a, v$logfile b where a.group#=b.group#;

Log File Member Status:

Member Status	Description
Invalid	Unable to access, must drop and recreate it.
Stale	File contents incomplete.
Deleted	No longer use, drop in OS level.
Blank	The file is use now.

select group#,status from v$log;

select member from v$logfile where group#=4;

Relocation And Rename:

shut immediate

Goto Redo log file location

cp -rf log4a.log redo10.log   # no Space use move command

startup mount

select member from v$logfile;

alter database rename file '/u01/app/oracle/oradata/PEARL/onlinelog/log4a.log' to '/u01/app/oracle/oradata/PEARL/onlinelogredo10.log';

select member from v$logfile;

alter database open;

select group#,status from v$log;

Logfile Views:

v$log, v$logfile, v$loghistory

Restriction to drop groups

• An Instance required at least 2 group of online redologfile.
• An active or current group cannot be dropped.
• When an online redo logfile group is dropped, the os file not dropped.

Restriction to drop Members

• Can’t drop the last valid member of the group.
• Can’t drop the current log file member.
• Can’t drop the unarchived group member.
• The OMF features group only allowed to drop a member physically.