Forum OpenACS Q&A: How to upgrade Oracle from 126.96.36.199 to 188.8.131.52 ?
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 184.108.40.206. 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 .
You can download the newer patchset directly from metalink.oracle.com, but while possible, it is exceedingly annoying to use wget to do so. E.g., for the 220.127.116.11 for 32 bit Solaris patchset, try:
$ wget -b --http-user=MY_METALINK_USERNAME --http-passwd=PASSWORD 'http://ap103aru.us.oracle.com/ARULink/PatchSearch/process_download/p2376472_8174_SOLARIS.zip?patch_file=p2376472_8174_SOLARIS.zip&aru=1697372'
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 18.104.22.168), ftp them from updates.oracle.com, using your Metalink username/password to log in. E.g., here are links to some of the 22.214.171.124 patchsets: Linux, Solaris 32 bit, Solaris 64 bit.
Patch sets are cumulative. For example, 126.96.36.199 would include all the fixes in 188.8.131.52.0 as well as new fixes for 184.108.40.206.0. 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 220.127.116.11 to 18.104.22.168 and then to 22.214.171.124 on Linux (RH) without a problem.
According to Oracle:
Versions Affected: 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168
Versions NOT affected: 22.214.171.124, 126.96.36.199, 188.8.131.52, 184.108.40.206, 220.127.116.11, 18.104.22.168
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.
I have downloaded the patch to get my version to 22.214.171.124 (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 126.96.36.199, right?
Has anyone been succesful in doing this? Do you have some pointers?
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.
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? ;)
I, too am interested in the perl script, or the logic that I could just write a C program to do this.
Note: The subject line quote is from Gomer on "Andy of Mayberry".
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.
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 email@example.com # 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...
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'
Anyway, please provide more details about what you're doing and how so we can help.
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?
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,
I have to upgrade from 8.1.7 to 188.8.131.52 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 184.108.40.206 Solaris. Could you help me finding this patch.