Forum OpenACS Q&A: Upgrading from OpenACS 4.6.3 on Oracle 8i to 5.1.4 on 9i

Hi Andrew,

I'm posting in some rough notes from the Sloan upgrade below. Feel free to bug me by email or AIM (adaegrumet) if you need anything.

1. db export/copy/import/viewfix
2. upgrades run from sqlplus
   
   • sequencing: all pre-ora9 scripts for all packages must be run first
   • recompiling package bodies and packages as we go
   • fixed upgrade scripts
   • skipped upgrade scripts (two reasons: @ usage points to newer code, or we already had the fix)
3. copy content-repository-content-files to new server and set permissions
4. upgrades run from aolserver
   
   • turn off outbound email (rollout support) and scheduled procs (new UpgradeModeP parameter) before starting the server.
     
   • zzz-postload.tcl
   • edit dotlrn-master.tcl and site-master.tcl temporarily working around code that breaks before upgrade is complete (lang_admin_p, num_users_online)
   • upgrade all packages in APM, navigate directly to /acs-admin/apm/packages-install
     
     • upgrade in 4 rounds, a subset of packages at a time, with intervening restarts, starting with the most core packages and working your way out to dotlrn [may not be strictly necessary]
5. back in sqlplus
   
   • run a few updates (missing-upgrades.sql) for upgrade scripts
     that should exist but don't
   • analyze tables, recompile broken objects
   • update a few APM parameters
6. mount localization package at acs-lang (tricky, have to conjure up the right URL, gui options not presented because acs-lang is marked as a service) and import all messages to be sure nothing falls through the cracks (a lot did, which is why we do this)
7. unedit dotlrn-master.tcl and site-master.tcl
8. WATCH OUT for new APM parameters -- for example, acs-mail-lite now uses the sendmail binary by default. Since this is controlled by a new parameter, it will get rolled out to your instance of acs-mail-lite with the default value, which means it will call the sendmail binary when your old system was probably using SMTP.
9. WATCH OUT for changed APM parameters -- for example, file-storage now uses the filesystem for storage by default, when it used to default to the database. When you upgrade in APM, the defaults get overwritten, and all new package instances (e.g. file-storage in classes created post-upgrade) will get the new default if you forget to restore defaults to your old values. This silent overwriting of package value defaults is a bug in OpenACS.

I've put my helper scripts etc here, they might be useful if only because they split apart the pre Ora9 from post Ora9 stuff. Check out the db.sql script in the top level directory.

http://grumet.net/writing/programmer/openacs/oacs46-51-helpers.tar.gz

Andrew, as your first step before doing anything else, you exported your 8i data and then imported it into a new 9i instance, correct? How long did that take?

My 8i instance is about 83 GB, so I fear an export/import (particularly the import) might take many days. For testing, I think I'll try using my RMAN physical hot backups to make a copy of the instance. And then eventually upgrade Production in place. Anyone here ever tried that before?

(Most of that 83 GB database is in other tablespaces that have nothing to do with OpenACS, but I still sure don't want to try upgrading them in place to 9i without testing that first on a disposable Dev instance.)

I decided to first upgrade from OpenACS 4.6.3 to 5.1.5.x in place on Oracle 8.1.7.4. 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
@@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 ;

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

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

$ 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

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

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!