Oracle Database 23ai / 26ai Vector Search Deep Dive

From AI Models to Semantic Search Using VECTOR Data Type and VECTOR_DISTANCE

For decades, Oracle Database has been the platform of choice for mission-critical transactional and analytical workloads. With Oracle Database 23ai and the Oracle Database 26ai, Oracle has taken a significant leap into the AI era by introducing native Vector Search capabilities directly inside the database engine.

Instead of moving data to external vector databases, Oracle now enables organizations to:

Store vectors natively
Generate embeddings inside the database
Perform semantic similarity searches
Build Retrieval Augmented Generation (RAG) solutions
Combine traditional SQL filtering with AI-powered semantic retrieval

This dramatically simplifies AI application architectures while leveraging Oracle’s strengths in security, scalability, availability, and SQL optimization.

In this article, we will perform a complete end-to-end walkthrough:

Understanding Vector Search
Choosing the right embedding model
Loading AI models into Oracle
Generating vectors using the VECTOR datatype
Performing semantic searches using VECTOR_DISTANCE

Understanding Vector Search

Traditional database searches rely on exact matches.

For example:

			
SELECT *
FROM documents
WHERE document_text LIKE '%database%';

This query only finds documents containing the exact word “database”.

What if the document contains:

Oracle AI Platform
Data Management System
Information Repository

Traditional searches may miss relevant content.

Vector Search solves this problem.

The AI model converts text into a mathematical representation called an embedding vector.

Such as:

			
"Oracle Database"
      ↓
[0.234, -0.876, 0.123, ...]

Semantically similar phrases generate vectors that are close together in vector space, like:

			
"Oracle Database"
"Oracle RDBMS"
"Oracle Data Platform"

Their vectors are positioned near each other. The database can then calculate similarity mathematically.

Oracle AI Vector Architecture

The overall workflow looks like:

			
Document
    ↓
Embedding Model
    ↓
VECTOR datatype
    ↓
Vector Index
    ↓
VECTOR_DISTANCE
    ↓
Semantic Search Results

		

Oracle 23ai introduces:

VECTOR datatype
Vector indexes
ONNX model support
DBMS_VECTOR package
VECTOR_DISTANCE functions

Oracle 26ai further enhances:

Hybrid Search
Improved ANN indexing
GPU acceleration integrations
Enhanced AI workflows
Better model management

Choosing the Right Embedding Model

The quality of your Vector Search depends heavily on the embedding model. A poor model produces poor search results.A good model creates semantically meaningful vectors.

Popular Embedding Models

all-MiniLM-L6-v2

Dimension: 384

Advantages:

Small
Fast
Low memory footprint
Excellent for demos and production workloads

Use Cases:

FAQ search
Knowledge bases
RAG applications

bge-small-en

Dimension: 384

Advantages:

Better semantic understanding
Higher retrieval quality

Use Cases:

Enterprise Search
Internal Documentation

bge-large-en

Dimension: 1024

Advantages:

Excellent accuracy

Disadvantages:

Higher storage requirements
Larger vectors

Use Cases:

High-quality semantic retrieval

multilingual-e5-large

Dimension:1024

Advantages:

Multi-language support

Ideal for:

Global organizations

Selecting a Model

General recommendation:

Workload	Recommended Model
Demo	all-MiniLM-L6-v2
RAG	bge-small-en
Enterprise Search	bge-large-en
Multilingual	e5-large

For most Oracle implementations:

all-MiniLM-L6-v2

provides the best balance between performance and accuracy.

Loading an ONNX Model into Oracle

Oracle supports ONNX models natively.

Suppose we downloaded:

all_MiniLM_L6_v2.onnx

Create a directory:

			
CREATE OR REPLACE DIRECTORY AI_MODELS AS
'/u01/models';

Grant access:

GRANT READ, WRITE ON DIRECTORY AI_MODELS TO vector_user;

			
BEGIN
  DBMS_VECTOR.LOAD_ONNX_MODEL(
      directory => 'AI_MODELS',
      file_name => 'all_MiniLM_L6_v2.onnx',
      model_name => 'MINILM_MODEL');
END;
/
Verify:
SELECT model_name,
       mining_function,
       algorithm
FROM user_mining_models;
MINILM_MODEL
Model successfully loaded.

		

Creating a Vector-Enabled Table

Oracle introduced a native VECTOR datatype.

			
CREATE TABLE documents
(
    doc_id NUMBER,
    title VARCHAR2(200),
    content CLOB,
    embedding VECTOR(384,FLOAT32)
);

		

Sample Data

			
INSERT INTO documents
VALUES
(
 1,
 'Oracle AI',
 'Oracle Database 23ai introduces vector search',
 NULL
);
INSERT INTO documents
VALUES
(
 2,
 'Machine Learning',
 'Embedding models generate vector representations',
 NULL
);
INSERT INTO documents
VALUES
(
 3,
 'Cloud Computing',
 'Oracle Cloud Infrastructure supports AI workloads',
 NULL
);
COMMIT;

		

Generating Embeddings

Now we convert text into vectors.

Oracle uses:

VECTOR_EMBEDDING()

For example:

			
UPDATE documents
SET embedding =
    VECTOR_EMBEDDING(
       MINILM_MODEL
       USING content
    );
    Commit:

		

Now every document contains a semantic vector representation.

Viewing Vector Data

Use query:

SELECT doc_id, embedding FROM documents;

			
[0.132,
-0.762,
0.451,
...
]

		

Actual vectors contain hundreds of dimensions.

Generating Query Vectors

Suppose a user searches for:

AI database capabilities

Generate query embedding:

			
SELECT VECTOR_EMBEDDING(
         MINILM_MODEL
         USING 'AI database capabilities'
       ) query_vector
FROM dual;

		

This creates a vector representation of the search phrase.

Measuring Similarity

Oracle supports several distance metrics.

Cosine Distance

Most commonly COSINE, measures angle between vectors.

Best for:

Text embeddings
Semantic Search
RAG

Euclidean Distance

EUCLIDEAN,
Measures geometric distance.

Dot Product

DOT

Useful for specific models.

VECTOR_DISTANCE Function

Syntax:

			
VECTOR_DISTANCE(
      vector1,
      vector2,
      COSINE
)

		

Smaller value means higher similarity.

For example:

			
SELECT VECTOR_DISTANCE(
          vector_a,
          vector_b,
          COSINE)
FROM dual;

		

First Semantic Search

Search phrase:

Oracle artificial intelligence

Query:

			
SELECT
       doc_id,
       title,
       VECTOR_DISTANCE(
          embedding,
          VECTOR_EMBEDDING(
             MINILM_MODEL
             USING 'Oracle artificial intelligence'
          ),
          COSINE
       ) similarity
FROM documents
ORDER BY similarity;

		

			
DOC_ID TITLE                SIMILARITY
------ ------------------- ----------
1      Oracle AI             0.12
3      Cloud Computing       0.41
2      Machine Learning      0.67

		

Document 1 is the closest semantic match.

Top-K Vector Search

Most applications need only the best matches.

For example:

			
SELECT *
FROM
(
    SELECT
          doc_id,
          title,
          VECTOR_DISTANCE(
              embedding,
              VECTOR_EMBEDDING(
                  MINILM_MODEL
                  USING 'vector databases'
              ),
              COSINE
          ) score
    FROM documents
    ORDER BY score
)
FETCH FIRST 5 ROWS ONLY;

		

This returns the Top-5 most relevant documents.

Creating a Vector Index

Without an index:

Full Table Scan

For millions of vectors this becomes expensive.

Create a vector index:

			
CREATE VECTOR INDEX doc_vec_idx
ON documents(embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH;

Benefits:

Faster retrieval
Approximate Nearest Neighbor (ANN)
Scales to millions of embeddings

Oracle 26ai Enhancements

Oracle Database 26ai extends the vector ecosystem with:

Improved vector indexing
Better ANN algorithms
Enhanced model lifecycle management
Hybrid retrieval optimization
RAG-focused SQL enhancements
AI agent integration patterns
Improved scalability for billions of vectors

Organizations building enterprise AI assistants can keep both transactional data and semantic vectors in the same Oracle platform.

0 Comments Leave a comment

Oracle 23ai transaction priority capabilities

Modern OLTP applications frequently suffer from blocking sessions caused by long-running or abandoned transactions. In earlier Oracle releases, DBAs typically had to manually identify and kill blocking sessions using commands such as:

ALTER SYSTEM KILL SESSION 'sid,serial#';

This approach disrupts application connectivity, impacts user sessions, and increases operational overhead.

Oracle Database 23ai introduces a powerful feature called Priority Transactions (also referred to as Automatic Transaction Rollback). This feature allows Oracle to automatically roll back lower-priority transactions when they block higher-priority transactions for longer than a configured threshold.

The major benefit:

High-priority business transactions continue processing.
Blocking transactions are automatically rolled back.
Sessions remain alive.
Applications can gracefully recover.
DBA intervention is reduced.

How Priority Transactions Work

Oracle introduces:

1. Transaction Priorities

Each session can be assigned one of the following priorities:

Priority	Description

HIGH	Never auto-rolled back

MEDIUM

Can be rolled back by HIGH

LOW	Can be rolled back by HIGH or MEDIUM

The priority is assigned at session level:

ALTER SESSION SET txn_priority = HIGH;

2. Wait Threshold Parameters

These parameters define how long a higher-priority transaction waits before Oracle automatically rolls back the blocker.

Parameter

Purpose

PRIORITY_TXNS_HIGH_WAIT_TARGET

Wait time for HIGH priority transaction

PRIORITY_TXNS_MEDIUM_WAIT_TARGET

Wait time for MEDIUM priority transaction

For example:

ALTER SYSTEM SET priority_txns_high_wait_target = 10;

This means if a HIGH priority transaction waits longer than 10 seconds on a row lock held by a LOW or MEDIUM transaction, Oracle automatically rolls back the blocking transaction.

3. Modes of Operation

Oracle supports two modes:

Mode	Description

ROLLBACK

Automatically rolls back blocking transactions

TRACK

Only tracks what would happen without rollback

For example:

ALTER SYSTEM SET priority_txns_mode = ROLLBACK;

Environment Used

Component	Value
Database	Oracle Database 23ai
Tool	SQL*Plus
Users	APP_LOW, APP_HIGH
OS	Linux

Step 1 – Create Demo Users

Connect as SYS:

			
CREATE USER app_low IDENTIFIED BY oracle;
GRANT CONNECT, RESOURCE TO app_low;
CREATE USER app_high IDENTIFIED BY oracle;
GRANT CONNECT, RESOURCE TO app_high;

Step 2 – Create Test Table

			
SQL> sqlplus app_low/oracle
SQL> CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    status   VARCHAR2(20)
);
INSERT INTO orders VALUES (1, 'PENDING');
COMMIT;
SQL> SELECT * FROM orders;
  ORDER_ID STATUS
---------- --------------------
         1 PENDING

		

Step 3 – Configure Priority Transaction Parameters

Connect as SYS and Enable automatic rollback mode:

ALTER SYSTEM SET priority_txns_mode = ROLLBACK;

Configure HIGH wait threshold:

ALTER SYSTEM SET priority_txns_high_wait_target = 5;

Configure MEDIUM wait threshold:

ALTER SYSTEM SET priority_txns_medium_wait_target = 10;

Verify the parameter settings:

			
SQL> SHOW PARAMETER priority_txns
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
priority_txns_high_wait_target       integer     5
priority_txns_medium_wait_target     integer     10
priority_txns_mode                   string      ROLLBACK

		

Step 4 – Start LOW Priority Transaction

Open Session-1:

Connect to user app_low and Set transaction priority to LOW:

			
SQL> ALTER SESSION SET txn_priority = LOW;
SQL> SHOW PARAMETER txn_priority
NAME            TYPE    VALUE
--------------- ------- -----
txn_priority    string  LOW

		

Update row WITHOUT commit. This session now holds a row lock.

			
SQL> UPDATE orders
SET status = 'PROCESSING'
WHERE order_id = 1;
1 row updated.
SQL>

		

Step 5 – Start HIGH Priority Transaction

