Simon Krenger

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.

As the first wrapper release in 2016, Tanuki released the latest wrapper version. So I hereby provide the latest version 3.5.28 of the Tanuki Java Service Wrapper for Windows x64.

Read the rest of this entry

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.

After nearly 9 months, Tanuki released a new version of their wrapper. So I hereby provide the latest version 3.5.27 of the Tanuki Java Service Wrapper for Windows x64.

Read the rest of this entry

Alright, so when we need to recreate a database link for some reason and we do not happen to have the password handy, we’re usually stuck. However, sometimes there is a way to recover passwords for database links.

The method described below only works for the “old” password versions (<= 11.2.0.2)!

Starting with Oracle 11.2.0.2, Oracle salts the password hashes, therefore you will need to crack the password and cannot just query it. However, if the database link was created pre-11.2.0.2, the password is saved in an “old” format without the salt. To check if there are any database links with this old format, query SYS.LINK$ like so:

So here is another post I will keep just for my own reference.

Whenever I need to perform an incomplete recovery of the database, I usually use the SET UNTIL TIME to specify the time for an incomplete recovery. Sadly, when NLS_LANG is not set properly, the time format that needs to be used is not something I can remember.

So here is an example for using SET UNTIL TIME with a sane time format:

run {
set until time "to_date('22-APR-2015 13:30:00','DD-MON-YYYY HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}

You can find more examples in the Oracle documentation.

Last week, I launched a new website for my csqlplus project.

What is csqlplus, you might ask. The csqlplus script is a wrapper for SQL*Plus and will allow you to query multiple databases at once, so bascially a SQL*Plus for clusters:

Often, Oracle database administrators need to run commands on multiple databases at once. With csqlplus, a wrapper for the sqlplus command, a DBA can quickly query multiple databases simultaneously.

Basically, csqlplus is a simple wrapper script to call sqlplus for all databases specified in a file.

You can download the script from the website www.csqlplus.org.

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: