Forum OpenACS Q&A: OpenACS 4.6.3 to 5.1.5+ upgrade, on Oracle

Posted by Andrew Piskorski on
I decided to first upgrade from OpenACS 4.6.3 to 5.1.5.x in place on Oracle We'll move everything to Oracle 10g later, once the new server is set up for that.

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_])'
Also, I found "delete" procedures still present in these OpenACS 5.1.5 files as well (fix these too):
  • acs-kernel/sql/oracle/test/rel-segments-test-types-create.sql
  • acs-content/sql/oracle/acs-content-create.sql
  • file-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:

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
-- 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:

cd /web/mysite-dev/packages/
sqlplus mysite/PASSWORD

-- 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 ;
Now start up AOLserver and use APM to uninstall each of those packages (the URL is "/acs-admin/apm/index?supertype=all"):
  • search
  • site-wide-search
  • static-pages
  • acs-content
  • acs-lang
Use the APM to uninstall and run the drop scripts for these packages:
  • 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

exec recompile_all_objects



(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


-- Do NOT run "upgrade-5.1.2-5.1.3.sql", it is bogus, instead do this:
-- 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.


-- 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

exec recompile_all_objects
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:

$ 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
Note that I left off the "-resolve" for Regexp-oracle.sqlj, as including it gave me this:
$ 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:

cd /web/mysite-dev/packages/
sqlplus mysite/PASSWORD
set def off
set sqlblanklines on
set serveroutput on size 1000000


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

exec recompile_all_objects

-- TODO: Fix any of your custom stuff that needs fixing...
To find what file some PL/SQL code (e.g., the bt_bug package) is in, a command like this is helpful:
$ 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!