This discussion is archived
10 Replies Latest reply: Jul 25, 2012 6:40 AM by Ben Speckhard RSS

Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?

866478 Newbie
Currently Being Moderated
Because a hardware platform change is involved, I seemingly have to use exp/imp to accomplish the task.

A straightforward attempt to do a full export on the 11.10.7 system (Solaris Sparc-64) and then a full import on 11.2.0.3 (Linux X64), but that yields errors:

IMP-00003: ORACLE error 20000 encountered
ORA-20000: Incompatible version of Workspace Manager Installed

and after the import, workspaces and history are missing, so no go.

It occurs to me that I could install version 11.1.0.7 on Linux and import the Solaris dumpfile into that, and then do an in-place upgrade of that 11.1 $ORACLE_HOME to 11.2.0.3? But that seems like a lot of (time-consuming) steps and then I'm left with a second upgraded 11.2.0.3 ORACLE_HOME instead of the single cleanly installed version I'd prefer.

The documentation and forum postings seem to hint that I might instead downgrade the version of Workspace Manager on 11.2 temporarily in order to get the data imported and then upgrade it back to the current version of OWM, but nowhere have I found explicit step-by-step instructions nor information on how to download older releases of OWM that can be used to accomplis this.

Any advice on the best approach will be much appreciated.

