Infrastructure at your Service

Daniel Westermann

PostgreSQL 11: Procedures and transaction control

By | Database Administration & Monitoring | No Comments
 

 Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL. Of course you can create functions which do not return anything but the possibility to create a procedure was not there. That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit. On top of that there was another commit which enables transaction control inside procedures which can be quite interesting. Lets see how…

Read More
Natascha Karfich

redhat event Ansible Automates Zurich

By | Unclassified | No Comments
 

 Here are some fresh personal impressions from the “Ansible Automates Zurich” half day event organized by redhat. There was a overwhelming interest for this event. About 200 people filled the room and there were many more on the waiting list. The event started with a networking lunch. My impression was, that the visitors were not only interested in the food, but also gaining some practical knowledge about Ansible. Most of them are already using Ansible….

Read More
Stéphane Haby

SQL Server 2017: Can I run a “standard” rebuild on the index after a Resumable Online Index Rebuild is suspended?

By | Database Administration & Monitoring, Development & Performance, SQL Server, Technology Survey | No Comments
 

 This article is an additional article to my precedent about “SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?” The goal is to see after I suspend the rebuild, if I can run a traditional rebuild without the option RESUME.   I start an index rebuild with the option Resumable: ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] REBUILD WITH (RESUMABLE = ON, ONLINE = ON) GO   In a new query window,…

Read More
Stéphane Haby

SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?

By | Database Administration & Monitoring, Development & Performance, SQL Server, Technology Survey | No Comments
 

 Last week, I presented this option in our internal event, the dbi xChange. My colleague David Barbarin asked me this question, what’s append if an interruption occurs (reboot, SQL Server service stopped,…) during a Resumable Online Index Rebuild? I was not able to answer because I forgot to do this test. The new enhancement for an index rebuild provides the capability to suspend, resume or abort an online index operation (msdn reference). I always use…

Read More
Franck Pachot

Result cache invalidation caused by DML locks

By | Oracle | No Comments
 

 In a previous post about Result Cache, I was using ‘set autotrace on’ in my session to see when the result cache hit saves the logical reads. I got a comment, by e-mail and with full test case, mentioning that the invalidation I had when doing some DML does not occur when autotrace is set to off. I reproduced the problem, on 18c because I use my cloud services to do my tests, but it…

Read More
Mouhamadou Diaw

Automatic Block Media Recovery in a DataGuard

By | Database Administration & Monitoring | No Comments
 

 With Oracle 12.2, in a Data Guard environment corrupted data blocks can be automatically replaced with uncorrupted copies of those blocks. There are just some requirements: • The physical standby database must be operating in real-time query mode, which requires an Oracle Active Data Guard license. • The physical standby database must be running real-time apply. Automatic block media recovery works in two directions depending on whether the corrupted blocks are encountered on the primary…

Read More
Clemens Bleile

Connect-times to the DB suddenly become very slow using sqlcl

By | Database Administration & Monitoring, Database management, Development & Performance, Oracle | 2 Comments
 

 I recently wrote a couple of sql-scripts which had to run on all of my customer’s DBs. The sql-scripts had to be started from a Linux-client, which does not have any Oracle client software installed. So I thought of using sqlcl (see http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html), because there is no need to “install” something then. All I needed was an installed JRE on the Linux-machine. Fortunately that was available. So I downloaded the newest version of sqlcl and…

Read More
Franck Pachot

18c PDB switchover

By | Oracle | No Comments
 

 In multitenant, the recovery and availability are at CDB level. But customers asked for a switchover at PDB level so Oracle has done that in 18c, based on refreshable PDBs. For this test I have two multitenant database on an Oracle Cloud service in 18c: CDB1 and CDB2. The only special thing I did was disable the mandatory TDE encryption, because I was not able to have the switchover working. With TDE encryption, I got…

Read More
Franck Pachot

Enabled, Accepted, Fixed SQL Plan Baselines

By | Oracle | No Comments
 

 When the documentation is not always clear, I prefer to build a test case to be sure about the behavior in different context and different versions. Here is a test on SQL Plan Management to show which plan is chosen among the different states of SQL Plan Baselines: Enabled, Accepted, Fixed. Thanks to Oracle ACE program, I have some Oracle Cloud credits to quickly provision a database, so I tested that on Oracle 18c.

Read More
Daniel Westermann

Parallel pg_dump is slow by default?

By | Database Administration & Monitoring | No Comments
 

 Short answer: Yes, it is. Being at a customer the last days we wanted to parallel pg_dump a 2TB database. We were quite surprised that it was quite slow and it was not immediately clear why it was. Well, the answer is in the documentation: When you go for parallel dumps you need to use the directory format and this comes with: “This format is compressed by default and also supports parallel dumps.”. Compression takes…

Read More
Elisa Usai

MySQL – Foreign keys and “Cannot delete or update a parent row: a foreign key constraint fails” error

By | Database Administration & Monitoring | No Comments
 

 As you know, foreign keys establish a sort of relationship between 2 tables. MySQL requires InnoDB storage engine to support foreign keys. In our example, we have the following parent table in a MySQL 5.7.21 server: mysqld3-(root@localhost) [sakila]> show create table actor\G *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON…

Read More