Oracle Redo log Overview

Redo log:

Record all changes made a data

It’s provide the recovery mechanism

Redo log can be organized into groups

Minimum two groups are required

How Oracle write the Redo log: 

LGWR write to redo log file in cycle method

When the current redo log file fills LGWR writing next available redo log file

Whenever a transaction was committed LGWR writes the transaction redo records from the redo log buffer

ARCHIVELOG MODE

If you have enabled archiving then the database cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived the file.

NOARCHIVELOG MODE

If archiving is disabled then when the last redo log file is full, LGWR continues by overwriting the first available active file.

Redo log files are following stage:

Current

Active

Inactive

Current:

The redo log file that LGWR is actively writing to is called the current redo log file

Active:

Redo log files that are required for instance recovery are called active redo log files

Inactive:

Redo log files that are no longer required for instance recovery are called inactive redo log files.

How to view redo log status:

SQL> select GROUP#,MEMBERS,STATUS from v$log;

How to create Redo Log group:

The create redo log file using SQL statement for ALTER DATABASE with ADD LOGFILE

SQL> Alter database Add logfile ('/u03/apps/oracle/oradata/PROD/redo04.log')  size 100M;

SQL> Alter database Add logfile group 5 ('/u03/apps/oracle/oradata/PROD/redo05.log') size 50M;

How to create Redo Log Members:

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u03/apps/oracle/oradata/PROD/redo008.log' TO GROUP 4;

NOTE:

Manually not able t o resize the redo log files.

How to rename and relocating the Redo log files:

It’s using ALTER DATABASE RENAME FILE command

Shut down the database.

Check redoes log locations

Copy the online redo log files to the new location.

Place the database in MOUNT mode.

Execute the below command

ALTER DATABASE RENAME FILE

’/u01/data/log2a.rdo’

TO

’/u02/data/log1c.rdo’;

Open database for normal option

SQL> ALTER DATABASE OPEN;

Execute the following query to rename the redo logs

SQL> alter database rename file '/oracle/CLONE/db/data2/log03b.dbf' to '/oracle/CLONE/db/data/log03b.dbf';

How to drop Redo log Groups:

Drop the redo log group must be use SQL statement ALTER DATABASE with DROP LOGFILE

NOTE:

Not able to drop the current and active log groups this groups are need to the recovery purpose

If you need to drop the current or Active group, first force a log switch to occur

Pre Check:

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

Now I will drop the first redo log group

SQL> Alter Database Drop Logfile Group 1;

How to Drop the Redo log Members:

SQL> Alter Database Drop Logfile Member '/u03/apps/oracle/oradata/PROD/redo02.log';

Forcing Log switches:

The log switch occurs when LGWR stops writing to one redo log group and starts writing to another

By default the log switch occurs automatically when the current redo log file group fills

You can force a log switch to make the currently active group inactive and available for redo log maintenance operations

The following SQL statement forces a log switch

SQL> alter system switch logfile;

Clearing a Redo Log File:

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database

Example:

SQL>ALTER DATABASE CLEAR LOGFILE GROUP  2;

Redo Log Data Dictionary Views:

V$LOG:

Displays the redo log file information from the control file

SQL>SELECT * FROM V$LOG;

V$LOGFILE:

Identifies redo log groups and members and member status

SQL>SELECT * FROM V$LOGFILE;

V$LOG_HISTORY:

Contains log history information