How Enterprise Manager Detects the Version of Oracle Databases

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

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;

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;

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;

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.

Please share

8 Comments

Sergey

March 13, 2017 Reply

More stuff to wonder:

https://blogs.oracle.com/UPGRADE/entry/dba_registry_history_vs_dba

🙂
Eric

March 13, 2017 Reply

Thank you for the posting this!

Maybe this explains why the 5th digit is not showing up in EM for databases where I have applied a bundle patch? bundle_series=DBBP

I’ve yet to log a SR for this. Anyone else experienced this or know of of a fix.
- Gokhan Atil
  
  March 14, 2017
  
  You are welcome. I do not know if DBBP creates different records on dba_registry_history. Let’s hear what Oracle Support says.
Paul

March 21, 2017 Reply

Hi,

I also found, that physical standby databases (mounted) don’t show their PSU level. If you open them read only it will discover the PSU. Makes sense to me.

Paul
- Gokhan Atil
  
  March 22, 2017
  
  Yes Paul, exactly! The most dba_views are not accessible on recovery mode. This is why they detected with only v$version information.
Steve

March 22, 2017 Reply

The 12c should be updated to something like:

SELECT NVL(SELECT version FROM
(SELECT version || ‘.’ || bundle_id version
FROM dba_registry_sqlpatch
WHERE BUNDLE_SERIES in (‘DBBP’,’PSU’)
ORDER BY ACTION_TIME DESC)
WHERE rownum = 1),
(SELECT version FROM v$instance)) version
FROM dual;
- Gokhan Atil
  
  March 22, 2017
  
  Yes Steve, you’re probably right. Thanks for sharing.
Gurinder Singh Wadhwa

May 29, 2017 Reply

Hi,

Is there any way, we can see applied PSU in Cloud COntrol 12c ?

As mentioned above, that this will be shown in 13c, is there any patch whihc we can apply to OMS/ agent to use new sql queries to extract DB Version along with PSU.