What is redo log thread?

In Oracle Database, a redo log thread refers to a specific instance of redo log processing within the database. Redo logs are crucial components of a database system, responsible for recording changes made to data (such as inserts, updates, and deletes) in a sequential manner. These changes are logged in redo log files to ensure data integrity and facilitate database recovery in the event of system failure.

Redo Log

• Purpose: Redo logs record all changes made to database blocks in the database buffer cache before they are written to the data files. This ensures that changes are durable and recoverable.
• Structure: Redo log files consist of redo log groups, which are further divided into individual redo log members. Redo log groups contain one or more redo log members, which are physical files stored on disk.
• Usage: Redo logs play a crucial role in database recovery, allowing the database to roll forward changes from redo logs during instance recovery or media recovery.

Redo Log Thread

• Definition: A redo log thread represents an individual stream of redo log records within an Oracle Database instance.
• Purpose: Redo log threads provide a mechanism for parallelizing redo log processing in a multi-instance Oracle Database environment.
• Configuration: In a single-instance database, there is typically only one redo log thread. In a Real Application Clusters (RAC) environment, each instance has its own redo log thread, allowing multiple instances to write redo log records concurrently.
• Failover: Redo log threads facilitate high availability and failover capabilities in RAC environments. If one instance fails, other instances can continue processing redo log records independently.

Redo Log Thread Switching

• Definition: Redo log thread switching refers to the process of transitioning redo log processing from one thread to another within an Oracle Database instance.
• Reasons for Switching: Redo log thread switching may occur during operations such as instance startup, shutdown, or failover in a RAC environment.
• Impact: Redo log thread switching is a critical operation that must be managed carefully to ensure data consistency and integrity. It involves coordinating redo log processing across multiple threads to maintain transactional consistency.

Each instance has it’s own personal set of redo and each redo thread is made up of at least two groups that have one or more members (files) .

Two instances will never write to the same redo files – each instance has it’s own set of redo logs to write to . Another instance may well READ some other instances redo logs – after that other instance fails for example – to perform recovery. Here is a scenario which helps us to understand the thread concepts .

Most V$ views work by selecting information from the corresponding GV$ view with a predicate “where instance_id = “.

So V$SESSION in single Instance(i.e, 1) is actually

SQL>select  *  from  gv$instance where inst_id= 1 ;

On a three node RAC database, if we select from v$session, we get sessions from that instance only. Selecting from GV$SESSION creates parallel query slaves on the other instances and gets the information back to our session.

This works fine in almost all cases. There are few exceptions: in case of redo logs, the RAC instance must see all the redo logs of other instances as they become important for its recovery. Therefore, V$LOG actually shows all the redo logs, of all the instances, not just of its own. Contrast this with V$SESSION, which shows only sessions of that instance, not all. So, if there are 3 log file groups per instance (actually, per “thread”) and there are 3 instances, V$LOG on any instance will show all 9 logfile groups, not 3.

When we select form GV$LOG, remember, the session gets the information from other instances as well. Unfortunately, the PQ servers on those instances also get 9 records each, since they also see the same information seen by the first instance. On a three instance RAC, we will get 3X9 = 27 records in GV$LOG!

To avoid this: 

1. Always select from V$LOG, V$LOGFILE and V$THREAD in a RAC instance. GV$ views are misleading.

2. Add a predicate to match  THREAD#  with  INST_ID. (Beware: thread numbers are by default the same as the instance_id; but we may have defined a different thread number while creating the database) as :

SQL> select * from gv$log where inst_log=thread# ;