📋Steps in this guide1/7

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

oracle 12cconfigurationintermediate

by OracleDba

13 views

Parallel Export/Import of Metadata

In previous releases the 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 parameter for both metadata and data import.

Wildcards in TRANSPORT_DATAFILES

When using the parameter, the datafile definition can now use wildcards in the file name. - * : 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.

Code/Command (click line numbers to comment):

# 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

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.

Code/Command (click line numbers to comment):

$ 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

$

Parameter File Contents Written to Log File

The contents of the parameter file specified by the parameter is written to the logfile, but not echoed to the screen. Create the following parameter file and run an export using it. If we check the top of the resulting log file we can see the parameter file contents.

Code/Command (click line numbers to comment):

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

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

REMAP_DIRECTORY

The parameter is a variant of the parameter, which allows you to remap the paths of multiple datafile references in a single shot. The and 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. You can see it in the context of a full parameter file below. This parameter is not present in the usage information.

Code/Command (click line numbers to comment):

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

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

DATA_OPTIONS Changes

There have been a number of changes to the parameter for export and import in Oracle Database 12.2. The new settings are displayed below in bold. These settings are described in the manuals ( export , import ), but they are briefly described below. - : 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. - : 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. - : Compresses data during network imports using the of . There are some caveats described here . - : Loads partition data in parallel into existing partitions on the destination database. The partitions must already be present with the correct attributes. - : 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 settings in the context of a full parameter file below. You can see the new import settings in the context of a full parameter file below. The compression setting is ignored as this is not a network import.

Code/Command (click line numbers to comment):

# 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
]

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

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

Miscellaneous

There are quite a few new features that don't need a demonstration or syntax example, including the following. - Data Pump now supports long identifiers for both its own object names and those in the metadata it exports and imports. - The data-bound collation feature was introduced on Oracle Database 12.2. Data pump includes support for data-bound collation. Collation metadata is always exported, including column, table and schema level settings. The metadata is only included in an DDL generated by an import if the parameter is set (implicitly or explicity) to 12.2 and the destination database supports collation. - Network imports using the NETWORK_LINK parameter now support LONG columns. There are still a number of other restrictions associated with network imports though. - Network imports using the NETWORK_LINK parameter can now use the ACCESS_METHOD parameter. - Both export and import support the the new big SCN size with FLASHBACK_SCN provided they are being used against a database that supports it. - Transportable tablespace exports support encrypted columns. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!