Forum OpenACS Development: Recovering an Oracle database in UTF8

I'm setting up a clone of the translation server and had take some extra steps over what is described in our recovery instructions (see https://openacs.org/doc/openacs-HEAD/backup-recovery.html) to get things to work. One thing, that I already changed in the doc, was to use FULL=Y and to make sure that oracle user names and tablespace names are identical (I'm importing on different machine).

The other thing I had to do was set NLS_LANG. Here are my complete import and export commands:

export NLS_LANG=AMERICAN_AMERICA.UFT8
/usr/sbin/export-oracle translate2 /tmp/translate2.tmp

# Transfer file to other machine
scp /tmp/translate2.dmp.gz mailto:peter@cph02.collaboraid.net:/tmp

ssh cph02.collaboraid.net
# Setup a clean database user and tablespace with same names as the ones exported from

export NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_CHARACTERSET=UTF8
export NLS_LANGUAGE=AMERICAN
export NLS_TERRITORY=AMERICA
imp translate2/translate2 FILE=/tmp/translate2.dmp  FULL=Y

Me having to set NLS_LANG may be because our databases were not correctly configured to utf8 charset upon installation so I'm hesitant to add the NLS_LANG setting to export-oracle script. I guess we could add a note about it in the recovery doc though.

Unrelated to the charset issue, here is an excerpt of the errors I get during the import (no blockers for me as I'm only interested in translations and a working server):

IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "TRANSLATE2"."ACS_MESSAGES_LATEST"                      ("
""MESSAGE_ID","REPLY_TO","SENT_DATE","SENDER","RFC822_ID","REVISION_ID","TIT"
"LE","MIME_TYPE","CONTENT") AS "
"select m.message_id, m.reply_to, m.sent_date, m.sender, m.rfc822_id,"
"          r.revision_id, r.title, r.mime_type, r.content"
"        from cr_items i, cr_revisions r, acs_messages m"
"        where i.item_id = m.message_id"
"            and r.revision_id = content_item.get_latest_revision(i.item_id)"
IMP-00041: Warning: object created with compilation warnings

IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "TRANSLATE2"."SURVEY_RESPONSES_LATEST"                  "
"  ("RESPONSE_ID","INITIAL_RESPONSE_ID","SURVEY_ID","TITLE","NOTIFY_ON_COMM"
"ENT_P","CREATION_DATE","CREATION_USER","INITIAL_USER_ID") AS "
"select sr.*, o.creation_date,"
"      o.creation_user,"
"      survey_response.initial_user_id(sr.response_id) as initial_user_id"
"  from survey_responses sr,"
"  acs_objects o,"
"  (select max(response_id) as response_id"
"          from survey_responses"
"        group by survey_response.initial_response_id(response_id)) latest"
"  where sr.response_id = o.object_id"
"  and sr.response_id= latest.response_id"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "TRANSLATE2"."NEWS_ITEM_FULL_ACTIVE"                    "
" ("ITEM_ID","PACKAGE_ID","REVISION_ID","PUBLISH_TITLE","PUBLISH_BODY","HTML"
"_P","PUBLISH_DATE","ARCHIVE_DATE","STATUS","ITEM_NAME","CREATOR_ID","ITEM_C"
"REATOR") AS "
"select"
"    ci.item_id as item_id,"
"    cn.package_id as package_id,"
"    revision_id,"
"    title as publish_title,"
"    content.blob_to_string(cr.content) as publish_body,"
"    decode(cr.mime_type,'text/html','t','f') as html_p,"
"    cr.publish_date,"
"    cn.archive_date,"
"    news.status(cr.publish_date, cn.archive_date) as status,"
"    ci.name as item_name,"
"    ps.person_id as creator_id,"
"    ps.first_names || ' ' || ps.last_name as item_creator"
"from"
"    cr_items ci,"
"    cr_revisions cr,"
"    cr_news cn,"
"    acs_objects ao,"
"    persons ps"
"where"
"    cr.item_id = ci.item_id"
"and (cr.revision_id = ci.live_revision"
"    or (ci.live_revision is null"
"    and cr.revision_id = content_item.get_latest_revision(ci.item_id)))"
"and cr.revision_id = cn.news_id"
"and ci.item_id = ao.object_id"
"and ao.creation_user = ps.person_id"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "TRANSLATE2"."NEWS_ITEM_REVISIONS"                      ("
""ITEM_ID","REVISION_ID","LIVE_REVISION","PUBLISH_TITLE","PUBLISH_BODY","PUB"
"LISH_DATE","ARCHIVE_DATE","LOG_ENTRY","HTML_P","MIME_TYPE","PACKAGE_ID","CR"
"EATION_DATE","STATUS","APPROVAL_NEEDED_P","ITEM_CREATOR","CREATION_USER","C"
"REATION_IP","ITEM_NAME") AS "
"select"
"    cr.item_id as item_id,"
"    cr.revision_id,"
"    ci.live_revision,"
"    cr.title as publish_title,"
"    content.blob_to_string(cr.content) as publish_body,"
"    cr.publish_date,"
"    cn.archive_date,"
"    cr.description as log_entry,"
"    decode(cr.mime_type,'text/html','t','f') as html_p,"
"    cr.mime_type as mime_type,"
"    cn.package_id,"
"    ao.creation_date as creation_date,"
"    news.status(cr.publish_date, cn.archive_date) as status,"
"    case when exists (select 1"
"                      from cr_revisions cr2"
"                      where cr2.revision_id = cn.news_id"
"                        and cr2.publish_date is null"
"                    ) then 1 else 0 end"
"        as"
"        approval_needed_p,"
"    ps.first_names || ' ' || ps.last_name as item_creator,"
"    ao.creation_user,"
"    ao.creation_ip,"
"    ci.name as item_name"
"from"
"    cr_revisions cr,"
"    cr_news cn,"
"    cr_items ci,"
"    acs_objects ao,"
"    persons ps"
"where"
"    cr.revision_id = ao.object_id"
"and cr.revision_id = cn.news_id"
"and ci.item_id = cr.item_id"
"and ao.creation_user = ps.person_id"
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "TRANSLATE2"."NEWS_ITEMS_UNAPPROVED"                    "
" ("ITEM_ID","PUBLISH_TITLE","PACKAGE_ID","CREATION_DATE","ITEM_CREATOR") AS"
" "
"select"
"    ci.item_id as item_id,"
"    cr.title as publish_title,"
"    cn.package_id as package_id,"
"    ao.creation_date as creation_date,"
"    ps.first_names || ' ' || ps.last_name as item_creator"
"from"
"    cr_items ci,"
"    cr_revisions cr,"
"    cr_news cn,"
"    acs_objects ao,"
"    persons ps"
"where"
"    cr.revision_id = ao.object_id"
"and ao.creation_user = ps.person_id"
"and cr.revision_id = content_item.get_live_revision(ci.item_id)"
"and cr.revision_id = cn.news_id"
"and cr.item_id = ci.item_id"
"and cr.publish_date is null"

Collapse
Posted by Dirk Gomez on
Read the doco :) The views are created with an error because some underlying objects are not there yet - hence the word force so that Oracle creates it with a state of invalid.

They'll be recompiled on first usage and your query will choke if they cannot be compiled properly.

Why did you take a full export?

Collapse
Posted by Peter Marklund on
I recreated our Oracle database with utf8 but our problems persisted. Turns out if AOLserver doesn't run with NLS_LANG=.UTF8 nothing will really work no matter what your database is created in...

I added a note about NLS_LANG in the i18n developers guide and  added it commented out in nsd-oracle. Also, I added OutputCharset and URLcharset commented out in the AOLserver config file template (etc/config.tcl) so that they can be easily commented in for internationalized sites.