Split Brain Syndrome in Oracle RAC

In a cluster, a private interconnect is used by cluster nodes to monitor each node’s status and communicate with each other. When two or more nodes fail to ping or connect to each other via this private interconnect, the cluster gets partitioned into two or more smaller sub-clusters each of which cannot talk to others over the interconnect. Oblivious of the existence of other cluster fragments, each sub-cluster continues to operate independently of the others. This is called “Split Brain”. In such a scenario, the integrity of the cluster and its data might be compromised due to uncoordinated writes to shared data by independently operating nodes. Hence, the split-brain must be resolved to protect the integrity of the cluster and its data.

Explanation-1:

It will arise when two or more instances attempt to control a cluster database. In a two-node environment, one instance attempts to manage updates simultaneously while another attempts to manage updates. In the Oracle RAC environment, all the instances/servers communicate with each other using high-speed interconnects on the private network. This private network interface or interconnect is redundant and is only used for inter-instance Oracle data block transfers. Now talking about the split-brain concept concerning Oracle RAC systems, it occurs when the instance members in an RAC fail to ping/connect to each other via this private interconnect, but the servers are all physically up and running and the database instance on each of these servers is also running. These individual nodes are running fine and can conceptually accept user connections and work independently. Due to lack of communication, the instance thinks that the other instance that it is not able to connect is down and it needs to do something about the situation. The problem is if we leave these instances running, the sane block might get read, and updated in these individual instances and there would be a data integrity issue, as the blocks changed in one instance, will not be locked and could be over-written by another instance. Oracle has efficiently implemented checks for the split-brain syndrome. When there is no network heart bit then Split brain is done using Disk (vote disk) information.

Explanation-2:

Oracle has efficiently implemented checks for the split-brain syndrome. In RAC if any node becomes inactive, or if other nodes are unable to ping/connect to a node in the RAC, then the node that first detects that one of the nodes is not accessible, will evict that node from the RAC group. e.g. there are 4 nodes in a RAC instance, and node 3 becomes unavailable, and node 1 tries to connect to node 3 and finds it not responding, then node 1 will evict node 3 out of the RAC groups and will leave only Node1, Node2 & Node4 in the RAC group to continue functioning. The split brain concepts can become more complicated in large RAC setups. For example, there are 10 RAC nodes in a cluster. And say 4 nodes are not able to communicate with the other 6. So there are 2 groups formed in this 10-node RAC cluster ( one group of 4 nodes and the other of 6 nodes). Now the nodes will quickly try to affirm their membership by locking the control file, and then the node that locks the control file will try to check the votes of the other nodes. The group with the most number of active nodes gets the preference and the others are evicted. Moreover, I have seen this node eviction issue with only 1 node getting evicted and the rest function fine, so I cannot really testify that that shows it works by experience, but this is the theory behind it. When we see that the node is evicted, usually Oracle rac will reboot that node and try to do a cluster reconfiguration to include back the evicted node. You will see Oracle error: ORA-29740 when there is a node eviction in RAC. There are many reasons for a node eviction like heartbeat not being received by the control, unable to communicate with the Clusterware, etc.

How does the Oracle Grid Infrastructure Clusterware resolve a 'Split Brain' situation?

Voting disk is used by Oracle Cluster Synchronization Services Daemon (ocssd) on each node, to mark its own attendance and also to record the nodes it can communicate with. In a split-brain situation, 

a voting disk is used to determine which node(s) will survive and which node(s) will be evicted.

Which Process evicts nodes from the RAC Cluster?

• CSSD Monitors and evicts nodes.
• Monitors nodes using 2 communication Channels :
• Private Interconnect <=> Network Heartbeat
• Voting Disk Based communication <=> Disk Heartbeat
• Evicts (forcibly removes nodes from a cluster) nodes dependent on heartbeat feedback(failures).

Now, who will decide which node will survive and which node will face fencing ??

The answer is Voting Disk.

In a split-brain situation, a voting disk will be used to determine which node(s) survive and which node(s) will be evicted. 

Please feel free to ask. thank you 🙂

Toufique Khan

Oracle 11g, 12c, and 19c are different versions of the Oracle DBMS

