datablogs

Thursday, February 19, 2026

MySQL Binlog Restore using Python

Selva | February 19, 2026 | No comments | Automation, Database Administration, MySQL, Zero Downtime

Its easy to recover mysql data like Amazon RDS , Its Sort and Easy Method if you are not able to understand manual method

https://github.com/selvackp/mysql-binlog-restore-using-python

Any additonal help please command with us

Wednesday, February 4, 2026

AWS Glue is always Powerful as Data Engineer Trust ?

Selva | February 04, 2026 | 2 comments | AWS, AWS Glue, Data Engineering, Migration, Troubleshooting

AWS Glue is a powerful data engineering platform when designed, tuned, and governed correctly.But We are treating it as a simple ETL utility often leads to cost, performance, and reliability issues.

As a Data Friend we need to have solid understanding on the AWS Glue

Myth 1: AWS Glue is only for simple ETL

Reality:

AWS Glue supports complex transformations including joins, aggregations, schema evolution, incremental processing, and large-scale distributed processing using Apache Spark. It is suitable for enterprise-grade data engineering workloads.

Myth 2: AWS Glue is serverless, so performance tuning is not required

Reality:

While infrastructure management is serverless, Glue jobs still require tuning

Worker types (G.1X, G.2X, G.4X)
Number of DPUs
Spark configurations
Partitioning and data layout
Poor tuning leads to high cost and slow execution.

Myth 3: AWS Glue works only with Amazon S3

Reality:

AWS Glue integrates with multiple data sources

Amazon RDS and Aurora
Amazon Redshift
DynamoDB
JDBC sources (Oracle, SQL Server, MySQL, PostgreSQL)
Streaming sources such as Kafka and Kinesis

Myth 4: AWS Glue is very expensive

Reality:

Glue becomes expensive mainly due to design issues

Over-provisioned DPUs
Full data reloads instead of incremental loads
Missing job bookmarks

With optimized design, Glue is often more cost-effective than always-on Spark clusters.

Myth 5: Glue Crawlers automatically handle schema management

Reality:

Crawlers may

Create excessive tables
Misinterpret schema changes
Perform poorly with nested or semi-structured data

Production systems typically require controlled schema management and governance.

Myth 6: AWS Glue replaces data warehouses

Reality:

AWS Glue is a data integration and transformation service. It complements data warehouses by preparing and transforming data before loading into analytics platforms.

Myth 7: Glue jobs are difficult to debug

Reality:

Glue supports debugging through

Amazon CloudWatch logs
Spark UI
Job bookmarks
Glue Studio monitoring

Most challenges arise from limited Spark expertise rather than Glue itself.

Myth 8: AWS Glue supports only batch processing

Reality:

AWS Glue also supports

Streaming ETL
Near real-time pipelines
Event-driven processing

It is not limited to scheduled batch workloads.

Myth 9: AWS Glue is a set-and-forget service

Reality:

Production Glue pipelines require

Cost and performance monitoring
Schema change handling
Failure alerts and retries
Version control and CI/CD

Glue jobs should be treated as production-grade software.

Myth 10: AWS Glue is only for data engineers

Reality:

With Glue Studio, SQL-based transformations, and visual workflows, Glue can be effectively used by analytics teams, architects, and platform teams.

If you having issues , Please connect with us for instant help !!!

Tuesday, December 23, 2025

Steps to Change TempDB Location in SQL Server

Selva | December 23, 2025 | No comments | Automation, Database Administration, SQL Server

Its very easy and simple , Just sharing you the steps without so much theories

Why we are moving it , when its in C Drive or SQL Data Drive it will cause IO issues , so that we are moving TempDB files into different location

Step 1: Check Current TempDB Files Location

Run the following query:

USE tempdb;
EXEC sp_helpfile;

Step 2: Plan New Location

- Create a new folder on the desired drive (e.g., D:\SQLData\TempDB).

- Ensure SQL Server service account has Full Control on that folder.

Step 3: Modify TempDB File Paths

Run these commands (adjust paths as needed):

USE master;
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'D:\SQLData\TempDB\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'D:\SQLData\TempDB\templog.ldf');

Repeat for additional files if they exist.

Step 4: Restart SQL Server Service

Stop and Start the SQL Server instance. Restart is mandatory for changes to take effect.

Step 5: Verify New Location