Open Session-2: Connect to sys and grant privileges on app_low.orders to user app_high

			
SQL> GRANT SELECT, UPDATE ON app_low.orders TO app_high;
SQL>

Now connect to user app_high and Set transaction priority to HIGH:

			
SQL> ALTER SESSION SET txn_priority = HIGH;
SQL> SHOW PARAMETER txn_priority
NAME            TYPE    VALUE
--------------- ------- -----
txn_priority    string  HIGH
SQL>

		

Now attempt update:

SQL> UPDATE app_low.orders SET status = 'SHIPPED' WHERE order_id = 1;

Initially, Session-2 waits because Session-1 holds the lock.

After 5 seconds, Oracle automatically rolls back the LOW priority transaction.

			
SQL> UPDATE app_low.orders SET status = 'SHIPPED' WHERE order_id = 1;
1 row updated.
SQL>

Step 6 – Observe Automatic Rollback in Session-1

Back in Session-1: Try another SQL statement:

			
SQL> SELECT * FROM orders;
ERROR:
ORA-63302: Transaction must roll back.

Oracle requires the application to acknowledge the rollback.

			
SQL> ROLLBACK;
Rollback complete.
SQL>

Now query orders table.

			
SQL> SELECT * FROM orders;
ORDER_ID STATUS
-------- --------------------
1        SHIPPED
SQL>

		

Step 7 – Monitor Priority Transactions

			
SQL> SELECT
    addr,
    xidusn,
    xidslot,
    xidsqn,
    txn_priority,
    priority_txns_wait_target
FROM v$transaction;
ADDR             XIDUSN XIDSLOT XIDSQN TXN_PRIORITY PRIORITY_TXNS_WAIT_TARGET
---------------- ------ -------- ------- ------------ ----------------------------
000000007A12ABCD      7       12   1234 HIGH                                 5

		

Step 8 – Check Blocking Sessions

			
SQL> SELECT
    sid,
    serial#,
    blocking_session,
    event,
    seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;
SID SERIAL# BLOCKING_SESSION EVENT                         SECONDS_IN_WAIT
--- ------- ---------------- ----------------------------- ----------------
45  12345   22               enq: TX - row lock contention                3

		

Step 9 – Try TRACK Mode

TRACK mode is useful before enabling automatic rollback in production. Instead of rolling back transactions, Oracle only tracks potential rollback events.

Enable TRACK mode as follows:

			
SQL> ALTER SYSTEM SET priority_txns_mode = TRACK;
SQL>

Now Oracle records statistics without actually terminating transactions. Following are the useful statistics:

			
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%priority%';

Important Production Considerations

I) Do Not Overuse HIGH Priority

If application only uses HIGH priority:

No transaction can be auto-rolled back
The feature becomes ineffective

Use priorities strategically.

II) Applications Must Handle Rollbacks

Applications should:

Catch ORA-63300
Catch ORA-63302
Retry transactions safely
Issue explicit ROLLBACK

III) Choose Wait Targets Carefully

Very small wait targets can cause excessive rollbacks.

Very large wait targets reduce effectiveness.

Recommended approach:

Start with TRACK mode
Observe contention patterns
Tune thresholds
Switch to ROLLBACK mode

IV) Monitor Frequently

Monitor:

Row lock contention
Rollback frequency
Wait events
Application retries

Useful wait event: enq: TX – row lock contention

0 Comments Leave a comment

MySQL InnoDB Cluster Rolling Patch Procedure from 8.4.6 to 8.4.8

Below is the rolling patching procedure for 3-node single-primary MySQL InnoDB Cluster:

Cluster nodes:
- prim01
- prim02
- prim03
Router host:
- prodapp
Upgrade path:
- MySQL Server / Router / Shell 8.4.6 → 8.4.8

This procedure assumes:

InnoDB Cluster in Single-Primary mode
Linux servers using systemd
MySQL Router on prodapp
Minimal/no downtime objective
Application traffic goes through MySQL Router

It’s recommended to upgrade in this order:

MySQL Router
MySQL Shell
Secondary nodes
Primary node
Metadata/status validation

Host	Role Before Upgrade
prim01	PRIMARY
prim02	SECONDARY
prim03	SECONDARY
prodapp	MySQL Router

1. Pre-Upgrade Checks

Run these before touching anything.

1.1 Verify cluster health

Connect using MySQL Shell:

			
MySQL  localhost:3306 ssl  JS > dba.getCluster().status()
{
    "clusterName": "testclust",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "prim01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "prim01:3306": {
                "address": "prim01:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.6"
            },
            "prim02:3306": {
                "address": "prim02:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.6"
            },
            "prim03:3306": {
                "address": "prim03:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.6"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "prim01:3306"
}
MySQL  localhost:3306 ssl  JS >

		

Ensure:

All members ONLINE
No replication lag
No instanceErrors

			
mysql> \status
--------------
mysql  Ver 8.4.6-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)
Connection id:          383
Current database:
Current user:           root@prim01
SSL:                    Cipher in use is TLS_AES_128_GCM_SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.4.6-commercial MySQL Enterprise Server - Commercial
Protocol version:       10
Connection:             10.xx.xx.xx via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 4 days 23 hours 54 min 37 sec
 
Threads: 16  Questions: 1575  Slow queries: 0  Opens: 587  Flush tables: 3  Open tables: 504  Queries per second avg: 0.003

		

1.2 Verify current versions

On all nodes:

			
mysql --version
mysqlsh --version
mysqlrouter --version

			
[mysqladm@prim03 ~]$ mysqld --version
/usr/sbin/mysqld  Ver 8.4.6-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)
[mysqladm@prim03 ~]
[mysqladm@prim03 shell]$ mysqlsh --version
mysqlsh   Ver 8.4.6-commercial for Linux on x86_64 - for MySQL 8.4.6 (MySQL Enterprise Server - Commercial)
[mysqladm@prim03 shell]$
[mysqladm@prim03 shell]$  mysqlrouter --version
MySQL Router  Ver 8.4.6 for Linux on x86_64 (MySQL Community - GPL)
[mysqladm@prim03 shell]$ 

		

1.3 Backup

Take backup:

Full database backup
MySQL Router config backup
MySQL config backup

1.4 Confirm primary node

Based on the cluster status above:

prim01 = PRIMARY

2. Upgrade mysqlsh to check the util.checkForServerUpgrade

Download the software from here and copy it to the server and unzip the respective binaries.

3. Upgrade Secondary Node prim03

Always upgrade SECONDARY nodes first, in this case prim02 and prim03 and secondary nodes.

V1053959-01-8.4.8 is the binary for mysqlshell 8.4.8

			
[mysqladm@prim03 ]$ cd V1053959-01-8.4.8
[mysqladm@prim03 V1053959-01-8.4.8]$ 
[mysqladm@prim03 shell]$ sudo yum install mysql-shell-commercial-8.4.8-1.1.el9.x86_64.rpm
Updating Subscription Management repositories.
Unable to read consumer identity
 
This system is not registered with an entitlement server. You can use subscription-manager to register.
 
Last metadata expiration check: 1:44:47 ago on Mon 30 Mar 2026 07:52:06 AM +03.
Dependencies resolved.
===========================================================================================================================================================================================================================
Package                                                      Architecture                                 Version                                                Repository                                          Size
===========================================================================================================================================================================================================================
Upgrading:
mysql-shell-commercial                                       x86_64                                       8.4.8-1.1.el9                                          @commandline                                        92 M
 
Transaction Summary
===========================================================================================================================================================================================================================
Upgrade  1 Package
 
Total size: 92 M
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                                                   1/1
  Upgrading        : mysql-shell-commercial-8.4.8-1.1.el9.x86_64                                                                                                                                                       1/2
  Cleanup          : mysql-shell-commercial-8.4.6-1.1.el9.x86_64                                                                                                                                                       2/2
  Running scriptlet: mysql-shell-commercial-8.4.6-1.1.el9.x86_64                                                                                                                                                       2/2
  Verifying        : mysql-shell-commercial-8.4.8-1.1.el9.x86_64                                                                                                                                                       1/2
  Verifying        : mysql-shell-commercial-8.4.6-1.1.el9.x86_64                                                                                                                                                       2/2
Installed products updated.
 
Upgraded:
  mysql-shell-commercial-8.4.8-1.1.el9.x86_64
 
Complete!
[mysqladm@prim03 shell]$ mysqlsh --version
mysqlsh   Ver 8.4.8-commercial for Linux on x86_64 - for MySQL 8.4.8 (MySQL Enterprise Server - Commercial)
[mysqladm@prim03 shell]$

		

4. Run upgrade precheck using util.checkForServerUpgrade

If there are any errors, fix them first before moving forward.

			
MySQL  localhost:3306 ssl  JS > util.checkForServerUpgrade('clust_admin@localhost:3306',{"password":"ClusterPass123!", "targetVersion":"8.4.8", "configPath":"/etc/my.cnf"})
The MySQL server at localhost:3306, version 8.4.6-commercial - MySQL Enterprise
Server - Commercial, will now be checked for compatibility issues for upgrade
to MySQL 8.4.8.
 
1) Issues reported by 'check table x for upgrade' command (checkTableCommand)
   No issues found
 
2) Checks for foreign keys not referencing a full unique index
(foreignKeyReferences)
   No issues found
 
3) Check for deprecated or invalid user authentication methods.
(authMethodUsage)
   No issues found
Errors:   0
Warnings: 0
Notices:  0
 
No known compatibility errors or issues were found.
MySQL  localhost:3306 ssl  JS >

		

5. Upgrade MySQL binaries on secondary node prim03

			
[mysqladm@prim03 ]$ sudo systemctl stop mysqld
[mysqladm@prim03 ]$ sudo yum install mysql-commercial*
Updating Subscription Management repositories.
Unable to read consumer identity
 
This system is not registered with an entitlement server. You can use subscription-manager to register.
 
Last metadata expiration check: 2:36:00 ago on Sun 29 Mar 2026 12:25:56 PM +03.
Dependencies resolved.
===========================================================================================================================================================================================================================
Package                                                                     Architecture                             Version                                         Repository                                      Size
===========================================================================================================================================================================================================================
Installing:
mysql-commercial-backup-debuginfo                                           x86_64                                   8.4.8-1.1.el9                                   @commandline                                    20 M
mysql-commercial-client-debuginfo                                           x86_64                                   8.4.8-1.1.el9                                   @commandline                                    24 M
mysql-commercial-client-plugins-debuginfo                                   x86_64                                   8.4.8-1.1.el9                                   @commandline                                   7.2 M
mysql-commercial-debuginfo                                                  x86_64                                   8.4.8-1.1.el9                                   @commandline                                    11 M
mysql-commercial-libs-compat-debuginfo                                      x86_64                                   8.4.8-1.1.el9                                   @commandline                                   2.4 M
mysql-commercial-libs-debuginfo                                             x86_64                                   8.4.8-1.1.el9                                   @commandline                                   2.5 M
mysql-commercial-server-debug                                               x86_64                                   8.4.8-1.1.el9                                   @commandline                                    28 M
mysql-commercial-server-debug-debuginfo                                     x86_64                                   8.4.8-1.1.el9                                   @commandline                                   177 M
mysql-commercial-server-debuginfo                                           x86_64                                   8.4.8-1.1.el9                                   @commandline                                   224 M
mysql-commercial-test-debuginfo                                             x86_64                                   8.4.8-1.1.el9                                   @commandline                                    26 M
Upgrading:
mysql-commercial-backup                                                     x86_64                                   8.4.8-1.1.el9                                   @commandline                                   3.9 M
mysql-commercial-client                                                     x86_64                                   8.4.8-1.1.el9                                   @commandline                                   3.4 M
mysql-commercial-client-plugins                                             x86_64                                   8.4.8-1.1.el9                                   @commandline                                   2.3 M
mysql-commercial-common                                                     x86_64                                   8.4.8-1.1.el9                                   @commandline                                   580 k
mysql-commercial-devel                                                      x86_64                                   8.4.8-1.1.el9                                   @commandline                                   7.8 M
mysql-commercial-icu-data-files                                             x86_64                                   8.4.8-1.1.el9                                   @commandline                                   2.3 M
mysql-commercial-libs                                                       x86_64                                   8.4.8-1.1.el9                                   @commandline                                   1.5 M
mysql-commercial-libs-compat                                                x86_64                                   8.4.8-1.1.el9                                   @commandline                                   1.4 M
mysql-commercial-server                                                     x86_64                                   8.4.8-1.1.el9                                   @commandline                                    55 M
mysql-commercial-test                                                       x86_64                                   8.4.8-1.1.el9                                   @commandline                                   384 M
 
