Redo log Files
The redo log files are only used for recovery. The Oracle server maintains online redo log files to minimize the loss of data in the database. Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log files record all changes made to data in the database buffer cache with some exceptions. Every instance of an Oracle primary database and logical standby database has an online redo log to protect the database in case of an instance failure. Physical standby databases do not use an online redo log, because physical standby databases are not opened for read/write I/O. Changes are not made to the physical standby database and new redo data is not generated.
View information of log files
Example: This example through we can get group number, sequence, bytes, members and status from the control file.
SQL> select group#, sequence#, bytes, members, status from v$log;
SQL> select * from v$logfile;
Output:
Example: This example through we can get information of log file history.
SQL> SELECT thread#, first_change#,
2 TO_CHAR(first_time,'MM-DD-YY HH12:MIPM'),
3 next_change#
4 FROM v$log_history;
Output:
Online Redo Log Groups in Oracle: Online redo log group is a set of identical copies of online redo log files. The background process LGWR concurrently writes the same information to all online redo log files in a group. The Oracle server needs a minimum of two online redo log file groups for the normal operation of a database.
Create New Online Redo Log Group
In some cases we might need to create additional log file groups. For example, adding groups can solve availability problems. To create a new group of online redo log files use the following command.
Example:
SQL> ALTER DATABASE ADD LOGFILE('C:/ORACLE/PRODUCT/10.2.0/ORADATA/SAPDATA/log5.rdo','C:/ORACLE/PRODU
CT/10.2.0/ORADATA/SAPDATA/log6.rdo') size 10M;
Drop Command
To drop a group of online redo log files use the following command.
Example:
ALTER DATABASE DROP LOGFILE GROUP 3;
Output:
Online Redo Log Members
Online redo log members means each online redo log file in a group is called a member. Each member in a group has identical log sequence number and the same size. The log sequence number is assigned each time the server starts writing to a log group to identify each redo log file uniquely. The current log sequence number is stored in the control file and in the header of all data files.
Create Online Redo Log Members
We can add new member to an existing redo log file group using the following command.
Example:
ALTER DATABASE ADD LOGFILE MEMBER
'C:/ORACLE/PRODUCT/10.2.0/ORADATA/SAPDATA/log01.rdo' TO GROUP 1,
'C:/ORACLE/PRODUCT/10.2.0/ORADATA/SAPDATA/log02.rdo' TO GROUP 2;
Drop Command
To drop a member of an online redo log group use the following command:
Example
ALTER DATABASE DROP LOGFILE MEMBER
'C:/ORACLE/PRODUCT/10.2.0/ORADATA/SAPDATA/log01.rdo';
Output:
