This discussion is archived
10 Replies Latest reply: Feb 11, 2013 9:14 PM by rcx4009v2 RSS

Exporting Data from one Server to Another server w/ Version Enabled Tables

rcx4009v2 Newbie
Currently Being Moderated
Hi,

I'm currently having a problem with regards to Exporting data to another server. This is the Scenario:

Source Server is Production Server with all of its Tables in the Schema are Version-Enabled.

Destination Server is a Test Server.

I exported data from Production Server using EXP command. Then in my Test Server I imported my data using IMP command (I already created tablespace and user for the Schema).

Import is successful in my Test server but when I execute my queries, There are no rows returned.

I checked my _LT tables and it contains my data. but when I query from the View created when version was enabled, no result is returned.

Am I missing something when I exported and imported my Schema? Should I have included the WMSYS schema when I created the .dump file?


Thanks in advance.
  • 1. Re: Exporting Data from one Server to Another server w/ Version Enabled Tables
    asahide Expert
    Currently Being Moderated
    Hi,

    Show us your import option.
    Could you set "IGNORE=Y" parameter?

    Regards,
  • 2. Re: Exporting Data from one Server to Another server w/ Version Enabled Tables
    rcx4009v2 Newbie
    Currently Being Moderated
    These are my Import Parameters:

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Produc
    tion
    With the Partitioning, OLAP and Data Mining options

    Import file: EXPDAT.DMP > D:\oracle dump files\Data_Backup.dmp

    Enter insert buffer size (minimum is 8192) 30720>

    Export file created by EXPORT:V10.02.01 via conventional path
    import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    List contents of import file only (yes/no): no > no

    Ignore create error due to object existence (yes/no): no > yes

    Import grants (yes/no): yes > yes

    Import table data (yes/no): yes > yes

    Import entire export file (yes/no): no > yes
  • 3. Re: Exporting Data from one Server to Another server w/ Version Enabled Tables
    asahide Expert
    Currently Being Moderated
    Hi,

    Put your export and import logs, pls.

    Regards,
  • 4. Re: Exporting Data from one Server to Another server w/ Version Enabled Tables
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Rcx,

    Importing and exporting versioned tables is best done by using the datapump. Even then, there are considerations and restrictions: [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/long_intro.htm#i1018057]check the documentation

    HTH,
    Stefan
  • 5. Re: Exporting Data from one Server to Another server w/ Version Enabled Tables
    rcx4009v2 Newbie
    Currently Being Moderated
    Hi Stefan,

    we tried using Export and Import using Data Pump.

    expdp system/password@orcl full=y directory=dmpdir2 dumpfile=FULL_DB.dmp

    impdp system/password@orcl full=y table_exists_action=truncate directory=dmpdir2 dumpfile=FULL_DB.dmp

    Still the same result as using exp and imp. _LT tables have data but when you query using the View, no results are found.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 6. Re: Exporting Data from one Server to Another server w/ Version Enabled Tables
    Stefan Jager Journeyer
    Currently Being Moderated
    Well,

    There's a few things you can do:

    Check for the views validity: select status from dba_objects where object_type = 'VIEW'; Maybe all it needs is a recompile.
    Check for the WM privileges (maybe the privileges did not export/import well: select * from ALL_WORKSPACE_PRIVS; )
    and
    Documentation says:
    A database with version-enabled tables can be exported to another Oracle database only if the other database has Workspace Manager installed and does not currently have
    any version-enabled tables or workspaces (that is, other than the LIVE workspace).
    Did you already have version-enabled tables or workspaces in the database you imported in?

    Besides, on a more general note: What is it you are trying to achieve? [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/long_repl.htm#i634199]Replication? A backup? OTA? Export/Import may not necessaruly be the best way to what you are trying to achieve, that's why I'm asking.

    HTH,
    Stefan
  • 7. Re: Exporting Data from one Server to Another server w/ Version Enabled Tables
    rcx4009v2 Newbie
    Currently Being Moderated
    Hi Stefan,

    We are actually trying to refresh the data of our QA/Dev server.
    QA/Dev Server does not have workspaces and version-enabled tables.
    Production Server contains workspaces and version-enabled tables.

    Also, before we did the import in the QA/Dev server, we dropped the user with cascade options to ensure that there won't be any conflicts.

    Upon checking my WM Views for my version-enabled tables, they are valid. But ALL_WORKSPACE_PRIVS and ALL_WM_VERSIONED_TABLES does not contain data.
  • 8. Re: Exporting Data from one Server to Another server w/ Version Enabled Tables
    Stefan Jager Journeyer
    Currently Being Moderated
    rcx4009v2 wrote:
    But ALL_WORKSPACE_PRIVS and ALL_WM_VERSIONED_TABLES does not contain data.
    Well, there you go. WM does not know of any versioned tables, so the whole thing does not work properly. Try exporting and importing including the WMSYS-schema, while specifying
    table_exists_action=truncate
    That way WM's metadata will come across as well, which it is missing now.

    Stefan
  • 9. Re: Exporting Data from one Server to Another server w/ Version Enabled Tables
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    What version of Workspace Manager are you using? Note that the source and target databases currently need to be the same version of Workspace Manager. This version can be found in the dba_registry or wm_installation view. In addition, the target database cannot have any existing workspaces or version enabled table.

    If those match, then I would check the import log for errors related to the WMSYS schema. Most likely there is some type of error preventing the import of the WMSYS metadata. It is not necessary to explicitly include the WMSYS schema, as it will be included in any FULL database export.

    Regards,
    Ben
  • 10. Re: Exporting Data from one Server to Another server w/ Version Enabled Tables
    rcx4009v2 Newbie
    Currently Being Moderated
    Hi All,

    So I managed to successfully Export/Import Full database with Version Enabled tables to my other server.

    What I did is the following:

    1. Export using EXP SYS/password@ORCL_TNS FULL=Y FILE=FULL_DB.DMP from the source Server

    2. I then Dropped the User for my Schema and then Drop WMSYS user both with cascade options in my destination Server.

    3. I re-installed Workspace Manager as indicated in Oracle Documentation (http://docs.oracle.com/cd/B14117_01/appdev.101/b10824/long_inst.htm)

    4. After reinstalling Workspace Manager, I then Imported my dump file using IMP SYS/password@ORCL_TNS FULL=Y IGNORE=Y FILE=FULL_DB.DMP

    After these steps, I can now query using the Views of my tables.

    Thank you all for the support.

Legend

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