How to find out whether Oracle database was upgraded ??

[Former Member]

Hi,

I need to find out whether my Oracle database was upgraded, If yes how to find out by SQL query .I know their is database_properties table, but when I try to query that it didnt gave me the output which I required.

Like in SAP we have uvers table which keep info about the upgrade history, whether such history is avaliable in oracle ?? . Iam looking out for output which is like old database Release 9.2.0.7 and new database release 10.2.0.2.

Whether similar such history is maintained in Oracle database.

Awaiting for your feedback.

Kind Regards

Neenad

[Former Member · Accepted solution]

I'm not aware of a version history maintained by the database.

But if you have done the database upgrade with ODMA then in $ORACLE_HOME/cfgtoollogs/odma you have the upgrade outputs and see according the timestamps when the database structure upgrade was done.

Or you can check dba_registry view to see the Date when the Oracle Dictionary objects were modified (which was during upgrade most likely) with 10.2.0.2

[Former Member]

Hi,

You can go to system in the menu bat-->status..

you will be provided with enough info abt DB details

and one more option is u can ref v$version

regards

jag

[Former Member]

Hi all,

After checking all the material,I dont think prior to 10g, oracle register its earlier version.

The views of v$version show the current version for various components and also dba_registry displays info about catlog/catproc versions.

In 10g, you can use the following query to know the version when the database was broughtup.

select startup_time,version,db_name from dba_hist_database_instance.

Anways thanks to all of you.

Kind Regards

Neenad

[Former Member]

select comp_name,version from dba_registry;

this is as far as I know the only resource to find out the internal compile version of PL/SQL procedures.

This is the resource find out if catcpu.sql or catpatch.sql was executed after installing a hotfix/patchset.

[Former Member]

Neenad,

Ref inventory and look file comps.xml you will find entry like VER="9.2.0.8.0" BASE_VER="9.2.0.1.0".

For current version u can ref v$version v$parameter dba_registry.

REgards

Vinod

[Former Member]

go to /oracle/<DB_SID>/102_64/OPatch

and execute -

opatch lsinventory

opatch lsinventory -all

opatch lsinventory -all -detail

With the last command you will get Oracle version info with the most detail and about every software component of the database.

You can get info like which file was replaced by which patch with time stamps and their location in the file system.

[Former Member]

Hi Neenad,

Go to st04 -->details analysis manu

and select V$values tab

and then select the views V$VERSION this view will give you the details regarding your database

Cheers

Paresh