USE tempdb;
EXEC sp_helpfile;

This should show the new paths.

Step 6: Clean Up Old Files

After SQL Server restarts successfully and TempDB is created in the new location, manually delete

the old tempdb files from the old drive.

SQL Server High CPU Issue Caused by Implicit Conversion (nvarchar vs varchar)

Selva | December 23, 2025 | No comments | Database Administration, Performance Tunning, SQL Server

Problem Overview

Recently, we encountered a high CPU utilization issue on a SQL Server instance — even though only one small SELECT query was executing.At first glance, it seemed unusual that such a simple query could consume so much CPU. However, on investigating the execution plan, we noticed that the optimizer was performing a conversion from varchar to nvarchar in the predicate.

Root Cause: Implicit Data Type Conversion

In SQL Server, when a query compares two different data types (for example, a column defined as varchar and a variable or literal passed as nvarchar), SQL Server performs an implicit conversion to make them compatible.

Unfortunately, this can:

Prevent index seeks (forcing a full scan instead)
Increase CPU usage
Cause plan cache bloat (multiple plans for same query)
Lead to unpredictable performance degradation

Example:

-- Table definition
CREATE TABLE Customer (
CustomerID INT,
CustomerCode VARCHAR(20)
);
-- Query from application
SELECT * FROM Customer WHERE CustomerCode = N'CUST001'; --
Notice the 'N' prefix (nvarchar)Even though an index exists on CustomerCode, SQL Server converts the column during
execution:
CONVERT_IMPLICIT(nvarchar(4000), [CustomerCode], 0)

As a result, the optimizer cannot perform an index seek, leading to a full table scan and

high CPU utilization.

Investigation Steps

Checked sys.dm_exec_requests and sys.dm_exec_query_stats – saw high CPU for a single query.
Reviewed the Actual Execution Plan – found CONVERT_IMPLICIT on the predicate.
Confirmed column type: varchar(20)
Confirmed query parameter or literal type: nvarchar

Resolution

Since the customer application could not modify the query, we proposed aligning the data types at the table level.
ALTER TABLE Customer ALTER COLUMN CustomerCode NVARCHAR(20);
After modifying the column data type to match the query, the optimizer was able to use the index correctly.

Result:

✔ CPU utilization dropped significantly

✔ Query execution time improved

✔ No functional impact

Best Practices to Avoid Implicit Conversions

Keep data types consistent between table columns and application parameters.
Avoid using the Unicode prefix (N'...') unless necessary.
Use parameter sniffing tests to identify mismatched parameter types.
In critical systems, standardize column data types across schemas and applications.

Summary

Even a small implicit conversion can lead to large performance issues in SQL Server.By ensuring data type alignment between the database schema and query parameters,you can prevent unnecessary scans and CPU spikes.

Friday, October 17, 2025

How to upgrade MySQL from 8.0 to 8.4 ?

Selva | October 17, 2025 | No comments | AWS, Database Administration, MySQL, Upgrade

Dear all my DBA Friends its time to upgrade to 8.4 . Yes We are getting notification from all the cloud partners as well on the customer side requests move to latest version

Here is detailed step for upgradtion on Ubuntu 22 . Before upgrading make sure you have noticed all the incompatible changes between 8.0 to 8.4 ,

https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-6.html

Best Practices for Updating MySQL from 8.0 to 8.4 | SQLFlash

Lets move on the steps ,

Environment :

Ubuntu 22
MySQL 8.0.43

Step 1 :

Please take complete backup of your existing database server

mysqldump -u root -p --routines --triggers --events --max_allowed_packet=1024M am80db > am80db_before_8.4_Upgrade.sql

Also take mysql database as well

Step 2 :

Download and add the MySQL APT repo package

wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.33-1_all.deb

root@ip-11-1-23-23:~# wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb

sudo dpkg -i mysql-apt-config_0.8.33-1_all.deb

--2025-10-17 13:45:36-- https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb

Resolving dev.mysql.com (dev.mysql.com)... 104.120.82.77, 2600:140f:1e00:486::2e31, 2600:140f:1e00:4b2::2e31

Connecting to dev.mysql.com (dev.mysql.com)|104.120.82.77|:443... connected.

HTTP request sent, awaiting response... 302 Moved Temporarily

