Procedure for migrating an Oracle Database from 12c to 19c on a new server using Data Pump:
Target Server Preparation:
1. Install Oracle Database 19c software on the target server.
2. Create a new Oracle home directory for the 19c database (e.g., /u01/app/oracle/product/19.0.0/dbhome_1).
3. Set environment variables (e.g., ORACLE_HOME, ORACLE_SID, PATH).
4. Create a new database instance (e.g., SID=NEWDB).
5. Configure the listener and tnsnames.ora files.
Data Pump Migration:
1. On the source server (12c), create a directory for the Data Pump export files (e.g., /u01/export).
2. Run the Data Pump export command to export the entire database:
expdp system/password@source_db schemas=ALL directory=EXP_DIR dumpfile=full_db_exp.dmp logfile=exp_log.log
1. On the target server (19c), create a directory for the Data Pump import files (e.g., /u01/import).
2. Run the Data Pump import command to import the entire database:
impdp system/password@new_db directory=IMP_DIR dumpfile=full_db_exp.dmp logfile=imp_log.log
1. Monitor the import process and check for any errors.
Faster Approach using Data Pump:
1. Use the PARALLEL option to speed up the export and import processes.
2. Use the COMPRESSION option to reduce the size of the export files.
3. Use the DATA_OPTIONS option to enable data transformation and optimization.
4. Use the TABLE_EXISTS_ACTION option to handle existing tables in the target database.
Example:
expdp system/password@source_db schemas=ALL directory=EXP_DIR dumpfile=full_db_exp.dmp logfile=exp_log.log parallel=4 compression=all data_options=transform_optimize
impdp system/password@new_db directory=IMP_DIR dumpfile=full_db_exp.dmp logfile=imp_log.log parallel=4 table_exists_action=replace
Post-Migration Tasks:
1. Update the database statistics.
2. Rebuild indexes and recompile stored procedures.
3. Test the migrated database thoroughly.
Note: Ensure you have sufficient disk space and memory on both servers to accommodate the Data Pump export and import processes. Additionally, consider using Oracle GoldenGate or other replication tools for a zero-downtime migration.
Target Server Preparation:
1. Install Oracle Database 19c software on the target server.
2. Create a new Oracle home directory for the 19c database (e.g., /u01/app/oracle/product/19.0.0/dbhome_1).
3. Set environment variables (e.g., ORACLE_HOME, ORACLE_SID, PATH).
4. Create a new database instance (e.g., SID=NEWDB).
5. Configure the listener and tnsnames.ora files.
Data Pump Migration:
1. On the source server (12c), create a directory for the Data Pump export files (e.g., /u01/export).
2. Run the Data Pump export command to export the entire database:
expdp system/password@source_db schemas=ALL directory=EXP_DIR dumpfile=full_db_exp.dmp logfile=exp_log.log
1. On the target server (19c), create a directory for the Data Pump import files (e.g., /u01/import).
2. Run the Data Pump import command to import the entire database:
impdp system/password@new_db directory=IMP_DIR dumpfile=full_db_exp.dmp logfile=imp_log.log
1. Monitor the import process and check for any errors.
Faster Approach using Data Pump:
1. Use the PARALLEL option to speed up the export and import processes.
2. Use the COMPRESSION option to reduce the size of the export files.
3. Use the DATA_OPTIONS option to enable data transformation and optimization.
4. Use the TABLE_EXISTS_ACTION option to handle existing tables in the target database.
Example:
expdp system/password@source_db schemas=ALL directory=EXP_DIR dumpfile=full_db_exp.dmp logfile=exp_log.log parallel=4 compression=all data_options=transform_optimize
impdp system/password@new_db directory=IMP_DIR dumpfile=full_db_exp.dmp logfile=imp_log.log parallel=4 table_exists_action=replace
Post-Migration Tasks:
1. Update the database statistics.
2. Rebuild indexes and recompile stored procedures.
3. Test the migrated database thoroughly.
Note: Ensure you have sufficient disk space and memory on both servers to accommodate the Data Pump export and import processes. Additionally, consider using Oracle GoldenGate or other replication tools for a zero-downtime migration.