Oracle 11g, 12c, and 19c are different versions of the Oracle Database management system, each introducing new features and improvements over its predecessor. Here's a brief overview of the key features and differences between them:

Oracle 11g:

Introduced in 2007, Oracle 11g focused on grid computing, which aimed to deliver enhanced performance, scalability, and reliability.

Key features included:

• Automatic SQL Tuning Advisor for automatic identification and tuning of high-load SQL statements.
• Advanced Compression for reducing storage requirements and improving performance.Advanced Compression: Improved storage efficiency and reduced disk space usage.
• Active Data Guard for real-time data protection and availability.Active Data Guard: Enabled the use of standby databases for read-only operations.
• No pluggable databases
• User-friendly browser-based interface for testing database changes
• Better data compression, leading to faster report generation
• SQL Developer graphical user interface tool for querying, defining, and manipulating data
• Supports viewing older versions of tables.
• Automatic Storage Management (ASM): Improved database performance and management by handling disk group storage.
• Data Guard Enhancements: Enhanced disaster recovery solutions.
• Real Application Testing: Allowed testing database changes before deploying them in production.Real Application Testing for database performance testing and validation.

Oracle 12c:

Released in 2013, Oracle 12c was a major release that introduced the concept of a multi-tenant architecture called Pluggable Databases (PDBs).

Key features included:

• The multitenant architecture allows multiple PDBs to be managed as a single container database (CDB), offering better resource utilization and management. 
• In-memory column store for accelerating database performance by storing tables and partitions in memory.
• Automatic Data Optimization (ADO) for managing the lifecycle of database segments,data tiering, and compression based on usage patterns.
• JSON supports storing, indexing, and querying JSON (JavaScript Object Notation) data within the database.
• Release Date: 2013 (12.1) and 2016 (12.2)
• The first version of the C-series, designed with cloud computing in mind
• Multi-tenant architecture and pluggable databases for improved scalability and security
• Includes 500 new features
• Supports cloud migration and has enhanced security features like Oracle Audit Vault and Database Firewall.
• Enhanced Security Features: Included features like data redaction, privilege analysis, and unified auditing.
• Improved Cloud Integration: Designed with cloud deployment in mind, allowing for easier migration to Oracle Cloud.

Oracle 19c:

Released in 2019, Oracle 19c is the latest long-term support release of the Oracle Database.

Key features include:

• Automatic Indexing, which uses machine learning algorithms to create, monitor, and drop indexes automatically to improve query performance.
• SQL Quarantine to identify and quarantine SQL statements causing performance issues.
• Real Application Clusters (RAC) improvements for enhanced scalability and high availability.
• Enhanced security features including Data Redaction, Privilege Analysis, and Password Management.
• Long-term release for the Oracle Database 12.2 product family, Marketed as the long-term support release, with extended support and stability.
• Provides extended support until 2026 and premier support until 2023
• Query Quarantine to manage system resources
• Simplified JSON support with SODA APIs
• Active Data Guard for minimizing downtime and data loss during repairs and upgrades.
• Each version has its unique features and improvements, with 19c being the most recent and offering the most advanced capabilities.
• Automatic Indexing: Automatically creates, maintains, and drops indexes based on workload analysis.
• Real-Time Statistics: Provides real-time statistics for better query optimization.
• Hybrid Partitioned Tables: Allows combining both internal and external partitions in a single table.
• JSON Enhancements: Improved support for JSON data types and operations.
• High Availability and Disaster Recovery Enhancements: Further improvements to Data Guard and other high-availability features.
• Each version of Oracle Database has built upon the capabilities of its predecessors, adding new features to improve performance, manageability, security, and support for modern applications and cloud environments.

Differences:

The most significant difference between these versions lies in their features and enhancements. Each version introduces new capabilities and improvements in performance, scalability, security, and manageability. Oracle 12c introduced the concept of a multi-tenant architecture, which Oracle 11g did not have. Oracle 19c continues to build upon the advancements made in previous versions, with a focus on automation, performance, and security enhancements. Additionally, each version may have different levels of support, with newer versions typically receiving longer support periods from Oracle. Overall, organizations upgrading their Oracle databases need to consider the specific features, enhancements, and support timelines of each version to determine the most suitable option for their needs.

