This discussion is archived
8 Replies Latest reply: Aug 10, 2011 12:26 PM by Ben Speckhard RSS

Export/Import full database dump fails to recreate the workspaces

874010 Newbie
Currently Being Moderated
Hi,

We are studying the possibility of using Oracle Workspace to maintain multiple versions of our business data. I recently did some tests with the import/export of a dump that contains multiple workspaces. I'm not able to import the dump successfully because it doesn't recreate the workspaces on the target database instance, which is an empty Oracle database freshly created. Before to launch the import with the Oracle Data Pump utility, I have the "LIVE" workspace that exists in the WMSYS.WM$WORKSPACES_TABLE table. After the import is done, there is nothing in that table...

The versions of the Oracle database and Oracle Workspace Manager are the same on source and target database:




Database version (from the V$VERSION view):

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE     10.2.0.4.0     Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production




Workspace Manager version (from the WM_INSTALLATION view):

OWM_VERSION: 10.2.0.4.3






In order to recreate the tablespaces successfully during the full import of the dump, the directory structure for the tablespaces is the same on the target database's computer. I used the instructions given in this document, section "1.6 Import and Export Considerations" at page 1-19:

http://www.wyswert.com/documentation/oracle/database/11.2/appdev.112/e11826.pdf







Considering that the release of Oracle database used is version 10.2.0.4, I use the following command to import the dump since it doesn't contain the WMSYS schema:

impdp system/<password>@<database> DIRECTORY=data_pump_dir DUMPFILE=expfull.dmp FULL=y TABLE_EXISTS_ACTION=append LOGFILE=impfull.log








The only hints I have in the import log file are the following block of lines:


