Question:  I am looking for a Oracle migration best practices manual which can take me through to steps I need to follow.  I want the best practices for migrating from Oracle 19i to 10g.  Is there a best practices manual for Oracle migrations?

Answer: No, there is not a best practices manual for Oracle migrations, but there are some specific best practices for migrations, in general.  There are several critical aspects to an Oracle migration and you need to consider these best practices:

- SQL migration best practices - Some say that it's always a best practice to Freeze your old execution plans, and roll-out SQL after careful testing

- Oracle new features best practices - Carefully choose and test all new features to deploy, justify the reasons, and perform careful QA testing prior to a production rollout.

- Testing best practices - You should thoroughly test the new migration using real-world representative workloads (benchmark tools, SQL tuning  Sets (STS), or 11g SPA workloads.  Also, the optimizer changes radically during a release migration, and one of the most important best practices for migrations is to re-analyze your schema using the latest release.

- Migration preparation best practices - Carefully read the migration guide for your OS, apply all patch updates from MOSC, review all changed/obsolete global parameters (init.ora/spfile). 
 

Best Practices for a migration rollout

See MOSC Note.419550.1 Different Upgrade Methods For Upgrading Your Database, and NOTE.316889.1, Complete checklist for manual upgrades to 10gR2.  Best practice include following a recommended/supported migration path, which would include a backup of the pre-migration database.

Migration Backups

The Oracle migration guide lists various migration backup best practices and your choices are (generally):

• DBUA

• Manual backups (look on MOSC for the complete checklist to migrate to 10g)

• Export/import (Data Pump)

• Transportable tablespaces
   

Migration verification testing

There are many "gotchas" during a migration, especially in 10g where the kernel was improved and re-written.  See my notes on important 10g upgrade best practices.

Also, see my related notes on Oracle best practices: