Simon Krenger

Anyone who has worked with Oracle has encountered the Oracle “hr” schema in one way or another. The Oracle example schema provides a few simple tables with example data to test out some queries or learn SQL.

So when working with MySQL, I like to have the same schema as well. Luckily, Andrei Ciobanu feels the same way and he provides a wonderful adaptation of this sample schema for MySQL on his website: HR Schema for MySQL and Maria DB.

The SQL script for the schema can be found on GitHub: nomemory/hr-schema-mysql (or in my fork: simonkrenger/hr-schema-mysql).

Thank you Andrei!

While upgrading an environment to Oracle 12c, one thing that needs to be done is to upgrade the RMAN catalog. One change introduced by Oracle is that there are new features used in the version 12 RMAN catalog. So if you try to upgrade an RMAN catalog running on Oracle Standard Edition, you will encounter the following error:

RMAN> upgrade catalog;

error creating create_deleted_object_seq
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-00439: feature not enabled: Partitioning

Mike Dietrich from the Oracle Upgrade blog has written an excellent article concerning this issue. Most specifically, MOS Note 1927265.1 notes implicitly that Enterprise Edition is a requirement:

Even though I am not really happy about the new recertification requirements from Oracle, I took it to myself to refresh my OCP certificate. I completed the exam 1Z0-060 and can call myself now a “Oracle Database 12c Administrator Certified Professional”.

Although I am not allowed to disclose information about the exam, I must say that the I found this exam easier than previous exams. Since the exam is a multiple-choice exam, one can often answer the questions by just guessing or by exclusion principle…

So today I tried to install the Oracle Preinstall RPM on a freshly installed Oracle Linux 7 machine. However, when I executed yum search rdbms the preinstall package was nowhere to be seen!

It turns out that we need to enable the Oracle Linux 7 “addons” repository in order to find the package. Swiss blogger Martin Berger put me on the right track.

To enable the repository, open the file /etc/yum.repos.d/public-yum-ol7.repo and find the following repository entry:

As of PSU 12.1.0.2, Oracle introduced “PDB State Management Across CDB Restart”, which will automatically put a pluggable database in a previously defined state on database startup. To show the current saved state of the PDBs, you can query the documented view cdb_pdb_saved_states:

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

CON_NAME		       INSTANCE_NAME		      STATE
------------------------------ ------------------------------ --------------
P1			       cdb2			      OPEN
P2			       cdb2			      OPEN

But beware: When you unplug and plug in the database, this saved state will be lost.

I think this is one of the more awesome improvements in 12.1.0.2, since the original startup trigger was more of a workaround than a real solution.

Storage costs are a major matter of expense in any large database environment. Therefore, in order to operate a cost-optimized environment, it makes sense to regularly review all databases in terms of datafile sizes. Usage of these files can vary dramatically, depending on the application using the database. Therefore, we need a way to quickly identify datafiles which we can shrink in order to save storage costs.

Because we cannot really influence how Oracle places the extents in a datafile, we cannot simply look at the “used” size of the datafile and resize it to that value, but need to find the HWM (high water mark) of each datafile.

With Oracle 12c, Oracle introduced a new way to insert primary keys into tables: The GENERATED BY DEFAULT and DEFAULT keywords.

In this post, I want to compare the performance of different methods to insert a primary key into a table in Oracle. The comparison will also include older methods, such as using a trigger or specifying the primary key in the INSERT statement. These methods can also be used in Oracle versions earlier than 12c.

First of all, let’s look at the possible ways to insert the value of a sequence into a table:

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

To connect to a PDB, you have to use the service name of the pluggable database. But what when there is an older legacy application that can only use an SID to connect to a database? And what if we just migrated an older legacy database to a PDB? When starting the application, you might see something like:

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

There is a parameter called USE_SID_AS_SERVICE_listener that can be used to tell the listener to handle SIDs like service names. So the listener will interpret the SID given in the connection as a service name and will then connect you to the database.