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.