A lot of enhancements has been done in Oracle 12.2 Release.  In this article We will explain the new features of datapump utility in oracle 12.2 Release.

1.Parfile content  written to logfile:

Till now, log file of expdp or impdp, doesn’t store the content of parfile, The log file used to look as below.

Export: Release 12.1.0.2.0 - Production on Tue Feb 21 11:50:56 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA parfile=expdp_full.par
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

So if the parfile is missing, From the logfile, we can’t find the details about the expdp/impdp.

With Oracle 12.2, The content of the parfile is written to logfile.

--- PARFILE CONTENT 

$cat expdp_full.par
dumpfile=test_schema.dmp
logfile=test.log
directory=TEST2
schemas=BSSTDBA

-- RUN EXPDP 

expdp parfile=expdp_full.par

Export: Release 12.2.0.1.0 - Production on Tue Feb 21 10:58:39 2017

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=expdp_full.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER



--- LOGFILE CONTENT

oracle@bttstdev64:~/TEST$ cat test.log
;;;
Export: Release 12.2.0.1.0 - Production on Tue Feb 21 10:58:39 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:  schemas=BSSTDBA
;;;  parfile:  directory=TEST2
;;;  parfile:  logfile=test.log
;;;  parfile:  dumpfile=test_schema.dmp
;;; **************************************************************************
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=expdp_full.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

We can see that the parfile has been printed in the logfile. So no need to worry, if the parfile is missing 🙂

 

2. Parallel operation for metadata during expdp/impdp:

Till Oracle 12cR1, parallel operation doesnt work for expdp/impdp of metadatas. Even if we use more parallel option, it will take always 1 thread.

Lets try to both the 12cR1 and 12cR2 .

12CR1:

-- PARFILE CONTENT

dumpfile=test_%U.dmp
logfile=test.log
directory=TEST2
parallel=4
content=metadata_only
full=y

-- RUN EXPDP 
# expdp parfile=expdp_full.par

Export: Release 12.1.0.2.0 - Production on Tue Feb 21 11:50:56 2017

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA parfile=expdp_full.par
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
^C
Export> status

Job: SYS_EXPORT_FULL_01
  Operation: EXPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 4
  Job Error Count: 0
  Dump File: /export/home/oracle/test_01.dmp
    bytes written: 4,096
  Dump File: /export/home/oracle/test_%u.dmp

Worker 1 Status:
  Instance ID: 1
  Instance name: OMDEVBAU
  Host name: sec60-1
  Process Name: DW00
  State: EXECUTING
  Object Schema: WMSYS
  Object Name: WM$EXP_MAP
  Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
  Completed Objects: 10
  Worker Parallelism: 1

So in Oracle 12cR1, Despite giving parallel=4 , Only one worker process was allocated.

Lets try the same Oracle 12cR2(12.2)

12CR2:

dumpfile=test_%U.dmp
logfile=test.log
directory=TEST2
parallel=4
content=metadata_only
full=y

$ expdp parfile=expdp_full.par

Export: Release 12.2.0.1.0 - Production on Tue Feb 21 10:53:46 2017

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA parfile=expdp_full.par


Export> status

Job: SYS_EXPORT_FULL_01
  Operation: EXPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 4
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /export/home/oracle/TEST/test_%u.dmp
  Dump File: /export/home/oracle/TEST/test_01.dmp
    bytes written: 32,768
  Dump File: /export/home/oracle/TEST/test_02.dmp
    bytes written: 20,480
  Dump File: /export/home/oracle/TEST/test_03.dmp
    bytes written: 49,152
  Dump File: /export/home/oracle/TEST/test_04.dmp
    bytes written: 20,480

Worker 1 Status:
  Instance ID: 1
  Instance name: DB12C
  Host name: bttstdev64
  Object start time: Tuesday, 21 February, 2017 10:54:29
  Object status at: Tuesday, 21 February, 2017 10:54:56
  Process Name: DW00
  State: EXECUTING

Worker 2 Status:
  Instance ID: 1
  Instance name: DB12C
  Host name: bttstdev64
  Object start time: Tuesday, 21 February, 2017 10:55:04
  Object status at: Tuesday, 21 February, 2017 10:55:07
  Process Name: DW01
  State: EXECUTING

Worker 3 Status:
  Instance ID: 1
  Instance name: DB12C
  Host name: bttstdev64
  Object start time: Tuesday, 21 February, 2017 10:55:05
  Object status at: Tuesday, 21 February, 2017 10:55:07
  Process Name: DW02
  State: EXECUTING

Worker 4 Status:
  Instance ID: 1
  Instance name: DB12C
  Host name: bttstdev64
  Object start time: Tuesday, 21 February, 2017 10:54:23
  Object status at: Tuesday, 21 February, 2017 10:54:24
  Process Name: DW03
  State: EXECUTING

So here we used parallel=4, and 4 worker processes were created. Which is a very important enhancement in term metadata expdp performance.

3. New substitution variable for DUMPFILE parameter

Now new substitution variable like %D,%M,%T etc is available for DUMPFILE parameter, which we use to suffix or prefix the timestamp in the dumpfile.

expdp dumpfile=test_schema_%T.dmp logfile=test.log directory=TEST2 schemas=BSSTDBA

--  GENERATED DUMPFILE 
-rw-r-----   1 oracle   oinstall   12288 Feb 21 12:20 test_schema_20170221.dmp

Other available variables:

 

DUMPFILE  FORMAT DUMPFILE OUTPUT EXAMPLE
%y, %Y Specifies the year in this format: YYYY. dumpfile=test_schema_%Y.dmp -> test_schema_2017.dmp
%d, %D Specifies current date in DD dumpfile=test_schema_%D.dmp -> test_schema_21.dmp
%m, %M Specifies current month in MM dumpfile=test_schema_%M.dmp -> test_schema_02.dmp
%t, %T Specifies the current date with YYYYMMDD dumpfile=test_schema_%T.dmp -> test_schema_20170221.dmp
%l, %L Specifies a system-generated unique file name dumpfile=test_schema_%L.dmp -> test_schema_01.dmp

 

Apart from above, there are few more features in datapump, Which we will discuss soon.

 

4. Import LONG column using NETWORK_LINK

Till Now, we are not able to import table with LONG column using NETWORK_LINK.  It throws below error during impdp.

ORA-00997: illegal use of LONG datatype error

But with 12cR2, we can import LONG column through NETWORK_LINK.