1st one:
==============================
Traitement du type d'objet DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Echec de la création du type d'objet PROCACT_SYSTEM avec erreur :
ORA-01843: ce n'est pas un mois valide
SQL en échec :
BEGIN
if (system.wm$_check_install) then
return ;
end if ;
begin execute immediate 'insert into wmsys.wm$workspaces_table
values(''LIVE'',
'''',
''194'',
'''',
''-1'',
'''',


2nd one at the end of the import process:
==============================
Traitement du type d'objet DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39083: Echec de la création du type d'objet PROCACT_SCHEMA avec erreur :
ORA-20000: Workspace Manager Not Properly Installed
SQL en échec :
BEGIN
declare
compile_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(compile_exception, -24344);
begin
if (system.wm$_check_install) then
return ;
end if ;

execute immediate 'alter package wmsys.ltadm compile';
execute immediate 'alter packag











The target operating system is in french... here is a raw translation:



1st one:
==============================
Treatment of object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type creation failed PROCACT_SYSTEM with error :
ORA-01843: invalid month
failed SQL :
BEGIN
if (system.wm$_check_install) then
return ;
end if ;
begin execute immediate 'insert into wmsys.wm$workspaces_table
values(''LIVE'',
'''',
''194'',
'''',
''-1'',
'''',


2nd one at the end of the import process:
==============================
Treatment of object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type creation failed PROCACT_SCHEMA with error :
ORA-20000: Workspace Manager Not Properly Installed
failed SQL :
BEGIN
declare
compile_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(compile_exception, -24344);
begin
if (system.wm$_check_install) then
return ;
end if ;

execute immediate 'alter package wmsys.ltadm compile';
execute immediate 'alter packag









By the way, the computer of the source database is Vista 64 in english and the destination computer is Vista 64 in french, do you think this has anything to do with these error messages? The parameters of the NLS_SESSION_PARAMETERS view are the same in the source and target database...





Thanks in advance for your help!


Joel Autotte
Lead Developer

Edited by: 871007 on Jul 13, 2011 7:31 AM
  • 1. Re: Export/Import full database dump fails to recreate the workspaces
    NoelKhan Journeyer
    Currently Being Moderated
    Hi Joel,

    [1]

    . . . .See {thread:id=774863}

    . . . .You mentioned you're performing a full import, but did you do a full database export ? In other words, when you exported, did you list specific tables to export (incorrect) or did you export the entire database (correct)? You can review Import and Export Considerations here.

    [2]

    . . . .The suspect the problem is due to localization settings (English vs. French). You can read up on how to configure localization here and look into calendar formats here.

    Regards,
    Noel
  • 2. Re: Export/Import full database dump fails to recreate the workspaces
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Joel,

    I haven't seen this exact error in the past. One thing you could try as a test is to import the dumpfile back into a database with the same localization settings. If it works, it would mean that it is failing due to the settings, and could be something that could looked into. If it continues to fail, then we would know it has to be something else entirely.

    The 2nd error is due to compiling some of the WMSYS packages. Are there any errors in the database in this schema prior to importing?

    Regards,
    Ben
  • 3. Re: Export/Import full database dump fails to recreate the workspaces
    874010 Newbie
    Currently Being Moderated
    Hi Noel,

    thanks for your help! The dump I created with the Data Pump "expdp" command is a full export. For the "Import and Export Considerations", I followed the instructions in the Oracle document (mentioned in my first post), which is a bit different than your document since it mentions the instructions when you work with the Data Pump "impdp" and "expdp" commands.

    As for the localization settings, I did the following query in my source and destination database to be sure that they were the same...

    SELECT * FROM NLS_DATABASE_PARAMETERS

    ...and they are the same. Now, is it the proper way to verify the localization parameters used by Oracle when you do an export and import? Because I did other research for this error "ORA-01843: not a valid month" when importing a dump and the localization settings were always mentioned as a possible source for the problem...

    I'll also give it a try with the old "imp" command to see if it works...


    Thanks!
  • 4. Re: Export/Import full database dump fails to recreate the workspaces
    874010 Newbie
    Currently Being Moderated
    Hi Ben,

    thank you for your suggestions. The localization settings are the same on the source and destination database, validated with the following query:

    SELECT * FROM NLS_DATABASE_PARAMETERS


    For the integrity of the WMSYS packages, I did the following query prior to importing and it didn't bring back any result:

    SELECT owner,
    object_type,
    object_name,
    status
    FROM dba_objects
    where owner = 'WMSYS'
    WHERE status = 'INVALID'
    ORDER BY owner, object_type, object_name;


    I'll try to import the dump with the old "imp" command...


    Thanks,
  • 5. Re: Export/Import full database dump fails to recreate the workspaces
    874010 Newbie
    Currently Being Moderated
    I tried to import the full database dump with the "imp" command and I had the same error message with more detail:


    . Import d'objets SYSTEM dans SYSTEM
    IMP-00017: Echec de l'instruction suivante avec erreur ORACLE 1843 :
    "BEGIN "
    "if (system.wm$_check_install) then"
    " return ;"
    " end if ;"
    " begin execute immediate 'insert into wmsys.wm$workspaces_ta"
    "ble"
    " values(''LIVE'',"
    " '''',"
    " ''194'',"
    " '''',"
    " ''-1'',"
    " '''',"
    " ''SYS'',"
    " *to_date(''09-DEC-2009 00:00:00'', ''DD-MON-YYYY HH"*
    *"24:MI:SS''),"*
    " '''',"
    " ''0'',"
    " ''UNLOCKED'',"
    " '''',"
    " '''',"
    " '''',"
    " '''',"
    " ''0'',"
    " '''',"
    " ''0'',"
    " ''37'',"
    " ''CRS_ALLNONCR'',"
    " '''',"
    " to_date(''06-APR-2011 14:24:57'', ''DD-MON-YYYY HH"
    "24:MI:SS''),"
    " ''0'',"
    " '''')'; end;"
    "COMMIT; END;"
    IMP-00003: Erreur ORACLE 1843 rencontrée
    ORA-01843: ce n'est pas un mois valide
    ORA-06512: à ligne 5




    The call to the TO_DATE function with the string of the date format is incompatible with the format and language set in the NLS parameters of my database. Since I know that the "imp" and "exp" commands work client side (unlike "impdp" and "expdp"), I did a full export of the source database from the computer on which I try to import the dump to have the same language exported in the dump and I was able to import the dump successfully.

    But, I would like to use the Data Pump tool instead of the old import and export tools. How would it be possible to set the NLS parameters that "impdp" must use before to launch the import of the dump?

    The NLS parameters are set to the "AMERICAN" settings in both source and destination database when I validate that with the "NLS_DATABASE_PARAMETERS" view. The dump I did with "expdp" on the source database exported the data with the "AMERICAN" settings as well...

    On the destination database, the computer language is in french and I guess it is considered by the Data Pump import tool since it outputs the log in the language of the computer and it tries to do a TO_DATE with the wrong date format because it works with the "CANADIAN FRENCH" settings...



    Any suggestions? I'll continue to read on my side and re-post if I find the solution.



    Thanks!

    Edited by: 871007 on Jul 19, 2011 8:42 AM

    Edited by: 871007 on Jul 19, 2011 8:43 AM

    Edited by: 871007 on Jul 19, 2011 8:43 AM
  • 6. Re: Export/Import full database dump fails to recreate the workspaces
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    What about the NLS_SESSION_PARAMETERS view for the destination database? Does that indicate 'AMERICAN' or 'FRENCH CANADIAN'? It is possible by using the NLS_LANG environment variable to override the default database settings. If it was being modified, just unset it prior to running impdp and it should work as expected.

    Also, feel free to file a SR on this. This is something that could be fairly easily fixed, so that in would work in all cases.

    Regards,
    Ben
  • 7. Re: Export/Import full database dump fails to recreate the workspaces
    874010 Newbie
    Currently Being Moderated
    Hi Ben,

    The NLS_LANG on both source and destination computers were blank. The NLS_SESSION_PARAMETERS view was showing the same parameter values on both computers as well.

    I recently did other tests to be able to import the dump with the Data Pump tools. Here is what I did to be able to import my dump successfully:

    1 - On the computer of the source database, set the NLS_LANG (in the Windows registry) parameter with the "CANADIAN FRENCH_CANADA.WE8MSWIN1252" value.
    2 - Do a full export of the database with the "expdp" command.
    3 - Create an empty Oracle database instance on the computer of the destination database.
    4 - On the computer of the destination database, set the NLS_LANG (in the Windows registry) parameter with the "CANADIAN FRENCH_CANADA.WE8MSWIN1252" value.
    5 - Import the full dump with the "impdp" command.


    And it worked. So we could say that the Data Pump tool really considers the NLS_LANG registry value to export and import dump files... Is it supposed to? If it doesn't, I'll create a SR.


    Thanks for your help.

    Edited by: 871007 on Aug 4, 2011 12:37 PM
  • 8. Re: Export/Import full database dump fails to recreate the workspaces
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Yes, on Windows I would expect it to use the registry setting, so that is working as expected. If you changed this to 'AMERICAN' for the duration of the import, the error regarding Workspace Manager should be fixed. If you need this fixed so that changing NLS_LANG is not needed, then file a SR.

    Regards,
    Ben

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points