4 Replies Latest reply on Apr 26, 2012 11:18 AM by MatthiasHoys

    Export APEX schemas from 10g to 11g


      We are currently using APEX 4.1.1 on Oracle 10g. We plan to migrate everything to a new server with Oracle 11gR2 as database. Is it possible to use export dump to export all APEX users and schemas from 10g and import them into 11g? Or will this not work? Is the APEX code base different between 10g and 11g?

      Matthias Hoys

      Edited by: mhoys on Apr 6, 2012 4:08 PM
        • 1. Re: Export APEX schemas from 10g to 11g
          Hi Matthias --

          Well -- lots of 'correct' answers on this one depending on what your situation is.

          If it's a small db and relatively small number of apps/workspaces I'd really recommend you bring up your 11g DB (custom install lets you remove the apex installation from the list of standard database components), install Apex from the download (instead of what ships with the db), and then bring over your (parsing) schemas and apps unless you have an overwhelming need to keep your existing apex schemas as-is. I've found that to be so much nicer when it's possible, especially if I've gone through lots of apex upgrades. If you're relying on the history in the Apex data dictionary, then that's a reason you might want to bring over your old Apex schemas, but if that isn't a big deal for you -- go clean slate. My 2 cents. You can use data pump (expdp/impdp) to move the parsing schemas if you want, or you might have a preferred method of migration. If you've never used it, expdp/impdp rocks compared to the old export/import.

          If it's a big db...that's a different story. RMAN can help with a migration.

          Some considerations:

          * What are we talking about - db size and number of apps and workspaces?
          * Are you changing operating systems (if so, what are they)?
          * Are you changing character sets between db's (and what are they)?
          * Are you changing your server directory structure, including the http server (or are you running the Apex Listener, or built-in, or ... you get the idea)...or is this something you still have to look into?
          * I'm sure you have this planned out, but wanted to mention it since only one instance was mentioned -- are you doing this in dev or qual first so you get to do a dry-run, and are they actually identical in structure?
          * Were your schemas created with their own tablespaces/datafiles or were they all create in one? Depending on how you're doing your migration, make sure you have that well-documented, and if something needs to change, now's a pretty good time to do it. Sometimes, if I have some complexity to my setup and it's changed significantly from environment to environment, I'll create my schemas with my storage defined prior to importing.

          Hope that helps!

          1 person found this helpful
          • 2. Re: Export APEX schemas from 10g to 11g
            Hello Ron,

            Thanks for the feedback. Our environment is relatively small, but I prefer to keep APEX's history. I know about Data Pump, so I will try to use that for the data migration. Yes, we have a test environment that I can use for a dry-run... We also can afford some down time, so nothing to worry here. I just wanted to know if it's technically possible to migrate an APEX installation to a new environment like this, and what the pitfalls are to watch out for. Things like user roles, public synonyms, hard coded host names or IP addresses in APEX tables and such. Guess I will have to try it out :-)

            Matthias Hoys

            Edited by: mhoys on Apr 10, 2012 10:47 AM
            • 3. Re: Export APEX schemas from 10g to 11g
              Any chance you could update us on your progress?

              I am facing the same upgrade process and would be interested to hear your experiences and results from the Data Pump migration/upgrade process


              • 4. Re: Export APEX schemas from 10g to 11g
                Well... I tried and and it doesn't work :( There are packages and library objects in SYS that remained invalid. APEX registers some stuff in Oracle's data dictionary (dba_registry and such) and I don't know how to enter that information... I ended up doing a clean APEX 4.1.1 install on 11g and then I exported/imported all workspaces, applications, static files, ... A bit of manual work, but I must admit, APEX is running super fast now! Probably also related to the new hardware we have :-)

                Matthias Hoys