Edited by: user11987684 on Jul 17, 2012 12:49 PM
  • 1. Re: Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    I am no expert in this area, but the recommended approach is to export/import into 11.1.0.7 in Linux, then upgrade to 11.2.0.3

    What versions of Workspace Manager are "Compatible"? [ID 1174056.1]

    How big is the database ? How much downtime can you afford ?

    One option could be RMAN's convert function for Transportable Tablespaces (moving the database from Solaris to Linux, rather than using exp/imp)

    Migration of an Oracle Database Across OS Platforms [ID 733205.1]

    HTH
    Srini
  • 2. Re: Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?
    866478 Newbie
    Currently Being Moderated
    I'm just in the "practice" stage right now doing a dry-run to figure out how long this process will take and exactly what the steps will be. The database is not large (only a couple GB of data) and I will probably be able to schedule multiple hours of downtime when the actual cutover day arrives. If I have to do an intermediate import to 11.1.0.7 on Linux, then so be it. I was just wondering if there were a clever way. For the other (non-OWM) instances on the machine, the direct export/import method worked like a charm.
  • 3. Re: Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    I do not believe you have a choice in this case - since the database is only a couple GB, export/import should be quick. Just ensure that you install/patch 11.1.0.6/11.1.0.7 on Linux and apply all of the same database patches (in the same order) as you did on Sparc. The "opatch lsinventory" command should be useful to determine what patches were applied to the OH on Sparc

    HTH
    Srini
  • 4. Re: Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?
    866478 Newbie
    Currently Being Moderated
    While importing into a freshly created empty database of the same version (11.1.0.7) on Linux, the error message below shows up in the import log:

    IMP-00003: ORACLE error 20000 encountered
    ORA-20000: Workspace Manager Not Properly Installed
    ORA-06512: at line 29

    I decided to ignore it and then run $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid objects.

    I am able to log into the versioned schema in the database and the workspaces and savepoints seem to be intact -- I can dbms_wm.gotoworkspace(), and other simple actions without error.

    Is there anything I can do to convince myself that Workspace Manager is really and truly OK before proceeding to the next step of upgrading to 11.2?
  • 5. Re: Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Can you pl post the complete export and import commands used, along with the first 20 and last 20 lines of the export and import logs ?

    HTH
    Srini
  • 6. Re: Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?
    866478 Newbie
    Currently Being Moderated
    Sure.


    exp full=y file=cedtest.dmp statistics=none log=cedtest.log

    Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
    server uses WE8MSWIN1252 character set (possible charset conversion)

    About to export the entire database ...
    . exporting tablespace definitions
    . exporting profiles
    . exporting user definitions
    . exporting roles
    . exporting resource costs
    . exporting rollback segment definitions
    . exporting database links
    . exporting sequence numbers
    . exporting directory aliases
    . exporting context namespaces
    . exporting foreign function library names
    . exporting PUBLIC type synonyms

    ....

    . . exporting table ZONE_LINKS 0 rows exported
    . exporting synonyms
    . exporting views
    . exporting referential integrity constraints
    . exporting stored procedures
    . exporting operators
    . exporting indextypes
    . exporting bitmap, functional and extensible indexes
    . exporting posttables actions
    . exporting triggers
    . exporting materialized views
    . exporting snapshot logs
    . exporting job queues
    . exporting refresh groups and children
    . exporting dimensions
    . exporting post-schema procedural objects and actions
    . exporting user history table
    . exporting default and system auditing options
    . exporting statistics
    Export terminated successfully without warnings.



    # Ignore=y because I pre-created tablespaces
    imp full=y file=cedtest.dmp log=cedtestimport.log ignore=y

    --- import
    Connected to: Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

    Export file created by EXPORT:V11.01.00 via conventional path
    import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
    import server uses AL32UTF8 character set (possible charset conversion)
    . importing SYSTEM's objects into SYSTEM
    IMP-00017: following statement failed with ORACLE error 439:
    "CREATE ROLE "GLOBAL_AQ_USER_ROLE" IDENTIFIED GLOBALLY "
    IMP-00003: ORACLE error 439 encountered
    ORA-00439: feature not enabled: Enterprise User Security
    . importing CED2_OWNER's objects into CED2_OWNER
    . importing CEDDEV_OWNER's objects into CEDDEV_OWNER
    . importing CED3_OWNER's objects into CED3_OWNER
    . importing CED3_DEVL's objects into CED3_DEVL
    . importing CED3_OPS's objects into CED3_OPS
    . importing CED3_HIST's objects into CED3_HIST
    . importing APITEST's objects into APITEST
    . importing SYSTEM's objects into SYSTEM
    . importing OLAPSYS's objects into OLAPSYS

    ....

    . importing CED2_OWNER's objects into CED2_OWNER
    . importing CEDDEV_OWNER's objects into CEDDEV_OWNER
    . importing CED3_OWNER's objects into CED3_OWNER
    . importing CED3_DEVL's objects into CED3_DEVL
    . importing CED3_OPS's objects into CED3_OPS
    . importing CED3_HIST's objects into CED3_HIST
    . importing APITEST's objects into APITEST
    . importing SYSTEM's objects into SYSTEM
    . importing OLAPSYS's objects into OLAPSYS
    . importing WKSYS's objects into WKSYS
    . importing WK_TEST's objects into WK_TEST
    . importing FLOWS_FILES's objects into FLOWS_FILES
    . importing FLOWS_030000's objects into FLOWS_030000
    . importing SCOTT's objects into SCOTT
    . importing CED2_OWNER's objects into CED2_OWNER
    . importing CEDDEV_OWNER's objects into CEDDEV_OWNER
    . importing CED3_OWNER's objects into CED3_OWNER
    . importing CED3_OPS's objects into CED3_OPS
    . importing APITEST's objects into APITEST
    Import terminated successfully with warnings.
  • 7. Re: Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post the output of this sql on the target database
    SQL> select comp_name, version, status from dba_registry order by 1;
    If the output shows that the Workspace Manager is not installed, pl follow the steps in this MOS Doc before attempting the import

    How do you manually install/deinstall Oracle Workspace Manager [ID 731576.1]

    Couple other things to note -

    >
    ..
    Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
    server uses WE8MSWIN1252 character set (possible charset conversion)
    ...
    >

    Pl set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 before starting the export.

    >
    ...
    import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
    import server uses AL32UTF8 character set (possible charset conversion)
    ...
    >

    Pl set NLS_LANG to AMERICAN_AMERICA. AL32UTF8 before starting the import.

    HTH
    Srini
  • 8. Re: Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?
    866478 Newbie
    Currently Being Moderated
    Srini Chavali wrote:
    Pl post the output of this sql on the target database
    SQL> select comp_name, version, status from dba_registry order by 1;
    Oracle workspace Manager was installed automatically already in the empty database which I created using dbca.

    Verified same results below before and after the import.

    Still get the "invalid

    JServer JAVA Virtual Machine 11.1.0.7.0 VALID
    OLAP Analytic Workspace 11.1.0.7.0 VALID
    OLAP Catalog 11.1.0.7.0 VALID
    OWB 11.1.0.7.0 VALID
    OracleApplication Express 3.0.1.00.12 VALID
    Oracle Database Catalog Views 11.1.0.7.0 VALID
    Oracle Database Java Packages 11.1.0.7.0 VALID
    Oracle Database Packages and Types 11.1.0.7.0 VALID
    Oracle Enterprise Manager 11.1.0.7.0 VALID
    Oracle Expression Filter 11.1.0.7.0 VALID
    Oracle Multimedia 11.1.0.7.0 VALID
    Oracle OLAP API 11.1.0.7.0 VALID
    Oracle Rules Manager 11.1.0.7.0 VALID
    Oracle Text 11.1.0.7.0 VALID
    Oracle Ultra Search 11.1.0.7.0 VALID
    Oracle Workspace Manager 11.1.0.7.0 VALID
    Oracle XDK 11.1.0.7.0 VALID
    Oracle XML Database 11.1.0.7.0 VALID
    Spatial 11.1.0.7.0 VALID


    Import still logs the error below however:

    IMP-00003: ORACLE error 20000 encountered
    ORA-20000: Workspace Manager Not Properly Installed
    ORA-06512: at line 29

    As before, it seems to possibly be spurious. The versioned tables seem to be intact after the import.

    >
    {quote}
    ..
    Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
    server uses WE8MSWIN1252 character set (possible charset conversion)
    ...
    {quote}

    Pl set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 before starting the export.

    {quote}
    ...
    import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
    import server uses AL32UTF8 character set (possible charset conversion)
    ...
    {quote}

    Pl set NLS_LANG to AMERICAN_AMERICA. AL32UTF8 before starting the import.
    Thanks for the pointer. This clears up those messages.
  • 9. Re: Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Thanks for the update

    I would still open an SR with Support for your error

    IMP-00003: ORACLE error 20000 encountered
    ORA-20000: Workspace Manager Not Properly Installed
    ORA-06512: at line 29

    It should not be reported if you are doing everything by the book.

    HTH
    Srini
  • 10. Re: Migrate OWM-enabled instance from 11.1 on Sparc-64 to 11.2 on Linux-x64?
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    That's correct. No errors should be reported when going from 11.1.0.7 to 11.1.0.7. The "ORA-20000: Workspace Manager Not Properly Installed" error can either be caused by a ORA-06550, ORA-00942, or ORA-24344 error. Are there any errors in the WMSYS schema or any schemas with version enabled tables reported on 11.1.0.7?

    Also, since your intention is to go to 11.2.0.3 on a different machine, it would be possible to upgrade the 11.1.0.7 installation to 11.2.0.3 and then do the export/import to the LinuxX64 machine. Either method should work however.

    Regards,
    Ben

Legend

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