Please feel free to ask. thank you 🙂

Toufique Khan 

How do you detect Row Chaining and Row Migration?

Row Migration:

Row migration refers to rows that were moved to another block due to an update making them too large to fit into their original blocks. Oracle will leave a forwarding pointer in the original block so indexes will still be able to find the row. The rowid of the migrated row does not change. Thus, ROWID which is stored in the index still refers to the old location of the row. An additional block, the new location of the row, must be read to fetch the required data.

Row Chaining:

A chained row is a row that is too large to fit into a single database data block. For example, if you use a 4KB blocksize for your database, and you need to insert a row if 8KB into it, Oracle will use 3 blocks and store the row in pieces.

Oracle has provided three ways to detect the Row Chaining and Row Migration.

• The ANALYZE command
• The dynamic views
• Report.txt method

SQL> SELECT count(e) FROM row_mig_chain_demo;

  COUNT(E)

----------------

         1

SQL>

SQL> SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic# AND lower(a.name) = 'table fetch continued row';

NAME                                                                          VALUE

---------------------------------------------------------------- ----------

table fetch continued row                                                5

Analyze the table to verify the chain count of the table:

SQL> ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

Table analyzed.

SQL>

SQL> SELECT chain_cnt FROM user_tables WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT

-----------------

         3

 

Three rows that are chained. Apparently, 2 of them are migrated (Rows 1 and 2) and one is truly chained (Row 3).

Total Number of table fetch continued row since instance startup?

SQL> SELECT name,value FROM v$sysstat WHERE name like '%table%';

SQL>  SELECT 'Chained or Migrated Rows = '||value FROM v$sysstat  WHERE name = 'table fetch continued row';

'CHAINEDORMIGRATEDROWS='||VALUE

-------------------------------------------------------------------

Chained or Migrated Rows = 2272

How many Rows in a Table are chained?

SQL> ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT chain_cnt, round(chain_cnt/num_rows*100,2) pct_chained,  avg_row_len, pct_free , pct_used FROM user_tables WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED

----------------- ---------------------- ---------------------- ---------------- -----------------

                     3                        100                      1009                  10                    40

PCT_CHAINED shows 100% which means all rows are chained or migrated.

List Chained Rows

You can look at the chained and migrated rows of a table using the ANALYZE statement with the LIST CHAINED ROWS clause. The results of this statement are stored in a specified table created explicitly to accept the information returned by the LIST CHAINED ROWS clause. These results are useful in determining whether you have enough room for updates to rows.

Creating a CHAINED_ROWS Table

To create the table to accept data returned by an ANALYZE ... LIST CHAINED ROWS statement, execute theUTLCHAIN.SQL or UTLCHN1.SQL script in $ORACLE_HOME/rdbms/admin. These scripts are provided by the database. They create a table named CHAINED_ROWS in the schema of the user submitting the script.

SQL> create table CHAINED_ROWS (

  owner_name         varchar2(30),

  table_name         varchar2(30),

  cluster_name       varchar2(30),

  partition_name     varchar2(30),

  subpartition_name  varchar2(30),

  head_rowid         rowid,

  analyze_timestamp  date

);

Table Created

After a CHAINED_ROWS table is created, you specify it in the INTO clause of the ANALYZE statement.

SQL> ANALYZE TABLE row_mig_chain_demo LIST CHAINED ROWS;

Table analyzed.

SQL> SELECT owner_name,

       table_name,

       head_rowid

 FROM chained_rows;

OWNER_NAME           TABLE_NAME                       HEAD_ROWID

---------------------------- ------------------------------------- ------------------

SCOTT                          ROW_MIG_CHAIN_DEMO   AAAPVIAAFAAAAkiAAA

SCOTT                          ROW_MIG_CHAIN_DEMO   AAAPVIAAFAAAAkiAAB

How to Avoid Chained and Migrated Rows?