Location: https://repo.mysql.com//mysql-apt-config_0.8.33-1_all.deb [following]

--2025-10-17 13:45:37-- https://repo.mysql.com//mysql-apt-config_0.8.33-1_all.deb

Resolving repo.mysql.com (repo.mysql.com)... 23.10.47.157, 2600:140f:1e00:a86::1d68, 2600:140f:1e00:a8f::1d68

Connecting to repo.mysql.com (repo.mysql.com)|23.10.47.157|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 18072 (18K) [application/x-debian-package]

Saving to: ‘mysql-apt-config_0.8.33-1_all.deb.1’

mysql-apt-config_0.8.33-1_all.deb.1 100%[============================================================================>] 17.65K --.-KB/s in 0.001s

2025-10-17 13:45:37 (25.2 MB/s) - ‘mysql-apt-config_0.8.33-1_all.deb.1’ saved [18072/18072]

(Reading database ... 99983 files and directories currently installed.)

Preparing to unpack mysql-apt-config_0.8.33-1_all.deb ...

Unpacking mysql-apt-config (0.8.33-1) over (0.8.33-1) ...

Setting up mysql-apt-config (0.8.33-1) ...

root@ip-11-1-23-23:~# sudo dpkg-reconfigure mysql-apt-config

File '/usr/share/keyrings/mysql-apt-config.gpg' exists. Overwrite? (y/N) y

root@ip-11-1-23-23:~# sudo apt update

Hit:1 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy InRelease

Hit:2 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-updates InRelease

Hit:3 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-backports InRelease

Hit:4 http://repo.mysql.com/apt/ubuntu jammy InRelease

Hit:5 http://security.ubuntu.com/ubuntu jammy-security InRelease

Get:6 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts Sources [965 B]

Get:7 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 Packages [14.5 kB]

Fetched 15.5 kB in 4s (3798 B/s)

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

10 packages can be upgraded. Run 'apt list --upgradable' to see them.

While configure it will ask to choose mysql-8.4 LTS and Click OK . Make sure you have selected 8.4 not 8.0

then update the package list in the ubuntu

apt update

root@ip-11-1-23-23:~# sudo apt update

Hit:1 http://repo.mysql.com/apt/ubuntu jammy InRelease

Hit:2 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy InRelease

Hit:3 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-updates InRelease

Hit:4 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-backports InRelease

Hit:5 http://security.ubuntu.com/ubuntu jammy-security InRelease

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

10 packages can be upgraded. Run 'apt list --upgradable' to see them.

Step 3 :

Lets start installing the package , make sure selecting conf options properly

sudo apt install mysql-server -y

root@ip-11-1-23-23:~# sudo apt install mysql-server -y

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

The following packages were automatically installed and are no longer required:

libcgi-fast-perl libcgi-pm-perl libclone-perl libencode-locale-perl libevent-pthreads-2.1-7 libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-parser-perl

libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libprotobuf-lite23

libtimedate-perl liburi-perl

Use 'sudo apt autoremove' to remove them.

The following additional packages will be installed:

mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-client-plugins mysql-community-server

mysql-community-server-core

The following packages will be REMOVED:

mysql-client-8.0 mysql-client-core-8.0 mysql-server-8.0 mysql-server-core-8.0

The following NEW packages will be installed:

mysql-client mysql-community-client mysql-community-client-core mysql-community-client-plugins mysql-community-server mysql-community-server-core

The following packages will be upgraded:

mysql-common mysql-server

2 upgraded, 6 newly installed, 4 to remove and 8 not upgraded.

Need to get 36.5 MB of archives.

After this operation, 47.3 MB of additional disk space will be used.

Get:1 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-server amd64 8.4.6-1ubuntu22.04 [59.2 kB]

Get:2 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-common amd64 8.4.6-1ubuntu22.04 [60.6 kB]

Get:3 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-client-plugins amd64 8.4.6-1ubuntu22.04 [1524 kB]

Get:4 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-client-core amd64 8.4.6-1ubuntu22.04 [1847 kB]

Get:5 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-client amd64 8.4.6-1ubuntu22.04 [2174 kB]

Get:6 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-client amd64 8.4.6-1ubuntu22.04 [59.3 kB]

Get:7 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-server-core amd64 8.4.6-1ubuntu22.04 [30.7 MB]

