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: