by 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
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.
|
1 2 3 |
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
|
1 2 3 |
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.
|
1 |
REMAP_DIRECTORY=source_directory_string:target_directory_string |
Reblogged this on ..:::: EasyOraDBA | Shadab Mohammad ::::...