ORA-06512: at "SYS.KUPW$WORKER ORA-39126: Worker unexpected fatal error in KUPW$WORKER

Issue:  Unable to export due to ORA-06512 ORA-39126 errors


ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [MARKER]
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95




Solution:

i. export backup taken using below EXPDP command terminated with fatal error


expdp as sysdba" FULL=YES directory=EXP_DMP dumpfile=Mydb_FULL.dmp logfile=Mydb_full.log CONTENT=METADATA_ONLY

[oracle@mytestlaba export_dmp]$ more Mydb_full.log
Export: Release 12.1.0.2.0 - Production on Fri Jun 30 10:52:09 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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_FULL_03": "/******** AS SYSDBA" FULL=YES directory=EXP_DMP dumpfile=Mydb_full.dmp logfile=Mydb_full.log CONTENT=METADATA_ONLY
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
Total estimation using BLOCKS method: 4.867 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [MARKER]
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+FRA/oradata/MYDBPRD/temp_1.dbf'
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 11259
----- PL/SQL Call Stack -----
object line object
handle number name
0xb64fe260 27116 package body SYS.KUPW$WORKER
0xb64fe260 11286 package body SYS.KUPW$WORKER
0xb64fe260 13515 package body SYS.KUPW$WORKER
0xb64fe260 3173 package body SYS.KUPW$WORKER
0xb64fe260 12035 package body SYS.KUPW$WORKER
0xb64fe260 2081 package body SYS.KUPW$WORKER
0xb6a82850 2 anonymous block
In procedure BUILD_OBJECT_STRINGS
In procedure BUILD_SUBNAME_LIST with MARKER:.
In function NEXT_PO_NUMBER
FORALL
FORALL
DBMS_LOB.TRIM
DBMS_LOB.TRIM
DBMS_METADATA.FETCH_XML_CLOB
DBMS_METADATA.FETCH_XML_CLOB
In procedure DETERMINE_FATAL_ERROR with ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+FRA/oradata/MYDBPRD/temp_1.dbf'
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [MARKER]
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+FRA/oradata/MYDBPRD/temp_1.dbf'

ii. Verified 'PROCACT' object status : found valid

[oracle@mytestlaba export_dmp]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 6 08:07:15 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> select owner,object_type,status,object_name from dba_objects where upper (object_name) like '%PROCACT%';

OWNER      OBJECT_TYPE STATUS  OBJECT_NAME
---------- ----------- ------- ----------------------
SYS        TYPE        VALID   KU$_PROCACT_T
SYS        VIEW        VALID   KU$_PROCACT_SYS_VIEW
SYS        TYPE        VALID   KU$_PROCACT_SCHEMA_T
SYS        VIEW        VALID   KU$_PROCACT_SCHEMA_VIEW
SYS        TYPE        VALID   KU$_PROCACT_INSTANCE_T
SYS        VIEW        VALID   KU$_PROCACT_INSTANCE_VIEW
SYS        VIEW        VALID   KU$_PROCACT_SYS_PKG_VIEW
SYS        VIEW        VALID   KU$_PROCACT_SCHEMA_PKG_VIEW

8 rows selected.





iii. Checked temp file status and files : got error


[oracle@mytestlaba export_dmp]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 6 08:09:34 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> select * from dba_temp_files;
select * from dba_temp_files
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+FRA/oradata/MYDBPRD/temp_1.dbf'



iv. Unable to locate temp files in ASM diskgroup

SQL> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES

----- ---------------- ---------  -- ------- -------  ---- --- BLOCKS CREATE_BYTES BLOCK_SIZE NAME CON_ID
------------------------------------------------

1 607860 27-MAR-13 3 1 ONLINE READ WRITE
0 0 5242880000 8192
+FRA/oradata/MYDBPRD/temp_1.dbf
0

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
-----------------------------------------------------------
CON_ID
----------
2 8.5988E+12 17-JUN-17 3 5 ONLINE READ WRITE
2.1475E+10 2621440 2.1475E+10 8192
+DATA/MYDBPRD/TEMPFILE/temp.370.947396711
0


FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------- ------ ---- ------ ---- ------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ------
NAME
----------------------------------------------
CON_ID
----------
4 3.0310E+10 08-JUN-13 3 2 ONLINE READ WRITE
0 0 2147483648 8192
+FRA/oradata/MYDBPRD/temp_2.dbf
0


FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
------- --------- ------- ---------- ---------- ------- ----------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
-------------------------------------------------
CON_ID
----------
6 3.8783E+10 27-MAR-14 3 3 ONLINE READ WRITE
1.0737E+10 1310720 1.0737E+10 8192
+DATA/MYDBPRD/TEMPFILE/temp.372.947396713
0

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------- --------- ---------- ---------- ------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------
CON_ID
----------
7 3.8783E+10 27-MAR-14 3 4 ONLINE READ WRITE
1.0737E+10 1310720 1.0737E+10 8192
+DATA/MYDBPRD/TEMPFILE/temp.371.947396713
0

SQL> set line 900 pages 900
SQL> /


FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME CON_ID
---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ------------------------------------------- ----------
1 607860 27-MAR-13 3 1 ONLINE READ WRITE 0 0 5242880000 8192 +FRA/oradata/MYDBPRD/temp_1.dbf 0
2 8.5988E+12 17-JUN-17 3 5 ONLINE READ WRITE 2.1475E+10 2621440 2.1475E+10 8192 +DATA/MYDBPRD/TEMPFILE/temp.370.947396711 0
4 3.0310E+10 08-JUN-13 3 2 ONLINE READ WRITE 0 0 2147483648 8192 +FRA/oradata/MYDBPRD/temp_2.dbf 0
6 3.8783E+10 27-MAR-14 3 3 ONLINE READ WRITE 1.0737E+10 1310720 1.0737E+10 8192 +DATA/MYDBPRD/TEMPFILE/temp.372.947396713 0
7 3.8783E+10 27-MAR-14 3 4 ONLINE READ WRITE 1.0737E+10 1310720 1.0737E+10 8192 +DATA/MYDBPRD/TEMPFILE/temp.371.947396713 0


v. Attempted to reuse the temp file names : got error

SQL> alter tablespace temp add tempfile '+FRA/oradata/MYDBPRD/temp_1.dbf' reuse;
alter tablespace temp add tempfile '+FRA/oradata/MYDBPRD/temp_1.dbf' reuse
*
ERROR at line 1:
ORA-01537: cannot add file '+FRA/oradata/MYDBPRD/temp_1.dbf' - file already part of database

SQL> 






vi. New TEMP tablespace created : successfully

[oracle@mytestlaba export_dmp]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 6 08:12:23 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> alter tablespace temp add tempfile '+FRA' size 1g;
Tablespace altered.


SQL> select * from dba_temp_files;
select * from dba_temp_files
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+FRA/oradata/MYDBPRD/temp_1.dbf'

SQL> create temporary tablespace TEMP1 tempfile '+FRA' size 1G;
Tablespace created.

 
SQL> alter database default temporary tablespace TEMP1;
Database altered.

SQL> drop tablespace TEMP including contents and datafiles;
Tablespace dropped.

SQL> select * from dba_temp_files;

FILE_NAME
--------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------- ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ---------- -----------
+FRA/MYDBPRD/TEMPFILE/temp1.289.948615227
5 TEMP1 1073741824 131072 ONLINE
1 NO 0 0 0 1072693248 130944

SQL> select file_id,file_name,bytes/1024/1024 from dba_temp_files where tablespace_name='TEMP1';

FILE_ID
----------
FILE_NAME
--------------------------------------------------
BYTES/1024/1024
---------------
5
+FRA/MYDBPRD/TEMPFILE/temp1.289.948615227
1024

SQL> alter database tempfile 5 resize 20G;
Database altered.


SQL> select file_id,file_name,bytes/1024/1024 from dba_temp_files where tablespace_name='TEMP1';

FILE_ID
----------
FILE_NAME
---------------------------------------------------------
BYTES/1024/1024
---------------
5
+FRA/MYDBPRD/TEMPFILE/temp1.289.948615227
20480

vii. Re attempted export : Success

[oracle@mytestlaba export_dmp]$ more MYDB_full.log
;;;
Export: Release 12.1.0.2.0 - Production on Thu Jul 6 08:30:02 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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_FULL_05": "/******** AS SYSDBA" FULL=YES directory=EXP_DMP dumpfile=MYDB_FULL.dmp logfile=MYDB_full.log CONTENT=METADATA_ONLY
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
Total estimation using BLOCKS method: 4.867 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE

..

.

.

.