Oracle Upgrades and Migrations

Oracle is synonym for relational database and is extensively used mission critical online and transactional systems. It is leading and most advanced relational database and Oracle consistently releases minor as well as major releases with new features. Enterprises needs to upgrade their Oracle databases to leverage these new features. Lately most of the enterprises are consolidating the hardware to cut down the operational costs. Upgrades and consolidation effort requires migration of the databases. There are multiple ways to migrate the databases.

  • Backup, Restore and Recover
    1. Take the full RMAN backup of source database
    2. Copy over the data to the server on which target database is built
    3. Restore it on the target database
    4. Ship the logs and recover the target database during downtime
  • Datapump Export and Import
    1. Alter database to read only mode or make sure no applications are updating tables in database
    2. Take the full export of the database using data pump
    3. Ship the dump files to server on which target database is built
    4. Import on the target database
    5. Connect the applications to new database
  • ETL Tools
    1. Develop ETL process using tools like Informatica
    2. Migrate data using the process
    3. Make sure all the data is copied
    4. Connect the applications to new database
  • Do your self parallel (Custom Tool)
    1. Connect to source database over db link
    2. Select data from source database using db link and insert into table on target database
    3. Identify all static tables or partitions and copy multiple tables or partitions in parallel (using controlled number of threads) using direct path insert
    4. For large tables, divide data into multiple chunks using row id ranges and copy the data over database link
    5. Migrate hot tables and partitions during down time