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 Autonomous Database Schema Advisor

First of all, check the database schemas you want to export from the source database using the Autonomous Database Schema Advisor. It analyzes the schemas and reports any concerns that may arise during the migration. The advisor is a schema that gets created in your source database by executing the install_adb_advisor.sql SQL script.

Download the script from MOS Doc ID 2462677.1, create the user – in the following example named ADBADVISOR -, login as that user, and run the REPORT procedure. That’s everything!

SQL> @install_adb_advisor.sql ADBADVISOR MyPassword11__
sqlplus ADBADVISOR/MyPassword11__@TNSALIAS
SQL> SET SERVEROUTPUT ON FORMAT WRAPPED
SQL> SET LINESIZE WINDOW
SQL> execute ADBADVISOR.REPORT(SCHEMAS=>'SCHEMANAME', ADB_TYPE=>'ATP');

2. 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,...

3. 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=...

4. 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=cluster,indextype,db_link
exclude=statistics

5. 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');

6. 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. In case you are even luckier and have the Advanced Compression and Advanced Security options, consider compressing and encrypting your export files.

parallel=n
compression=ALL
compression_algorithm={BASIC | LOW | MEDIUM | HIGH}
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.

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

7. 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

8. 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

9. 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

10. Do NOT Start Export as SYSDBA

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

11. 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]

12. 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”.

13. Use Automation Tools

Automate your migration process to reduce human error:

Summary

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

parallel=n
compression=ALL
compression_algorithm={BASIC | LOW | MEDIUM | HIGH}
encryption=ALL
encryption_pwd_prompt=yes

flashback_scn=...  OR  flashback_time=...
filesize=integer[B | KB | MB | GB | TB]
exclude=cluster,indextype,db_link
exclude=statistics
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

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

For import:

parallel=n
encryption_pwd_prompt=yes
exclude=cluster,indextype,db_link
exclude=statistics
schemas=yourschemaname
dumpfile=https://objectstorage...
logfile=import.log
directory=data_pump_dir
credential=def_cred_name
remap_tablespace=yourTBSname: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

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