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 Sep 31 12:53:21 2020 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, 31 September, 2020 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, 31 September, 2020 9:17:38 Object status at: Sunday, 31 September, 2020 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, 31 September, 2020 9:17:12 Object status at: Sunday, 31 September, 2020 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, 31 September, 2020 9:16:58 Object status at: Sunday, 31 September, 2020 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, 31 September, 2020 9:17:18 Object status at: Sunday, 31 September, 2020 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, 31 September, 2020 9:17:38 Object status at: Sunday, 31 September, 2020 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, 31 September, 2020 9:17:27 Object status at: Sunday, 31 September, 2020 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, 31 September, 2020 9:17:30 Object status at: Sunday, 31 September, 2020 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, 31 September, 2020 9:17:31 Object status at: Sunday, 31 September, 2020 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.
Recommended Posts
