Infrastructure at your Service

Category Archives: Database Administration & Monitoring

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
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
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
Mouhamadou Diaw

EDB Failover Manager 3.0 and PostgreSQL 10.1

By | Database Administration & Monitoring | 3 Comments

Edb Failover Manager edb-efm30 is now released and supports PostgreSQL 10.1. Efm is an EDB tool which can be used to perform a switchover and a failover in a PostgreSQL standby environment. In this blog we are going to see how we can install and configure efm in a standby environment. We suppose that the standby is already confugured and is running. We also have not configured any VIP. I just use 3 virtual machines…

 
Read More
David Hueber

ODA migration challenges: Non-OMF to OMF + 11.2.0.3 to 11.2.0.4

By | Database Administration & Monitoring | One Comment

To do some application and performances tests, I had to copy a database from a third party Linux server to an ODA X7-2M. Looks pretty simple on the paper, but 2 small challenges came into the game. The first was that of course the source database was in Non-OMF while ODA works fully in OMF. The second was that the source database is running 11.2.0.3 which is not supported and cannot be installed on the…

 
Read More