This discussion is archived
4 Replies Latest reply: Apr 26, 2012 4:18 AM by MatthiasHoys RSS

Export APEX schemas from 10g to 11g

MatthiasHoys Explorer
Currently Being Moderated
Hello,

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?

Thanks,
Matthias Hoys
http://matthiashoys.wordpress.com

Edited by: mhoys on Apr 6, 2012 4:08 PM
  • 1. Re: Export APEX schemas from 10g to 11g
    rmhardma Oracle ACE
    Currently Being Moderated
    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!

    Ron
  • 2. Re: Export APEX schemas from 10g to 11g
    MatthiasHoys Explorer
    Currently Being Moderated
    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 :-)

    Cheers,
    Matthias Hoys

    Edited by: mhoys on Apr 10, 2012 10:47 AM
  • 3. Re: Export APEX schemas from 10g to 11g
    276501 Newbie
    Currently Being Moderated
    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

    Cheers

    PMcM
  • 4. Re: Export APEX schemas from 10g to 11g
    MatthiasHoys Explorer
    Currently Being Moderated
    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

Legend

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