10 Replies Latest reply: Jul 25, 2012 8:40 AM by Ben Speckhard-Oracle RSS

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

    866478
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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-Oracle
                            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