I previously posted some information about how cloud control could gather all the PSU information for all the database in your estate (here)
However it seems there are a couple of issues with this as it stands....
1. Databases that have been created on an already patched home don't work......
2. 12c has thrown a total spanner in the works.....
Covering the first point off
We regularly create new database in homes that have already been patched.
For instance if we have a home that is currently at 11.2.0.4.3 we may run a create database statement using that software.
So for example for a database called 'TEST'
[/oracle/11.2.0.4.3.DB/dbs]# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 17 11:09:49 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 271437824 bytes
Fixed Size 2252336 bytes
Variable Size 213909968 bytes
Database Buffers 50331648 bytes
Redo Buffers 4943872 bytes
SQL> create database;
Database created.
SQL>
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
So thats created a database available for use which is at 11.2.0.4.3, however if we check the registry$history view we get
SQL> select * from registry$history;
no rows selected
So nothing.... To make the PSU information be recorded in the dictionary we have to run the catbundle script - this re-runs some of the fixes (but essentially does nothing as we already have the right versions of things) - the only thing we really need it do do is update the registry. So if we run it as below
@catbundle psu apply
The key bit is these few lines that add the data we want to query
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.4',
9 3,
10 'PSU',
11 'PSU 11.2.0.4.3');
1 row created.
SQL> COMMIT;
Commit complete.
Now when we check the data is there.
SQL> select version,comments from registry$history;
VERSION COMMENTS
------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11.2.0.4 PSU 11.2.0.4.3
The PSU documentation is very unclear in regards to this - it implies it's only needed if you've used DBCA to create the database (which would have used an image before PSU to create it - well unless you didn't use the prebuilt db's)
The catbundle script must be run to update the registry if you want to query the data - otherwise it's not really required.
In the case of an upgrade we are instructed to run catuppst - this runs the PSU in for us - so that's how this is handled.
So the issue is just for newly created database in existing psu'd homes.
OK that concludes issue 1 with the script.....
Now onto issue 2.....
Well 12c has completely changed everything in a lot of ways and it seems the old way of doing psu has been totally replaced...
If you look at a readme for a 12c PSU you'll see that instead of catbundle you need to run
cd $ORACLE_HOME/OPatch
./datapatch -verbose
So it's a totally different method
It actually goes off and runs some sqlpatch.pl perl script to actually load any updated sql into the database.
Which would still be OK but......
There is no data stored in registry$history any more for 12c patches!
Instead the data is being put in registry$sqlpatch which has the friendly view on top of DBA_REGISTRY_SQLPATCH.
So you need to look in a different place!
And you still have the issue with new database created in a home that is already patched - they still don't show.
In 12c you do have the option though of querying the inventory at the os level directly using functions from the DBMS_QOPATCH package - so you could easily build something to compare whats at the os with whats recorded in the database.
We're currently working on a plsql block to handle all these eventualities (well i say 'we' - Susan in the team is doing that). It can't resolve the missing psu information in pre 12c but at least if something flags up as no psus at all applied at least you can go and check that genuinely is the case - hopefully there are not too many of these.
Just as a footnote i often see it asked why doesn't the PSU version show up in the header info when you connect to Oracle - it only ever shows up the first 4 digits - the last is always 0 even if a PSU is applied.
So for example an 11.2.0.4.3 database reports as 11.2.0.4.0 in the header - well the answer is that this header information is retrieved from an x$'table' (x$version in this case) - this is not a real table but just a memory structure - this is populated based on values from some of the oracle binaries - so for the header to be changed would need a new version of these files each time - i guess the view was taken that this is too much of an overhead and there would be too many copies of code to maintain.
Anyway - a summary of working out the PSU version - as you can see it's not as easy as it first might appear - but you can get most of the way there if you know where to look....
Comments
Post a Comment