Log Buffer #434: A Carnival of the Vanities for DBAs

This Log Buffer Edition throws spotlight on some of the salient blog posts from Oracle, SQL Server and MySQL.

Oracle:

  • STANDARD date considerations in Oracle SQL and PL/SQL
  • My good friend, Oracle icon Karen Morton passed away.
  • Multiple invisible indexes on the same column in #Oracle 12c
  • Little things worth knowing: Data Guard Broker Setup changes in 12c
  • Things that are there but you cannot use

SQL Server:

MySQL 5.7 Community Contributor Award Program 2015!

A couple of months ago the MySQL Community team announced a new award program called "MySQL 5.7 Community Contributor Award Program” where we wanted to identify & reward the most active community members / contributors / active bloggers from the MySQL community world. Below you can find a list of our current winners who met any of following criteria:

  1. The most active code contributor
  2. Bug reporter
  3. The most active MySQL blogger
  4. People who play (more...)

Log Buffer #433: A Carnival of the Vanities for DBAs

This Log Buffer Edition covers Oracle, SQL Server and MySQL blogs of the running week.

Oracle:

  • While checking the sources of the Cassandra/NetBeans integration into GitHub yesterday, something went very badly wrong and ALL the source files in my Maven project that disappeared!
  • AWR Reports, Performance Hub, historisches SQL Monitoring in 12c
  • Oracle Database Mobile Server 12c: Advanced data synchronization engine
  • ORA-39001, ORA-39000 and ORA-39142
  • ORA-15410: Disks in disk group do not (more...)

Log Buffer #432: A Carnival of the Vanities for DBAs

Yet again, this log buffer edition brings some rich blog posts from Oracle, SQL Server and MySQL.

Oracle:

  • Installing Oracle XE, ORDS and Apex on CentOS
  • Major Growth is Expected in the DBaaS Space. Are Your Skills Ready?
  • How to Hide Actions in OBPM 12c Workspace
  • You can configure auto refresh for ADF BC cached LOV and this works out of the box, no special coding is needed.
  • Search and Replace in Oracle SQL Developer (more...)

Log Buffer #431: A Carnival of the Vanities for DBAs

This Log buffer edition covers Oracle, SQL Server and MySQL blog posts about new features, tips, tricks and best practices.

Oracle:

  • Traditionally, assigning specific processes to a certain set of CPUs has been done by using processor sets (and resource pools). This is quite useful, but it requires the hard partitioning of processors in the system. That means, we can’t restrict process A to run on CPUs 1,2,3 and process B to run on (more...)

Pillars of PowerShell: SQL Server – Part 1

Introduction

This is the sixth and final post in the series on the Pillars of PowerShell, at least part one of the final post. The previous posts in the series are:

  1. Interacting
  2. Commanding
  3. Debugging
  4. Profiling
  5. Windows OS

PowerShell + SQL Server is just cool! You will see folks talk about the ability to perform a task against multiple servers at a time, automate implementing a configuration or database change, or just obtaining a bit of consistency when (more...)

ORDER BY CASE

Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a CASE statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort.

I gave my students this ORDER BY clause as an example:

  CASE
    WHEN filter = 'Debit' THEN 1
    WHEN filter = 'Credit' THEN 2
     (more...)

Log Buffer #430: A Carnival of the Vanities for DBAs

This Log Buffer Edition cuts through the crowd and picks some of the outstanding blog posts from Oracle, SQL Server and MySQL.


Oracle:

  • Continuous Delivery (CD) is a software engineering approach in which teams keep producing valuable software in short cycles and ensure that the software can be reliably released at any time.
  • Query existing HBase tables with SQL using Apache Phoenix.
  • Even though WebLogic with Active GridlLink are Oracle’s suggested approach to deploy (more...)

Log Buffer #429: A Carnival of the Vanities for DBAs

This Log Buffer Edition gathers a wide sample of blogs and then purifies the best ones from Oracle, SQL Server and MySQL.

