V $ log V $ logfile in Oracle RAC Environment

Generally, in the Oracle RAC environment, V $ view can query information about the instance you are connected to, while GV $ view contains information about all instances. However, in the RAC environment, when we query the V $ log view, it is common sense that the V $ log view should see the information of the log group that you connect to the instance. However, V $ log is an exception. That is to say, in the V $ log view, not only the redo log group contained in your instance is displayed, redo log groups of all other instances will also appear in this view. You can query the V $ log view from any node to obtain the same result. This situation also applies to V $ logfile. What exactly is this? If you are confused, let's look at it.

 

1. Understand recovery of databases, instances, and Rac Databases
Database:
A database is a collection of data files, such as control files, data files, online log files, parameter files, password files, and archived logs.
Instance:
Including the memory structure (SGA) and a series of background processes, both of which are called an oracle instance.
For more information about the replicel database and instance description, see Oracle instances and Oracle databases (Oracle architecture)
RAC database and recovery:
A rac database can have multiple instances. That is to say, SGA + background process is built on different nodes and some data files of the database are shared.
However, for the redo/undo parts, they use separate redo and undo, but this does not mean that they are only part of each instance (node.
The recovery of the RAC database requires the archived log generated by all nodes and the redo information in all nodes so that the history of the RAC database can be fully displayed.
Second, RAC database recovery is usually completed on a single node. That is to say, when the remaining nodes are closed, V $ log should also be able to see the redo log information of all instances.

 

2. Understand redo threads
When speaking in the context of multiple database instances, the redo log for each database instance is also referred to as
Redo thread. In typical comprehensions, only one database instance accesses an Oracle database, so only one thread is present.
In an Oracle Real Application Clusters environment, however, two or more instances concurrently access a single database and
Each instance has its own thread of redo. A separate redo thread for each instance avoids contention for a single set of redo
Log Files, thereby eliminating a potential performance bottleneck.

In a single instance environment, there is only one redo thread, and Its thread # is usually 1.
In the RAC environment, multiple instances share one database. Therefore, an instance has a redo thread to avoid competition and improve performance.

 

3. V $ log and V $ logfile

-- Let's look at the query view below to see V $ log and V $ logfile -- Environment SQL> select * from V $ version where rownum <2; banner export Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-productionsql> show parameter cluster_dname type value values ------------- ---------------------------- cluster_database Boolean truecluster_database _ Instances integer 2 ---> query v $ logsql> select group #, thread #, sequence #, members, archived, status, first_time, next_time from V $ log; group # thread # sequence # members arc status first_time next_time ---------- ------------ ---------- --- ---------------- hour 1 1 13 2 no current hour 14:38:36 2 1 12 2 Yes inactive 14:04:29 14:38:36 3 2 1 1 2 no current 14:44:50 14:44:50 14:04:32 4 2 10 2 Yes inactive 14:38:33 -- the preceding query shows that the current database has four log groups, each log group has two members. There are two redo threads, namely thread 1 and thread 2. -- Log group 1 and group 2 are managed by thread 1, that is, log group 1 and group 2 are located in instance 1. Similarly, log group 3 and group 4 are located in instance 2. -- We can see from the above that the V $ log view itself already contains the redo log group information of all the data instances, which is queried from any node of the RAC database, the view returns the same information. -- If there are three instances and each instance has two log groups, the V $ log should contain 3*2 = 6 rows (number of instances * Number of log groups for each instance ). If there are four instances and one log group for each instance, there are four lines of records. -- We can also see that there is a group above each instance that belongs to the current group (current). For example, for instance 1, group 1 is the current group, for instance 2, group 3 is the current group. -- Check whether the SQL> show parameter instance_nuname type value ---------------------------------------- ---------------------------------- instance_number integer 1sql> archive log list; database Log mode archive modeautomatic archival enabledarchive destination use_db_recovery_file_destoldest online log sequence 12 next log sequence to archive 13 current log sequence 13 ---> the current log group is 13, at instance 1sql> show P Arameter instance_nuname type value -------------------------------------- ------------------------------------ instance_number integer 2sql> archive log list; database Log mode archive modeautomatic archival enabledarchive destination use_db_recovery_file_destoldest online log sequence 10 next log sequence to archive 11 Current Log sequence 11 ---> the current log group is 11, located in instance 1, the two verifications are the same as what we see in the V $ log instance. --> Query v $ logfilesql> select * from V $ logfile; group # status type member is _ ---------- ------- offline --- 2 online + asm_data/mmbo/onlinelog/offline No 2 online + fra_data/mmbo/onlinelog/group_2.258.821029385 Yes 1 online + asm_data/mmbo /onlinelog/group_1.261.821029381 No 1 online + fra_data/mmbo/onlinelog/group_1.257.8210293 83 Yes 3 online + asm_data/mmbo/onlinelog/group_3.266.821029785 No 3 online + fra_data/mmbo/onlinelog/reply Yes 4 online + asm_data/mmbo/onlinelog/reply No 4 online + fra_data/ mmbo/onlinelog/group_4.260.821029789 yes8 rows selected. -- Because each group of logs contains two members, eight rows of records are returned for View query. As mentioned above, this view also contains the log members of all instances. -- From the above view, we can see that the current database has a total of 4 log groups and 8 log members, each of which is located in the flashback area. ---> Query GV $ logsql> select inst_id, group #, thread #, sequence #, members, archived, status, first_time, next_time from GV $ log; inst_id group # thread # sequence # members arc status first_time next_time ------------ ---------- --- ------------------ seconds starting 2 1 13 2 no current 14:38:36 seconds 1 4:38:36 2 3 2 11 2 no current 2013/08/05 14:44:50 2013/08/05 14:44:50 2 4 2 10 2 Yes inactive 2013/08/05 14:04:32 2013/08/05 14:38:33 1 1 1 1 13 2 no current 2013/08/05 14:38:36 1 2 12 2 Yes inactive instances 14:04:29 14:38:36 1 3 2 11 2 no current 2013/08/05 14:44:50 14:44:50 1 4 2 10 2 Yes inactive 2013/08/05 14:04:32 rows selected. --> close any node and query v $ log and GV $ log SQL> Ho srvctl stop instance-D mmbo-I mmbo2sql> select group #, thread #, sequence #, members, archived, status, first_time, next_time from V $ log; group # thread # sequence # members arc status first_time next_time ---------- ------------ ---------- --- ---------------- hour 1 1 13 2 no current 14:38:36 2 1 1 12 2 Yes inactive 14:04:29 2 013/08/05 14:38:36 3 2 11 2 Yes active 14:44:50 16:21:22 4 2 10 2 Yes inactive 14:04:32 14: 38: 33sql> select inst_id, group #, thread #, sequence #, members, archived, status, first_time, next_time from GV $ log; inst_id group # thread # sequence # members arc status first_time next_time ---------- --- begin ------------------------------------------------------------------------------ ---------- Listen 1 1 1 13 2 no current 2013/08/05 14:38:36 1 2 1 12 2 Yes inactive 2013/08/05 14:04:29 2013/08/05 14:38:36 1 3 2 11 2 Yes active 2013/08/05 14:44:50 2013/08/05 16:21:22 1 4 2 10 2 Yes inactive 14:04:32 14:38:33 -- as shown in the preceding query, for the current 2-node database, V $ log and GV $ log present the same information. gv $ log only lists the case where inst_id is column 1-as shown in the test results above, the GV $ log view is similar to the Union all, And the GV $ logfile is the same as this, so we will not describe -- selec T 1 as inst_id, V. * From v $ log V -- at this time, V $ log is located in instance 1 -- Union all -- select 2 as inst_id, V. * From v $ log V -- at this time, V $ log is located in instance 2 -- Author: Robinson Cheng -- Blog: Login = (V $ controlfile * Instances quantity) -- GV $ datafile = (V $ datafile * Instances quantity) -- GV $ log = (V $ log * Instances quantity) -- GV $ archive_log = (V $ archive_log * Instances Quantity) --> How do I query the redo information of the current instance? -- You can use the following method to query the redo information of an instance. SQL> select group #, thread #, sequence #, members, archived, status, first_time, next_time 2 from V $ log where thread # = (select value from V $ parameter where name = 'thread '); group # thread # sequence # members arc status first_time next_time ---------- ------------ ---------- --- ---------------- hour 1 1 13 2 no current 14:38:36 14:04:29 pm

4. Postscript
A. For databases in the RAC environment, we can query the redo log group information of all instances of the current database from the V $ log view. The V $ logfile is similar.
B. The ultimate goal of obtaining redo information for all instances from the V $ log view is to be able to access all redo information when the database is restored from any node.
C. You can add the corresponding thread as the filter condition to query the redo information of the instance.

 

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)