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.
