What should I consider when migrating to Autonomous Database using Data Pump?

Oracle Data Pump enables you to move data and metadata from one database to another, providing an extensive range of benefits. The followings are tips and best practices to consider:

1. Run the Cloud Premigration Advisor Tool (CPAT)

First of all, check the database schemas you want to export from the source database using the Cloud Premigration Advisor Tool (CPAT) as described in Doc ID 2758371.1.

# download and unzip p32613591_112048_Generic.zip
./premigration.sh --connectstring jdbc:oracle:oci:@ --sysdba --targetcloud ATPS --pdbname PDB1 --schemas SAMPLEUSER

2. Character Set

If your source database uses a single-byte character set (Autonomous Database Advisor will recognize this), then check Why do I get “ORA-12899 value too large for column”​ when migrating to Autonomous Database?

3. SYSDATE and SYSTIMESTAMP

Oracle Autonomous Database servers are configured to the UTC time zone. As there is no access to the underlying operating system, this configuration cannot be changed. If your application use SYSDATE or SYSTIMESTAMP SQL functions and expect the time in the local time zone, you’ll need to use CURRENT_DATE and CURRENT_TIMESTAMP instead (unless your clients are in the UTC time zone).

If you can’t change your application’s code, then stay tuned for a workaround.

4. Removed Features

Check the list of removed features in Autonomous Database on Shared Infrastructure and whether you are using any of them, e.g. Java in DB.

5. Use Schema Based Export

To reduce the amount of data being exported and transferred, export only the schemas you want to migrate instead of the full database, including SYS objects and other stuff you don’t need.

schemas=schema1,schema2,...

6. Ensure Consistent Exports

Ensure that your export operation is performed with consistent data using a specific SCN or timestamp. This will increase the UNDO requirements for the duration of the export.

flashback_scn=...  OR  flashback_time=...

7. Exclude unnecessary Database Objects

Oracle recommends excluding specific object types while exporting the data. It is also recommended to exclude schema statistics during export and recreate fresh ones after import.

exclude=db_link,cluster
-- for Autonomous Data Warehouse, additionally
exclude=index,indextype,materialized_view,materialized_view_log,materialized_zonemap

8. Increase Export Performance

For faster exports, consider using large UNDO and TEMP tablespaces, increasing the size of pga_aggregate_target, and making sure to have current dictionary statistics.

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');

For optimal Data Pump performance, it is recommended that you set STREAMS_POOL_SIZE to the value returned by the result set of the following query:

select 'ALTER SYSTEM SET STREAMS_POOL_SIZE='||(max(to_number(trim(c.ksppstvl)))+67108864)||' SCOPE=SPFILE;'
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and lower(a.ksppinm) in ('__streams_pool_size', 'streams_pool_size');

9. Use Parallelism, Compression, and Encryption

If you are a lucky user of an Enterprise Edition, use parallelism to accelerate the export and import processes, especially for larger databases. Export your schemas into multiple Data Pump files using the %U substitution variable. In case you are even luckier and have the Advanced Compression and Advanced Security options, consider compressing and encrypting your export files.

dumpfile=export_%U.dmp
parallel=<number_of_OCPUs>
compression=ALL
compression_algorithm=MEDIUM
encryption=ALL
encryption_pwd_prompt=yes

Higher compression means using more CPU resources but getting a higher compression ratio, i.e., smaller export files to push through the network. It’s recommended to use MEDIUM. Use HIGH only of you have a special purpose, as it is very CPU intensive.

For the best import performance use the HIGH autonomous database service for your import
connection and set the PARALLEL parameter to at least the number of OCPUs you have in your Autonomous database.

10. Generate detailed Loggings and Metrics

Set LOGTIME to ALL to specify that messages displayed during export and import operations are assigned with a timestamp. Setting METRICS to YES adds additional information about the job reported to the Data Pump log file.

logtime=all
metrics=yes

11. Estimate the Space Needed for your Export

Especially for large databases, make sure you have sufficient storage for your export files. Estimate the space needed first based on database blocks or statistics, without actually performing the export operation.

estimate=[BLOCKS | STATISTICS]
estimate_only=yes

12. Use Parameter File

Use a parameter file for export and import to avoid issues resulting from typing a long command line on the shell. Put all parameters in a text file, one per line, and execute expdp and impdp with the parfile parameter.

expdp parfile=my_export_parfile.par
impdp parfile=my_import_parfile.par

13. Do NOT Start Export as SYSDBA

For this, I’ll just put a screenshot from the doc site:

14. Use Multipart Upload to Object Storage

Multipart uploads allow uploading a large object (the export dump file) as individual parts in parallel to reduce the amount of time you spend uploading to Object Storage. Oracle recommends performing a multipart upload for objects larger than 100MB. The maximum size for an uploaded object is 10TB. You can control the maximum file size in data pump using the FILESIZE parameter.

FILESIZE=integer[B | KB | MB | GB | TB]

15. Check the Integrity of Uploaded Files to Object Storage

To ensure that no bit was lost in transfer, use checksum on your source system and Object Storage. On source:

md5sum export.dmp

You’ll get something like “136bb5a59561ae09a693aa3a8facac3d”.

In the Autonomous database run (adjust the values for your region and tenancy):

SQL> SELECT OBJECT_NAME, BYTES, CHECKSUM
FROM DBMS_CLOUD.LIST_OBJECTS('OBJ_STORE_CRED', 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/yournamespace/b/yourbucketname/');

the CHECKSUM column should display the same value as on-premises “136bb5a59561ae09a693aa3a8facac3d”.

16. Use Automation Tools

Automate your migration process to reduce human error:

Summary

Finally, we end up with this data pump configuration for export:

parallel=<number_of_OCPUs>
compression=ALL
compression_algorithm=MEDIUM
encryption=ALL
encryption_pwd_prompt=yes

flashback_scn=...  OR  flashback_time=...
filesize=integer[B | KB | MB | GB | TB]
exclude=db_link,cluster
schemas=yourschemaname
dumpfile=export_%U.dmp
logfile=export.log
directory=data_pump_dir
logtime=all
metrics=yes

-- for Autonomous Data Warehouse, additionally 
data_options=group_partition_table_data
exclude=index,indextype,materialized_view,materialized_view_log,materialized_zonemap

-- for estimation only
estimate=[BLOCKS | STATISTICS]
estimate_only=yes

For import:

parallel=<number_of_OCPUs>
encryption_pwd_prompt=yes
exclude=db_link,cluster
schemas=yourschemaname
dumpfile=https://objectstorage...export_%U.dmp
logfile=import.log
directory=data_pump_dir
credential=def_cred_name
remap_tablespace=%:DATA
transform=segment_attributes:n
transform=dwcs_cvt_iots:y
transform=constraint_use_default_index:y
logtime=all
metrics=yes

-- for Autonomous Data Warehouse, additionally
partition_options=merge
exclude=index,indextype,materialized_view,materialized_view_log,materialized_zonemap

Would you like to get notified when the next post is published?