I recently helped a customer get the most out of Data Pump. Here’s how it all started.

For legal reasons, we must keep a backup of our data for five years. We want to use Data Pump exports. Do you have any recommendations?

Before someone flames me, I know that an export is not a backup, but we’re not talking about disaster recovery here.

Data Pump Export

Here are some of the Data Pump export parameters I would use.

Log File

logtime=all
metrics=yes
logfile=dataexport.log

Whenever you store data for the long term, I think it’s a good idea to store relevant metadata too. So, be sure to save the log file together with the dump files, and include diagnostic information using logtime and metrics.

Dump Files

dumpfile=dataexport_%T_%L.dmp
filesize=10000M
reuse_dumpfiles=yes

The dumpfile specification contains the %T wildcard, which translates into YYYYMMDD at runtime. It’s handy to have the date in the file names. Using %L allows the creation of multiple dump files.

I recommend breaking the dump files into smaller pieces using filesize. Smaller files are easier to handle and transport, and avoid some issues I’ve seen with object storage and huge files.

Content

schemas=myapp
tables=mytab
exclude=statistics

I would advise against doing a full export. It contains far too much information that you won’t need, like tablespace definitions, SQL management objects, and audit trail. A schema or table export is more suitable.

Be sure to exclude statistics. They take time to export and have no value. To narrow down the objects you export, you can also use the include parameter.

The parameters include and exclude are mutually exclusive in Oracle Database 19c. That’s no longer the case in later releases.

Compression

compression=all
compression_algorithm=high

Use compression to make the dump files smaller. I think it’s worth spending a few more CPU cycles on export and compressing as much as possible to reduce the dump file size, so use the high algorithm.

Creating a compressed export requires the Advanced Compression Option. However, importing a compressed backup does not require the same option. Always check the license guide for up-to-date information.

If you don’t have a license for Advanced Compression Option, you can compress the dump file using OS utilities. Unless you also encrypt the dump file. Compressing an encrypted dump file gives no benefit.

Integrity

data_options=verify_stream_format
checksum=yes

Since you are storing data for long-term storage, it’s good to double-check that there’s no corruption in the data. Data Pump writes into the dump file in a streaming manner, and you can check it along the way using data_options.

I’d also recommend generating a checksum and storing that in the dump file using the checksum parameter.

You can periodically check your dump for corruption by using impdp ... verify_only=yes. It will re-calculate the dump file checksum and match that against what is stored in the dump file.

Note that checksum is a feature of Oracle Database 21c.

Usually, using the default checksum_algorithm is fine for checking data integrity. If you want to guard against someone tampering with the dump file, it’s better to use checksum_algorithm=sha512. But for proper protection of your data, use encryption.

Encryption

encryption=all
encryption_mode=password

You can encrypt the data in the dump using the encryption parameter.

I prefer using a separate passphrase to protect the dump file, instead of using the database master encryption key. If you use the latter, you also need to have a backup of the database keystore.

Using Data Pump encryption requires a license for Advanced Security Option. Always check the license guide for up-to-date information.

Miscellaneous

flashback_scn=systimestamp
parallel=<n>

Remember to make a consistent export using flashback_scn. Alternatively, export from a quiesced database or a snapshot standby.

Use whatever parallel degree that you can afford. Normally, on x86-64 architectures, the optimal setting is twice the number of physical cores.

Using OCI Object Storage

The customer also stated:

We plan on storing the dumps in OCI object storage.

To reduce the storage costs, use either:

• Archive storage
• Standard storage and assign to the infrequent access tier.

Your Opinion

How would you store data for the long term? Do you have any experiences with the above Data Pump parameters?

Drop a comment and let me know.

Happy exporting!