Infrastructure at your Service

Category Archives: Database Administration & Monitoring

Stéphane Haby

SQL Server, Integration Services and Package Versions

By | Database Administration & Monitoring | No Comments

A lot of people ask me about the different versions of SQL Server Integration Services and packages. I create rapidly this little table to summarize it: With SQL Server 2008 and 2008 R2, the SSIS version is the same than SQL Server Integration Services 10.0. With SQL Server 2005, the SSIS version is not contained in the service name. To get this information, you must check directly in the MsDtsSrvr.exe file. How to know your…

 
Read More
Joël Cattin

“FRA full” alerts flood the Alert Log

By | Database Administration & Monitoring | 4 Comments

We discovered a strange behavior in the Alert Log when the Fast Recovery Area (FRA) is full and the database wants to write something inside it (for example an archivelog). This case concern Oracle 11.2.0.3 databases and higher. Here is a demo with a 12c database (12.1.0.2) : First, to reproduce the behavior, I set a very small size to the FRA : SQL> alter system set db_recovery_file_dest_size = 1M; System altered. SQL> As you can see, the…

 
Read More
Daniel Westermann

Representing ranges with one data type?

By | Database Administration & Monitoring | No Comments

How many applications do you know that need to define a validity of a row? And how many of these do that by adding two columns: valid_from and valid_to or similar column names? Well, in PostgreSQL (you already suspect it ) there is much more elegant way to do that. PostgreSQL knows the concept of range types. Several of these are pre-defined and you get them once you install PostgreSQL: int4range — Range of integer…

 
Read More
Stéphane Haby

SQL Server Tips: Logical names don’t match with physical files names

By | Database Administration & Monitoring | No Comments

During a copy of a database from an environment to another (from Production to pre-production for instance), people usually don’t changed the logical name of the data or log files when they rename the database and the physical file names. In my test environment, I create 2 copies of AdventureWorks and you can see that the logical name is the same for all three databases. I can choose 2 ways to detect if logical names…

 
Read More
Franck Pachot

Configure the Resource Manager with SQL Developer

By | Database Administration & Monitoring, Oracle | No Comments

Yes, the topics for the OCM 12c upgrade are online and I’m already looking at the topics. I’m talking about the ‘Configure the Resource Manager’ one here. Configuring Resource Manager in commandline is not easy. At the OCM exam you can do things faster when you have a GUI. Of course, you need to know where to find the command line API in case no GUI is available. In 11g, even if no Cloud Control…

 
Read More
Franck Pachot

DataGuard broker properties – Part I: imported parameters

By | Database Administration & Monitoring, Oracle | One Comment

When you are using the DataGuard Broker, you should always use the broker (with DGMGRL or OEM) to change the parameters that are managed, rather than changing them with ALTER SYSTEM. But do you know which parameters are concerned? I’ve divided the parameters in two parts. All are set by the broker, but only some of them are read when you add a new database to the configuration.  

 
Read More
Daniel Westermann

Connecting your PostgreSQL instance to an Oracle database – The PostgreSQL 9.5 way

By | Database Administration & Monitoring | 2 Comments

As you might know PostgreSQL 9.5 is currently in alpha 2. One of the new features will be the possibility to import foreign schemas. Having this it is no longer required to create all the foreign tables which saves a lot of work. Setting up oracle_fdw was done in exactly the same way as in the previous post. Make sure you use the latest version of oracle_fdw as some internals changed in PostgreSQL 9.5 which…

 
Read More
Grégory Steulet

MySQL FATAL: error 1040: Too many connections & 1135 Can’t create a new thread

By | Database Administration & Monitoring | No Comments

Hi folks, Some of you probably already got error 1040 by playing with sysbench for instance and multiplying the number of MySQL threads. Documentation and blogs usually simply state that you have to increase the number of max_connections and the open files limit. If you tried and you didn’t succeed this blog is for you. Despite the following entries in /etc/my.cnf the max_connections and open_files_limit are not set correctly: max_connections = 8000 open_files_limit = 8192…

 
Read More