DBA Hub

📋Steps in this guide1/4

Datapump New features in Oracle 12.2 DBACLASS

Datapump New features in Oracle 12.2 Release. A lot of new features like parfile contents in log file, parallelism in metadata and few more and substitution variable in expdp.

oracle clusteringintermediate
by OracleDba
13 views
1

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. 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 . We can see that the parfile has been printed in the logfile . So no need to worry, if the parfile is missing 🙂

Code/Command (click line numbers to comment):

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

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

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: So in Oracle 12cR1, Despite giving parallel=4 , Only one worker process was allocated. Lets try the same Oracle 12cR2(12.2) 12CR2: So here we used parallel=4, and 4 worker processes were created. Which is a very important enhancement in term metadata expdp performance.

Code/Command (click line numbers to comment):

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
-- 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

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
3

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 . Other available variables: Apart from above, there are few more features in datapump, Which we will discuss soon.

Code/Command (click line numbers to comment):

1
2
3
4
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
4

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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!