Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Oracle 8i is a little old... (extended support ended almost 8 years ago). Rather than trying to solve whatever problems you have why don't you export the data (as you say you can do) and import it into an 11.2 database? –  Ben Nov 11 '13 at 13:32
    
Sadly not no... Though given the elderly nature of this stuff that too would be fraught with difficulty :) –  user2978573 Nov 11 '13 at 15:56
1  
Whoever asked you to upgrade from 8.1.5 (released in Feb 1999) to 8.1.7 (released in Aug 2000) deserves a turd in the face. –  vegatripy Nov 12 '13 at 13:47
add comment

migrated from stackoverflow.com Nov 11 '13 at 13:29

This question came from our site for professional and enthusiast programmers.

1 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

share|improve this answer
    
Cheers! that's the sort of info I was hoping to get :) I had my doubts it would work, but it does, wel, it opens at least and I can run SQL against it. Though our "good reason" is we have a Satellite in orbit that depends on it. –  user2978573 Nov 12 '13 at 14:44
    
I can only say 'lol' –  vegatripy Nov 12 '13 at 15:34
    
There isn't any chance to test it in a new environment? The export-import migration to 11g is the same method. –  vegatripy Nov 12 '13 at 15:44
    
If you know anything about such environments they are very risk averse, and they test things up the ying yang, (as they say :) Then things get stamped, God signs off, and everyone is happy. Which is why we get to upgrade to the latest point upgrade, for which there is/was a fully vendor supported path. The problem isn't the DB per se, but the testing, and getting sign off and the mission to accept it. It's a bureaucratic dead zone. –  user2978573 Nov 13 '13 at 8:31
    
So as of update 2 (above) it looks like there is evidence of the old 8.1.7 upgrade after all. –  user2978573 Nov 13 '13 at 12:53
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.