- 27 Jun, 2024
- 0 Comments
- 4 Mins Read
What is redo log thread?
In Oracle Database, a redo log thread is a sequence of redo logs used to record all changes made to the database. Redo logs are crucial for database recovery as they store the history of all changes to the data. Each redo log thread corresponds to a single instance in an Oracle Real Application Clusters (RAC) environment, ensuring that all changes made by that instance are captured and can be recovered if necessary.
Redo Log
The primary purpose of redo logs in an Oracle Database is to ensure data integrity and enable database recovery in the event of a failure. Redo logs serve as a vital component for recording all changes made to the database, providing a mechanism for rolling forward these changes during recovery operations
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.
Redo Log Thread
In Oracle Database, a redo log thread is a sequence of redo logs used to record all changes made to the database. Redo logs are crucial for database recovery as they store the history of all changes to the data. Each redo log thread corresponds to a single instance in an Oracle Real Application Clusters (RAC) environment, ensuring that all changes made by that instance are captured and can be recovered if necessary.
Key concepts:
These are files that store a record of changes made to the database, such as DML (INSERT, UPDATE, DELETE) and DDL (CREATE, ALTER, DROP) operations.
Each redo log is identified by a unique log sequence number.
A redo log thread is a sequence of redo log files used by a single instance in a RAC environment.
In a single-instance database, there is only one redo log thread, and all redo log entries for the database are recorded in this thread , allowing multiple instances to write redo log records concurrently.
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:
Redo log thread switching refers to the process in which the Oracle Database switches between different redo log threads. This is particularly relevant in Oracle Real Application Clusters (RAC) environments, where multiple instances (each with its own redo log thread) are running concurrently. However, log thread switching itself isn’t a common term; the typical focus is on log file switching within a thread.
Reasons for Switching:
Redo log thread switching may occur during operations such as instance startup, shutdown, or failover in a RAC environment.
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#