Forum OpenACS Q&A: How to upgrade Oracle from to ?

Perhaps this is a silly question, but how the heck do you upgrade Oracle from an to point release? I can't seem to find any info anywhere on where/how to download the patch sets.

FYI, the reason I've decided I need to do upgrade, is I ran into an IMP-00015 import bug which prevents primary key and unique constraints from being created properly when exporting from user A and importing to user B. The bug is supposedly fixed in Here are a bunch more links on about that bug: one, two, three, four. And here's Oracle's list of bugs fixed in all 8.1.x patch sets .

I have seen this error also. I am interested in the resolution for sure.


If you look at you'll find the and patchsets available for download via anonymous FTP. There are however instructions on Metalink to download other patchsets, click on the "Patches" button in the upper left corner and follow the instructions given in the resulting page.
Yick. does indeed have only older, out of date patchsets.

You can download the newer patchset directly from, but while possible, it is exceedingly annoying to use wget to do so. E.g., for the for 32 bit Solaris patchset, try:

$ wget -b --http-user=MY_METALINK_USERNAME --http-passwd=PASSWORD ''

But there's a better way. Once you use their god-awful patch search tool to figure out the patchset number you need (# 2376472 for, ftp them from, using your Metalink username/password to log in. E.g., here are links to some of the patchsets: Linux, Solaris 32 bit, Solaris 64 bit.

And does anybody know if the patchsets must be applied incrementally or if could be applied to any previous 8.1.7.x release?
From the patch set notes:

Patch sets are cumulative. For example, would include all the fixes in as well as new fixes for This means that unless the patch set documentation indicates otherwise the patch set can be applied to any earlier 8.1.7 version. There is no requirement to install all intermediate patch sets.

I went from to and then to on Linux (RH) without a problem.

Thank you, I thought I remembered reading that.  Information overload--the patchset release notes are often over 100 pages if you include all the bugfix tables...
Posted by Andrew Piskorski on
FYI, if you're using conventional path for your exports with Oracle or, be wary - there's a bug that could silently corrupt some data in your exports.

According to Oracle:
Versions Affected:,,,,
Versions NOT affected:,,,,,

There's a patch for this, but it sounds like the best workaround is to just always use direct path exports, which are faster anyway.

Posted by Bruno Mattarollo on


I have downloaded the patch to get my version to (running on Linux) and tryied to do an export from an openacs installation (user == arsdigita) and import it back under another user. I tried:

imp development/**** file=oraexp20030205.dmp fromuser=arsdigita touser=development

with no success, Oracle keeps complaining that the tablespace ARSDIGITA does not exist.

I deleted the tablespace arsdigita because I need this to test our procedure to get an export from our production database into our development and staging ones but since those are residing on the same Oracle server, I need to import them in different tablespaces and I can't seem to be able to.

This seems like one of the bugs that should have been solved by, right?

Has anyone been succesful in doing this? Do you have some pointers?

Posted by Janine Ohmer on
Sounds like Oracle's infamous issue with LOB segments.

What you are doing (moving data from one tablespace to another via exp/imp) will work fine for everything but LOB data. For unknown reasons, Oracle insists on restoring LOBs to the same tablespace (by name) that they came from.

There are a few ways to deal with this:

  • Move the LOBs to the destination tablespace before doing the export (or one of the same name, if these are being done on different systems)
  • Recreate the Arsdigita tablespace, do the import, and then move the LOBs to the tablespace you want them to be on
  • Use Perl to change the name of the LOB segment tablespace in your dump. This has always worked for me, but is highly unrecommended by Oracle. They worry about binary data in the dump being corrupted by doing this
*I* would consider this behavior to be a bug, but Oracle does not.
Posted by Bruno Mattarollo on
Thank you Janine for the super fast response!

I will try with Perl to change the name in the export ... It's just a "substitution" that I should do, right? In my case every time "ARSDIGITA appears next to TABLESPACE" I should change it to "DEVELOPMENT" or whatever tablespace I am trying to import it in to?

I will give this a try. Thank you again!

Shameful request coming: Do you have a sample Perl script that I could use? ;)

Posted by Roger Williams on
Incredibly, I was subscribed to this thread and I am fighting an oracle migration issue that has the exact same symptom (starting about 5 days ago). I was making all kind of "plan b"s in case I could not figure this out.

I, too am interested in the perl script, or the logic that I could just write a C program to do this.

Thanks again!

Note: The subject line quote is from Gomer on "Andy of Mayberry".

Posted by Janine Ohmer on
This is how I've done it:

perl -pi.bak -0777 -e 's/TABLESPACE \"<OLDNAME>\"/TABLESPACE
\"<NEWNAME>\"/gism' <your dumpfile>

(I originally got this from the guys at S&R)

This will create a backup copy of your dump file and then will go through and do the substitution.  It's very important that the new tablespace name be in captial letters - it won't work otherwise.

Good luck!

Posted by Janine Ohmer on
BTW, that perl command should be typed in all on one line.  Sorry that's not obvious from my last post.
Posted by Bruno Mattarollo on

Oh! Thanks Janine!!!

I wrote a small python program to do the same because I thought that I might contribute something as well :)

Here is the code:

# Author: Bruno Mattarollo
# Creation Date: 05th Feb 2003
# License: Free, do what you want with this!
# DISCLAIMER! This might corrupt your dump file
#     I AM NOT RESPONSIBLE FOR THE USE OF THIS! Don't blame me for any
#     problem that might occur. DON'T USE THIS IN PRODUCTION!

import re


compiled_re = re.compile("(TABLESPACE)\s+(\"" + FROM_TABLESPACE + "\")")

def main():
    print "About to start this miserable task ... :("
    fh = open(DUMP_FILE, 'rb')
    oh = open(OUTPUT_FILE, 'wb')
    # Maybe reading bigger chunks of the file would be better!
    # My file is 3.3GB!!!! :((((((
    chunk =*1024)
    while (chunk):
       if ( ):
           # We have a match!
           print "We have a match!"
           # We need to replace the FROM_TABLESPACE with the TO_TABLESPACE
           chunk = re.sub(r'(TABLESPACE)(\s+)(\"' + FROM_TABLESPACE + '\")',
                  r'\1\2"' + TO_TABLESPACE + '"',
       # If you change the chunk size above, change it here as well!
       chunk =*1024)

    # We are done
    print "Ouch! Finally!"

if __name__ == '__main__':

I coded this in 4 minutes and ran it on our export (as it says in the code it's 3.3GB) and I am importing it right now without problems so far... 😊... crossing my fingers...

Posted by Bruno Mattarollo on
Sorry for this additional post but I am sure that your solution will be MUCH faster than my python program :)

Thanks again!!!

Posted by Bruno Mattarollo on
Just for the record, I processed my dump file with my python script and imported the file into Oracle. This dump contains more than 2GB of JPGs (binary) and PDF's and everything looks fine using our OpenACS service to look at the data.
Posted by Roger Williams on
Hello Janine:

You really bailed me out today. I got the perl thing to work, the IMP went thru and now we are ready to test our 200MB tablespace against all of the code changes on the new box.

I owe ya' 😊


Posted by Janine Ohmer on
You're welcome!  I don't have much time to post anymore, so when I do I like to make it count. :)))
Every time i put this patch in and i try tot connect to my sqlplus i get a ORA 24314 error.  I have listener promplems...Can ANYONE help with this in plain english...PLEASE!  Im on a windows 2000 server.  Nothing fancy...this is just getting to be a pain since i  have to take all of oracle out to take this patch out...ugh!
Posted by Sebastiano Pilla on
I suppose that all the Oracle environment variables are properly set... Aren't you by chance applying the patch when logged via Terminal Services?

Anyway, please provide more details about what you're doing and how so we can help.

Posted by Guan Yang on

I used your Python script because the Perl one-liner printed a lot of error messages like this:

Malformed UTF-8 character (overflow at 0xca0c1314, byte 0x45, after start byte 0xff) in substitution (s///), <> chunk 1.

However, one thing occured to me. You're using 1 MB chunks. What if the string TABLESPACE <OLDNAME> lies exactly on a chunk boundary?


Posted by Bruno Mattarollo on
Hello Guan,

I would say you are having a tough luck (sorry). You will get an error when trying the import and then you can try with a different chunksize. But that's a good question indeed ... :)

A modification to the script could be to look for a match at the end of a chunk for any match for any substring of "TABLESPACE " and if that happens, read the following chunk, check that at the beginning you can actually match the whole string you are looking for (concatenating both the previous chunk and this one) and do the replacement there ... :-?

Just my 2 cents,


Posted by amer safdar on
I have to upgrade from 8.1.7 to on a Solaris (32 bit) but I can't seem to find the patch on metalink. I have tried all the recommeded stirngs e.g. patch Solaris. Could you help me finding this patch.

Thanks Amer.