Get:8 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-server amd64 8.4.6-1ubuntu22.04 [69.3 kB]

Fetched 36.5 MB in 5s (7141 kB/s)

Preconfiguring packages ...

(Reading database ... 99983 files and directories currently installed.)

Preparing to unpack .../mysql-server_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-server (8.4.6-1ubuntu22.04) over (8.0.43-0ubuntu0.22.04.2) ...

(Reading database ... 99986 files and directories currently installed.)

Removing mysql-server-8.0 (8.0.43-0ubuntu0.22.04.2) ...

update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode

Removing mysql-client-8.0 (8.0.43-0ubuntu0.22.04.2) ...

Removing mysql-client-core-8.0 (8.0.43-0ubuntu0.22.04.2) ...

Removing mysql-server-core-8.0 (8.0.43-0ubuntu0.22.04.2) ...

(Reading database ... 99784 files and directories currently installed.)

Preparing to unpack .../0-mysql-common_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-common (8.4.6-1ubuntu22.04) over (5.8+1.0.8) ...

Selecting previously unselected package mysql-community-client-plugins.

Preparing to unpack .../1-mysql-community-client-plugins_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-community-client-plugins (8.4.6-1ubuntu22.04) ...

Selecting previously unselected package mysql-community-client-core.

Preparing to unpack .../2-mysql-community-client-core_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-community-client-core (8.4.6-1ubuntu22.04) ...

Selecting previously unselected package mysql-community-client.

Preparing to unpack .../3-mysql-community-client_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-community-client (8.4.6-1ubuntu22.04) ...

Selecting previously unselected package mysql-client.

Preparing to unpack .../4-mysql-client_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-client (8.4.6-1ubuntu22.04) ...

Selecting previously unselected package mysql-community-server-core.

Preparing to unpack .../5-mysql-community-server-core_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-community-server-core (8.4.6-1ubuntu22.04) ...

Selecting previously unselected package mysql-community-server.

Preparing to unpack .../6-mysql-community-server_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-community-server (8.4.6-1ubuntu22.04) ...

Setting up mysql-common (8.4.6-1ubuntu22.04) ...

Installing new version of config file /etc/mysql/conf.d/mysql.cnf ...

Installing new version of config file /etc/mysql/my.cnf.fallback ...

Setting up mysql-community-server-core (8.4.6-1ubuntu22.04) ...

Setting up mysql-community-client-plugins (8.4.6-1ubuntu22.04) ...

Setting up mysql-community-client-core (8.4.6-1ubuntu22.04) ...

Setting up mysql-community-client (8.4.6-1ubuntu22.04) ...

Setting up mysql-client (8.4.6-1ubuntu22.04) ...

Setting up mysql-community-server (8.4.6-1ubuntu22.04) ...

Installing new version of config file /etc/apparmor.d/usr.sbin.mysqld ...

Installing new version of config file /etc/mysql/mysql.cnf ...

Configuration file '/etc/mysql/mysql.conf.d/mysqld.cnf'

==> Modified (by you or by a script) since installation.

==> Package distributor has shipped an updated version.

What would you like to do about it ? Your options are:

Y or I : install the package maintainer's version

N or O : keep your currently-installed version

D : show the differences between the versions

Z : start a shell to examine the situation

The default action is to keep your current version.

*** mysqld.cnf (Y/I/N/O/D/Z) [default=N] ? Y

Installing new version of config file /etc/mysql/mysql.conf.d/mysqld.cnf ...

update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode

Setting up mysql-server (8.4.6-1ubuntu22.04) ...

Processing triggers for man-db (2.10.2-1) ...

Processing triggers for libc-bin (2.35-0ubuntu3.11) ...

Scanning processes...

Scanning candidates...

Scanning linux images...

Running kernel seems to be up-to-date.

Restarting services...

Service restarts being deferred:

systemctl restart networkd-dispatcher.service

systemctl restart unattended-upgrades.service

systemctl restart user@0.service

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.

Step 4 :

Lets do the final restart and check the status of MySQL

sudo systemctl restart mysql
sudo systemctl status mysql

root@ip-11-1-23-23:~# sudo systemctl restart mysql

root@ip-11-1-23-23:~# sudo systemctl status mysql

● mysql.service - MySQL Community Server

Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

