RE: detecting version of restored database controlfile
Date: Mon, 24 Jun 2013 19:08:07 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9A707F3_at_exmbx09.thus.corp>
Jeremy,
I hadn't realised it by I actually had compatible set to 11.2.0.0 in my parameter file.
I've just checked an instance of 11.1.0.7 - which had compatible set to 11.1.0.0 in the parameter file.
The control files and data files showed compatible 11.1.0.0; so I changed the parameter to 11.1.0.7 and bounced the database - this changed the values in the files to 11.1.0.7 (0xb1007 - odd pattern, but maybe the "1.0" in the middle is one nybble each).
I think the init.ora parameter dictates the compatability the software will want to run at, and this will show in "show parameter" or the parameter file if it's set. The file content will show the actual most recently run compatability.
The default value for compatible seems to be the lowest consistent with the Oracle version - so for my 11.1 software is was 11.0.0 - I haven't checked for 11.2.x.x, though. If 11.2 follows the pattern then maybe you need to get the information from the control file, then change the parameter to match before starting up. I can only guess that your software was running at a higher compatibility that the data files.
Regards
Jonathan Lewis
From: Jeremy Schneider [jeremy.schneider_at_ardentperf.com] Sent: 24 June 2013 18:18
To: Jonathan Lewis
Cc: Oracle-L
Subject: Re: detecting version of restored database controlfile
Interesting - most of the values in v$parameter seem to be instance parameters, not database parameters - but you're saying that "compatible" here is coming from the controlfile rather than the spfile or init.ora?
regardless, on my test database compatible is returning 11.2.0 (like yours) which unfortunately doesn't tell me whether it's 11.2.0.2 or 11.2.0.3 and then after waiting for all the 11.2.0.2 datafiles to restore, the open resetlogs fails [ORA-39700: database must be opened with UPGRADE option]. Furthermore, I just dumped the controlfile header from an 11.2.0.3 db that I had handy with oradebug, and it has exactly the same Compatibility Vsn - 0xb200000 ... so i should be able to detect the 10.2 databases but it won't predict whether I'm going to get the open failure on 11.2.0.2 dbs.
raj it's difficult to secure OEM access - i can't hard-code the password into this program because it's a script, and i can't give the operators a password which allows them full access to the OEM backend DB. one possibility might be setting up a special read-only account for the OEM db, but I'd rather hold off on that for the moment.
--
http://about.me/jeremy_schneider
On Mon, Jun 24, 2013 at 11:44 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:
The control file itself holds the COMPATIBLE version. If you can do an "oradebug dump controlf 1" you should get something like:
DUMP OF CONTROL FILES, Seq # 18595 = 0x48a3 V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID%75036341=0x997befb5, Db Name='TESTDB11'
(This was an 11.2 database with compatible = 11.2.0.0 , which you can see the in b 20 00 00)
In my case the controlfile blocks were 16KB, and the compatible field was in block 2, bytes 25 - 28 (with endianness: 27, 28, 25, 26).
If you can see v$controlfile, though, you can see v$parameter, so could simply check the compatible parameter.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Jeremy Schneider [jeremy.schneider_at_ardentperf.com<mailto:jeremy.schneider_at_ardentperf.com>] Sent: 24 June 2013 16:13
To: Oracle-L
Subject: detecting version of restored database controlfile
Just curious, does anyone know an easy way to detect what version a controlfile is? I'm writing a program that needs to restore from a backup, and the original source database could be one of several versions. Of course if you try to open a DB with the wrong software version then it errors out -- so I'd like to detect and correct this condition as early as possible (ideally before restoring and recovering all the datafiles). So far haven't figured out a way to get the version from the RMAN recovery catalog or v$controlfile or v$database or anywhere else. Of couse v$version and v$instance show the software I'm using to do the restore, not the software of the control & data files I'm restoring. Anybody know a way to figure this out? Seems like the sort of thing that really should have been in the controlfile and recovery catalog...
-J
--
http://about.me/jeremy_schneider
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jun 24 2013 - 21:08:07 CEST