How to Use Online Redo Log Files in Oracle using VB.NET

In this article, we will learn online redo log files in Oracle.
  • 2352

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:

log1.png

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:

log2.png

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:

log3.png

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:

log4.png

Categories

More Articles

© 2018 DotNetHeaven. All rights reserved.