Active: active (running) since Fri 2025-10-17 13:48:46 UTC; 7s ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process: 95024 ExecStartPre=/usr/share/mysql-8.4/mysql-systemd-start pre (code=exited, status=0/SUCCESS)

Main PID: 95063 (mysqld)

Status: "Server is operational"

Tasks: 36 (limit: 9387)

Memory: 444.4M

CPU: 1.905s

CGroup: /system.slice/mysql.service

└─95063 /usr/sbin/mysqld

Oct 17 13:48:42 ip-10-1-94-82 systemd[1]: Starting MySQL Community Server...

Oct 17 13:48:46 ip-10-1-94-82 systemd[1]: Started MySQL Community Server.

Step 5 :

Dont try mysql_upgrade is no longer exist on MySQL 8.0.16 then lets complete mysql_secure_installation and update proper password for the root user

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'ewewewewewe';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Step 6 :

Lets verify the MySQL Upgrade has been success or not on the log file , Highlighted logs clarifing upgrade successfully completed

sudo tail -n 50 /var/log/mysql/error.log

2025-10-17T13:47:41.670888Z 0 [System] [MY-015015] [Server] MySQL Server - start.

2025-10-17T13:47:42.054060Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.6) starting as process 94072

2025-10-17T13:47:42.129906Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2025-10-17T13:47:49.394972Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2025-10-17T13:47:49.429761Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80023' to '80300'.

2025-10-17T13:47:53.024004Z 1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80023' to '80300' completed.

2025-10-17T13:47:57.289846Z 4 [System] [MY-013381] [Server] Server upgrade from '80043' to '80406' started.

2025-10-17T13:48:07.600863Z 4 [System] [MY-013381] [Server] Server upgrade from '80043' to '80406' completed.

2025-10-17T13:48:08.000645Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2025-10-17T13:48:08.000744Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

2025-10-17T13:48:08.081364Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

2025-10-17T13:48:08.082336Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.6' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.

2025-10-17T13:48:39.677032Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.4.6).

2025-10-17T13:48:41.686113Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 10 user: 'Ruban'.

2025-10-17T13:48:41.689689Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 11 user: 'Ruban'.

2025-10-17T13:48:42.651459Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.4.6) MySQL Community Server - GPL.

2025-10-17T13:48:42.651514Z 0 [System] [MY-015016] [Server] MySQL Server - end.

2025-10-17T13:48:43.120978Z 0 [System] [MY-015015] [Server] MySQL Server - start.

2025-10-17T13:48:43.492756Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.6) starting as process 95063

2025-10-17T13:48:43.529553Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2025-10-17T13:48:45.123068Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2025-10-17T13:48:46.095152Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2025-10-17T13:48:46.095299Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

2025-10-17T13:48:46.181691Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

2025-10-17T13:48:46.182507Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.6' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.

Step 7 :

Final Validation from my end ,

Reference :

https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-6.html
https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-0.html
Best Practices for Updating MySQL from 8.0 to 8.4 | SQLFlash

Lets chat if you need further assistance on this , Please comment

Tuesday, September 16, 2025

Oracle Standard Edition Log Shipping in Windows

Selva | September 16, 2025 | No comments | Backup, Oracle, Oracle 11g, Point In Time Restore

In today’s always-on world, database downtime can cost businesses a fortune. Setting up a reliable Disaster Recovery (DR) solution ensures your organization can quickly recover from unexpected failures.

This guide walks you through a practical, step-by-step process of implementing Oracle 11g Standard Edition DR, from enabling archive logs to recovering the database at the DR site.

Why This Matters

Business Continuity: Minimize downtime during outages.
Data Protection: Ensure no transaction is lost.
Compliance: Meet RTO/RPO requirements.

Prerequisites

Before starting, ensure:

Oracle 11g Standard Edition is installed on both DC (Primary) and DR (Standby) servers.
Network connectivity & firewall rules allow archive log and backup file transfer between DC and DR.
Adequate storage is provisioned for backups and archive logs.

High-Level DR Workflow


[DC: Primary Database]
   |
   |--- Archive Logs + RMAN Backups ---> [DR: Standby Database]
                                              |
                                              --> Restore + Recover --> Open DB

Step-by-Step Implementation

Step 1: Install Oracle 11g

Install Oracle 11g Standard Edition on both DC and DR servers.

Step 2: Enable Archive Logging (DC)

Archive logs capture every committed change — critical for recovery.


