Enable SSL-encryption for MariaDB Galera Cluster

Imagine you have MariaDB Galera cluster with nodes running in different data centers. Data centers are not connected via secured VPN tunnel.
As database security is very important you must ensure that traffic between nodes is fully secured.

Galera Cluster supports encrypted connections between nodes using SSL protocol and in this post I want to show how to encrypt all cluster communication using SSL encryption.

Check current SSL configuration.

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_ssl';

Delete large amounts of data on Galera Cluster using pt-archiver

Galera Cluster is excellent virtually synchronous multi-master database cluster. It has many benefits which you can check on GaleraCluster.
But beside benefits it has some limitations and one of them is handling large transactions.

Large replication data sets could degrade performance of whole cluster causing cluster freezing, increased memory consumption, crashing nodes, etc. To avoid this issues it is recommended to split large transactions into smaller chunks.

In this post I want to show you (more...)

Beware of ORA-19721 on 12c using Transportable Tablespace (Oracle changed behavior)

Almost every big database has it's hot data which is used often, and cold data which is rarely touched. From version 9i I have used transportable tablespace feature to exclude cold (archive) data from database and keep it on cheap storage or tapes.

If someone needs to query some of archive tables it was very easy to plug in tablespace for few days and after archive data is not needed anymore tablespace could be easily (more...)

Using In-Memory Option with SQL Plan Baselines, SQL Profiles and SQL Hints

Oracle database In-Memory option was introduced in patchset. It is great feature to improve performance of analytic queries. For mixed workload OLTP environments In-Memory option could improve performance of analytic queries without significant negative affect on quick OLTP queries or DML operations.

So you have decided that In-Memory option could be great for you and now you want to implement this option for your critical production database.

But in your code (more...)

Reduce Hard Parse time using SQL Profile

Few days ago we had concurrency problem with "cursor: pin S wait on X" wait event. This wait event is mostly associated with parsing in some form.

After quick diagnosis I’ve found problematic query. It was fairly complex query which was executed very often with average 0.20 seconds of execution time. As this query was using bind variables, Oracle reused existing plan and problems with "cursor: pin S wait on X" wait (more...)

Using Adaptive Cursors Sharing with SQL Plan Baselines

We have several databases where automatic capturing of sql plan baselines is enabled for a few schemas.

Execution of some queries deeply depend on variables where is not always the best to reuse same execution plan for all executions. For those queries I want to avoid using literals and inefficient execution plans. Also, I want to use SQL plan baselines as I have automatic capturing enabled.

Question is, can I make Adaptive Cursor Sharing to (more...)

Slow full table scan due to row chaining

Few days ago I’ve received complaint that simple count on 2 million rows table is running forever.

This was the statement:

select count(1)
from CLIENT k
where k.expires is null;

I've used fake names for table name and columns.
Database version:

Indeed, query was running longer than I would expect. Oracle was using FULL SCAN of the table with "db file sequential read" wait events. This was little odd (more...)

Detecting Soft Corruption on 12c – V$NONLOGGED_BLOCK, ORA-01578/ORA-26040

Last week we have created standby database in our dev environment and performed some ETL actions on primary side. Loading data or rebuilding indexes was performed with NOLOGGING option. After few days we noticed lots ORA-01578/ORA-26040 errors.
Corruption happened because we forgot to enable force logging.

As this was new dev database there wasn’t backup, but maybe not everything was lost. If only corrupted segments are indexes we could easily rebuild them.

Then I’ve learnt (more...)

Unindexed Foreign Keys on empty/unused table and locks

It is widely known that unindexed foreign keys can be performance issue. Unindexed foreign keys on child tables can cause table locks or performance problems in general.
There are many articles on this subject so I won't go in details.

My plan is to show simple demo case where empty child table with unindexed foreign key column can cause big problems.

Imagine that you have highly active table (supplier) with lots DML operations from many (more...)

Confusion and problems with lost+found directory in MySQL/Galera cluster configuration

The lost+found directory is filesystem directory created at root level of mounted drive for ext file systems. It is used by file system check tools (fsck) for file recoveries.

In MySql world it can cause confusion or possible problems with synchronisation in Galera cluster configuration.

Let’s check some examples.

