Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> Determining last installed CPU from database
        
(Message started by: Pete Finnigan on Oct 4^th, 2006, 12:30pm)

Title: Determining last installed CPU from database
Post by Pete Finnigan on Oct 4^th, 2006, 12:30pm

Hello all,

does anyone know some smart and reliable way of determining which CPU was last installed from running instance? I know you can get it from opatch, but I'm more interested in getting the info from instance only.

The goal is an automated script that would obtain this info from large number of databases on multiple servers, different platforms.

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Oct 4^th, 2006, 4:25pm

Hi,

Getting the last CPU installed is not easy or reliable. If you have older databases then there were differnet methods to install patches i.e. shell scripts, the OUI and OPatch. Even if you have consistency and all patches are installed via OPatch then listing out with OPatch may not be reliable if all of the post installation steps were not run or didnt complete.

You can use OEM in later versions but this uses the repository anyway. You could also query the repository that OPatch uses directly but whats the point. The method used by some is to checksum the packages that have been updated before and after CPU's and then compare the checksums. This can be done remotely via PL/SQL but you need to know the checksums!

cheers

Pete

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Oct 4^th, 2006, 5:20pm

Thanks! I knew about checksumming packages, I was just hoping that there would be easier way :)

Thank you anyway, your website and blog are wery useful!

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Nov 2^nd, 2006, 10:31am

what about using dba_registry_history ? Note it does not show the patches applied to the oracle home before the database has been created.

Code:

select ACTION_TIME,COMMENTS,ID from dba_registry_history;

ACTION_TIME COMMENTS ID
---------------------- ---------- ----------
20.10.2006 09:48:51.11 CPUOct2006 5490848

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Nov 2^nd, 2006, 11:12am

Thank you, that is basically what I was looking for!

Unfortunately, it should be noted this view is new in 10gR2 so it does not help with old instances.

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Nov 2^nd, 2006, 11:23am

it is available in later 9iR2 and 10gR1 too

Code:

SQL> select version from v$instance;
VERSION
-----------------
9.2.0.8.0

SQL> select ACTION_TIME,COMMENTS,ID from dba_registry_history;

no rows selected

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Nov 2^nd, 2006, 11:42am

Interesting - my testin 10gR1 does not have it and Oracle lists it as a new feature of 10gR2 (http://dba-services.berkeley.edu/docs/oracle/manual-10gR2/server.102/b14237/whatsnew.htm#i202863)

Maybe it's silently installed by latest patchsets even on older releases :)

Thanks for you help and interest ! I've just added your blog to my RSS reader ;-)

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Nov 2^nd, 2006, 11:55am

I think it started with 10.1.0.4 + CPU January and all patches released later. Not 100% about the exact release...

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Nov 2^nd, 2006, 12:31pm

Since CPU jan 2006 you can use sys.registry$history
Check Note:352783.1 (https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=352783.1)

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Nov 2^nd, 2006, 12:34pm

Unfortunately dba_registry_history does not exist in 9i.

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Nov 2^nd, 2006, 1:16pm

on 11/02/06 at 12:34:00, Marcel-Jan wrote:

Unfortunately dba_registry_history does not exist in 9i.

please read my post above

Title: Re: Determining last installed CPU from database
Post by Pete Finnigan on Nov 2^nd, 2006, 1:17pm

Thanks Laurent ! The Metalink note is very useful!