How Enterprise Manager Detects the Version of Oracle Databases

    Mar 12, 2017 10:08:16 AM by Gokhan Atil

    You know that patch numbering has been changed since November 2015, and the new format replaces the numeric 5th digit of the bundle version with a release date in the form “YYMMDD”. Let’s say you applied PSU 160719 to your 11.2.0.4 database, the exact version of your database becomes 11.2.0.4.160799. We also know that PSUs do not change the Oracle release version information that can be queried from v$version (Doc ID 861152.1), so when you query your database, you still see 11.2.0.4.0:

    select * from v$version;
    BANNER
    -------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE 11.2.0.4.0 Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production

    On the other hand, when you list your database targets on Oracle Enterprise Manager, it shows the exact version of Oracle Database.

    I wondered how EM13c gets the information, and saw that it uses different queries depending on version of the target database. So first it queries v$version and then if the database version is lower than 11.2.0.2, it runs the following query:

    SELECT NVL(
    (SELECT version FROM
    (SELECT version || '.' || id version
    FROM dba_registry_history
    WHERE NAMESPACE = 'SERVER'
    ORDER BY ACTION_TIME DESC) WHERE rownum = 1),
    (SELECT version FROM v$instance)) version
    FROM dual;

    If the database version is greater than or equal to 11.2.0.2, and lower than 12.1.0.2, then it runs the following query:

    SELECT NVL(
      (SELECT version FROM
    (SELECT version || '.' || id version
    FROM dba_registry_history
    WHERE NAMESPACE = 'SERVER'
    AND BUNDLE_SERIES = 'PSU'
    ORDER BY ACTION_TIME DESC) WHERE rownum = 1),
    (SELECT version FROM v$instance)) version
    FROM dual;

    If the target database version is greater than or equal to 12.1.0.2, then it runs the following query:

    SELECT NVL(
    (SELECT version FROM
    (SELECT version || '.' || bundle_id version
    FROM dba_registry_sqlpatch
    WHERE BUNDLE_SERIES = 'PSU'
    ORDER BY ACTION_TIME DESC) WHERE rownum = 1),
    (SELECT version FROM v$instance)) version
    FROM dual;

    I found it interesting so I wanted to share it. One day, if you need to get the exact version of your database (including PSU), it may be worth your while.

    Tags: Oracle

    Gokhan Atil

    Written by Gokhan Atil

    Gokhan Atil has over 15 years of experience in the IT industry and a strong background in database management (Oracle 8i,9i,10g,11g), software development and UNIX systems. He is an Oracle certified professional for EBS R12, Oracle 10g and 11g. Gokhan specializes in high availability solutions, performance tuning and monitoring tools. Gokhan is a founding member and current vice president of Turkish Oracle User Group (TROUG). He’s also a member of Independent Oracle User Group (IOUG). Gokhan presented at various conferences, and he is one of co-authors of “Expert Oracle Enterprise Manager 12c” book.