SQL>SELECT LOG_MODE FROM V$DATABASE;

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN;

Step 3: Force Logging & Supplemental Logs

Ensure redo logs are always generated and enough data is captured for recovery.


SQL>ALTER DATABASE FORCE LOGGING;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Step 4: Configure Recovery Parameters

Increase recovery area size if required:


SQL>SHOW PARAMETER db_recovery;
SQL>ALTER SYSTEM SET db_recovery_file_dest_size=10g;

Step 5: Configure RMAN

Enable control file autobackup and retention policy:


RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Step 6: Take Full Backup (DC)


RMAN>BACKUP DATABASE;

This will back up all datafiles and perform an autobackup of control file & SPFILE.

Step 7: Prepare DR Instance

Shutdown instance
Remove old datafiles (oradata)
Start in NOMOUNT mode


STARTUP NOMOUNT;

Step 8: Move Backup Files to DR

Transfer flash_recovery_area folder to the DR server.

Step 9: Restore Control File & Database


RMAN>RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN>ALTER DATABASE MOUNT;
RMAN>RESTORE DATABASE;

Step 10: Recover Database

Update the catalog in RMAN before proceeding Recover Database

RMAN> CATALOG START WITH 'C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG';
searching for all files that match the pattern C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG no files found to be unknown to the database

Use RMAN or SQL*Plus:


SQL>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Enter AUTO to apply logs automatically.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 1046243 generated at 09/16/2025 08:25:59 needed for thread 1
ORA-00289: suggestion : C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_20_NDL89QG0_.ARC
ORA-00280: change 1046243 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1048238 generated at 09/16/2025 08:35:03 needed for thread 1
ORA-00289: suggestion : C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_21_NDL89XF1_.ARC
ORA-00280: change 1048238 for thread 1 is in sequence #21
ORA-00278: log file 'C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_20_NDL89QG0_.ARC' no longer needed for this recovery

Finally open with resetlogs:


SQL>ALTER DATABASE OPEN RESETLOGS;

Step 11: Validate

Run validation queries:


SQL>SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;
SQL>SELECT file#, checkpoint_change# FROM v$datafile_header ORDER BY file#;
SQL>SELECT checkpoint_change# FROM v$database;

Ensure no archive log gaps remain.

RMAN Handy Scripts

Here are some ready-to-use RMAN scripts for ongoing maintenance:

Catalog & Recover


RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  CATALOG START WITH 'C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG';
  RESTORE ARCHIVELOG ALL;
  RECOVER DATABASE;
  DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
  RELEASE CHANNEL c1;
}

Point-in-Time Recovery


RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  SET UNTIL SEQUENCE 18 THREAD 1;
  RESTORE DATABASE;
  RECOVER DATABASE;
  RELEASE CHANNEL c1;
}

Key Takeaways

Automate archive log shipping from DC to DR to ensure near real-time recovery capability.
Regularly run RMAN crosschecks to keep backup metadata in sync.
Validate DR site periodically by performing trial recovery exercises.

With this approach, you can be confident that your Oracle 11g environment is disaster-ready and downtime can be minimized.

datablogs

Data will talk to you if you're willing to listen !!!

Categories

Thursday, February 19, 2026

MySQL Binlog Restore using Python

Wednesday, February 4, 2026

AWS Glue is always Powerful as Data Engineer Trust ?

Tuesday, December 23, 2025

Steps to Change TempDB Location in SQL Server

SQL Server High CPU Issue Caused by Implicit Conversion (nvarchar vs varchar)

Friday, October 17, 2025

How to upgrade MySQL from 8.0 to 8.4 ?

Tuesday, September 16, 2025

Oracle Standard Edition Log Shipping in Windows

Why This Matters

Prerequisites

High-Level DR Workflow

Step-by-Step Implementation

Step 1: Install Oracle 11g

Step 2: Enable Archive Logging (DC)

Step 3: Force Logging & Supplemental Logs

Step 4: Configure Recovery Parameters

Step 5: Configure RMAN

Step 6: Take Full Backup (DC)

Step 7: Prepare DR Instance

Step 8: Move Backup Files to DR

Step 9: Restore Control File & Database

Step 10: Recover Database

Step 11: Validate

RMAN Handy Scripts

Catalog & Recover

Point-in-Time Recovery

Key Takeaways