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

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 8.1.7.2. Here are a bunch more links on metalink.oracle.com about that bug: one, two, three, four. And here's Oracle's list of bugs fixed in all 8.1.x patch sets .

Hello,

I have downloaded the patch to get my version to 8.1.7.4 (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 8.1.7.4, right?

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

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? ;)

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!

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:

#!/usr/bin/python
#
#
# Author: Bruno Mattarollo bruno.mattarollo@diala.greenpeace.org
# 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

DUMP_FILE="arsdigita-export-file-20021218-2.dmp"
OUTPUT_FILE="dump-cleaned.dmp"
FROM_TABLESPACE="ARSDIGITA"
TO_TABLESPACE="DEVELOPMENT"

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 = fh.read(1024*1024)
    while (chunk):
       if ( compiled_re.search(chunk) ):
           # 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 + '"',
                  chunk)
       oh.write(chunk)
       # If you change the chunk size above, change it here as well!
       chunk = fh.read(1024*1024)

    # We are done
    fh.close()
    oh.close()
    print "Ouch! Finally!"

if __name__ == '__main__':
    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...

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

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,

/B