EXPDP Hangs at Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

We got a request to do a schema refresh,

We prepared the parfile as below

$cat DEV_SCHEMA.par 
directory=MY_DIR 
dumpfile=DEV_SCHEMA_%u.dmp
logfile=DEV_SCHEMA.log
SCHEMAS=DEV_SCHEMA1,DECSCHEMA01
estimate=statistics 
parallel=8 
filesize=2G

and ran in background as

$ nohup expdp system/***** parfile=DEV_SCHEMA.par  &

$tail -f nohup.out

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_05": system/******** parfile=DEV_EXPDP.par
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

The job started hanging at MARKER

So started checking the status and killed the job

$ expdp system/***** attach=SYS_EXPORT_SCHEMA_05

Export: Release 12.2.0.1.0 - Production on Sun Dec 9 12:53:21 2018

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_SCHEMA_05
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: 7C944D8FD62639B2E0SDJU81F8D
Start Time: Sunday, 09 December, 2018 9:16:28
Mode: SCHEMA
Instance: *****
Max Parallelism: 8
Timezone: +00:00
Timezone version: 26
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** parfile=DEV_SCHEMA.par
ESTIMATE STATISTICS
TRACE 0
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 8
Job Error Count: 0
Job heartbeat: 193

Dump File: /oraSDBTINSTmnt/backup/DEV_SCHEMA_%u.dmp
size: 2,147,483,648
Dump File: /oraSDBTINSTmnt/backup/DEV_SCHEMA_01.dmp
size: 2,147,483,648
bytes written: 36,864
Dump File: /oraSDBTINSTmnt/backup/DEV_SCHEMA_02.dmp
size: 2,147,483,648
bytes written: 602,112
Dump File: /oraSDBTINSTmnt/backup/DEV_SCHEMA_03.dmp
size: 2,147,483,648
bytes written: 1,929,154,560
Dump File: /oraSDBTINSTmnt/backup/DEV_SCHEMA_04.dmp
size: 2,147,483,648
bytes written: 74,592,256
Dump File: /oraSDBTINSTmnt/backup/DEV_SCHEMA_05.dmp
size: 2,147,483,648
bytes written: 253,952
Dump File: /oraSDBTINSTmnt/backup/DEV_SCHEMA_06.dmp
size: 2,147,483,648
bytes written: 1,269,760
Dump File: /oraSDBTINSTmnt/backup/DEV_SCHEMA_07.dmp
size: 2,147,483,648
bytes written: 696,320

Worker 1 Status:
Instance ID: 1
Instance name: SDBTINST
Host name: myhostname
Object start time: Sunday, 09 December, 2018 9:17:38
Object status at: Sunday, 09 December, 2018 9:17:46
Process Name: DW00
State: WORK WAITING

Worker 2 Status:
Instance ID: 1
Instance name: SDBTINST
Host name: myhostname
Access method: direct_path
Object start time: Sunday, 09 December, 2018 9:17:12
Object status at: Sunday, 09 December, 2018 9:39:04
Process Name: DW01
State: WORK WAITING

Worker 3 Status:
Instance ID: 1
Instance name: SDBTINST
Host name: myhostname
Object start time: Sunday, 09 December, 2018 9:16:58
Object status at: Sunday, 09 December, 2018 12:51:8
Process Name: DW02
State: EXECUTING
Object Schema: DSRC1E2DSMUSR
Object Name: TESTATBLE
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Partition: P20150110
Completed Objects: 80,960
Worker Parallelism: 1

Worker 4 Status:
Instance ID: 1
Instance name: SDBTINST
Host name: myhostname
Object start time: Sunday, 09 December, 2018 9:17:18
Object status at: Sunday, 09 December, 2018 9:28:34
Process Name: DW03
State: WORK WAITING

Worker 5 Status:
Instance ID: 1
Instance name: SDBTINST
Host name: myhostname
Object start time: Sunday, 09 December, 2018 9:17:38
Object status at: Sunday, 09 December, 2018 9:17:47
Process Name: DW04
State: WORK WAITING

Worker 6 Status:
Instance ID: 1
Instance name: SDBTINST
Host name: myhostname
Object start time: Sunday, 09 December, 2018 9:17:27
Object status at: Sunday, 09 December, 2018 9:17:49
Process Name: DW05
State: WORK WAITING

Worker 7 Status:
Instance ID: 1
Instance name: SDBTINST
Host name: myhostname
Object start time: Sunday, 09 December, 2018 9:17:30
Object status at: Sunday, 09 December, 2018 9:17:46
Process Name: DW06
State: WORK WAITING

Worker 8 Status:
Instance ID: 1
Instance name: SDBTINST
Host name: myhostname
Object start time: Sunday, 09 December, 2018 9:17:31
Object status at: Sunday, 09 December, 2018 9:24:50
Process Name: DW07
State: WORK WAITING

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

It Seems to be bug from oracle.

Bug 21644640  EXPDP appears to hang at "Processing object type SCHEMA_EXPORT/STATISTICS/MARKER"

To Solve :

Workaround
 Specify EXCLUDE=STATISTICS as expdp parameter

Then we added the parameter EXCLUDE=STATISTICS as

$cat DEV_SCHEMA.par 
directory=MY_DIR 
dumpfile=DEV_SCHEMA_%u.dmp
logfile=DEV_SCHEMA.log
SCHEMAS=DEV_SCHEMA1,DECSCHEMA01
exclude=statistics 
parallel=8 
filesize=2G

Then job ran fine.

 

Leave a Reply

Your email address will not be published. Required fields are marked *