Increasing PCTFREE can help to avoid migrated rows. If you leave more free space available in the block, then the row has room to grow. You can also reorganize or re-create tables and indexes that have high deletion rates. If tables frequently have rows deleted, then data blocks can have partially free space in them. If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to grow. Reorganizing the table ensures that the main free space is totally empty blocks.

        The ALTER TABLE ... MOVE statement enables you to relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those that cannot be modified using ALTER TABLE. You can also use the ALTER TABLE ... MOVE statement with the COMPRESS keyword to store the new segment using table compression.

1. Alter Table Move

First, count the number of Rows per Block before the ALTER TABLE MOVE

SQL> SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows" FROM row_mig_chain_demo GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;

  Block-Nr  Rows

------------- ----------

         2066             3  

Now, de-chain the table, the ALTER TABLE MOVE rebuilds the row_mig_chain_demo table in a new segment, specifying new storage parameters:

SQL> ALTER TABLE row_mig_chain_demo MOVE

   PCTFREE 20

   PCTUSED 40

   STORAGE (INITIAL 20K

            NEXT 40K

            MINEXTENTS 2

            MAXEXTENTS 20

            PCTINCREASE 0);  

Table altered.

Again count the number of Rows per Block after the ALTER TABLE MOVE

SQL> SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows" FROM row_mig_chain_demo GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;

Block-Nr  Rows

----------- ----------

2322       1

2324       1

2325       1

2. Rebuild the Indexes for the Table

Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid, and new statistics should be collected after moving the table.

SQL> ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS

*

ERROR at line 1:

ORA-01502: index 'SYS.SYS_C007354' or partition of such index is in unusable state

This is the primary key of the table which must be rebuilt.

SQL>  ALTER INDEX  SYS_C007354 REBUILD ;

Index altered.

SQL> ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT chain_cnt, round(chain_cnt/num_rows*100,2) pct_chained,  avg_row_len, pct_free , pct_used FROM user_tables WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED

----------------- ---------------------- ---------------------- ---------------- -----------------

                     3                     33.33                      1009                  20                    40

If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

Detect all Tables with Chained and Migrated Rows

Using the CHAINED_ROWS table, you can find out the tables with chained or migrated rows.

1. Create the CHAINED_ROWS table

[oracle@rac01 ~]$ cd $ORACLE_HOME/rdbms/admin

[oracle@rac01 admin]$ sqlplus scott/tiger

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 3 00:07:16 2024

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @utlchain.sql

Table created.

2. Analyse all or only your Tables

SQL> SELECT 'ANALYZE TABLE '||table_name||' LIST CHAINED ROWS INTO CHAINED_ROWS;'

 FROM user_tables

/

  2    3

'ANALYZETABLE'||TABLE_NAME||'LISTCHAINEDROWSINTOCHAINED_ROWS;'

--------------------------------------------------------------------------------

ANALYZE TABLE ICOL$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE COL$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE IND$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE TAB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE CLU$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE LOB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE COLTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE SUBCOLTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE NTAB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE REFCON$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE OPQTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

11 rows selected.

SQL> ANALYZE TABLE ICOL$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE COL$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE IND$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE TAB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE CLU$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE LOB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE COLTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE SUBCOLTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE NTAB$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE REFCON$ LIST CHAINED ROWS INTO CHAINED_ROWS;

ANALYZE TABLE OPQTYPE$ LIST CHAINED ROWS INTO CHAINED_ROWS;

Table analyzed.

3. Show the RowIDs for all chained rows

This will allow you to quickly see how much of a problem chaining is in each table. If chaining is prevalent in a table, then that table should be rebuild with a higher value for PCTFREE.

OWNER_NAME  TABLE_NAME  ROW_COUNT

---------------------- -------------------- --------------------

SYS                       COL$                  1

SYS                       IND$                  7

SYS                       TAB$                  5

Conclusion: Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

• Row migration is typically caused by UPDATE operation
• Row chaining is typically caused by INSERT operation.
• SQL statements that are creating/querying these chained/migrated rows will degrade the performance due to more I/O work.
• To diagnose chained/migrated rows use ANALYZE command, query V$SYSSTAT view
• To remove chained/migrated rows use higher PCTFREE using ALTER TABLE MOVE. 

Please feel free to ask. thank you 🙂

Toufique Khan