Forum OpenACS Development: Recovering an Oracle database in UTF8
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"
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?
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.