Simon Krenger

In the past few months, I worked closely with Packt Publishing to create a new video course for MySQL 8. Today, the video course was released into the world. I am pround to present to you the new video course “MySQL 8 Recipes“:

In this video course, I tried to include the most common tasks for database administrators while focussing on the new features of MySQL 8. I included the following main chapters:

• Install and configure a new MySQL 8 database
• Upgrade existing databases to MySQL 8
• Perform typical administration tasks
• Master the new querying features in MySQL 8
• Perform performance-tuning tasks
• Optimize your database
• Access databases with PHP, Python, or Java

The video course was released under ISBN-13 9781788393638 and is available per now. Of course, since MySQL 8 is still under development, there will hopefully be many other new features in the new release. But I hope you enjoy this video course. Purchase it here.

In another post I explained how to create a full backup using MySQL Enterprise Backup. Of course, the most important part of the backup is not “making the backup” but the restore of said backup! A backup is worthless if you cannot restore it.

This is a fun one.

We developed a script to process certain indexes on a database somehow, it kept missing some of the indexes that clearly existed. We then found the problem: DBA_INDEXES had more entries than DBA_SEGMENTS. See the following example:

SQL> SELECT owner, index_name as i_name from dba_indexes WHERE owner = 'SIMON'
  2  MINUS
  3  SELECT owner, segment_name as i_name FROM dba_segments WHERE owner = 'SIMON';

OWNER                I_NAME
-------------------- --------------------------------------------------
SIMON                IDX_...
SIMON                IDX_...
SIMON                IDX_...
SIMON                PK_...
SIMON                PK_...
SIMON                UNQ_...
SIMON                UNQ_...

7 rows selected.

So here we see that there are clearly indexes for which there are no segments. We then looked at the tables where these indexes are located and noticed a particular thing: All the corresponding tables for these indexes were empty.

So the reason for this behaviour is called “Deferred Segment Creation“. This means that when a “CREATE TABLE” statement is issued and no rows are inserted, there are no segments that are created. This behaviour can be controlled by the DEFERRED_SEGMENT_CREATION parameter.

This makes sense in large schemas, where not all tables are populated. Instead of having segments created and extents allocated, only the definition of the table is saved. As soon as the table has at least one row, the segments are automatically created.

So today, during the upgrade of a database, all other databases using the same ORACLE_HOME stopped dead in their tracks. The ALERT log reported the following:

2014-09-30 13:34:41.622000 +02:00
Archived Log entry 24398 added for thread 1 sequence 24398 ID 0x2d91d796 dest 1:
2014-09-30 13:47:39.764000 +02:00
Errors in file /u00/app/oracle/diag/rdbms/kdb12345/kdb12345/trace/kdb12345_psp0_57147560.trc:
ORA-07274: spdcr: access error, access to oracle denied.
IBM AIX RISC System/6000 Error: 2: No such file or directory
PSP0 (ospid: 57147560): terminating the instance due to error 7274
2014-09-30 13:47:41.346000 +02:00
System state dump requested by (instance=1, osid=57147560 (PSP0)), summary=[abnormal instance termination].
System State dumped to trace file /u00/app/oracle/diag/rdbms/kdb12345/KDB12345/trace/KDB12345_diag_49938448_20140930134741.trc
2014-09-30 13:47:44.779000 +02:00
Instance terminated by PSP0, pid = 57147560

Hmm, so what to do? In the trace file, we find out a bit more:

When working with the new multitenant architecture of Oracle 12c, one is often confronted with questions like “Ok, is this or that possible in the pluggable database? And where is that value stored?”. In this article, I want to show you where the parameters of the pluggable databases are stored. Hint: there is no SPFILE for a pluggable database!

Consider the following container database (CDB), named “cdb2”, with the following pluggable databases (PDBs). Using SQL*Plus, connected to the CDB, we can issue show pdbs to get a list of the PDBs and their status:

As we are migrating the last databases from Oracle 10gR2 to the latest 11.2.0.4 release, there are some errors that we stumbled upon along our way. For example, when we tried to upgrade one particular database, during the upgrade the following error was thrown:

SQL> SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
  2   WHERE
  3    value = 'TRUE' and parameter = 'Oracle Database Vault';
SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
                 *
ERROR at line 1:
ORA-01722: invalid number

Ouch. That is something that you do not want to see during an upgrade! Needless to say, the upgrade won’t continue here, so how to fix this?

Well, basically this is a well-known error, and there is MOS note 1409844.1 that describes how to fix the problem: Catupgrd Returns : Ora-01722 Select To_number(‘DATA_VAULT_OPTION_ON’) From V$option

As you might know, I am primarily an Oracle guy. This means that for all my backup needs, I am using Oracle Recovery Manager (RMAN). I recently had the task to implement MySQL Enterprise Backup for a MySQL database (mysql-advanced-5.6.15-linux-glibc2.5-x86_64). My most important resource for this task was the MySQL Enterprise Backup User’s Guide.

Before starting, here is the deal: While “ALTER USER simon PASSWORD EXPIRE” exists, the “PASSWORD UNEXPIRE” statement does not exist in Oracle, see the ALTER USER statement in the language reference. Now, there is a good reason that command does not exist.

One problem that occasionally pops up is that a DBA needs to recreate a user with a password that he does not know. This happened to me when I had to create a schema with the same password on a development database. After I set an initial password for the developer, he exclaimed that he wanted the same password that he had for the schema X, but he did not want to tell me the password (don’t ask here, that’s a completely other story).

One way to do this is to use ALTER USER ... IDENTIFIED BY VALUES. Using the excellent article on the ALTER USER commands from Laurent Schneider, we can generate a dynamic SQL query to set a password without knowing the password itself!

Here is the query to read the password from sys.user$ from Laurents article:

As with all the new features that Oracle 12c brings us, we want to test them out! So one of the lesser known features in the new release is the Automatic Data Optimization with the Heat Map mechanism. Oracle describes the feature in a White Paper:

Heat Map is a new feature in Oracle Database 12c that automatically tracks usage information at the row and segment levels. Data modification times are tracked at the row level and aggregated to the block level, and modification times, full table scan times, and index lookup times are tracked at the segment level. Heat Map gives you a detailed view of how your data is being accessed, and how access patterns are changing over time. Programmatic access to Heat Map data is available through a set of PL/SQL table functions, as well as through data dictionary views.

Sounds great, how can we use it? It turns out, that is relatively easy, all you have to do is to set the HEAT_MAP parameter to “ON” (source) and add an ILM policy to your table: