Change Repository DB Connection in EM 13c

Sometimes it’s hard to find what you’re looking for in the Oracle documentation or on MOS if you don’t already know what exactly to search for. This happened to me while trying to find out  how the method of changing the DB connection for the EM repository changed in 13c. So I thought my findings are worth sharing.

In earlier releases of Enterprise Manager (Grid Control or Cloud Control), connection settings were stored in a file named “”. While this file still existed on the EM 13.2 environment I was working on, there wasn’t any connection string in it.

After searching for quite a while (sifting a wealth of outdated documents), I found out there’s a specific emctl command to set the DB connection. This command already exists since 11g where it had to be used when the repository DB was put in a RAC. Now it seems to be the only way to change the connect string, be it RAC or single instance.

Continue reading

Enable HyperFIDO U2F Key on Linux

Recently, I bought the Hypersecu HyperFIDO K5 Key to help me secure access to several websites and services with U2F (“Universal Two-Factor Authorization”).

This works fine and easy on Windows, but with Linux things get a little complicated: The key isn’t accessible to all users by default. This has to be activated using udev rules, which is widely documented on the web, but very often erroneous or outdated. Here’s what I found:

Continue reading

Proposing a SQL-only Diagnosis for Blocking Locks in Single Instance and RAC

When you have to troubleshoot blocking locks in your Oracle DB, you might be lucky enough to have a GUI tool handy that helps you with that (e.g. Enterprise Manager or TOAD). Otherwise you’ll likely have to resort to Oracle’s pre-installed script “utllockt.sql”. Unfortunately, this script only shows locks in the DB instance it’s running on. However, locks chains may span multiple instances in a clustered environment.

In this brief post I’d like to propose a RAC-aware alternative to “utllockt.sql” using only SQL so it can be run in any Oracle client.


Picture: Mike Gabelmann / flickr / CC-BY-NC-SA

Continue reading

Visualizing System Statistics in SQL Developer – OTN Appreciation Day

Tim Hall inspired me with his #ThanksOTN (or “OTN Appreciation Day“) campaign to continue my mini-series on leveraging SQL Developer Reports for DBA tasks. Today with visualizing historical System Statistics from Statspack for performance analyses.

#ThanksOTN I have three favourite Oracle features to rave about today:

  1. Analytic SQL
  2. Statspack
  3. SQL Developer (particularly the reports)!

Continue reading

orachk “Could not login to (SID)”

Today I stumbled across a strange error when I tried to run orachk on a customer’s system:

$ ./orachk
Enter ORACLE_HOME for PRODDB : /opt/oracle/product/rdbms/12.1.0
Could not login to PRODDB using /opt/oracle/product/rdbms/12.1.0. Try again (3 attempts remaining)


A quick internet search revealed nothing useful, so I checked MOS. Document 1989401.1 had the right hint: It was due to a customized “login.sql” that had an “ALTER SESSION …” in it. This leads to incorrect results for orachk’s scripts. Disabling login.sql (or glogin.sql, if you use this one) solved the problem.

login.sql can be so evil!

Side note: The Oracle Docs say that “login.sql” is executed from your current directory or from the directory list in $SQLPATH. Neither of that applied to my environment, but ORACLE_PATH was set and pointed to a directory containing login.sql. Looks like this important detail is missing in the docs.

OPatch 12c: emocmrsp gone missing

tl;dr: The option “-ocmrf” is no longer mandatory nor implemented anymore.

Last week, the quarterly PSU came out, and just a few hours later I tried to apply it to a Oracle Restart + DB system under OL 6.7.

This would have run smoothly if it wasn’t for the fact that this was the first patch for this system and therefore I needed to create a OCM response file for silent patching with “opatchauto”.

Too bad that there was no executable “emocmrsp” unter “./ocm/bin” when I tried to use the latest OPatch-Tool ( WTF?!

Continue reading