If you have come across situation where in expdp with PARALLEL option for RAC database throws below errors on you:
Problem Scenario:
oracle@TESTHOSTDB01:[/backup/EXPDIR]$cat expdp_MD_parfile.par
"expdp_MD_parfile.par" 3 lines, 237 characters
DIRECTORY=EXPDIR DUMPFILE=expdp_MD.dmp LOGFILE=EXPDIR:expdp_MD.log EXCLUDE=STATISTICS CONTENT=METADATA_ONLY PARALLEL=6 SCHEMAS=ITESTK,ITESTB,IPTESTK,ITESTN
oracle@TESTHOSTDB01:[/backup/EXPDIR]$expdp \'/ as sysdba \' PARFILE="expdp_MD_parfile.par"
'
Export: Release 12.2.0.1.0 - Production on Tue Feb 4 09:12:30 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
Starting "SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" PARFILE=expdp_MD_parfile.par
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CREATE_OBJECT_ROWS [DEFAULT_ROLE]
DEFAULT_ROLE:"IPYEGOV"
ORA-19505: failed to identify file "/backup/EXPDIR/expdp_MD.dmp"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 7
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 12098
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPF$FILE", line 9298
ORA-06512: at "SYS.KUPF$FILE_INT", line 1111
ORA-06512: at "SYS.KUPF$FILE", line 9241
ORA-06512: at "SYS.KUPW$WORKER", line 10727
----- PL/SQL Call Stack -----
object line object
handle number name
7000103bf33ed18 32239 package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
7000103bf33ed18 12119 package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
7000103bf33ed18 11099 package body SYS.KUPW$WORKER.CREATE_OBJECT_ROWS
7000103bf33ed18 14526 package body SYS.KUPW$WORKER.FETCH_XML_OBJECTS
7000103bf33ed18 3689 package body SYS.KUPW$WORKER.UNLOAD_METADATA
7000103bf33ed18 13063 package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
7000103bf33ed18 2311 package body SYS.KUPW$WORKER.MAIN
700010067a5a2d0 2 anonymous block
Object row index into parse items is: 13
Parse item count is: 39
In function CHECK_FOR_REMAP_NETWORK
Nothing to remap
In procedure BUILD_OBJECT_STRINGS - non-base info
In procedure BUILD_SUBNAME_LIST with DEFAULT_ROLE:IPYEGOV.
In function NEXT_PO_NUMBER
PO number assigned: 3013
KUPF$FILE.WRITE_LOB
In procedure DETERMINE_FATAL_ERROR with ORA-19505: failed to identify file "/backup/EXPDIR/expdp_MD.dmp"
ORA-06512: at "SYS.KUPF$FILE", line 9298
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 7
ORA-06512: at "SYS.KUPF$FILE_INT", line 1111
ORA-06512: at "SYS.KUPF$FILE", line 9241
Job "SYS"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at Tue Feb 4 09:13:48 2020 elapsed 0 00:01:15
Solution:
Use cluster=n parameter along with.
oracle@TESTHOSTDB01:[/backup/EXPDIR]$vi expdp_MD_parfile.par
"expdp_MD_parfile.par" 3 lines, 237 characters
DIRECTORY=EXPDIR DUMPFILE=expdp_MD.dmp LOGFILE=EXPDIR:expdp_MD.log EXCLUDE=STATISTICS CONTENT=METADATA_ONLY PARALLEL=6 cluster=n
SCHEMAS=ITESTK,ITESTB,IPTESTK,ITESTN
Even after using cluster=n not working then try dropping existing dormant export tables using below method:
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
where owner_name='SYS' and state='NOT RUNNING';
09:30:36 SQL> col OWNER_NAME for a20
09:30:47 SQL> cl JOB_NAME for a30
SP2-0158: unknown CLEAR option "JOB_NAME"
SP2-0158: unknown CLEAR option "for"
SP2-0158: unknown CLEAR option "a30"
09:30:56 SQL> col JOB_NAME for a30
09:31:01 SQL> col JOB_MODE for a20
09:31:14 SQL> col OPERATION for a20
09:31:23 SQL> /
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
------------- ------------------------- ------------- ------------- ------------ -----------------
SYS SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0
2 rows selected.
Elapsed: 00:00:00.03
09:31:24 SQL>
drop table SYS_EXPORT_SCHEMA_01;
drop table SYS_EXPORT_SCHEMA_02;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Output of the Drop SYS_EXPORT_SCHEMA_01 ~~~~~~~~~~~~~~~~~~~~~~~~~~
09:33:22 SQL> drop table SYS_EXPORT_SCHEMA_01;
Table dropped.
Elapsed: 00:00:00.57
09:33:28 SQL> drop table SYS_EXPORT_SCHEMA_02;
Table dropped.
Elapsed: 00:00:00.78
oracle@TESTHOSTDB01:[/backup/EXPDIR]$vi expdp_MD_parfile.par
"expdp_MD_parfile.par" 3 lines, 237 characters
DIRECTORY=EXPDIR DUMPFILE=expdp_MD.dmp LOGFILE=EXPDIR:expdp_MD.log EXCLUDE=STATISTICS CONTENT=METADATA_ONLY PARALLEL=6 cluster=n
SCHEMAS=ITESTK,ITESTB,IPTESTK,ITESTN
Problem Scenario:
oracle@TESTHOSTDB01:[/backup/EXPDIR]$cat expdp_MD_parfile.par
"expdp_MD_parfile.par" 3 lines, 237 characters
DIRECTORY=EXPDIR DUMPFILE=expdp_MD.dmp LOGFILE=EXPDIR:expdp_MD.log EXCLUDE=STATISTICS CONTENT=METADATA_ONLY PARALLEL=6 SCHEMAS=ITESTK,ITESTB,IPTESTK,ITESTN
oracle@TESTHOSTDB01:[/backup/EXPDIR]$expdp \'/ as sysdba \' PARFILE="expdp_MD_parfile.par"
'
Export: Release 12.2.0.1.0 - Production on Tue Feb 4 09:12:30 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
Starting "SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" PARFILE=expdp_MD_parfile.par
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CREATE_OBJECT_ROWS [DEFAULT_ROLE]
DEFAULT_ROLE:"IPYEGOV"
ORA-19505: failed to identify file "/backup/EXPDIR/expdp_MD.dmp"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 7
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 12098
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPF$FILE", line 9298
ORA-06512: at "SYS.KUPF$FILE_INT", line 1111
ORA-06512: at "SYS.KUPF$FILE", line 9241
ORA-06512: at "SYS.KUPW$WORKER", line 10727
----- PL/SQL Call Stack -----
object line object
handle number name
7000103bf33ed18 32239 package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
7000103bf33ed18 12119 package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
7000103bf33ed18 11099 package body SYS.KUPW$WORKER.CREATE_OBJECT_ROWS
7000103bf33ed18 14526 package body SYS.KUPW$WORKER.FETCH_XML_OBJECTS
7000103bf33ed18 3689 package body SYS.KUPW$WORKER.UNLOAD_METADATA
7000103bf33ed18 13063 package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
7000103bf33ed18 2311 package body SYS.KUPW$WORKER.MAIN
700010067a5a2d0 2 anonymous block
Object row index into parse items is: 13
Parse item count is: 39
In function CHECK_FOR_REMAP_NETWORK
Nothing to remap
In procedure BUILD_OBJECT_STRINGS - non-base info
In procedure BUILD_SUBNAME_LIST with DEFAULT_ROLE:IPYEGOV.
In function NEXT_PO_NUMBER
PO number assigned: 3013
KUPF$FILE.WRITE_LOB
In procedure DETERMINE_FATAL_ERROR with ORA-19505: failed to identify file "/backup/EXPDIR/expdp_MD.dmp"
ORA-06512: at "SYS.KUPF$FILE", line 9298
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 7
ORA-06512: at "SYS.KUPF$FILE_INT", line 1111
ORA-06512: at "SYS.KUPF$FILE", line 9241
Job "SYS"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at Tue Feb 4 09:13:48 2020 elapsed 0 00:01:15
Solution:
Use cluster=n parameter along with.
oracle@TESTHOSTDB01:[/backup/EXPDIR]$vi expdp_MD_parfile.par
"expdp_MD_parfile.par" 3 lines, 237 characters
DIRECTORY=EXPDIR DUMPFILE=expdp_MD.dmp LOGFILE=EXPDIR:expdp_MD.log EXCLUDE=STATISTICS CONTENT=METADATA_ONLY PARALLEL=6 cluster=n
SCHEMAS=ITESTK,ITESTB,IPTESTK,ITESTN
Even after using cluster=n not working then try dropping existing dormant export tables using below method:
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
where owner_name='SYS' and state='NOT RUNNING';
09:30:36 SQL> col OWNER_NAME for a20
09:30:47 SQL> cl JOB_NAME for a30
SP2-0158: unknown CLEAR option "JOB_NAME"
SP2-0158: unknown CLEAR option "for"
SP2-0158: unknown CLEAR option "a30"
09:30:56 SQL> col JOB_NAME for a30
09:31:01 SQL> col JOB_MODE for a20
09:31:14 SQL> col OPERATION for a20
09:31:23 SQL> /
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
------------- ------------------------- ------------- ------------- ------------ -----------------
SYS SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0
2 rows selected.
Elapsed: 00:00:00.03
09:31:24 SQL>
drop table SYS_EXPORT_SCHEMA_01;
drop table SYS_EXPORT_SCHEMA_02;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Output of the Drop SYS_EXPORT_SCHEMA_01 ~~~~~~~~~~~~~~~~~~~~~~~~~~
09:33:22 SQL> drop table SYS_EXPORT_SCHEMA_01;
Table dropped.
Elapsed: 00:00:00.57
09:33:28 SQL> drop table SYS_EXPORT_SCHEMA_02;
Table dropped.
Elapsed: 00:00:00.78
oracle@TESTHOSTDB01:[/backup/EXPDIR]$vi expdp_MD_parfile.par
"expdp_MD_parfile.par" 3 lines, 237 characters
DIRECTORY=EXPDIR DUMPFILE=expdp_MD.dmp LOGFILE=EXPDIR:expdp_MD.log EXCLUDE=STATISTICS CONTENT=METADATA_ONLY PARALLEL=6 cluster=n
SCHEMAS=ITESTK,ITESTB,IPTESTK,ITESTN
No comments:
Post a Comment