Forum OpenACS Q&A: Problem with intermedia migrating acs3 instance

Collapse
1: Problem with intermedia migrating acs3 instance
Posted by Rich Graves on 10/24/02 10:24 PM
We're trying to migrate an acs 3.4.10 installation from Oracle 8.1.6 on RedHat 6.2 to Oracle 8.1.7.4 on RedHat 7.3. We succeeded in getting Oracle 8.1.7.4 running (the bit we missed was running glibc-stubs a second time, after applying the 8.1.7.4 patch) but can't get site-wide-search to work.

On the Oracle 8.1.6/RedHat 6.2 box, we ran exp with this param file: 

USERID=user/password
FILE=exp_user.dmp
BUFFER=40000000
FULL=N
OWNER=USER
COMPRESS=Y
GRANTS=Y
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y
CONSISTENT=Y
and imported that file with 
USERID=user/password
FILE=/tmp/exp_user.dmp
BUFFER=40000000
FULL=N
FROMUSER=USER
TOUSER=USER
GRANTS=Y
INDEXES=Y
ROWS=Y
COMMIT=N
LOG=imp_user.log

After some tracing of pl/sql problems that turned out to be broken in the original database in the first place, we got almost everything working and were able to point aolserver at the new instance. But full-text search breaks with "index marked as LOADING" and trying to run www/doc/sql/recreate-site-wide-search.sql instance, I get 

begin
*
ERROR at line 1:
ORA-20000: interMedia Text error:
DRG-10700: preference does not exist: sws_user_datastore
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.CTX_DDL", line 80
ORA-06512: at line 2


old   3:   ctx_ddl.set_attribute('sws_user_datastore', 'procedure',
'sws_user_proc_&1');
new   3:   ctx_ddl.set_attribute('sws_user_datastore', 'procedure',
'sws_user_proc_staging');
begin
*
ERROR at line 1:
ORA-20000: interMedia Text error:
DRG-12603: CTXSYS does not own user datastore procedure:
SWS_USER_PROC_STAGING
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.CTX_DDL", line 105
ORA-06512: at line 3


drop index sws_ctx_index
*
ERROR at line 1:
ORA-29868: cannot issue DDL on a domain index marked as LOADING


create index sws_ctx_index on site_wide_index (datastore)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object
Help? We've got some pretty good dbas, but intermedia isn't the most friendly or widely used package.
Collapse
2: Response to Problem with intermedia migrating acs3 instance (response to 1)
Posted by Tilmann Singer on 10/24/02 10:40 PM
The proc sws_user_proc_staging is meant to be owned by the ctxsys user, which it is when installing it normally because the installation script reconnects as ctxsys before creating it. The import seems to have recreated that procedure owned by your normal database user, instead of ctxsys. Try to drop the sws_user_proc_staging proc in the ctxsys scheme and manually run the block in doc/sql/site-wide-search.sql that is executed as ctxsys (line 958 to 974 on my copy).

This is just a guess though.

Collapse
3: Response to Problem with intermedia migrating acs3 instance (response to 1)
Posted by Michael Bryzek on 10/25/02 12:25 AM
You can also try:

drop index sws_ctx_index force

to get rid of an index that is in an invalid state.

Collapse
4: Response to Problem with intermedia migrating acs3 instance (response to 1)
Posted by Brian Fenton on 10/25/02 10:31 AM
Hi Rich,
It's site-wide-search.sql that you need to run again, NOT recreate-site-wide-search.sql. Ignore any errors about objects already existing. The bit you're interested in is where the script connects as ctxsys and creates the sws_user_proc_&1 procedure (and does a few other things). After that, if the sws_user_proc_&1 procedure is there (and owned by the ctxsys user) and you still have problems, you can run recreate-site-wide-search.sql to rebuild the Intermedia indexes. Be warned, it can be slow!
Collapse
5: Response to Problem with intermedia migrating acs3 instance (response to 1)
Posted by Rich Graves on 10/28/02 10:44 PM
Things got a little interesting elsewhere, so I didn't have any time to follow up on this until now.

It looks like I got it to work, but I'm not confident enough in the process to subject our production database to it. We will probably take the easy (?) way out by cold-copying the .dbf files and performing a version migration. DBA sez

Looks like it's just a matter of running a few scripts. I say "looks like" because the various docs I've found on M-L are somewhat vague about DBs w/ interMedia.

:->

How I probably got a working context by migrating:

  1. exp from the 8.1.6 database
  2. copy files, imp into the 8.1.7.4 database
  3. run the same imp a second time (seems to be some ordering/recursion problem in the creation of various pl/sql procs)
  4. drop index sws_ctx_index force
  5. Run @load-site-wide-search
  6. Still broken, so @recreate-site-wide-search
Success!

The errors between steps 5 and 6 were: 

old   3:   ctx_ddl.set_attribute('sws_user_datastore', 'procedure', 'sws_user_proc_&1');
new   3:   ctx_ddl.set_attribute('sws_user_datastore', 'procedure', 'sws_user_proc_staging');
begin
*
ERROR at line 1:
ORA-20000: interMedia Text error:
DRG-10701: preference STAGING.SWS_USER_DATASTORE already exists
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.CTX_DDL", line 55
ORA-06512: at line 2


create index sws_ctx_index on site_wide_index (datastore)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: interMedia Text error:
DRG-10707: a value is required for attribute PROCEDURE of preference
sws_user_datastore
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 78
ORA-06512: at line 1

alter index sws_ctx_index rebuild parameters('sync')
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: interMedia Text error:
DRG-10561: index SWS_CTX_INDEX is not valid for requested operation
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 214
ORA-06512: at line 1