Forum OpenACS Q&A: OpenACS 4.6.3 to 5.1.5+ upgrade, on Oracle 8.1.7.4
The actual upgrade wasn't that bad, but I felt lost for a while figuring out what to do. So, here's what I actually did to upgrade, based heavily on both the official docs and lists of steps from Andrew Grumet, Miguel Marin, and Andrei Popov (several of which are referenced from the official docs). Note that I'm going to present the steps straight through once from start to finish, but in practice, I actually iterated through them a few times testing things on my destktop:
First, vendor import the new OpenACS code into your CVS repository,
merge it to your Head, and resolve conflicts. There have been a lot
of fixes on the OpenACS 5.1 branch since 5.1.5, but 5.1.6 has not yet
been released. Therefore, I recommend that use the latest stuff on
the 5.1 branch, from CVS. I pulled down the branch as of 2005-06-18,
and decided to tag it as "openacs-5-1-5-2005-06-18" in my
CVS when doing the vendor import.
So just to review, the CVS steps will look basically like this:
$ cd /tmp/imp/
$ cvs import -m "Importing the OpenACS 5.1.x branch as of 2005-06-18, after 5.1.5, before 5.1.6." mysite OpenACS openacs-5-1-5-2005-06-18
$ cd /web/
$ cvs co -d merg -kk -j openacs-4-6-3-final -j openacs-5-1-5-2005-06-18 mysite
$ cd /web/merg/
# Resolve conflicts, etc...
$ cd /web/
$ cvs co -d mysite-new mysite
(Now, in reality, it may be pretty hard to resolve conflicts and merge in the new OpenACS code without at least partially upgrading your site first, so you can test things. I actually loaded an export of my production site into Oracle on my desktop, upgraded, and ran an instance out of my "merg" directory above, fixing conflicts and committing.)
Look at step 7 of the
upgrade-4.6.3-to-5.html docs.
Copy that Tcl code into "tcl/zzz-upgrade-463-50.tcl", and
CVS commit it. You only actually need that code once, during the
upgrade, but it doesn't hurt to have around later, and by CVS
committing it you won't forget it when you go to upgrade your Staging
or Production site later, etc.
For compatibility with Oracle 9i and later, OpenACS 5.x changed all the old PL/SQL "delete" procedures to "del", as "delete" is a reserved word in PL/SQL (and it's actually enforeced in 9i and later). Change that in your custom PL/SQL code as well. Here's a command to help you find (all?) the lines you should change:
find . -path '*/sql/postgresql' -prune -o -path '*/sql/oracle/upgrade' -prune -o \( -name "*.sql" -a ! -name "upgrade*.sql" \) -print | xargs egrep '(\.delete\(|procedure *delete[^a-zA-Z_]|end *delete[^a-zA-Z_])'
acs-kernel/sql/oracle/test/rel-segments-test-types-create.sqlacs-content/sql/oracle/acs-content-create.sqlfile-storage/sql/oracle/file-storage-notifications-drop.sql
Ok, now you have all of the new OpenACS 5.1.5+ stuff working, merged in and committed into your CVS repository, great! Now to do the actual upgrade of our website, which we'll call "mysite".
Start out with your old OpenACS 4.6.3 based code, the same
CVS checkout you've had all along for years now (which I assume is
located in "/web/mysite-dev/").
First let's get rid of some OpenACS packages which are either completely obsolete, or which I figure will be easier to re-install later rather than upgrading. In my case (but maybe not in yours!), the data in these packages is completely disposable, so dropping them is no problem.
Manually run the following drop scripts rather than letting the APM do it, as the APM leaves some of these out:
Now start up AOLserver and use APM to uninstall each of those packages (the URL is "cd /web/mysite-dev/packages/site-wide-search/sql/ sqlplus mysite/PASSWORD set def off set sqlblanklines on set serveroutput on size 1000000 exec recompile_all_objects @@content-revision-sws-drop.sql @@sws-package-all-drop.sql -- Type ";", it is missing from the above script. -- I did not have these packages, if you do, uncomment and run these -- drop scripts as well: --@@file-storage-sws-drop.sql --@@news-sws-drop.sql --@@bboard-sws-drop.sql cd /web/mysite-dev/packages/ sqlplus mysite/PASSWORD @@search/sql/oracle/search-drop.sql @@static-pages/sql/oracle/static-pages-drop.sql @@static-pages/sql/oracle/static-pages-sws-drop.sql @@acs-content/sql/oracle/acs-content-drop.sql @@acs-lang/sql/oracle/message-catalog-drop.sql -- Drop some stuff that the above drop scripts forgot: drop view static_pagesi ; drop view static_pagesx ; drop trigger acs_contents_sws_itr ; drop trigger acs_contents_sws_utr ; drop package sws_service ;
/acs-admin/apm/index?supertype=all"):
- search
- site-wide-search
- static-pages
- acs-content
- acs-lang
- acs-mail
Now shut down AOLserver, move your old code out of the way, and move
your new OpenACS 5.1.5 based code into
/web/mysite-dev/. To reiterate,
/web/mysite-dev/ is now a different directory
than it was above, with new, updated contents.
Now, with AOLserver still shut down, do lots of data model upgrades in sqlplus. (Some of this you do need to do in sqlplus, some you could do later via the APM, but I wasn't sure where precisely to draw the line, so if I had any reason to upgrade a package manually in sqlplus, I tended to run all its upgrade scripts manually.)
cd /web/mysite-dev/packages/acs-kernel/sql/oracle/upgrade/ sqlplus mysite/PASSWORD set def off set serveroutput on size 1000000 @@upgrade-4.6.3-4.6.4.sql @@upgrade-4.6.4-4.6.5.sql @@upgrade-4.6.5-4.6.6.sql @@upgrade-4.7d-4.7.2d.sql @@upgrade-4.7.2d-5.0d.sql @@upgrade-5.0d-5.0d2.sql @@upgrade-5.0d2-5.0d3.sql @@upgrade-5.0d6-5.0d7.sql exec recompile_all_objects @@upgrade-5.0d7-5.0d9.sql @@upgrade-5.0d9-5.0d13.sql @@upgrade-5.0.0b1-5.0.0b2.sql @@upgrade-5.0.0b2-5.0.0b3.sql @@upgrade-5.0.0b3-5.0.0b4.sql @@upgrade-5.0.0b4-5.0.0b5.sql @@upgrade-5.1.1d1-5.1.1d2.sql @@upgrade-5.1.2d1-5.1.2d2.sql @@upgrade-5.1.2d2-5.1.2d3.sql @@upgrade-5.1.4-5.1.4d1.sql
(The two upgrade scripts in the middle ubove will throw some "PLS-00302: component 'DEL' must be declared" errors, which Andrew Grumet said is fine; the package gets recompiled later and all is well. In general, I got various errors at various steps, which I have mostly not otherwise noted here in these instructions. So, you may well see some other errors. The trick is figuring out just what each error means and what - if anything - you should actually do about it.)
Manually copy the "acs" package and package body definitions out of
"packages/acs-kernel/sql/oracle/acs-create.sql" and paste
them into sqlplus. Then continue:
cd /web/mysite-dev/packages/ sqlplus mysite/PASSWORD set def off set sqlblanklines on set serveroutput on size 1000000 exec recompile_all_objects @@acs-service-contract/sql/oracle/upgrade/upgrade-4.7d2-4.7d3.sql @@acs-service-contract/sql/oracle/upgrade/upgrade-4.7d3-5.0d1.sql @@acs-authentication/sql/oracle/acs-authentication-create.sql @@acs-lang/sql/oracle/acs-lang-create.sql @@acs-subsite/sql/oracle/user-profiles-create.sql @@acs-subsite/sql/oracle/upgrade/upgrade-4.6.4.1-4.6.4.2.sql @@acs-subsite/sql/oracle/upgrade/upgrade-5.0d4-5.0d5.sql -- Do NOT run "upgrade-5.1.2-5.1.3.sql", it is bogus, instead do this: @@acs-subsite/sql/oracle/site-node-selection.sql -- Note: I think Andrew Grumet has already fixed the bug, so by the -- time YOU upgrade to the latest on the OpenACS 5.1.x branch, it -- should be fine. @@notifications/sql/oracle/upgrade/upgrade-4.6.2-4.6.4.sql @@notifications/sql/oracle/upgrade/upgrade-4.6.4-5.0d1.sql @@notifications/sql/oracle/upgrade/upgrade-5.0.0a5-5.0.0a6.sql @@notifications/sql/oracle/upgrade/upgrade-5.0.0b4-5.1.0d2.sql @@notifications/sql/oracle/upgrade/upgrade-5.1.0d2-5.1.0d3.sql -- You must actually start sqlplus from this directory, because some -- of these upgrade scripts use the "@../foo.sql" syntax: cd /web/mysite-dev/packages/acs-content-repository/sql/oracle/upgrade/ sqlplus mysite/PASSWORD set def off set sqlblanklines on set serveroutput on size 1000000 @@upgrade-4.7d6-5.0d1.sql @@upgrade-5.0.0-5.1.0d1.sql @@upgrade-5.0.0b3-5.0.0b4.sql @@upgrade-5.1.0-5.1.1.sql @@upgrade-5.1.1d2-5.1.1d3.sql @@upgrade-5.1.2d3-5.1.2d4.sql @@upgrade-5.1.2d5-5.1.2d6.sql @@upgrade-5.1.2d6-5.1.2d7.sql @@upgrade-5.1.2d8-5.1.2d9.sql @@upgrade-5.1.5d2-5.1.5d3.sql exec recompile_all_objects @@upgrade-5.1.5-5.1.6d1.sql exec recompile_all_objects
Now start AOLserver twice.
The first time, the tcl/zzz-upgrade-463-50.tcl gets run.
The second time AOLserver comes up, it will actually be working
correctly.
(If you see cryptic, mysterious Tcl errors, which turn out to be
because packages/acs-templating/tcl/template-init.tcl was
not sourced when AOLserver started up... it's because you forgot to
run the zzz-upgrade-463-50.tcl code and then restart
AOLserver.)
Now using the APM ("/acs-admin/apm/packages-install/"),
upgrade packages like so:
We already ran the upgrade scripts for these manually, so uncheck all the upgrade scripts when installing these:
- acs-kernel
- acs-service-contract
Upgrade these via the APM, and let it load all the upgrade scripts:
- search
- acs-bootstrap-installer
- acs-tcl
Upgrade via APM, turn off all SQL scripts, turn on the
java/*.sqlj scripts:
- acs-content-repository
TODO: The cr_after_upgrade callback is broken, and I don't know what's it's supposed to actually do, see Bug #2508.
On my desktop, the *.sqlj files loaded just fine, quite quickly (I never did figure out why exactly). On my Dev server, the APM hung trying to load them, with a JRE process spinning indefinitely. The problem seems to be the old LD_ASSUME_KERNEL snafu with Oracle 8i.
So, I simply restarted AOLserver, killed the offending JRE process (I had to kill -9 it), and loaded the missing java files into Oracle manually:
Note that I left off the "-resolve" for Regexp-oracle.sqlj, as including it gave me this:$ cd /web/mysite-dev/packages/acs-content-repository/java/ $ export LD_ASSUME_KERNEL=2.2.5 $ loadjava -user outpost_dev/outpost_devpassword -resolve PerlTools.jar $ loadjava -user outpost_dev/outpost_devpassword -resolve Util-oracle.sqlj $ loadjava -user outpost_dev/outpost_devpassword -resolve XMLExchange-oracle.sqlj $ loadjava -user outpost_dev/outpost_devpassword Regexp-oracle.sqlj
$ loadjava -user outpost_dev/outpost_devpassword -resolve Regexp-oracle.sqlj
Error while resolving class com/arsdigita/content/Regexp
ORA-04043: object com/arsdigita/content/Regexp does not exist
loadjava: 1 errors
From that I suspect that Regexp-oracle.sqlj won't actually work at all, but heck, I've no idea what this Java stuff is really for anyway; the method above at least appears to have loaded the Java objects into Oracle correctly. You can see them in sqlplus with a query like this:
column nn format a35 select object_name as nn ,object_type ,status ,created ,last_ddl_time from user_objects where object_type like '%JAVA%' order by created ,object_name ,object_type ;
We already ran the upgrade scripts for these manually, so uncheck all the upgrade scripts when installing these:
- acs-subsite
- notifications
Upgrade these via APM, and let it load all upgrade scripts. This is every remaining package which says "upgrade" rather than "install" next to it:
- acs-admin
- acs-api-browser
- acs-developer-support
- acs-interface
- acs-mail-lite
- acs-messaging
- acs-reference
- acs-templating
- general-comments
- monitoring
- workflow
- bug-tracker
Clean up a few more things in sqlplus:
To find what file some PL/SQL code (e.g., the bt_bug package) is in, a command like this is helpful:cd /web/mysite-dev/packages/ sqlplus mysite/PASSWORD set def off set sqlblanklines on set serveroutput on size 1000000 @@acs-kernel/sql/oracle/test/rel-segments-test-types-create.sql @@acs-content/sql/oracle/acs-content-create.sql -- Do this, it was was missing from the upgrade scripts (Bug #2493): alter table workflows add ( description clob ,description_mime_type varchar2(200) default 'text/plain' ); -- Fix some invalid PL/SQL packages: --drop function ttl__create_file; drop package workflow_case; @@workflow/sql/oracle/workflow-procedural-create.sql @@notifications/sql/oracle/notifications-replies-package-create.sql exec recompile_all_objects -- TODO: Fix any of your custom stuff that needs fixing...
$ find . -path '*/sql/postgresql' -prune -o -name "*.sql" -print | xargs grep -i 'package *bt_bug[^a-z_]*'
Finally, check the permissions on any newly installed packages, check that everything on your newly upgraded website seems to be working ok, and... You're done!
