Migrating to Autonomous Database Best Practice 2 Data Pump

YouTube video

In this article, we will discuss the best practices for migrating data to an Autonomous Database using Data Pump. Migrating data is a crucial step in the process of transitioning to an Autonomous Database, and it is important to follow these guidelines to ensure a smooth and successful migration.

Rule 1: Use the correct user for exporting data

When exporting data, it is important to use the right user. The user ‘dbas’ is not suitable for exporting data. Instead, use the ‘system’ user or an equivalent user with the necessary privileges, such as the ‘x full database role’ or ‘data pump roles’. These users are specifically designed for exporting data and will ensure a successful migration.

Rule 2: Always have a dump file and use a parameter file

Having a dump file is essential for a successful data migration. It is important to avoid writing long command lines with backslashes to break them into several lines. Instead, use a parameter file, commonly known as a par file, which makes the migration process easier and safer. If something goes wrong, having a parameter file will simplify troubleshooting and save time.

Rule 3: Split data into multiple files during export

To optimize the migration process, it is recommended to split the export data into multiple files, especially if you intend to upload the data to the object storage. This practice allows for parallel uploading, which significantly improves the performance for large data sets. Use the ‘%u’ parameter in the dump file name to split the data based on the parallel degree.

Rule 4: Exclude statistics during export

Excluding statistics during the export process not only speeds up the import process but also allows the Autonomous instance to collect and manage the statistics on your behalf. This ensures that the statistics are accurate and up to date, providing better performance and optimization opportunities.

Rule 5: Include telemetry during export

Enabling telemetry during the export process is highly recommended as it provides valuable insight into the migration process. Telemetry captures precise timestamps and information about the imported objects, the methods used, and the workers involved. This information is crucial for troubleshooting and optimizing the migration.

Rule 6: Make consistent exports

Consistency is key when exporting data. Using the “flashback timestamp” option ensures that the exported data is consistent and reliable. The newer timestamp option is preferable as it provides better results. Consistent exports are essential for a successful migration and guarantee the integrity of the data.

Rule 7: Utilize parallel processing during export

If you have an enterprise edition, it is highly recommended to use parallel processing during the export process. This significantly improves the export performance by utilizing the maximum capacity of your system. By default, we suggest using twice the number of CPU cores for on-premises exports and the number of OCPU’s for exports from OCI.

Rule 8: Remove column encryption before migration

If your data includes column encryption, it is important to remove the encryption before migrating to an Autonomous Database. Unlike other databases, Autonomous Database does not support column encryption. Removing encryption ensures that your data is compatible with the Autonomous Database and avoids any issues during the migration process.

Rule 9: Consider compression for large uploads

If you have an advanced compression license, using compression during the migration can significantly reduce the file size and optimize the upload process, especially for large data sets. The compression algorithm “medium” provides a good balance between CPU load and file size. If time is not a constraint, “high” compression can be used for the smallest possible file size.

Rule 10: Use additional parameters for customization

Data Pump provides additional parameters that can be used to customize the migration process. For example, the “tables” parameter allows you to export a subset of a table by creating a view on it. This subset will be treated as a table during the export and can be renamed during the import process. Utilizing these additional parameters can enhance the migration process and make it more tailored to your specific requirements.

Rule 11: Follow documentation for restrictions and best practices

It is essential to refer to the official documentation for the specific restrictions and best practices when migrating to an Autonomous Database. The documentation provides up-to-date information about the features and functionalities supported by the database. Following the guidelines in the documentation ensures a successful migration with minimal issues or complications.

In conclusion, migrating data to an Autonomous Database requires careful planning and adherence to best practices. Following the guidelines discussed in this article will help you achieve a successful migration and ensure the integrity and performance of your data. Remember to consult the official documentation for the most accurate and up-to-date information before proceeding with the migration process.