Oracle:

  • If you take a look at the “alter user” command in the old 9i documentation, you’ll see this: DEFAULT ROLE Clause.
  • There’s been an interesting recent discussion on the OTN Database forum regarding “Index blank blocks after a large update that was rolled back.”
  • 12c Parallel Execution New Features: 1 (more...)

Log Buffer #428: A Carnival of the Vanities for DBAs

The Log Buffer Edition once again is sparkling with some gems, hand-picked from Oracle, SQL Server and MySQL.

Oracle:

  • Oracle GoldenGate 12.1.2.1.1  is now certified with Unity 14.10.  With this certification, customers can use Oracle GoldenGate to deliver data to Teradata Unity which can then automate the distribution of data to multiple Teradata databases.
  • How do I change DNS servers on Exadata storage servers.
  • Flushing Shared Pool Does Not Slow (more...)

Coverting MySQL database character set to UTF8

Recently I was engaged to convert the character set for few MySQL databases from latin1 to UTF8 collation utf8_general_ci. This article describes the approach taken for doing the same.

First I will describe various levels at which we can change the character set and collation and then we will see how to convert existing data in a database to required character set.

Backup your database:

Before even thinking about changing character set for your database, (more...)

Log Buffer #427: A Carnival of the Vanities for DBAs

This Log Buffer Edition covers various blog posts from the last week regarding Oracle, SQL Server and MySQL.

Oracle:

  • Merging Overlapping Date Ranges with MATCH_RECOGNIZE
  • The latest version of Enterprise Manager, EM 12.1.0.5, has been announced!
  • Kdump is the Linux kernel crash-dump mechanism. In the event of a server crash, Kdump creates a memory image (vmcore) that can help in determining the cause of the crash.
  • APEX Connect Presentation and Download of (more...)

Bash Arrays & MySQL

Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of Download MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure (more...)

Pointing MySQL Read-Replica to new master after failover

Here I am describing a simple scenario where we have a read replica (other than slave) which is currently pointing to master mysql instance.
We have another slave which is also pointing to same master. What we are going to see is, how to point read replica to new master after failover.

Environment:

To make you familier with environment, we have 3 instances of mysql

mysql_a -> Current master
mysql_b -> Current slave
mysql_c -> read replica  (more...)

Eclipse on Fedora

While I previously blogged about installing Netbeans 8, some of my students would prefer to use the Eclipse IDE. This post shows how to install and configure Eclipse IDE, include the mysql-connector-java.jar, and write Java to access the MySQL.

You can download Eclipse IDE and then open it in Fedora’s Archive Manager. You can use the Archive Manager to Extract the Eclipse IDE to a directory of your choice. I opted to extract (more...)

“show slave hosts” on master not reporting hostname

If you have multiple MySQL slaves or complex topology, its difficult to remember what slaves are replicating from which master.

We can go to slave servers and check “show slave status” which list master host, but if we want to find out other way, we have can run “show slave hosts” from master.
This will list down all the slaves that are getting replicated from this master.

Only problem I faced was, “show slave (more...)

Restoring Slave when GTID is enabled on master

This is a quick post on issues I faced while restoring a slave when GTID was enabled on master.

I have master created few days back and now I am trying to create a slave. I have GTID enabled on master.

Master status:

root [mysql] >show master status G
*************************** 1. row ***************************
             File: bin_log.000002
         Position: 16682
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-59
1 row in set (0.00 sec)
root [mysql] >show  (more...)

Test MySQL on AWS quickly

Using sysbench to performance test AWS RDS MySQL hardware is an easy three step  operation. 

Sysbench creates synthetic tests and they are done on a 1 mil row 'sbtest' table that sysbench creates in the MySQL database you indicate. The test doesn't intrude with your database schema, and it doesn't use your data, so it is quite safe. The test is an OLTP test trying to simulate event operations in the database as it (more...)

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:
pt-heartbeat
pt-table-checksum
pt-table-sync


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...)