We are moving our Oracle database from an old IBM box to a new IBM 670 eServer. Currently we are using Oracle8i and will be upgrading to Oracle9i in this migration. My question pertains to the best way to move the data from our Oracle database to the new system. We are looking at Oracle's export and import utilities. Is this the best way?
As with many Oracle-related questions, the answer to this one is "it depends." The factors I'd consider are:
1. Size of the database/time to export and import: If the database is large, there could be a long outage waiting for the export and import utilities to complete. Export/import are trustworthy, but you're also going to reorganize the database's storage which may produce some undesirable side effects. For example, blocks will become more compressed and rows that used to be in separate blocks may now end up in one block. If that happens often enough, some applications may run into concurrency issues due to too many applications needing to place locks in one block.
2. Application support: I've encountered at least a few applications that "don't work" once they've undergone and export/import and the application vendor firmly desupports the database in that case. It's not too common, but something to watch out for.
3. Storage reorganization desired: If you're using dictionary-managed tablespaces now and wish to convert to locally-managed tablespaces (LMTs), then export/import is one way to get to your desired configuration all at once.
4. Other things to consider: Why use export/import at all? There's nothing wrong with the upgrade procedure and it is almost certain to be shorter in duration than an export/import if you have much data. I wouldn't shy away from doing an upgrade unless you have a good reason to recreate the database (as you would have to for export/import).
Dig Deeper on Oracle database installation, upgrades and patches
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.