Friday, 16 March 2018

Data Pump (expdp, impdp) Enhancements in Oracle Database 12c Release 2 (12.2)

This article provides an overview of the main Data Pump enhancements in Oracle Database 12c Release 2 (12.2).

Some of these features are not listed in the expdp -help or impdp -help usage text.

1. Parallel Export/Import of Metadata


In previous releases the PARALLEL parameter was only relevant to the export/import of data, with all metadata processed serially. In Oracle 12.2 both metadata and data can be exported in parallel provided it is not a transportable tablespace operation.

The order of operations is important during an import, so the possible levels of parallelism may vary throughout the import job. If possible, the import will use parallelism up to the value of the PARALLEL parameter for both metadata and data import.

2. Wildcards in TRANSPORT_DATAFILES


When using the TRANSPORT_DATAFILES parameter, the datafile definition can now use wildcards in the file name.

Oracle Database Tutorials and Materials, Oracle Database Certifications, Oracle Database Learning

◈ * : Zero to many characters.
◈ ? : Exactly one character.

The wildcards are not allowed in directory names, just file names, and the wildcards can't match files that are not present in the transport set, or an error will be produced.

# This
transport_datafiles=/my/path/file10.dbf,/my/path/file11.dbf,/my/path/file12.dbf

# becomes one of these alternatives.
transport_datafiles=/my/path/file*.dbf
transport_datafiles=/my/path/file1*.dbf
transport_datafiles=/my/path/file1?.dbf

3. New Substitution Variables for File Names


Multiple files are generated by parallel exports, so each file needs to have a unique name. This is achieved using substitution variables. In previous releases the only substitution variable available was "%U", which generated a two digit number from 01-99. Oracle 12.2 includes additional substitution variables.

The following substitution variables are only available for export operations.

◈ %d, %D : The current day of the month in DD format.
◈ %m, %M : The current month in MM format.
◈ %y, %Y : The current year in YYYY format.
◈ %t, %T : The current date in YYYYMMDD format.

The following substitution variables are available for both export and import operations.

◈ %U : Unchanged from previous releases. A two digit number incremented between 01-99.
◈ %l, %L : This starts off similar to "%U", producing a two digit number between 01-99, but it can extend up to 2147483646, so the resulting file name is not a fixed length.

The following example shows the usage of two of the new substitution variables in a parallel export. The output is edited to reduce its size.

$ expdp system/OraPasswd1@pdb1 schemas=OE directory=TEST_DIR dumpfile=OE_%T_%L.dmp logfile=expdpOE.log parallel=4

Export: Release 12.2.0.1.0 - Production on Wed Mar 22 16:04:32 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@pdb1 schemas=OE directory=TEST_DIR dumpfile=OE_%T_%L.dmp logfile=expdpOE.log parallel=4
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
.
.
.
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /tmp/OE_20170322_01.dmp
  /tmp/OE_20170322_02.dmp
  /tmp/OE_20170322_03.dmp
  /tmp/OE_20170322_04.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Wed Mar 22 16:05:41 2017 elapsed 0 00:01:07

$

4. Parameter File Contents Written to Log File


The contents of the parameter file specified by the PARFILE parameter is written to the logfile, but not echoed to the screen.

Create the following parameter file and run an export using it.

cat > /tmp/parfile.txt <<EOF
USERID=test/test@pdb1
schemas=TEST
directory=TEST_DIR
dumpfile=TEST.dmp
logfile=expdpTEST.log
EOF

expdp parfile=/tmp/parfile.txt

If we check the top of the resulting log file we can see the parameter file contents.

$ head -15 /tmp/expdpTEST.log
;;;
Export: Release 12.2.0.1.0 - Production on Mon Aug 21 19:45:00 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
;;; *********************************************************
;;; Parfile values:
;;;  parfile:  logfile=expdpTEST.log
;;;  parfile:  dumpfile=TEST.dmp
;;;  parfile:  directory=TEST_DIR
;;;  parfile:  schemas=TEST
;;;  parfile:  userid=test/********@pdb1
;;; **********************************************************
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/********@pdb1 parfile=/tmp/parfile.txt
$

5. REMAP_DIRECTORY


The REMAP_DIRECTORY parameter is a variant of the REMAP_DATAFILE parameter, which allows you to remap the paths of multiple datafile references in a single shot. The REMAP_DIRECTORY and REMAP_DATAFILE parameters are mutually exclusive.

The basic syntax is a search and replace, with the documentation recommending the inclusion terminators and double-quotes. Since this would have to be escaped at the command line, it's easier to view the setting as it would be in a parameter file.

REMAP_DIRECTORY="'/u02/oradata/cdb1/pdb1/':'/u02/oradata/cdb3/pdb3/'"

You can see it in the context of a full parameter file below.

cat > /tmp/parfile.txt <<EOF
USERID=system/OraPasswd1@pdb1
schemas=TEST
directory=TEST_DIR
dumpfile=TEST.dmp
logfile=impdpTEST.log
remap_directory="'/u02/oradata/cdb1/pdb1/':'/u02/oradata/cdb3/pdb3/'"
EOF

impdp parfile=/tmp/parfile.txt

This parameter is not present in the impdp -help usage information.

6. DATA_OPTIONS Changes


There have been a number of changes to the DATA_OPTIONS parameter for export and import in Oracle Database 12.2. The new settings are displayed below in bold.

# Export
DATA_OPTIONS = [XML_CLOBS | GROUP_PARTITION_TABLE_DATA | VERIFY_STREAM_FORMAT]

# Import
DATA_OPTIONS = [DISABLE_APPEND_HINT | SKIP_CONSTRAINT_ERRORS |
               ENABLE_NETWORK_COMPRESSION | REJECT_ROWS_WITH_REPL_CHAR |
               TRUST_EXISTING_TABLE_PARTITIONS | VALIDATE_TABLE_DATA]

These settings are described in the manuals (export, import), but they are briefly described below.

◈ GROUP_PARTITION_TABLE_DATA: Unloads all partitions as a single operation producing a single partition of data in the dump file. Subsequent imports will not know this was originally made up of multiple partitions.

◈ VERIFY_STREAM_FORMAT: Validates the format of the data stream before it is written to the dump file, so you are less likely to have problems with the contents of the dumpfile.

◈ ENABLE_NETWORK_COMPRESSION: Compresses data during network imports using the ACCESS_METHOD of DIRECT_PATH. There are some caveats described here.

◈ TRUST_EXISTING_TABLE_PARTITIONS: Loads partition data in parallel into existing partitions on the destination database. The partitions must already be present with the correct attributes.

◈ VALIDATE_TABLE_DATA: Validates numbers and dates during imports. Writes ORA-39376 errors inclduing the column information to the log file if it finds invalid data.

You can see the new export DATA_OPTIONS settings in the context of a full parameter file below.

cat > /tmp/parfile.txt <<EOF
USERID=system/OraPasswd1@pdb1
schemas=TEST
directory=TEST_DIR
dumpfile=TEST.dmp
logfile=expdpTEST.log
data_options=group_partition_table_data,verify_stream_format
EOF

expdp parfile=/tmp/parfile.txt

You can see the new import DATA_OPTIONS settings in the context of a full parameter file below. The compression setting is ignored as this is not a network import.

cat > /tmp/parfile.txt <<EOF
USERID=system/OraPasswd1@pdb1
schemas=TEST
directory=TEST_DIR
dumpfile=TEST.dmp
logfile=impdpTEST.log
data_options=enable_network_compression,trust_existing_table_partitions,validate_table_data
EOF

impdp parfile=/tmp/parfile.txt