Oracle 12.2 version has a lot of new features and this blog will highlight datapump enhancements in 12.2 release.

1. Parfile contents are written to logfile:

In previous version, expdp or impdp parfile contents never displayed in logfile. So if you lose the parfile and  you can’t see what parameters has been used in expdp/impdp from logfile.

cat test.log
;;;
Export: Release 12.1.0.2.0 - Production on Thu Jun 8 19:47:57 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_SCHEMA_01": "sys/********@PDB AS SYSDBA" parfile=expdp_test
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

With oracle 12.2, the logfile display the parfile contents.

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=test
;;; parfile: dumpfile=test_schema.dp
;;; parfile: logfile=test.log
;;; parfile: directory=refresh
;;; **************************************************************************
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "sys/********@PDB AS SYSDBA" parfile=expdp_test
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

2. Parallel Metadata export

In previous oracle versions, parallel operation does not work for datapump export/import. Only one worker process is created even we specify more parallel operation.

Export: Release 12.1.0.2.0 - Production on Thu Jun 8 19:58:30 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": "sys/********@PDB AS SYSDBA" parfile=expdp_test
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
Export> status
Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 3
Job Error Count: 0
Dump File: /backup_store/refresh/test_01.dp
bytes written: 4,096
Dump File: /backup_store/refresh/test_%u.dp
Worker 1 Status:
Instance ID: 1
Instance name: PDB
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

In 12.2 version, more worker processes generated depends upon parallel operation specified. Metadata objects passed immediately to workers as they are found. For example, Worker 1 finds a set of TABLE definitions, they are handed off to worker 2. Estimate phase still happens but meta data no longer help by metadata.

Export: Release 12.2.0.1.0 - Production on Thu Jun 8 20:04:36 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
Job: SYS_EXPORT_FULL_01
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 516F3DB5665E24F1E0530708030AEAAE
Start Time: Thursday, 08 June, 2017 20:00:07
Mode: FULL
Instance: PDB
Max Parallelism: 3
Timezone: +12:00
Timezone version: 26
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND "sys/********@PDB AS SYSDBA" parfile=expdp_test
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 3
Job Error Count: 0
Job heartbeat: 1
Dump File: /backup_store/refresh/test_%u.dp
Dump File: /backup_store/refresh/test_01.dp
bytes written: 8,282,112
Dump File: /backup_store/refresh/test_02.dp
bytes written: 4,911,104

Worker 1 Status:
 Instance ID: 1
 Instance name: PDB
 Host name: RT01
 Object start time: Thursday, 08 June, 2017 20:01:18
 Object status at: Thursday, 08 June, 2017 20:02:39
 Process Name: DW00
 State: EXECUTING

Worker 2 Status:
 Instance ID: 1
 Instance name: PDB
 Host name: RT01
 Object start time: Thursday, 08 June, 2017 20:03:43
 Object status at: Thursday, 08 June, 2017 20:03:51
 Process Name: DW01
 State: EXECUTING

Worker 3 Status:
 Instance ID: 1
 Instance name: PDB
 Host name: RT01
 Object start time: Thursday, 08 June, 2017 20:01:14
 Object status at: Thursday, 08 June, 2017 20:02:53
 Process Name: DW02
 State: EXECUTING
 Object Schema: Test
 Object Name: PSMSGS
 Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
 Completed Objects: 35,394
 Total Objects: 35,394
 Worker Parallelism: 1

3. New substitution variables for dumpfile name

Pre 12.2, %U generates a fixed width 2- digit number as below.
Dump File: /backup_store/refresh/test_%u.dp
Dump File: /backup_store/refresh/test_01.dp
Dump File: /backup_store/refresh/test_02.dp
New options for 12.2 export/import as below.
• %l or %L –  Incrementing number from 01 up to 2147483646
Dump File: /backup_store/refresh/test%t_%l.dp
Dump File: /backup_store/refresh/test20170608_01.dp
Dump File: /backup_store/refresh/test20170608_02.dp
New options in 12.2 expdp only:
  •  %d or %D: Day of Month in DD format
  • %m or %M: Number of Month in MM format
  • %y or %Y: Year in YYYY format
  • %t or %T: Full date in YYYYMMDD format

4. REMAP_DIRECTORY parameter

This parameter lets you remap directories when you move databases between platforms.
with this you can change directory specifications without changing filenames.
REMAP_DIRECTORY=source_directory_string:target_directory_string

1 Comment

Leave a Reply