I have MySQL database with datadir=/data in configuration file. I have deleted lost+found directory and restarted MySQL service.

When I list my databases this is result:


How to Pass Arguments to OS Shell Script from Oracle Database

Imagine you have several Oracle databases on the same host under same OS user.

In scripts directory you have shell script that kills OS processes.
Idea is to call OS script from database procedure and kill problematic process using shell script.

Script will run simple query to get process id and kill that process.

But how to assure that this script will execute in correct environment for correct database?

One way is to create one (more...)

ASM not starting with ORA-00845 – how to fix ASM parameter file

Few days ago I saw great post from Norman Dunbar on how to fix a broken ASM spfile.

With version 11gR2 ASM spfile can be stored in ASM diskgroup and by default Oracle Installer will put it there. So if you want to create pfile from spfile your ASM instance should be up and running.

If you have incorrect parameter in ASM spfile which is blocking ASM to start than you have slight problem. You (more...)

Restore to Restore Point on Standard Edition (no Flashback technology)

Restore points and Flashback database are nice features introduced in 10g database that provide efficient point in time recovery to reverse unwanted data changes.

But what if you have Standard Edition database:

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;
alter database flashback on
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

In Standard Edition you don’t have Flashback Database feature, but you can still create restore points and (more...)

12c Migrate Database from non-ASM to ASM using online relocation of data files

There are many articles explaining how to migrate database from file system into ASM. You could use RMAN to create an image copy of the database into ASM and switch to the database copy, restore database from backup sets into ASM or create duplicate database.

All of these RMAN features are available on Oracle versions before 12c.

In this post I will use slightly different approach - using online relocation of data files into ASM.


MariaDB – Measure Replicaton Lag and Check / Fix Replication Inconsistencies using Percona tools

Percona Toolkit is collection of command-line tools to perform many MySQL tasks like creating backups, finding duplicate indexes, managing replication, etc.

In this post I will talk about how to measure replication lag and check/fix replication inconsistencies with this tools:

I am using environment from previous blog post.
Master-Master replication with MariaDB 10.0.16 database on Debian 7.

Install Percona Toolkit on both nodes:

$ sudo wget percona.com/get/percona-toolkit.deb

$ (more...)

MariaDB(MySQL) Master-Master Replication

The simplest and probably most common replication method is master-slave replication. Basically, data is replicated from master database to the slave. In case of master database failure you must get the slave database up-to-date before failover and then promote slave to be new master.

Another method is to set up replication in both directions called master-master replication. But you must be aware that this setup brings some potential issues as data changes are happening on (more...)

ORA-19599 block corruption when filesystemio_options=SETALL on ext4 file system using Linux

Few days ago I experienced strange issue in my development environment running on OEL 5.8 with EXT4 filesystem. Note - EXT4 filesystem is supported from OEL 5.6 version.

This was virtual machine running oldish Oracle database.

I noticed that backup for my database is failing because of archive log corruption. As this is development database I've simply deleted corrupted archive logs and initiated full backup again. But backup (more...)

Mount ASM diskgroups with new ASM instance

Imagine you have 11gR2 Oracle Restart configuration with database files located in ASM.

After server crash you realized that local disks are corrupted and with local disks you lost all Oracle installations. Even though this is important system you don’t have database backup (always take backups!).

But you managed to save all ASM disks as they were located on separate storage.

This will be small beginner guide on how to help yourself in such (more...)

Increase disk space for VM running Linux

When I create virtual machines on my notebook I always create too small disk for root partition or partition where I put Oracle binaries. After a while when I want to perform upgrade, or install another Oracle software, there is not enough space. This time I want to note steps about how to increase disk free space.

I can easily extend or shrink my logical volumes because I am using LVM in my virtual machines. (more...)

Using Oracle Flex ASM with single instance database

Oracle Flex ASM was introduced in 12c version. This is one of the best features introduced with new version in my opinion.

I won’t speak in detail about Flex ASM because you can find more information in documentation. In this post I will concentrate on how Flex ASM handles crash of ASM instance.

For this test I’ve created 2 node cluster - 12c Grid Infrastructure with Flex ASM enabled.

$ asmcmd showclustermode
ASM cluster : (more...)