Transaction Summary
===========================================================================================================================================================================================================================
Install  10 Packages
Upgrade  10 Packages
 
Total size: 984 M
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
 
 
[mysqladm@prim03 ]$ mysqld --version
/usr/sbin/mysqld  Ver 8.4.8-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)
[mysqladm@prim03 ]$
[mysqladm@prim03 ]$ sudo systemctl start mysqld
[mysqladm@prim03 ]$ 

		

5.1 Verify node rejoins cluster

Inside mysqlsh:

cluster.status({extended:1})

6. Upgrade Secondary Node prim02

Repeat same process as mentioned in poing 3 and 5.

Verify the cluster status and notice prim02 and prim03 are now on version “8.4.8” and primary prim01 is still on version “8.4.6”.

			
MySQL  localhost:3306 ssl  JS > \c clust_admin@localhost:3306
MySQL  localhost:3306 ssl  JS > dba.getCluster().status()
{
    "clusterName": "testclust",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "prim01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "prim01:3306": {
                "address": "prim01:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.6"
            },
            "prim02:3306": {
                "address": "prim02:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            },
            "prim03:3306": {
                "address": "prim03:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "prim01:3306"
}
MySQL  localhost:3306 ssl  JS >

		

7. Switchover PRIMARY Away from prim01

Important:
You cannot always promote a higher patch-version node over a lower-version primary using cluster.setPrimaryInstance() during rolling upgrades. Community reports confirm version ordering restrictions during in-group rolling upgrades.

Best practice:

Stop Group Replication on old primary
Allow automatic election

7.1 Stop Group Replication on prim01

Connect to prim01:

			
STOP GROUP_REPLICATION;
[mysqladm@prim01 ]$ sudo systemctl stop mysqld
[mysqladm@prim01 ]$

The cluster automatically elects

prim02 OR prim03 as new PRIMARY and application should continue working through Router.

7.2 Verify new PRIMARY

On prim02 or prim03 connect to mysql shell and check the cluster status

			
MySQL  localhost:3306 ssl  JS > \c clust_admin@localhost:3306
MySQL  localhost:3306 ssl  JS > dba.getCluster().status()
{
    "clusterName": "testclust",
    "defaultReplicaSet": {
          "name": "default",
        "primary": "prim02:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE_PARTIAL",
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.",
        "topology": {
            "prim01:3306": {
                "address": "prim01:3306",
                "memberRole": "SECONDARY",
                "mode": "n/a",
                "readReplicas": {},
                "role": "HA",
                "status": "(MISSING)
            },
            "prim02:3306": {
                "address": "prim02:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            },
            "prim03:3306": {
                "address": "prim03:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "prim01:3306"
}
MySQL  localhost:3306 ssl  JS >

		

8. Upgrade Former PRIMARY prim01

Connect to prim01 and repeat same process as mentioned in poing 3 and 5.

Once completed, verify the cluster status and notice all the nodes prim01,prim02, and prim03 are now on version “8.4.8”

			
MySQL  localhost:3306 ssl  JS > dba.getCluster().status()
{
    "clusterName": "testclust",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "prim03:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "prim01:3306": {
                "address": "prim01:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            },
            "prim02:3306": {
                "address": "prim02:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            },
            "prim03:3306": {
                "address": "prim03:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "prim03:3306"
}
MySQL  localhost:3306 ssl  JS >

		

9. Upgrade MySQL Router on prodapp

			
mysqladm@prodapp ]$ sudo systemctl stop mysqlrouter
mysqladm@prodapp ]$
mysqladm@prodapp ]$ sudo yum install mysql-router-commercial*rpm
Updating Subscription Management repositories.
Unable to read consumer identity
 
This system is not registered with an entitlement server. You can use subscription-manager to register.
 
Last metadata expiration check: 0:05:20 ago on Sun 29 Mar 2026 04:19:06 PM +03.
Dependencies resolved.
===========================================================================================================================================================================================================================
Package                                                               Architecture                               Version                                           Repository                                        Size
===========================================================================================================================================================================================================================
Installing:
mysql-router-commercial                                     x86_64                                     8.4.8-1.1.el9                                     @commandline                                      39 M
 
Transaction Summary
===========================================================================================================================================================================================================================
Install  1 Package
 
Total size: 39 M
Installed size: 155 M
Is this ok [y/N]: y
Downloading Packages:
-
-
- 
Complete!
[mysqladm@prodapp shell]$
[mysqladm@prodapp shell]$ mysqlrouter --version
MySQL Router  Ver 8.4.8 for Linux on x86_64 
mysqladm@prodapp ]$ sudo systemctl start mysqlrouter
mysqladm@prodapp ]$

		

This completes the patching procedure. However, review the logs and do a healthcheck to ensure things are working as expected.

0 Comments Leave a comment

Setting up MySQL InnoDB Multi-DC ClusterSet

So far, we have built a highly available 3-node MySQL 8.4 InnoDB Cluster with MySQL Router handling intelligent application routing. You may refer it here

But what happens when your infrastructure requirements evolve further?

What if you need:

Disaster Recovery (DR)
Cross-region replication
Geo-distributed reads
Regional failover
Data center isolation
Multi-site redundancy

This is where Replica Clusters become incredibly powerful.

MySQL 8.4 allows you to extend an existing InnoDB Cluster into a ClusterSet-style topology using:

cluster.createReplicaCluster()

This enables an entirely separate InnoDB Cluster to replicate asynchronously from the primary cluster while still preserving high availability within each site.

What Is a Replica Cluster?

A Replica Cluster is:

a fully independent InnoDB Cluster
with its own Group Replication quorum
its own PRIMARY node
its own failover handling
asynchronously replicating from another cluster

Think of it as:

“A cluster replicating from another cluster.”

Component	Purpose
Primary Cluster	Main production workload
Replica Cluster	DR / remote site / reporting / geo-redundancy

Prod Site

Host	Role
prim01	PRIMARY
prim02	SECONDARY
prim03	SECONDARY

DR Site

Host	Role
stand01	PRIMARY
stand02	SECONDARY
stand03	SECONDARY

Router Nodes

Host	Role
prodapp	Primary Router
drapp	DR Router

Why Replica Clusters Matter

Traditional async replication usually introduces:

single-node replicas
fragile failover processes
replication management complexity

Replica Clusters solve this elegantly.

Benefits

Site-Level High Availability

Even if an entire DR node fails, the DR cluster remains operational.

Regional Disaster Recovery

Protect against:

data center outages
network isolation
cloud region failures

Read Scaling Across Regions

Remote users can read from geographically closer replica clusters.

Reduced Operational Complexity

You manage clusters instead of dozens of individual replicas.

Preparing the Replica Cluster

Before creating a Replica Cluster, you must first build another standalone InnoDB Cluster.

This means repeating the earlier setup steps on:

stand01
stand02
stand03

Including:

MySQL installation
GTID configuration
MySQL Shell setup
Group Replication configuration
Cluster creation

Create the DR InnoDB ClusterSet

Connect to the primary prod instance and check the cluster status:

			
[vagrant@prim01 ~]$ mysqlsh --js
MySQL Shell 8.4.9
MySQL  JS > \c clust_admin@localhost:3306
Creating a session to 'clust_admin@localhost:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 12
Server version: 8.4.8 Source distribution
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3306 ssl  JS >
 MySQL  localhost:3306 ssl  JS > var cluster=dba.getCluster()
 MySQL  localhost:3306 ssl  JS > cluster.status();
{
    "clusterName": "prod",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "prim01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "prim01:3306": {
                "address": "prim01:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            },
            "prim02:3306": {
                "address": "prim02:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            },
            "prim03:3306": {
                "address": "prim03:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.9"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "prim01:3306"
}
 MySQL  localhost:3306 ssl  JS >

		

Now lets create a Replica Cluster “DRCluster”



MySQL  localhost:3306 ssl  JS > cluster.createReplicaCluster('clust_admin@stand01:3306', 'DRCluster', {recoveryMethod: 'clone'});
Setting up replica 'DRCluster' of cluster 'prod' at instance 'Stand01:3306'.

A new InnoDB Cluster will be created on instance 'Stand01:3306'.

Validating instance configuration at stand01:3306...

This instance reports its own address as Stand01:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'Stand01:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

* Checking transaction state of the instance...

* Checking connectivity and SSL configuration to PRIMARY Cluster...

Monitoring Clone based state recovery of the new member. Press ^C to abort the operation.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: Stand01:3306 is being cloned from prim01:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed
* Clone process has finished: 73.68 MB transferred in about 1 second (~73.68 MB/s)

Creating InnoDB Cluster 'DRCluster' on 'Stand01:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

Cluster "memberAuthType" is set to 'PASSWORD' (inherited from the ClusterSet).
* Configuring ClusterSet managed replication channel...
** Changing replication source of Stand01:3306 to prim01:3306

* Waiting for instance 'Stand01:3306' to synchronize with PRIMARY Cluster...
** Transactions replicated  ############################################################  100%

* Updating topology

* Waiting for the Cluster to synchronize with the PRIMARY Cluster...
** Transactions replicated  ############################################################  100%

Replica Cluster 'DRCluster' successfully created on ClusterSet 'ProdClusterSet'.

<Cluster:DRCluster>
 MySQL  localhost:3306 ssl  JS >

What Happens Internally?

This single command performs a huge amount of orchestration automatically.

Internally MySQL Will:

validate DR cluster health
establish asynchronous replication
configure recovery channels
synchronize GTID positions
create replication accounts
register cluster metadata
establish ClusterSet relationships

This is dramatically simpler than manually configuring:

asynchronous replication
relay logs
replication users
failover metadata
topology management

Connect to DR cluster’s primary node “stand01” and check the cluster status of DR

 MySQL  localhost:3306 ssl  JS > \c clust_admin@stand01:3306
Creating a session to 'clust_admin@stand01:3306'
Please provide the password for 'clust_admin@stand01:3306': ***********
Save password for 'clust_admin@stand01:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 846
Server version: 8.4.9 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  stand01:3306 ssl  JS > var dr = dba.getCluster()
 MySQL  stand01:3306 ssl  JS > dr.status()
{
    "clusterName": "DRCluster",
    "clusterRole": "REPLICA",
    "clusterSetReplicationStatus": "OK",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "Stand01:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "Stand01:3306": {
                "address": "Stand01:3306",
                "memberRole": "PRIMARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLagFromImmediateSource": "",
                "replicationLagFromOriginalSource": "",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.9"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "domainName": "ProdClusterSet",
    "groupInformationSourceMember": "Stand01:3306",
    "metadataServer": "prim01:3306"
}
 MySQL  stand01:3306 ssl  JS >

So far only Stand01 node is the part of DRCluster. Lets add Node02 “Stand02”

 MySQL  stand01:3306 ssl  JS > dr.addInstance('clust_admin@stand02:3306',{recoveryMethod: 'clone'})

Clone based recovery selected through the recoveryMethod option

Validating instance configuration at stand02:3306...

This instance reports its own address as Stand02:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'Stand02:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

A new instance will be added to the InnoDB Cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

* Waiting for the Cluster to synchronize with the PRIMARY Cluster...
** Transactions replicated  ############################################################  100%

* Configuring ClusterSet managed replication channel...
** Changing replication source of Stand02:3306 to prim01:3306

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: Stand02:3306 is being cloned from stand01:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: Stand02:3306 is shutting down...

* Waiting for server restart... ready
* Stand02:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 80.00 MB transferred in about 1 second (~80.00 MB/s)

State recovery already finished for 'Stand02:3306'

The instance 'Stand02:3306' was successfully added to the cluster.

 MySQL  stand01:3306 ssl  JS >

Add Node03 “Stand03”

 MySQL  stand01:3306 ssl  JS > dr.addInstance('clust_admin@stand03:3306',{recoveryMethod: 'clone'})

Clone based recovery selected through the recoveryMethod option

Validating instance configuration at stand03:3306...

This instance reports its own address as Stand03:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'Stand03:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

A new instance will be added to the InnoDB Cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

* Waiting for the Cluster to synchronize with the PRIMARY Cluster...
** Transactions replicated  ############################################################  100%


* Configuring ClusterSet managed replication channel...
** Changing replication source of Stand03:3306 to prim01:3306

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: Stand03:3306 is being cloned from stand02:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: Stand03:3306 is shutting down...

* Waiting for server restart... ready
* Stand03:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 79.96 MB transferred in about 1 second (~79.96 MB/s)

State recovery already finished for 'Stand03:3306'

The instance 'Stand03:3306' was successfully added to the cluster.

 MySQL  stand01:3306 ssl  JS >

Check the cluster state now:

 MySQL  stand01:3306 ssl  JS > var cluster = dba.getClusterSet()
 MySQL  stand01:3306 ssl  JS > cluster.status({extended:1})
{
    "clusters": {
        "DRCluster": {
            "clusterRole": "REPLICA",
            "clusterSetReplication": {
                "applierStatus": "APPLIED_ALL",
                "applierThreadState": "Waiting for an event from Coordinator",
                "applierWorkerThreads": 4,
                "receiver": "Stand01:3306",
                "receiverStatus": "ON",
                "receiverThreadState": "Waiting for source to send event",
                "replicationSsl": "TLS_AES_128_GCM_SHA256 TLSv1.3",
                "replicationSslMode": "REQUIRED",
                "source": "prim01:3306"
            },
            "clusterSetReplicationStatus": "OK",
            "globalStatus": "OK",
            "status": "OK",
            "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
            "topology": {
                "Stand01:3306": {
                    "address": "Stand01:3306",
                    "memberRole": "PRIMARY",
                    "mode": "R/O",
                    "readReplicas": {},
                    "replicationLagFromImmediateSource": "",
                    "replicationLagFromOriginalSource": "",
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.4.9"
                },
                "Stand02:3306": {
                    "address": "Stand02:3306",
                    "memberRole": "SECONDARY",
                    "mode": "R/O",
                    "readReplicas": {},
                    "replicationLagFromImmediateSource": "",
                    "replicationLagFromOriginalSource": "",
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.4.9"
                },
                "Stand03:3306": {
                    "address": "Stand03:3306",
                    "memberRole": "SECONDARY",
                    "mode": "R/O",
                    "readReplicas": {},
                    "replicationLagFromImmediateSource": "",
                    "replicationLagFromOriginalSource": "",
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.4.9"
                }
            },
            "transactionSet": "62d91734-599f-11f1-9160-000c29008120:1-172,88dca1c7-5927-11f1-bfd4-000c29008120:1-4",
            "transactionSetConsistencyStatus": "OK",
            "transactionSetErrantGtidSet": "",
            "transactionSetMissingGtidSet": ""
        },
        "prod": {
            "clusterRole": "PRIMARY",
            "globalStatus": "OK",
            "primary": "prim01:3306",
            "status": "OK",
            "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
            "topology": {
                "prim01:3306": {
                    "address": "prim01:3306",
                    "memberRole": "PRIMARY",
                    "mode": "R/W",
                    "readReplicas": {},
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.4.8"
                },
                "prim02:3306": {
                    "address": "prim02:3306",
                    "memberRole": "SECONDARY",
                    "mode": "R/O",
                    "readReplicas": {},
                    "replicationLagFromImmediateSource": "",
                    "replicationLagFromOriginalSource": "",
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.4.8"
                },
                "prim03:3306": {
                    "address": "prim03:3306",
                    "memberRole": "SECONDARY",
                    "mode": "R/O",
                    "readReplicas": {},
                    "replicationLagFromImmediateSource": "",
                    "replicationLagFromOriginalSource": "",
                    "role": "HA",
                    "status": "ONLINE",
                    "version": "8.4.9"
                }
            },
            "transactionSet": "62d91734-599f-11f1-9160-000c29008120:1-172,88dca1c7-5927-11f1-bfd4-000c29008120:1-4"
        }
    },
    "domainName": "ProdClusterSet",
    "globalPrimaryInstance": "prim01:3306",
    "metadataServer": "prim01:3306",
    "primaryCluster": "prod",
    "status": "HEALTHY",
    "statusText": "All Clusters available."
}
 MySQL  stand01:3306 ssl  JS >

 MySQL  stand01:3306 ssl  JS > dba.getCluster("prod").status({extended:1})
{
    "clusterName": "prod",
    "clusterRole": "PRIMARY",
    "defaultReplicaSet": {
        "GRProtocolVersion": "8.0.27",
        "communicationStack": "MYSQL",
        "groupName": "62d91734-599f-11f1-9160-000c29008120",
        "groupViewChangeUuid": "AUTOMATIC",
        "groupViewId": "17798676523160962:15",
        "name": "default",
        "paxosSingleLeader": "OFF",
        "primary": "prim01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "prim01:3306": {
                "address": "prim01:3306",
                "applierWorkerThreads": 4,
                "fenceSysVars": [],
                "memberId": "88dca1c7-5927-11f1-bfd4-000c29008120",
                "memberRole": "PRIMARY",
                "memberState": "ONLINE",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            },
            "prim02:3306": {
                "address": "prim02:3306",
                "applierWorkerThreads": 4,
                "fenceSysVars": [
                    "read_only",
                    "super_read_only"
                ],
                "memberId": "0a53de89-5921-11f1-a23f-000c2902182d",
                "memberRole": "SECONDARY",
                "memberState": "ONLINE",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLagFromImmediateSource": "",
                "replicationLagFromOriginalSource": "",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            },
            "prim03:3306": {
                "address": "prim03:3306",
                "applierWorkerThreads": 4,
                "fenceSysVars": [
                    "read_only",
                    "super_read_only"
                ],
                "memberId": "052d5aea-5921-11f1-9505-000c290a23a7",
                "memberRole": "SECONDARY",
                "memberState": "ONLINE",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLagFromImmediateSource": "",
                "replicationLagFromOriginalSource": "",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.9"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "domainName": "ProdClusterSet",
    "groupInformationSourceMember": "prim01:3306",
    "metadataVersion": "2.2.0"
}
 MySQL  stand01:3306 ssl  JS > dba.getCluster("DRCluster").status({extended:1})
{
    "clusterName": "DRCluster",
    "clusterRole": "REPLICA",
    "clusterSetReplicationStatus": "OK",
    "defaultReplicaSet": {
        "GRProtocolVersion": "8.0.27",
        "communicationStack": "MYSQL",
        "groupName": "8036f0bb-5a0a-11f1-bdae-000c2982f4cd",
        "groupViewChangeUuid": "AUTOMATIC",
        "groupViewId": "17799136691478461:7",
        "name": "default",
        "paxosSingleLeader": "OFF",
        "primary": "Stand01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "Stand01:3306": {
                "address": "Stand01:3306",
                "applierWorkerThreads": 4,
                "fenceSysVars": [
                    "read_only",
                    "super_read_only"
                ],
                "memberId": "27c01409-5921-11f1-99dd-000c2982f4cd",
                "memberRole": "PRIMARY",
                "memberState": "ONLINE",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLagFromImmediateSource": "",
                "replicationLagFromOriginalSource": "",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.9"
            },
            "Stand02:3306": {
                "address": "Stand02:3306",
                "applierWorkerThreads": 4,
                "fenceSysVars": [
                    "read_only",
                    "super_read_only"
                ],
                "memberId": "46295f82-5921-11f1-abf2-000c29dac10b",
                "memberRole": "SECONDARY",
                "memberState": "ONLINE",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLagFromImmediateSource": "",
                "replicationLagFromOriginalSource": "",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.9"
            },
            "Stand03:3306": {
                "address": "Stand03:3306",
                "applierWorkerThreads": 4,
                "fenceSysVars": [
                    "read_only",
                    "super_read_only"
                ],
                "memberId": "15a97e8f-5921-11f1-ae16-000c297ffab0",
                "memberRole": "SECONDARY",
                "memberState": "ONLINE",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLagFromImmediateSource": "",
                "replicationLagFromOriginalSource": "",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.9"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "domainName": "ProdClusterSet",
    "groupInformationSourceMember": "Stand01:3306",
    "metadataServer": "prim01:3306",
    "metadataVersion": "2.2.0"
}
 MySQL  stand01:3306 ssl  JS >

DRCluster completes. Now on DR app host Bootstrap router AGAINST DRCluster.


[vagrant@drapp ~]$ sudo mkdir -p /mysqlrouter/
[vagrant@drapp ~]$ id mysqlrouter
uid=994(mysqlrouter) gid=990(mysqlrouter) groups=990(mysqlrouter)
[vagrant@drapp ~]$ sudo chown mysqlrouter:mysqlrouter /mysqlrouter/
[vagrant@drapp ~]$
[vagrant@drapp ~]$ sudo mysqlrouter --bootstrap clust_admin@stand01:3306 -d /mysqlrouter --account router_admin --user=mysqlrouter

Please enter MySQL password for clust_admin:
# Bootstrapping MySQL Router 8.4.9 (MySQL Community - GPL) instance at '/mysqlrouter'...

Please enter MySQL password for router_admin:
- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /mysqlrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'DRCluster'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /mysqlrouter/mysqlrouter.conf

InnoDB Cluster 'DRCluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447
- Read/Write Split Connections: localhost:6450

## MySQL X protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449

[vagrant@drapp ~]$ ls -ltr /mysqlrouter/
total 16
drwx------. 2 mysqlrouter mysqlrouter    6 May 27 19:19 run
-rw-------. 1 mysqlrouter mysqlrouter   83 May 27 19:19 mysqlrouter.key
-rwx------. 1 mysqlrouter mysqlrouter  146 May 27 19:19 stop.sh
-rwx------. 1 mysqlrouter mysqlrouter  294 May 27 19:19 start.sh
-rw-------. 1 mysqlrouter mysqlrouter 2308 May 27 19:19 mysqlrouter.conf
drwx------. 2 mysqlrouter mysqlrouter   29 May 27 19:19 log
drwx------. 2 mysqlrouter mysqlrouter  116 May 27 19:19 data
[vagrant@drapp ~]$

Expected Router Behavior

PROD router

			
RW -> prod cluster
RO -> prod replicas

DR router BEFORE failover

			
RW port -> rejects writes
RO port -> works

Because DRCluster is only-replica.

0 Comments Leave a comment

Building a 3-Node MySQL 8.4 InnoDB Cluster with MySQL Router

High availability is no longer optional for modern applications. Whether you’re running mission-critical workloads, customer-facing applications, or internal enterprise systems, database resilience and failover capability are essential.

In this walkthrough, we’ll build a 3-node MySQL 8.4 InnoDB Cluster with MySQL Router acting as the application connectivity layer. By the end, you’ll have a production-grade MySQL topology capable of automatic failover, read/write splitting, and simplified client connectivity.

In this we’ll cover:

Preparing MySQL nodes
Configuring InnoDB Cluster
Enabling Group Replication
Adding secondary nodes
Deploying MySQL Router
Exposing HA endpoints to applications

Architecture Overview

Our setup consists of:

Hostname	IP Address	Role
prim01	192.168.122.152	Primary Node
prim02	192.168.122.140	Secondary Node
prim03	192.168.122.139	Secondary Node
prodapp	192.168.122.155	MySQL Router Host

The deployment uses:

MySQL 8.4 LTS
Group Replication
Single-Primary topology
MySQL Shell
MySQL Router

Applications connect only to the Router layer, while Router intelligently forwards traffic to the correct backend node.

Step 1 — Prepare Storage Layout

On each database node, create dedicated directories for:

Data files
Binary logs
General logs
Slow query logs

			
sudo mkdir -p /mysqldata
sudo mkdir -p /mysqlbinlogs
sudo mkdir -p /mysqllogs
sudo chown mysql:mysql /mysqldata /mysqlbinlogs /mysqllogs/

This separation improves:

operational clarity
backup strategy
log rotation management
disk performance tuning

You should also verify the MySQL OS account:

			
[vagrant@prim01 ~]$ id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)

Step 2 – Install MySQL on each node of the cluster

MySQL binary installation and initilization has been already covered in previous blog here.

Step 3 – Configure MySQL

Update /etc/my.cnf on each node.

			
datadir=/mysqldata
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_bin=/mysqlbinlogs/binlogs
general_log=ON
general_log_file=/mysqllogs/general_prim01.log
slow_query_log_file=/mysqllogs/slow_prim01.log

		

Binary Logging

Binary logs are mandatory for:

replication
point-in-time recovery
Group Replication

Slow Query Logging

Enabling slow logs early helps identify:

missing indexes
inefficient queries
performance bottlenecks

General Logs

Useful during:

troubleshooting
connection debugging
authentication investigations

Step 4 – Validate Standalone MySQL

Before clustering, confirm MySQL is healthy.

			
[vagrant@prim01 ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1436
Server version: 8.4.8 Source distribution
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

		

Also verify:

MySQL service status
error logs
startup warnings

Step 5 – Install MySQL Shell

MySQL Shell is the orchestration tool for InnoDB Cluster management.

Install it on all cluster nodes:

			
[vagrant@prim01 ~]$ sudo yum install mysql-shell*
Last metadata expiration check: 0:11:31 ago on Tue 26 May 2026 12:47:11 PM UTC.
Dependencies resolved.
=============================================================================================================================================================================================
 Package                                            Architecture                      Version                                 Repository                                                Size
=============================================================================================================================================================================================
Installing:
 [1m[32mmysql-shell                                       (B[m x86_64                            8.4.9-1.el8                             mysql-tools-8.4-lts-community                             93 M
 [1m[32mmysql-shell-debugsource                           (B[m x86_64                            8.4.9-1.el8                             mysql-tools-8.4-lts-community                            2.2 M
Transaction Summary
=============================================================================================================================================================================================
Install  2 Packages
Total download size: 95 M
Installed size: 506 M
Is this ok [y/N]: y
...
..
.
Installed:
  mysql-shell-8.4.9-1.el8.x86_64                                                          mysql-shell-debugsource-8.4.9-1.el8.x86_64                                                         
Complete!

		

Step 6 – Configure the Instance for InnoDB Cluster

Launch MySQL Shell:

			
[vagrant@prim01 ~]$ mysqlsh --js
MySQL Shell 8.4.9
Copyright (c) 2016, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS >

		

Step 7 – Create Cluster Admin account

This can be done using the command

dba.configureInstance(‘root:password@localhost:3306’,{‘clusterAdmin’:’your_user’})

			
MySQL  JS >  dba.configureInstance('root@localhost:3306',{'clusterAdmin':'clust_admin'})
Configuring local MySQL instance listening at port 3306 for use in an InnoDB Cluster...
This instance reports its own address as prim01:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Assuming full account name 'clust_admin'@'%' for clust_admin
Password for new account: ***********
Confirm password: ***********
applierWorkerThreads will be set to the default value of 4.
NOTE: Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+
Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Creating user clust_admin@%.
Account clust_admin@% was successfully created.
Configuring instance...
The instance 'prim01:3306' was configured to be used in an InnoDB Cluster.
Restarting MySQL...
NOTE: MySQL server at prim01:3306 was restarted.
 MySQL  JS >

		

Step 8 – Validate Instance Configuration

 MySQL  JS > dba.checkInstanceConfiguration('clust_admin@localhost:3306')
Please provide the password for 'clust_admin@localhost:3306': ***********
Save password for 'clust_admin@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Validating local MySQL instance listening at port 3306 for use in an InnoDB Cluster...

This instance reports its own address as prim01:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 'prim01:3306' is valid for InnoDB Cluster usage.

{
    "status": "ok"
}
 MySQL  JS >

At this point, the instance is ready to participate in the cluster.

Step 9 – Repeat Configuration on All Nodes

Repeat Steps 1–8 on all the cluster nodes, in this case on prim02 and prim03

Step 10 – Create the InnoDB Cluster

Connect to the first node “prim01” using the cluster admin account and create an innodb cluster with the name “prod”

			
[vagrant@prim01 ~]$ mysqlsh --js
MySQL Shell 8.4.9
MySQL  JS > \c clust_admin@localhost:3306
Creating a session to 'clust_admin@localhost:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 12
Server version: 8.4.8 Source distribution
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3306 ssl  JS >
  MySQL  localhost:3306 ssl  JS > var cluster = dba.createCluster('prod')
A new InnoDB Cluster will be created on instance 'prim01:3306'.
Validating instance configuration at localhost:3306...
This instance reports its own address as prim01:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'prim01:3306'. Use the localAddress option to override.
* Checking connectivity and SSL configuration...
Creating InnoDB Cluster 'prod' on 'prim01:3306'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
 MySQL  localhost:3306 ssl  JS > 
 

		

What Happens Internally?

When the cluster is created:

Group Replication is initialized
metadata schemas are created
recovery accounts are provisioned
replication channels are established

At this stage, only one node exists in the cluster.

Step 11 – Add Secondary Nodes

Add the second node:

 MySQL  localhost:3306 ssl  JS > cluster.addInstance('clust_admin@prim02:3306')

NOTE: The target instance 'prim02:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'prim02:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
Validating instance configuration at prim02:3306...

This instance reports its own address as prim02:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'prim02:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

A new instance will be added to the InnoDB Cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: prim02:3306 is being cloned from prim01:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: prim02:3306 is shutting down...

* Waiting for server restart... ready
* prim02:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 74.31 MB transferred in about 1 second (~74.31 MB/s)

State recovery already finished for 'prim02:3306'

The instance 'prim02:3306' was successfully added to the cluster.

 MySQL  localhost:3306 ssl  JS >

Then add the third node:

 MySQL  localhost:3306 ssl  JS > cluster.addInstance('clust_admin@prim03:3306')

NOTE: The target instance 'prim03:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'prim03:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
WARNING: The instance 'prim03:3306' is only read compatible with the cluster, thus it will join the cluster in R/O mode.
Validating instance configuration at prim03:3306...

This instance reports its own address as prim03:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'prim03:3306'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

A new instance will be added to the InnoDB Cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: prim03:3306 is being cloned from prim02:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed


NOTE: prim03:3306 is shutting down...

* Waiting for server restart... ready
* prim03:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 73.65 MB transferred in about 1 second (~73.65 MB/s)

State recovery already finished for 'prim03:3306'

The instance 'prim03:3306' was successfully added to the cluster.

 MySQL  localhost:3306 ssl  JS >

Clone-Based Recovery

MySQL Shell recommends Clone Recovery for provisioning. This is one of the most powerful features in MySQL 8.x clustering.

Traditional replication setup often requires:

backups
restores
binlog positioning
manual synchronization

Clone recovery eliminates all of that.

The donor node physically transfers data files to the joining node automatically.

Step 12 – Verify Cluster Status

Retrieve cluster status:

			
MySQL  localhost:3306 ssl  JS > var cluster=dba.getCluster()
 MySQL  localhost:3306 ssl  JS > cluster.status();
{
    "clusterName": "prod",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "prim01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "prim01:3306": {
                "address": "prim01:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            },
            "prim02:3306": {
                "address": "prim02:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.8"
            },
            "prim03:3306": {
                "address": "prim03:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.9"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "prim01:3306"
}
 MySQL  localhost:3306 ssl  JS >

		

This confirms cluster is up and healthy.

Step 13 – Deploying MySQL Router

Without MySQL Router:

applications must track failovers
clients need topology awareness
connection strings become complex

Router abstracts all cluster intelligence away from applications.

Step 14 – Install MySQL Router

On the application host install the router binaries.

			
[vagrant@prodapp ~]$ sudo dnf install -y mysql-router
MySQL Connectors Community                                                                                                                                   163 kB/s | 193 kB     00:01
MySQL 8.4 LTS Community Server                                                                                                                               1.4 MB/s | 1.9 MB     00:01
MySQL Tools 8.4 LTS Community                                                                                                                                838 kB/s | 1.0 MB     00:01
Dependencies resolved.
=============================================================================================================================================================================================
 Package                                           Architecture                      Version                                  Repository                                                Size
=============================================================================================================================================================================================
Installing:
 mysql-router-community                            x86_64                            8.4.9-1.el8                              mysql-tools-8.4-lts-community                            5.2 M
Transaction Summary
=============================================================================================================================================================================================
Install  1 Package
Total download size: 5.2 M
Installed size: 21 M
Downloading Packages:
mysql-router-community-8.4.9-1.el8.x86_64.rpm                                                                                                                2.8 MB/s | 5.2 MB     
Installed:
  mysql-router-community-8.4.9-1.el8.x86_64
Complete!
Note: By default router is installed under /usr/bin
[vagrant@prodapp ~]$

		

Verify installation: By default router is installed under /usr/bin

			
[vagrant@prodapp ~]$ which mysqlrouter
/usr/bin/mysqlrouter
[vagrant@prodapp ~]$

Step 15 – Prepare Router Directory

			
[vagrant@prodapp ~]$ sudo mkdir -p /mysqlrouter/
[vagrant@prodapp ~]$ id mysqlrouter
uid=994(mysqlrouter) gid=990(mysqlrouter) groups=990(mysqlrouter)
[vagrant@prodapp ~]$ sudo chown mysqlrouter:mysqlrouter /mysqlrouter/
[vagrant@prodapp ~]$

		

Step 16 – Configure Host Resolution on Router Host

Update /etc/hosts:

			
[vagrant@prodapp ~]$ sudo vi /etc/hosts
168.122.152         prim01  ### Node01
168.122.140         prim02  ### Node02
168.122.139         prim03  ### Node03
168.122.155		    prodapp ### Router

		

Step 17 – Bootstrap MySQL Router

This is where the magic happens.

			
[vagrant@prodapp ~]$ sudo mysqlrouter --bootstrap clust_admin@prim01:3306 -d /mysqlrouter --account router_admin --user=mysqlrouter
Please enter MySQL password for clust_admin:
# Bootstrapping MySQL Router 8.4.9 (MySQL Community - GPL) instance at '/mysqlrouter'...
Please enter MySQL password for router_admin:
- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /mysqlrouter/mysqlrouter.conf
# MySQL Router configured for the InnoDB Cluster 'prod'
After this MySQL Router has been started with the generated configuration
    $ mysqlrouter -c /mysqlrouter/mysqlrouter.conf
InnoDB Cluster 'prod' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447
- Read/Write Split Connections: localhost:6450
## MySQL X protocol
- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449
[vagrant@prodapp ~]$ ls -ltr /mysqlrouter/
total 16
drwx------. 2 mysqlrouter mysqlrouter    6 May 27 19:19 run
-rw-------. 1 mysqlrouter mysqlrouter   83 May 27 19:19 mysqlrouter.key
-rwx------. 1 mysqlrouter mysqlrouter  146 May 27 19:19 stop.sh
-rwx------. 1 mysqlrouter mysqlrouter  294 May 27 19:19 start.sh
-rw-------. 1 mysqlrouter mysqlrouter 2308 May 27 19:19 mysqlrouter.conf
drwx------. 2 mysqlrouter mysqlrouter   29 May 27 19:19 log
drwx------. 2 mysqlrouter mysqlrouter  116 May 27 19:19 data
[vagrant@prodapp ~]$

		

Router automatically:

discovers the cluster
creates internal accounts
generates configuration
configures routing ports
stores credentials securely

Router Endpoints

After bootstrap, Router exposes multiple ports.

Purpose	Port
Read/Write	6446
Read-Only	6447
Read/Write Split	6450
X Protocol RW	6448
X Protocol RO	6449

Step 18 – Enable Router AutoStart

Enable startup on boot:

			
[vagrant@prodapp ~]$ ps -ef |grep mysqlrouter
vagrant    36309   35747  0 19:22 pts/1    00:00:00 grep --color=auto mysqlrouter
[vagrant@prodapp ~]$ sudo systemctl enable mysqlrouter
Created symlink /etc/systemd/system/multi-user.target.wants/mysqlrouter.service → /usr/lib/systemd/system/mysqlrouter.service.
[vagrant@prodapp ~]$ sudo systemctl start mysqlrouter
[vagrant@prodapp ~]$
[vagrant@prodapp ~]$ sudo systemctl status mysqlrouter
● mysqlrouter.service - MySQL Router
   Loaded: loaded (/usr/lib/systemd/system/mysqlrouter.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2026-05-27 19:22:53 UTC; 5s ago
 Main PID: 36335 (mysqlrouter)
   Status: "running"
    Tasks: 4 (limit: 12193)
   Memory: 3.4M
   CGroup: /system.slice/mysqlrouter.service
           └─36335 /usr/bin/mysqlrouter
May 27 19:22:53 prodapp systemd[1]: Starting MySQL Router...
May 27 19:22:53 prodapp systemd[1]: Started MySQL Router.
[vagrant@prodapp ~]$
[vagrant@prodapp ~]$ ps -ef |grep mysqlrouter
mysqlro+   36335       1  0 19:22 ?        00:00:00 /usr/bin/mysqlrouter
vagrant    36344   35747  0 19:23 pts/1    00:00:00 grep --color=auto mysqlrouter
[vagrant@prodapp ~]$

		

Point your application to Router Host/IP and Port as follow:

Read/Write Connections: 6446
Read/Only Connections: 6447
Read/Write Split Connections: 6450

0 Comments Leave a comment

Fixing Oracle RAC 19c Cluster State Stuck in “ROLLING PATCH” (DBAAS-70289)

[FATAL] [DBAAS-70289] Cluster is not in NORMAL or FORCED state. Current detected state is: ‘ROLLING PATCH’. ACTION: Cluster should be in NORMAL or FORCED state to perform the current operation.

			
[FATAL] [DBAAS-70289] Cluster is not in NORMAL or FORCED state.
Current detected state is: 'ROLLING PATCH'.
ACTION: Cluster should be in NORMAL or FORCED state to perform the current operation.

This issue occurs when Oracle Clusterware still believes the cluster is in a rolling patching state, even though patching activities may already be completed successfully.

In this article, I’ll explain how I verified the cluster state and fixed the issue on both RAC nodes.

Environment

Oracle RAC 19c
2-Node Cluster
Grid Infrastructure Version: 19.0.0.0.0

Problem Symptoms

The cluster state was showing:

The cluster upgrade state is [ROLLING PATCH]

This prevented further DBAAS or cluster-related operations from completing successfully.

Step 1: Verify Clusterware Release Version

Run the following command on both nodes as the grid user.

Node01

			
[grid@drvm01 ~]$ crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [19.0.0.0.0]

Node02

			
[grid@drvm02 ~]$ crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [19.0.0.0.0]

Step 2: Check Current Cluster State

Node01

			
[grid@drvm01 ~]$ crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [19.0.0.0.0].
The cluster upgrade state is [ROLLING PATCH].
The cluster active patch level is [4036886287].

Node02

			
[grid@drvm02 ~]$ crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [19.0.0.0.0].
The cluster upgrade state is [ROLLING PATCH].
The cluster active patch level is [4036886287].

At this stage, the cluster was clearly stuck in ROLLING PATCH mode.

Step 3: Verify Installed Grid Infrastructure Patches

Run the following command to verify applied patches on Node01

			
[grid@drvm01 ~]$ kfod op=patches
---------------
List of Patches
===============
34697081
36758186
37860476
37960098
37962938
37962946
38162614

		

Verify the output from Node02

			
[grid@drvm02 ~]$ kfod op=patches
---------------
List of Patches
===============
34697081
36758186
37860476
37960098
37962938
37962946
38162614

		

The patch lists were identical on both nodes, confirming patch consistency across the cluster.

Step 4: Reset Cluster State from ROLLING PATCH to NORMAL

After confirming both nodes had the same patch inventory, the cluster state can be corrected by executing the following command.

crsctl stop rollingpatch

Note: This command resets the cluster upgrade state from ROLLING PATCH to NORMAL.

Step 5: Validate the Cluster State Again

Node01

			
[grid@drvm01 ~]$ crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [19.0.0.0.0].
The cluster upgrade state is [NORMAL].
The cluster active patch level is [4036886287].

Node02

			
[grid@drvm02 ~]$ crsctl query crs activeversion -f
Oracle Clusterware active version on the cluster is [19.0.0.0.0].
The cluster upgrade state is [NORMAL].
The cluster active patch level is [4036886287].

The cluster state successfully changed to NORMAL.

Root Cause

This issue generally occurs when:

Rolling patching was interrupted
Post-patching steps were skipped
Clusterware metadata was not updated correctly
One of the nodes was rebooted unexpectedly during patching

Even though all patches are correctly applied, Oracle Clusterware may still maintain the cluster state as ROLLING PATCH.

0 Comments Leave a comment

Understanding RMAN Backup Optimization and Why PDB$SEED Datafiles Are Skipped in Oracle 19c

In Oracle 19c multitenant environments, you may notice an interesting behavior while taking RMAN backups with:

CONFIGURE BACKUP OPTIMIZATION ON;

When backup optimization is enabled, RMAN often skips backing up the PDB$SEED datafiles if they were already backed up previously. However, when backup optimization is disabled or when using the FORCE option, RMAN backs up all datafiles including PDB$SEED.

This behavior is expected and is part of RMAN’s optimization logic. In this article, we will explore:

What RMAN backup optimization does
Why PDB$SEED gets skipped
How BACKUP FORCE changes the behavior
Practical examples
Important considerations for DBAs

What Is RMAN Backup Optimization?

RMAN backup optimization is a feature that prevents Oracle from backing up files that already have a sufficient backup according to the configured retention policy.

It can be enabled using:

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

When enabled, RMAN checks whether a datafile, archived redo log, or backup set already exists and satisfies the backup requirements. If yes, RMAN skips it to save:

Backup time
Storage space
Network bandwidth

This is especially useful in large environments with many unchanged files.

Understanding the Role of PDB$SEED

In a multitenant database architecture, PDB$SEED is a template pluggable database used for creating new PDBs.

Characteristics of PDB$SEED:

Read-only by default
Rarely changes
Shared template for PDB creation
Contains system metadata and seed structures

Since the seed database remains mostly unchanged, its datafiles often remain identical across backups.

Why RMAN Skips PDB$SEED Datafiles

Suppose you run a backup like this:

BACKUP DATABASE PLUS ARCHIVELOG;

with backup optimization enabled.

RMAN evaluates each datafile and determines whether it already has a valid backup. Since PDB$SEED datafiles typically do not change, RMAN identifies them as already backed up and skips them.

You may see messages similar to:

			
skipping datafile 2; already backed up 2 time(s)
skipping datafile 4; already backed up 2 time(s)
skipping datafile 6; already backed up 2 time(s)

These datafiles usually belong to PDB$SEED.
This behavior is completely normal.

			
SQL> alter session set container=pdb$seed;
 
Session altered.
 
SQL> show con_id
 
CON_ID
------------------------------
2
SQL> select file#,name from V$datafile;
 
     FILE# NAME
---------- ------------------------------------------------------------------------------------------
         2 +DATAC9/TESTPOC/DATAFILE/system.423.1223460133
         4 +DATAC9/TESTPOC/DATAFILE/sysaux.425.1223460133
         6 +DATAC9/TESTPOC/DATAFILE/undotbs1.424.1223460135
 
SQL>

		

When Backup Optimization Enabled

Changed datafiles are backed up
New archive logs are backed up
Unchanged PDB$SEED datafiles are skipped

This improves backup efficiency.

Why BACKUP FORCE Includes PDB$SEED Again

If you run:

BACKUP FORCE DATABASE;

RMAN ignores optimization checks and backs up everything regardless of previous backups.
Similarly, if backup optimization is disabled:

CONFIGURE BACKUP OPTIMIZATION OFF;

RMAN performs a full backup of all eligible files.

This includes:

CDB root datafiles
All PDB datafiles
PDB$SEED datafiles

Difference Between Normal Backup and FORCE Backup

Feature	Optimization ON	BACKUP FORCE
Skips unchanged files	Yes	No
Backs up PDB$SEED repeatedly	Usually No	Yes
Backup size	Smaller	Larger
Backup duration	Faster	Slower
Storage consumption	Lower	Higher

Why Oracle Designed It This Way

Oracle treats backup optimization as a mechanism to avoid redundant backups.

Since PDB$SEED is read-only and static:

Backing it up repeatedly provides little value
It unnecessarily increases backup size
It consumes additional I/O and storage

Therefore, RMAN intelligently skips it when optimization is enabled.

Important Considerations

1. Skipping Does NOT Mean Unprotected

Even though RMAN skips PDB$SEED, the previous valid backup is still available for restore and recovery operations.

RMAN catalog/controlfile metadata tracks these backups.

2. Retention Policy Matters

If old backups containing PDB$SEED become obsolete and are deleted, RMAN may back up the seed datafiles again in future runs.

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Once backups age out, RMAN ensures another valid copy exists.

3. FORCE Should Be Used Carefully

BACKUP FORCE DATABASE;

frequently uing FORCE can:

Increase backup storage usage
Increase backup windows
Create redundant backup pieces

It is useful for:

Compliance backups
Migration snapshots
Full refresh copies
Special archival requirements

But usually unnecessary for daily backups.

How to Verify Which Files Were Skipped

You can query RMAN output logs or use:

			
LIST BACKUP OF DATABASE;
SELECT con_id, file#, name FROM v$datafile ORDER BY con_id;
alter session set container=pdb$seed;
select file#,name from V$datafile;

Typically: CON_ID = 2 corresponds to PDB$SEED

Let us simulate it:

			
[oracle@dbvm bkp_poc]$ rman target /
 
Recovery Manager: Release 19.0.0.0.0 - Production on Mon May 25 18:33:28 2026
Version 19.29.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: POC (DBID=1861214910)
 
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TESTPOC are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECOC9/testpoc/controlfile/snapcf_poc.f';
 
RMAN>

		

			
RMAN> RUN
{
sql 'alter system archive log current';
BACKUP
TAG FULL_DATABASE_BACKUP
FORMAT '/DBBKP/bkp_poc/POC_DB_BKP_%s_%p_%t_%T' As COMPRESSED BACKUPSET DATABASE;
RBACKUP FORMAT '/DBBKP/bkp_poc/POC_BKP_%s_%p_%t_%T' ARCHIVELOG ALL;
BACKUP FORMAT '/DBBKP/bkp_poc/POC_BKP_CONTROLFILE_%s_%p_%t_%T' CURRENT CONTROLFILE;
}
sql statement: alter system archive log current
 
Starting backup at 25-MAY-26
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATAC9/TESTPOC/DATAFILE/sysaux.410.1223460383
channel ORA_DISK_1: starting piece 1 at 25-MAY-26
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATAC9/TESTPOC/DATAFILE/system.423.1223460133
channel ORA_DISK_2: starting piece 1 at 25-MAY-26
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATAC9/TESTPOC/DATAFILE/system.395.1223460905
channel ORA_DISK_3: starting piece 1 at 25-MAY-26
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATAC9/TESTPOC/DATAFILE/sysaux.419.1223460927
channel ORA_DISK_4: starting piece 1 at 25-MAY-26
channel ORA_DISK_5: starting compressed full datafile backup set
channel ORA_DISK_5: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATAC9/TESTPOC/DATAFILE/system.426.1223460397
channel ORA_DISK_5: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATAC9/TESTPOC/DATAFILE/undotbs1.411.1223460405
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_7: starting compressed full datafile backup set
channel ORA_DISK_7: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATAC9/TESTPOC/DATAFILE/undotbs2.409.1223460383
channel ORA_DISK_7: starting piece 1 at 25-MAY-26
channel ORA_DISK_8: starting compressed full datafile backup set
channel ORA_DISK_8: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATAC9/TESTPOC/DATAFILE/users.407.1223460413
channel ORA_DISK_8: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_43_1_1234205478_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00013 name=+DATAC9/TESTPOC/DATAFILE/users.429.1223460903
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_7: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_44_1_1234205478_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_7: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_7: starting compressed full datafile backup set
channel ORA_DISK_7: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATAC9/TESTPOC/DATAFILE/sysaux.425.1223460133
channel ORA_DISK_7: starting piece 1 at 25-MAY-26
channel ORA_DISK_8: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_45_1_1234205478_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_8: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_8: starting compressed full datafile backup set
channel ORA_DISK_8: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATAC9/TESTPOC/DATAFILE/undotbs1.424.1223460135
channel ORA_DISK_8: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_46_1_1234205479_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00011 name=+DATAC9/TESTPOC/DATAFILE/undotbs1.418.1223460933
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_49_1_1234205480_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00012 name=+DATAC9/TESTPOC/DATAFILE/undo_2.430.1223460937
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_50_1_1234205484_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_4: finished piece 1 at 25-MAY-26
.
.
Finished backup at 25-MAY-26
.
Starting Control File and SPFILE Autobackup at 25-MAY-26
piece handle=+RECOC9/TESTPOC/AUTOBACKUP/2026_05_25/s_1234205666.536.1234205667 comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAY-26
 
RMAN>

		

			
RMAN> RUN
{
sql 'alter system archive log current';
BACKUP
TAG FULL_DATABASE_BACKUP
FORMAT '/DBBKP/bkp_poc/POC_DB_BKP_%s_%p_%t_%T' As COMPRESSED BACKUPSET DATABASE;
RBACKUP FORMAT '/DBBKP/bkp_poc/POC_BKP_%s_%p_%t_%T' ARCHIVELOG ALL;
BACKUP FORMAT '/DBBKP/bkp_poc/POC_BKP_CONTROLFILE_%s_%p_%t_%T' CURRENT CONTROLFILE;
}
sql statement: alter system archive log current
 
Starting backup at 25-MAY-26
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
skipping datafile 2; already backed up 2 time(s)
skipping datafile 4; already backed up 2 time(s)
skipping datafile 6; already backed up 2 time(s)
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATAC9/TESTPOC/DATAFILE/sysaux.410.1223460383
channel ORA_DISK_1: starting piece 1 at 25-MAY-26
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATAC9/TESTPOC/DATAFILE/system.395.1223460905
channel ORA_DISK_2: starting piece 1 at 25-MAY-26
.
.
channel ORA_DISK_2: starting piece 1 at 25-MAY-26
Finished backup at 25-MAY-26

		

			
RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE BACKUP OPTIMIZATION OFF;
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION OFF;
new RMAN configuration parameters are successfully stored
RMAN>

		

			
RMAN> RUN
{
sql 'alter system archive log current';
BACKUP
TAG FULL_DATABASE_BACKUP
FORMAT '/DBBKP/bkp_poc/POC_DB_BKP_%s_%p_%t_%T' As COMPRESSED BACKUPSET DATABASE;
RBACKUP FORMAT '/DBBKP/bkp_poc/POC_BKP_%s_%p_%t_%T' ARCHIVELOG ALL;
BACKUP FORMAT '/DBBKP/bkp_poc/POC_BKP_CONTROLFILE_%s_%p_%t_%T' CURRENT CONTROLFILE;
}
sql statement: alter system archive log current
Starting backup at 25-MAY-26
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATAC9/TESTPOC/DATAFILE/sysaux.410.1223460383
channel ORA_DISK_1: starting piece 1 at 25-MAY-26
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATAC9/TESTPOC/DATAFILE/system.423.1223460133
channel ORA_DISK_2: starting piece 1 at 25-MAY-26
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATAC9/TESTPOC/DATAFILE/system.395.1223460905
channel ORA_DISK_3: starting piece 1 at 25-MAY-26
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATAC9/TESTPOC/DATAFILE/sysaux.419.1223460927
channel ORA_DISK_4: starting piece 1 at 25-MAY-26
channel ORA_DISK_5: starting compressed full datafile backup set
channel ORA_DISK_5: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATAC9/TESTPOC/DATAFILE/system.426.1223460397
channel ORA_DISK_5: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATAC9/TESTPOC/DATAFILE/undotbs1.411.1223460405
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_7: starting compressed full datafile backup set
channel ORA_DISK_7: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATAC9/TESTPOC/DATAFILE/undotbs2.409.1223460383
channel ORA_DISK_7: starting piece 1 at 25-MAY-26
channel ORA_DISK_8: starting compressed full datafile backup set
channel ORA_DISK_8: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATAC9/TESTPOC/DATAFILE/users.407.1223460413
channel ORA_DISK_8: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_94_1_1234206240_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00013 name=+DATAC9/TESTPOC/DATAFILE/users.429.1223460903
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_7: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_95_1_1234206240_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_7: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_7: starting compressed full datafile backup set
channel ORA_DISK_7: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATAC9/TESTPOC/DATAFILE/sysaux.425.1223460133
channel ORA_DISK_7: starting piece 1 at 25-MAY-26
channel ORA_DISK_8: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_96_1_1234206240_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_8: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_8: starting compressed full datafile backup set
channel ORA_DISK_8: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATAC9/TESTPOC/DATAFILE/undotbs1.424.1223460135
channel ORA_DISK_8: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_97_1_1234206241_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00011 name=+DATAC9/TESTPOC/DATAFILE/undotbs1.418.1223460933
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_100_1_1234206243_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00012 name=+DATAC9/TESTPOC/DATAFILE/undo_2.430.1223460937
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_101_1_1234206246_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_4: finished piece 1 at 25-MAY-26
.
.
Finished backup at 25-MAY-26

		

			
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE BACKUP OPTIMIZATION ON;
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION OFF;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
 
RMAN> show all;
RMAN configuration parameters for database with db_unique_name TESTPOC are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECOC9/testpoc/controlfile/snapcf_poc.f';
 
RMAN>

		

			
RMAN> RUN
{
sql 'alter system archive log current';
BACKUP
TAG FULL_DATABASE_BACKUP
FORMAT '/DBBKP/bkp_poc/POC_DB_BKP_%s_%p_%t_%T' As COMPRESSED BACKUPSET DATABASE;
RBACKUP FORMAT '/DBBKP/bkp_poc/POC_BKP_%s_%p_%t_%T' ARCHIVELOG ALL;
BACKUP FORMAT '/DBBKP/bkp_poc/POC_BKP_CONTROLFILE_%s_%p_%t_%T' CURRENT CONTROLFILE;
}
sql statement: alter system archive log current
Starting backup at 25-MAY-26
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATAC9/TESTPOC/DATAFILE/sysaux.410.1223460383
channel ORA_DISK_1: starting piece 1 at 25-MAY-26
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATAC9/TESTPOC/DATAFILE/system.423.1223460133
channel ORA_DISK_2: starting piece 1 at 25-MAY-26
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATAC9/TESTPOC/DATAFILE/system.395.1223460905
channel ORA_DISK_3: starting piece 1 at 25-MAY-26
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATAC9/TESTPOC/DATAFILE/sysaux.419.1223460927
channel ORA_DISK_4: starting piece 1 at 25-MAY-26
channel ORA_DISK_5: starting compressed full datafile backup set
channel ORA_DISK_5: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATAC9/TESTPOC/DATAFILE/system.426.1223460397
channel ORA_DISK_5: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATAC9/TESTPOC/DATAFILE/undotbs1.411.1223460405
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_7: starting compressed full datafile backup set
channel ORA_DISK_7: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATAC9/TESTPOC/DATAFILE/undotbs2.409.1223460383
channel ORA_DISK_7: starting piece 1 at 25-MAY-26
channel ORA_DISK_8: starting compressed full datafile backup set
channel ORA_DISK_8: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATAC9/TESTPOC/DATAFILE/users.407.1223460413
channel ORA_DISK_8: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_117_1_1234206789_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00013 name=+DATAC9/TESTPOC/DATAFILE/users.429.1223460903
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_7: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_118_1_1234206789_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_7: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_7: starting compressed full datafile backup set
channel ORA_DISK_7: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATAC9/TESTPOC/DATAFILE/sysaux.425.1223460133
channel ORA_DISK_7: starting piece 1 at 25-MAY-26
channel ORA_DISK_8: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_119_1_1234206789_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_8: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_8: starting compressed full datafile backup set
channel ORA_DISK_8: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATAC9/TESTPOC/DATAFILE/undotbs1.424.1223460135
channel ORA_DISK_8: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_120_1_1234206790_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00011 name=+DATAC9/TESTPOC/DATAFILE/undotbs1.418.1223460933
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_123_1_1234206794_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00012 name=+DATAC9/TESTPOC/DATAFILE/undo_2.430.1223460937
channel ORA_DISK_6: starting piece 1 at 25-MAY-26
channel ORA_DISK_6: finished piece 1 at 25-MAY-26
piece handle=/DBBKP/bkp_poc/POC_DB_BKP_124_1_1234206797_20260525 tag=FULL_DATABASE_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_4: finished piece 1 at 25-MAY-26
Finished backup at 25-MAY-26

		

Best Practice Recommendations

Recommended for Daily Backups

CONFIGURE BACKUP OPTIMIZATION ON;

Advantages:

Faster backups
Reduced storage usage
Efficient handling of static files like PDB$SEED

Use FORCE Only When Needed

Use BACKUP FORCE for:

One-time full backups
Regulatory compliance copies
Air-gapped backup generation
Migration baselines

Avoid using it in every scheduled backup job unless required.

0 Comments Leave a comment

How to Clean Up Oracle Patch Storage Using OPatch Utility

Managing Oracle patch storage is an important maintenance activity for database administrators. Over time, Oracle homes accumulate backup files, patch artifacts, and unnecessary inventory data that can consume significant disk space.

Oracle provides the OPatch cleanup utility to safely remove obsolete patch backup files and reclaim storage without impacting the current Oracle installation.

This blog explains how to clean up Oracle patch storage step by step using the OPatch utility.

Why Clean Oracle Patch Storage?

Frequent patching activities create backup copies and metadata under the Oracle Home. Over time, this can lead to:

Increased Oracle Home size
Filesystem space exhaustion
Slower patch management operations
Inventory clutter

Regular cleanup helps maintain a healthy Oracle environment and optimizes storage utilization.

Prerequisites

Before performing cleanup:

Ensure you have a valid backup of the Oracle Home.
Verify no patching activity is currently running.
Run the commands as the Oracle software owner.
Set the correct ORACLE_HOME environment variable.

			
[oracle@leoprd ~]$ export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
[oracle@leoprd ~]$ export PATH=$ORACLE_HOME/OPatch:$PATH

Step 1: Check Current Oracle Inventory

[oracle@leoprd ~]$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

This command displays:

Installed patches
Patch history
Oracle Home details
Inventory information

			
Oracle Interim Patch Installer version 12.2.0.1.36
Inventory load successful
Installed Top-level Products:
Oracle Database 19c
...

		

This serves as a baseline before cleanup.

Step 2: Generate Cleanup Report

Before deleting files, generate a report showing what will be removed:

			
[oracle@leoprd ~]$ opatch util cleanup -invPtrLoc $ORACLE_HOME/oraInst.loc -report
The following directories will be cleaned:
/u01/app/oracle/product/19c/dbhome_1/.patch_storage
...

The -report option performs a dry run and lists:

Backup files eligible for deletion
Patch storage directories
Reclaimable disk space

Review the output carefully before proceeding.

Step 3: Perform Oracle Patch Cleanup

Once verified, execute the actual cleanup:

[oracle@leoprd ~]$ opatch util cleanup -invPtrLoc $ORACLE_HOME/oraInst.loc

This command removes obsolete patch backup files and cleans patch storage directories.

Typically includes:

Old patch backup files
Temporary patch artifacts
Unused rollback data
Obsolete patch storage entries

Step 4: Verify Oracle Inventory After Cleanup

After cleanup completes, validate the inventory again:

[oracle@leoprd ~]$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

This ensures:

Oracle inventory remains healthy
Installed patches are still registered correctly
No corruption occurred during cleanup

Compare the output with the earlier inventory check.

Step 5: Check Oracle Home Disk Usage

Finally, verify how much space has been reclaimed.

			
[oracle@leoprd ~]$ du -sh $ORACLE_HOME
[oracle@leoprd ~]$ du -sh $ORACLE_HOME/.patch_storage
12G    /u01/app/oracle/product/19c/dbhome_1

You should observe reduced storage consumption after cleanup.

0 Comments Leave a comment

Rebuilding an Oracle Data Guard Standby Database Using RMAN FROM SERVICE

Oracle 12c introduced one of the most useful Data Guard recovery features: RMAN active restore from service. Instead of manually copying backup pieces, transferring datafiles, or using duplicate commands, you can rebuild a standby database directly across the network from the primary database using RMAN.

This approach is especially useful when:

The standby database is corrupted
Datafiles are missing
Storage migration is required
ASM disk groups changed
You want to recreate the standby quickly without backup shipping

In this article, we will walk through a complete example using the following RMAN commands:

			
run
{
restore standby controlfile from service leo_dr;
alter database mount;
}
run
{
set newname for database to '+DATAC12/';
restore database from service leo_dr;
switch datafile all;
switch tempfile all;
recover database noredo from service leo_dr;
}

		

Architecture Used

			
| Role             | Host           | DB Unique Name |
| ---------------- | -------------- | -------------- |
| Primary Database | primary-server | leo_dr         |
| Standby Database | standby-server | leo_stby       |

Environment assumptions:

Oracle Database 19c
ASM storage
Data Guard configured previously
TNS connectivity working
Password file synchronized
Oracle Net configured correctly

What Does FROM SERVICE Actually Do?

The RMAN FROM SERVICE clause allows the standby database to:

Connect to the primary database over Oracle Net
Stream backup sets directly
Restore files without needing local backup copies

This eliminates:

SCP file transfers
Shared NFS backup storage
Manual backup handling

RMAN performs an active network restore.

Prerequisites

Before starting, verify the following carefully.

1. Verify TNS Connectivity

On the standby server do a tnsping

tnsping leo_dr

Also test SQL*Plus connectivity:

sqlplus sys@leo_dr as sysdba

If this fails, fix Oracle Net before proceeding.

2. Ensure Password Files Match

Password files must be identical between primary and standby.

On primary:

scp $ORACLE_HOME/dbs/orapwleo_dr standby:/tmp

On standby:

mv /tmp/orapwleo_dr $ORACLE_HOME/dbs/orapwleo_stby

3. Start Standby Instance in NOMOUNT

Create/initiate a standby pfile or spfile.

Example minimal pfile:

			
db_name='LEO'
db_unique_name='leo_stby'
compatible='19.0.0'
control_files='+DATAC12'

Start the instance:

startup nomount;

4. Configure Listener

Ensure listener knows the standby instance.

Example listener.ora:

			
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = leo_stby)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = leo_stby)
    )
  ) 
  

		

Reload listener:

lsnrctl reload

5. Restore Standby Controlfile

Now connect RMAN to the standby instance.

			
rman target /
run
{
  restore standby controlfile from service leo_dr;
  alter database mount;
}

		

What Happens Here?

restore standby controlfile from service leo_dr

RMAN connects remotely to the primary database service leo_dr and restores a standby-compatible controlfile.

This controlfile contains:

Datafile structure
Redo log metadata
Checkpoint information
Database incarnation details

No local backup is required.

alter database mount

Once the controlfile is restored:

The standby instance mounts the database
Oracle now understands the physical database structure

At this stage:

Controlfiles exist
Datafiles do not yet exist

6. Redirect Datafiles to ASM

Now restore the actual database files.

			
run
{
  set newname for database to '+DATAC12/';
  restore database from service leo_dr;
  switch datafile all;
  switch tempfile all;
  recover database noredo from service leo_dr;
}

		

Understanding Each Command

set newname for database to ‘+DATAC12/’

This is extremely important.

It tells RMAN:

Restore all datafiles into ASM diskgroup +DATAC12.

Without this:

RMAN may attempt restoring to original filesystem paths
Restore could fail due to missing directories
ASM migration would not occur properly

Example resulting ASM paths:

+DATAC12/LEO_STBY/DATAFILE/system.257.123456789

This is commonly used during:

Filesystem → ASM migration
Diskgroup migration
Storage refreshes

restore database from service leo_dr

This command:

Connects to primary database service
Creates backupsets on-the-fly
Streams them over Oracle Net
Restores datafiles directly to standby storage

No intermediary backup files are required.

This includes:

SYSTEM tablespace
SYSAUX
UNDO
USERS
Application tablespaces

RMAN Channels During Restore

You will typically see messages like:

channel ORA_DISK_1: using network backup set from service leo_dr

This confirms network restore is active.

switch datafile all

After restore completes:

RMAN updates the controlfile
New ASM filenames become official database filenames

Without this step:

Controlfile still references old paths

This command is mandatory after SET NEWNAME.

switch tempfile all

Same logic applies to tempfiles.

RMAN updates tempfile references to new ASM locations.

recover database noredo from service leo_dr

This step performs recovery using incremental changes streamed from the primary.

NOREDo

means:

Recovery uses restored backup data
Does not expect archived redo already present locally

RMAN fetches required recovery blocks directly from the primary service.

This is extremely useful when:

Archive logs are unavailable
Standby was completely rebuilt
Gap resolution is impossible

Why NOREDO Matters

Traditional recovery requires:

Archived redo logs
FAL gap resolution
Log shipping

NOREDO bypasses much of this complexity during rebuild operations.

RMAN essentially synchronizes the standby directly from the source database.

7. Enable Managed Recovery

After recovery completes:

alter database recover managed standby database disconnect from session;

Or for real-time apply:

			
alter database recover managed standby database using current logfile disconnect from session;

Verify Data Guard Status

select process,status,thread#,sequence# from v$managed_standby;

Verify database role:

			
select database_role,open_mode from v$database;
PHYSICAL STANDBY  MOUNTED

0 Comments Leave a comment

How to Enable Transparent Data Encryption (TDE) in MySQL 8.0 on Windows and Linux

Data security is no longer optional. Whether you’re running MySQL in production, development, or a cloud-native environment, protecting sensitive data at rest is a critical requirement.

One of the most effective ways to secure stored data in MySQL is by enabling Transparent Data Encryption (TDE).

In this guide, we’ll walk through how to enable TDE in MySQL 8.0 using the keyring_file plugin on both Windows and Linux platforms. We’ll also demonstrate how to encrypt tables and databases, verify encryption status, and convert existing objects to use TDE.

What is TDE in MySQL?

Transparent Data Encryption (TDE) encrypts your InnoDB tablespaces automatically, protecting data stored on disk without requiring application changes.

With TDE enabled, MySQL encrypts:

Table data
Tablespaces
Associated storage files

The encryption and decryption happen transparently while MySQL is running.

TDE Plugin Differences Between Windows and Linux

The implementation steps are nearly identical on both operating systems. The only difference is the plugin library file used during startup.

Platform	Plugin
Windows	`keyring_file.dll`
Linux	`keyring_file.so`

Although this walkthrough uses a Windows example, the same approach applies to Linux with the appropriate plugin filename.

Step 1 – Configure MySQL for TDE

To enable TDE, configure the keyring_file plugin inside your MySQL configuration file.

Windows: my.ini
Linux: my.cnf

Configuration on Windows

			
early-plugin-load=keyring_file.dll
keyring_file_data="E:\MYSQL\CONFIG\keyring\keyring"
innodb_file_per_table=ON

Configuration on Linux

			
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring
innodb_file_per_table=ON

Important Prerequisite

Before restarting MySQL, ensure the directory structure exists for the keyring_file_data path.

Windows -> E:\MYSQL\CONFIG\keyring\

Linux -> /var/lib/mysql-keyring/

If the directory does not exist, MySQL may fail to initialize the keyring plugin.

Step 2 – Restart MySQL and Verify the Plugin

After updating the configuration, restart MySQL and confirm the plugin is active.

Verify Plugin Status

			
SHOW PLUGINS;
+----------------------------------+----------+--------------------+------------------+-------------+
| Name                             | Status   | Type               | Library          | License     |
+----------------------------------+----------+--------------------+------------------+-------------+
| keyring_file                     | ACTIVE   | KEYRING            | keyring_file.dll | PROPRIETARY |
+----------------------------------+----------+--------------------+------------------+-------------+

		

Verify Keyring Variables

			
SHOW VARIABLES LIKE '%keyring%';
+--------------------+---------------------------------+
| Variable_name      | Value                           |
+--------------------+---------------------------------+
| keyring_file_data  | E:\MYSQL\CONFIG\keyring\keyring |
| keyring_operations | ON                              |
+--------------------+---------------------------------+

		

At this point, MySQL is ready to support encrypted tablespaces.

Step 3 – Verify No Tables Are Encrypted Yet

Before enabling encryption, let’s confirm there are currently no encrypted tables.

			
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND CREATE_OPTIONS LIKE '%ENCRYPTION%';
  

		

Step 4 – Create a Table Without TDE

First, create a normal unencrypted table and insert some sample data:

			
CREATE TABLE table_without_tde (
    id INT,
    secret TEXT
);
INSERT INTO table_without_tde VALUES (101,'Hello');

		

Step 5 – Create a Table With TDE Enabled

Now create an encrypted table using ENCRYPTION='Y' and insert some data.

			
CREATE TABLE table_with_tde (
    id INT,
    secret TEXT
) ENCRYPTION='Y';
INSERT INTO table_with_tde VALUES (201,'Hello');

		

Step 6 – Validate the Data by querying both the tables.

Encrypted Table

			
SELECT * FROM table_with_tde;
+------+--------+
| id   | secret |
+------+--------+
| 201  | Hello  |
+------+--------+

		

Non-Encrypted Table

			
SELECT * FROM table_without_tde;
+------+--------+
| id   | secret |
+------+--------+
| 101  | Hello  |
+------+--------+

		

Applications interact with encrypted and non-encrypted tables exactly the same way.

Step 7 – Now verify Encryption Status

			
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND CREATE_OPTIONS LIKE '%ENCRYPTION%';
  +------------------+----------------+----------------+
| TABLE_SCHEMA     | TABLE_NAME     | CREATE_OPTIONS |
+------------------+----------------+----------------+
| mt_config        | table_with_tde | ENCRYPTION='Y' |
+------------------+----------------+----------------+

		

This confirms the table (table_with_tde) is encrypted.

Step 8 – Encrypt an Existing Table

TDE can also be enabled on existing tables using ALTER TABLE.

			
ALTER TABLE mt_config.table_without_tde ENCRYPTION='Y';
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND CREATE_OPTIONS LIKE '%ENCRYPTION%';
  +------------------+-------------------+----------------+
| TABLE_SCHEMA     | TABLE_NAME        | CREATE_OPTIONS |
+------------------+-------------------+----------------+
| mt_config        | table_with_tde    | ENCRYPTION='Y' |
| mt_config        | table_without_tde | ENCRYPTION='Y' |
+------------------+-------------------+----------------+

		

Both tables are now encrypted.

Enabling TDE at the Database Level

Starting from MySQL 8.0.16, encryption can also be enabled at the database level. This allows all newly created tables inside the database to inherit encryption automatically.

Step 9 – Create an Encrypted Database

			
CREATE DATABASE db1 ENCRYPTION='Y';
USE db1;
CREATE TABLE test (
    id INT,
    secret TEXT
);
INSERT INTO test VALUES (301,'Hello');

		

Verify encryption:

			
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND CREATE_OPTIONS LIKE '%ENCRYPTION%';
  +--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| db1          | test       | ENCRYPTION='Y' |
+--------------+------------+----------------+

		

The table inherited encryption automatically from the database.

Step 10 – Alter an Existing Database for TDE

Now let’s see what happens when encryption is enabled on an existing database.

Create a normal database first, and in it create an unencrypted table

			
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE testtab (
    id INT,
    secret TEXT
);

		

At this point, database as well as the table is not encrypted.

Enable Encryption on the Database

ALTER DATABASE testdb ENCRYPTION='Y';

Now create another table:

			
CREATE TABLE testtab2 (
    id INT,
    secret TEXT
);

Verify encryption settings:

			
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND CREATE_OPTIONS LIKE '%ENCRYPTION%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| db1          | test       | ENCRYPTION='Y' |
| testdb       | testtab    | ENCRYPTION='Y' |
| testdb       | testtab2   | ENCRYPTION='Y' |
+--------------+------------+----------------+

		

0 Comments Leave a comment