kaysariqbal

[kaysariqbal]

PostgreSQL 13 logical replication configuration

PostgreSQL 13 logical replication configuration

Prerequisite: Two VM hosts with PostgreSQL 13 preinstalled. Master Node: pg13-master; 192.168.56.71 Standby Node: pg13-replica; 192.168.56.72 Logical replication uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe to and may subsequently re-publish data to allow…

[kaysariqbal]

PostgreSQL 13 stream replication configuration using rsync in Linux

PostgreSQL 13 stream replication configuration using rsync in Linux

Prerequisite: Two VM hosts with PostgreSQL 13 preinstalled. Master Node: pg13-master; 192.168.56.69 Standby Node: pg13-standby; 192.168.56.70 rsync should be installed on the nodes to transfer WAL files to the standby node. Configuration Steps: Configure passwordless authentication as continuous WAL archiving will be performed using rsync from master to standby node. On the master, execute…

[kaysariqbal]

PostgreSQL 13 standby configuration using file based log shipping by rsync in Linux

PostgreSQL 13 standby configuration using file based log shipping by rsync in Linux

Prerequisite: Two VM hosts with PostgreSQL 13 preinstalled. Master Node: pg13-master; 192.168.56.67 Standby Node: pg13-standby; 192.168.56.68 rsync should be installed on the nodes to transfer WAL files to the standby node. Configuration Steps: Configure passwordless authentication as continuous WAL archiving will be performed using rsync from master to standby node. On the master, execute…

[kaysariqbal]

MySQL 8.0 table level replication in Windows

MySQL 8.0 table level replication in Windows

The way to achieve table-level replication in MySQL is to use the combination of two replication parameters named replicate-do-db and replicate-ignore-table along with other mandatory replication parameters. You need to identify the name of the database of your tables you need to replicate and from the database opt out the tables that you don’t want to replicate using the two parameters. Take a…

[kaysariqbal]

Installing MySQL on CentOS Linux using the MySQL yum repository

Installing MySQL on CentOS Linux using the MySQL yum repository

Requirements: MySQL 8.0 Cent OS 7 VM host or Physical Server/Desktop Internet on the installation Server/Desktop Installation: Create an operating system user named as “mysql” by logging in to the operating system root user and add the user to the “sudo” list. # useradd mysql # passwd mysql # usermod -aG wheel mysql Install the wget package to download the mysql yum repository. # yum install…

[kaysariqbal]

ORA-01031: insufficient privileges raised during materialized view creation on another schema

ORA-01031: insufficient privileges raised during materialized view creation on another schema

Scenario:

ORA-01031: insufficient privileges raised during the creation of materialized view (MVIEW) on another schema by the SYSTEM user.

CREATE MATERIALIZED VIEW kaysar.mview_test1 BUILD IMMEDIATE AS (SELECT * from kaysar.test);

[Error] Execution (4: 26): ORA-01031: insufficient privileges

Investigation:

The SYSTEM already has the privilege of CREATE_MATERIALIZED_VIEWto create the MVIEW on…

[kaysariqbal]

[ERROR] mariadbd: Out of sort memory

[ERROR] mariadbd: Out of sort memory

Scenario:

Got below error from application end.

SQL state [HY001]; error code [1038]; (conn=15945) Out of sort memory, consider increasing server sort buffer size; nested exception is java.sql.SQLException: (conn=15945) Out of sort memory, consider increasing server sort buffer size at…

[kaysariqbal]

Detected table cache mutex contention at instance 1: % waits. Additional table cache instance activated. Number of instances after activation: 2.

Detected table cache mutex contention at instance 1: % waits. Additional table cache instance activated. Number of instances after activation: 2.

Scenario:

All the three nodes of Galera cluster uses 98 percent of memory and swapping heavily, no DML could be performed.

Investigation:

From the error log on the node 3 the below error has been notified:

Detected table cache mutex contention at instance 1: 60% waits. Additional table cache instance activated. Number of instances after activation: 2.

Querying the global status for the variable t…

[kaysariqbal]

Obtain DDL scripts for MariaDB/MySQL schema objects

Obtain DDL scripts for MariaDB/MySQL schema objects

mysqldump backup utility of MySQL/MariaDB  supports the export of metadata or specifically creating DDL scripts with the option –no-data.

$ mysqldump -uroot -p test --no-data --routines --events --triggers > test_ddl.sql

Note: Remember to mention the –routines, –events and –triggers also to export the related objects.

[kaysariqbal]

Obtain DDL scripts for oracle schema objects

Obtain DDL scripts for oracle schema objects

Background:

During the application development phase and more often the DDL needs to be backed up for the schema objects in .SQL format which supports the readability and recover or portability of the objects creation later on when needed. This could be easily done by the oracle data pump utility.

Workaround:

Step1: First make the export dump for the intended schema for the metadata only.

$ expdp…

[kaysariqbal]

MySQL Replication on CentOs 7: Configure binary log file position based replication (Fresh server with no data)

MySQL Replication on CentOs 7: Configure binary log file position based replication (Fresh server with no data)

Prerequisites:

Two virtual machine each of 2GB RAM, 10 GB HDD and Two vCPU with CentOS 7 installed on these.

Workaround:

Configure the networking between two nodes:

Step1: Change the host name of the master node to master-node and make entries to the /etc/hosts file.

# hostnamectl set-hostname master-node # echo '192.168.56.103 master-node' >> /etc/hosts # echo '192.168.56.104 slave-node-01' >>…

[kaysariqbal]

ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled

ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled

MariaDB Error:

ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) Cause: The user who is executing the create function doesn't have the required SUPER privilege. Solution: AS for security concern the user couldn't be given the SUPER privilege hence setting the…

[kaysariqbal]

ORA-39142: incompatible version number 5.1 in dump file

ORA-39142: incompatible version number 5.1 in dump file

Error:

Experienced the below error during the import of the export dump file to the Oracle 12.1.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39142: incompatible version number 5.1 in dump…

[kaysariqbal]

Implement purging of table data by dropping partitions automatically

Implement purging of table data by dropping partitions automatically

Applications generates temporary data which may need less than minutes to days, these data need to be purged to claim spaces on database and housekeeping. One way to do this is to create a partitioned table and drops the older partitions based on a regular interval.

The below example shows how to do this by dropping the oldest partition by a scheduler jobs.

Step1: Create a stored procedure which…

[kaysariqbal]

Interval partitioning in oracle

Interval partitioning in oracle

Range partitioning is a great way to manage historical data in oracle database, data to be inserted in a partitioned table should have the specific partition exists beforehand otherwise the insert operation will fail. How to create partition automatically when the data added to the table?

Interval partitioning is used to implement this feature which is an enhancement to the range partitioning.…

[kaysariqbal]

Connect to MariaDB from Oracle and perform DML on remote MariaDB table

Background:In enterprises most cases their database infrastructures or applications are built on different databases, sometimes data needs to by transferred or synced from one database to another heterogenous database. Oracle supports heterogenous connectivity to popular databases like MySQL, SQL Server, MariaDB, DB2, Sybase and more on the list. Here we will try to establish connection from…

[kaysariqbal]

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_****

ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_****

Error:

Errors in file /u01/app/oracle/diag/rdbms/bpay/bpay/trace/bpay_j000_24499.trc: ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_586″ ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: at “SYS.DBMS_STATS”, line 47207 ORA-06512: at “SYS.DBMS_STATS_ADVISOR”, line 882 ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 20059 ORA-06512: at…