2

I'm trying to move an Oracle 10g database from 10.1.0.5 running on W2K Server to 10.2.0.5 running on a Windows x64 install (either Win 7 or 2008 R2). I can't upgrade the source server, so I can't change the source DB to 10.2.0.5 and go from there. I can't install 10.1.0.5 on my destination system because 10.1.0.5 won't run on x64. So what do I do? Could I use expdp/impdp in full mode?

(Note: I'm not an Oracle DBA. I also don't have access to one.)

3

Leaving the question aside why in the world you would want to stay on Oracle 10g, my preference to this would be the long way. I've done many "upgrades" this general way:

  • Build a new empty 10.2.0.5 DB on your new server
  • Identify the schemas that your application uses on the old server, including any accounts that an application uses that don't have any objects.
  • Create tablespaces on your new server that have the same tablespace names you are using on the old server
  • If you are using DB roles for your application users, then create the same roles on the new DB. If these roles have any system privileges granted, then grant the same system privileges in the new DB.
  • expdp the schemas that your application uses, including schemas that have tables and any application users. Kind of like this (for schemas HR and SCOTT -- obviously you change this as needed):

    expdp "userid='/ as sysdba'" schemas=hr,scott dumpfile=old_database.dmp logfile=old_database.log

  • Copy/move the DMP file to your new server

  • impdp the schemas on your new server, such as:

    impdp "userid='/ as sysdba'" dumpfile=old_database.dmp logfile=imp.log

  • In case your old DB has any PUBLIC db links or PUBLIC synonyms, you'll want to migrate those over as well.

Most times, I prefer migrating schemas via impdp rather than trying to DBUA it. I concede that DBUA can be faster, but with an in-place upgrade, you carry over whatever problems or skeletons your old DB had.

This might be a more familiar example of the same dichotomy: If you've ever upgraded a PC from Win XP --> to Vista --> to 7, you know how screwed up it can get from doing "upgrade" installs. Most people I know would rather backup their documents, do a clean Win 7 install, and reinstall your programs. Yes, this takes hours but you get a lot cleaner, more stable system.

  • Thanks for the answer. This looks like a good starting point for me. – Bob Dively Jul 21 '14 at 19:44
  • Also, just to answer the why 10g question: For no other reason than my own vast ignorance. Going from 10.1 to 10.2 and then maybe going to 11g via DBUA felt more logical, but that's based on nothing at all other my gut after reading a lot. – Bob Dively Jul 21 '14 at 19:45
  • I would highly recommend that you go 11gR2 (11.2.0.4) or 12c. 10g ended mainstream (premier) support in 2010. 11gR2 is expected to end mainstream support in 2015. This is unless you have some legacy requirement, such as an application that must run on Oracle 10g. – Joshua Huber Jul 21 '14 at 19:56
  • OK, cool. I'll give 11gR2 or 12c a run first and fall back to 10g if necessary. Thanks again. – Bob Dively Jul 21 '14 at 20:14
  • I believe you're not supposed to run impdp/expdp as sysdba in general. Better to create a user with the imp/exp_full_database to do that. – Mat Jul 21 '14 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.