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:
