Oracle upgrade, v$version what is it actually doing

oracleoracle-8i

There are 42 threads tagged as oracle8i, none of them answer my question…

This is Oracle 8i (8.1.5.0.0) 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.3.0.0 - Production
TNS for Solaris: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production

What I want to know is, is this interrogating the instance, or asking the DB itself?

Why I'm asking:

I've been asked to upgrade an 8i DB (8.1.5.0.0 -> 8.1.7.4.0) but the server (Solaris 2.6 5/98 on a U60) has both 8.1.5.0.0 & 8.1.7.0.0 on it. and 10 years or so ago, attempts were made to upgrade from 8.1.5 to 8.1.7, and then it may or may not have upgraded and downgraded, etc. The datafiles themselves were actually on the the 8.1.7 filesystem and sym-linked to 8.1.5. the init file has compatible=8.1.5 in it. So I don't know where the instance is at, version wise, or how to find out.

I've run the Oracle checks for dictionary corruption that you can get going from 8.1.5 to 8.1.6 and there are no signs of that.

When I tried to do an export, (usual story, "it's very important, Why?" "Does that mean we have no backups?") it barfed with an error indicative of the fact that it didn't have the tables/schemas necessary to do an export, so in 8.1.5 I ran the following as Oracle suggested:

@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catexp.sql
@?/rdbms/admin/utlrp

As system:
@?/sqlplus/admin/pupbld

Then I quit out, shutdown, startup and I'm apparently golden, I get a valid export with exp.

So, it's been messed about with, and I'm not a DBA. I'm a UNIX admin and I dabble with Oracle, I'm comfortable with installs, and building instances and throwing them about, Dataguard, RMAN, etc. I know my Plato. That is I know that I know next to nothing about the internals of an Oracle DB, just enough to know how complex all this stuff is. We don't have a DBA.

So what is v$version telling me? Is there anything, which will actually interrogate the instance & schemas to tell me what version they're at? Or is it suck it and see?

[UPDATE]
As an experiment I changed the oracle accounts environment to boot from 8.1.7 (as I duplicated the files, prior to making changes) and this is what I get. Bear in mind that this is the same init scripts & dbf files:

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS  PAR    THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------- --- ---------- ------- -----------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE
---------- --- ----------------- ------------------
              1 <dbname>
<hostname>
8.1.7.0.0         11-NOV-13 OPEN    NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE


SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.0.0 - Production 
NLSRTL Version 3.4.1.0.0 - Production

While this is what it looks like on 8.1.5:

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS  PAR    THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------- --- ---------- ------- -----------
LOGINS     SHU DATABASE_STATUS
---------- --- -----------------
              1 <dbname>
<hostname>
8.1.5.0.0         11-NOV-13 OPEN    NO           1 STARTED
ALLOWED    NO  ACTIVE


SQL> select * from v$version
  2  ;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.3.0.0 - Production
TNS for Solaris: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production

So basically, v$version just reports stuff about the DB, not about the instance, even the CORE information is radically different.

So, given that I can't update to newer hardware, or a later & better version of Oracle anyone got a clue?

I plan to scrub 8.1.7 and re-install 8.1.7.4 and see what happens. But it would be useful to know if what I plan to do is possible before I get into the world of hurt of a barfed upgrade. 🙂

[UPDATE 2]
As suggested below the SQL on the running system (8.1.5.0.0) gives:

DB Version: 8.1.5.0.0
Compatibility: 8.1.5

PL/SQL procedure successfully completed.

While the output of strings on a copy of the dbf files in the 8.1.7 drive is even more interesting:

strings TS_SYSTEM_<db>_01.dbf |grep "[0-9]\{1,2\}\.[0-9]\{1\}"

Gives interesting snippets:

8.0.0.0.0
>@--  GREATEST and LEAST are not yet supported for ADTs in 8.0.2.
      :new.prop_versid_m := '0.0';
      :new.prop_versid_m := '0.0';
8.1.7.0.0 RDBMS version for NLS parameters,
8.1.5.0.0 RDBMS version for NLS parameters,
[...]
'/export1/app/oracle/product/8.0.5.0/bin
[...]
-- no CFILE in 8.0.2
8.1.6,
8.1.5,
8.1.5,
8.1.5
8.1.5
V3.6.2
[...]
-- pre 8.1.5 version

-- 8.1.5+ version
-- pre 8.1.5 version
M-- This is a vestigal function that was released to customers in 8.1.3 Beta.
]"    -- For compatibility with 8.1
T"    -- For compatibility with 8.1
M"    -- For compatibility with 8.1
[...]
#3-- Recovery Action Kinds (Obsolete as of 8.1.6) --

So it looks like it does have vestiges of the upgrade and roll back in it. Could be interesting.

Best Answer

I bet your database files are not upgraded to 8.1.7.0.

I can't get how you could open the DB from one or another release without errors. I wonder that since this is a patch release upgrade (http://docs.oracle.com/cd/A84870_01/doc/server.816/a76957/migintro.htm#10138), and the COMPATIBLE parameter in init file its in 8.1.5, when you start the DB from 8.1.7 it builds compatible memory and disk structures with 8.1.5 version and "works" (at least, it opens. But I wouldnt go with this even in TEST environment)

To check the DB version You can also use the DBMS_UTILITY.DB_VERSION procedure. I don't know if it will return the instance or the database files version. You can run something like this in sqlplus:

SQL> set serveroutput on
SQL> DECLARE
       v_version varchar2(10);
       v_comp varchar2(10);
     BEGIN
       DBMS_UTILITY.DB_VERSION ( v_version, v_comp);
       DBMS_OUTPUT.PUT_LINE ('DB Version: ' || v_version);
       DBMS_OUTPUT.PUT_LINE ('Compatibility: ' || v_comp);
     END;
     /

Another option is the hardway. In linux if I use strings with my SYSTEM TABLESPACE datafile I can grep the release version from them. But ... I cannot claim this is a reliable method :\

$ strings o1_mf_system_91ck66x1_.dbf | grep "[0-9]\{1,2\}\.[0-9]\{1\}" | grep -i "Catalog Views" | head
 Oracle Database Catalog Views Re Oracle XML Database Version 11.2
COracle Database Catalog Views Release 11.2.0.3.0 - 64bit Production"DBMS_REGISTRY_SYS.VALIDATE_CATALOG
COracle Database Catalog Views Release 11.2.0.3.0 - 64bit Production"DBMS_REGISTRY_SYS.VALIDATE_CATALOG
COracle Database Catalog Views Release 11.2.0.3.0 - 64bit Production"DBMS_REGISTRY_SYS.VALIDATE_CATALOG

Anyway, In a mess like this I'll go for export-import migration, and forget about what has been done in the past. You said you can perform export with no problems, right? If the database its now open with 8.1.5, and the application has been hard tested with no errors, let's assume it's on 8.1.5 and migrate. Perform a new fresh 8.1.7 install, stop the application, export every application related schema from the production DB, and import into the new 8.1.7. Done.

Now, me and everyone will tell you that this is ridiculous. Why this? I'll try to migrate to a new server and DB version at any cost precisely because the elderly nature of your environment will make it only harder and harder in the future, not to mention the lack of security and new features of having a 13 years old software release running in a server powerless that your cellphone.

I cant imagine a good reason to not migrate that server entirely, even more when (based on what you said) no one knows what kind of mess did in the past. Fix the problem, not the symptom =)

Regards

Related Question