|
In this Document
Oracle Database - Enterprise Edition -
Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
You are performing a schema data pump import (impdp) and are
constantly getting the following errors during the import
ORA-31693: Table
data object "<schema>"."<tablename>" failed
to load/unload and is being skipped due to error:
ORA-00604: error occurred at recursive SQL level 2
ORA-00942: table or view does not exist
You have enabled tracing for the ORA-942 and from the trace files, you
see:
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0,
level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=4va1rghfz28hj) -----
SELECT COUNT(*) FROM SYS_SQL_FILE_TABLE_0
and
dbkedDefDump():
Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=8bg2vzmunu954) -----
CREATE OR REPLACE FORCE VIEW
"SYS"."AQ$KUPC$DATAPUMP_QUETAB_1" AS SELECT q_name QUEUE,
qt.msgid MSG_ID, corrid CORR_ID, priority MSG_PRIORITY,
decode(s.subscriber_type,2,'UNDELIVERABLE',
decode(h.dequeue_time, NULL,decode(l.dequeue_time, NULL, decode(state, 0,
'READY',
1, 'WAIT',
2, 'PROCESSED',
3, 'EXPIRED',
8, 'DEFERRED',
10, 'BUFFERED_EXPIRED'),(decode(l.transaction_id, NULL, 'UNDELIVERABLE',
'PROCESSED'))), (decode(h.transaction_id, NULL, 'UNDELIVERABLE',
'PROCESSED')))
) MSG_STATE, cast(FROM_TZ(qt.delay, '00:00')
at time zone sessiontimezone as date) delay, cast(FROM_TZ(qt.delay, '00:00')
at time zone sessiontimezone as timestamp) DELAY_TIMESTAMP, expiration,
cast(FROM_TZ(qt.enq_time, '00:00')
at time zone sessiontimezone as date) enq_time, cast(FROM_TZ(qt.enq_time, '00:00')
at time zone sessiontimezone as timestamp)
enq_timestamp, enq_uid ENQ_USER_ID, qt.enq_tid ENQ_TXN_ID,
decode(h.transaction_id, NULL,
decode(l.transaction_id, NULL, TO_DATE(NULL), l.dequeue_time),
cast(FROM_TZ(h.dequeue_time, '00:00')
at time zone sessiontimezone as date)) DEQ_TIME, decode(h.transaction_id,
NULL,
decode(l.transaction_id, NULL, TO_TIMESTAMP(NULL), l.dequeue_time),
cast(FROM_TZ(h.dequeue_time, '00:00')
at time zone sessiontimezone as timestamp))
DEQ_TIMESTAMP, decode(h.dequeue_user, NULL, l.dequeue_user, h.dequeue_user)
DEQ_USER_ID, decode(h.transaction_id, NULL, l.transaction_id,
h.transaction_id) DEQ_TXN_ID, h.retry_count retry_count, decode (state, 0,
exception_qschema,
1, exception_qschema,
2, exception_qschema,
NULL) EXCEPTION_QUEUE_OWNER, decode (state, 0, exception_queue,
1, exception_queue,
2, exception_queue,
NULL) EXCEPTION_QUEUE, user_data, h.propagated_msgid PROPAGATED_MSGID,
sender_name SENDER_NAME, sender_address SENDER_ADDRESS, sender_protocol
SENDER_PROTOCOL, dequeue_msgid ORIGINAL_MSGID, decode (h.dequeue_time, NULL,
decode (l.dequeue_time, NULL,
decode (state, 3, exception_queue, NULL),
decode (l.transaction_id, NULL, NULL, exception_queue)),
decode (h.transaction_id, NULL, NULL, exception_queue))
ORIGINAL_QUEUE_NAME, decode (h.dequeue_time, NULL, decode (l.dequeue_time,
NULL,
decode (state, 3, exception_qschema, NULL),
decode (l.transaction_id, NULL, NULL, exception_qschema)),
decode (h.transaction_id, NULL, NULL, exception_qschema))
ORIGINAL_QUEUE_OWNER, decode(s.subscriber_type,2,
'Messages to be cleaned up later',
decode(h.dequeue_time, NULL,
decode(state, 3,
decode(h.transaction_id, NULL,
decode (expiration , NULL ,
'MAX_RETRY_EXCEEDED',
'TIME_EXPIRATION'),
'INVALID_TRANSACTION', NULL,
'MAX_RETRY_EXCEEDED'), NULL),
decode(h.transaction_id, NULL,
'PROPAGATION_FAILURE', NULL)))
EXPIRATION_REASON, decode(h.subscriber#, 0, decode(h.name, '0', NULL,
h.name),
s.name) CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL FROM
"KUPC$DATAPUMP_QUETAB_1" qt, "AQ$_KUPC$DATAPUMP_QUETAB_1_H"
h LEFT OUTER JOIN "AQ$_KUPC$DATAPUMP_QUETAB_1_L" l ON h.msgid =
l.msgid AND h.subscriber# = l.subscriber# AND h.name = l.name AND h.address#
= l.address#, "AQ$_KUPC$DATAPUMP_QUETAB_1_S" s WHERE qt.msgid =
h.msgid AND ((h.subscriber# != 0 AND h.subscriber# = s.subscriber_id) OR
(h.subscriber# = 0 AND h.address# = s.subscriber_id)) AND (qt.state != 7 OR
qt.state != 9 ) UNION ALL SELECT q.name QUEUE, b.msgid MSG_ID, b.corrid
CORR_ID, b.priority MSG_PRIORITY, decode (TO_CHAR(b.state), '4', 'IN MEMORY',
'5', 'DEFERRED',
'12', 'EXPIRED INMEMORY',
NULL) MSG_STATE, cast (null as DATE) DELAY, cast (null as TIMESTAMP)
DELAY_TIMESTAMP, b.expiration EXPIRATION, cast(FROM_TZ(b.enq_time, '00:00')
at time zone sessiontimezone as date) ENQ_TIME, cast(FROM_TZ(b.enq_time,
'00:00')
at time zone sessiontimezone as timestamp)
ENQ_TIMESTAMP, b.enq_uid ENQ_USER_ID, cast (null as VARCHAR2(30)) ENQ_TXN_ID,
cast (null as DATE) DEQ_TIME, cast(null as TIMESTAMP) DEQ_TIMESTAMP, cast
(null as number) DEQ_USER_ID, cast(null as VARCHAR2(30)) DEQ_TXN_ID,
b.retry_count RETRY_COUNT, b.exceptionq_schema EXCEPTION_QUEUE_OWNER,
b.exceptionq_name EXCEPTION_QUEUE,
sys.dbms_aq_bqview.get_adt_payload(b.queue_id, b.msg_num,
"SYS"."KUPC$_MESSAGE"(NULL, NULL)) USER_DATA, cast(null
as RAW(16)) PROPAGATED_MSGID, b.sender_name SENDER_NAME, b.sender_address
SENDER_ADDRESS, b.sender_protocol SENDER_PROTOCOL, b.dequeue_msgid
ORIGINAL_MSGID, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_NAME, cast(null as
VARCHAR2(30)) ORIGINAL_QUEUE_OWNER, decode(b.state, 12, 'TIME_EXPIRATION',
NULL) EXPIRATION_REASON, s.name CONSUMER_NAME, s.address ADDRESS, s.protocol
PROTOCOL FROM SYS.qt40238_BUFFER b, all_queues q,
"AQ$_KUPC$DATAPUMP_QUETAB_1_S" s WHERE s.subscriber_id =
b.subscriber_id AND bitand(s.subscriber_type, 8) != 8 AND bitand(b.state, 4)
= 4 AND q.qid = b.queue_id UNION ALL SELECT p.q_name QUEUE, p.msgid MSG_ID,
p.corrid CORR_ID, p.priority MSG_PRIORITY, decode (TO_CHAR(b.state), '2',
'SPILLED',
'3', 'DEFERRED SPILLED',
'10', 'EXPIRED SPILLED',
NULL) MSG_STATE, cast(FROM_TZ(p.delay, '00:00')
at time zone sessiontimezone as date) DELAY, cast(FROM_TZ(p.delay, '00:00')
at time zone sessiontimezone as timestamp) DELAY_TIMESTAMP, p.expiration
EXPIRATION, cast(FROM_TZ(p.enq_time, '00:00')
at time zone sessiontimezone as date) ENQ_TIME, cast(FROM_TZ(p.enq_time,
'00:00')
at time zone sessiontimezone as timestamp)
ENQ_TIMESTAMP, p.enq_uid ENQ_USER_ID, p.enq_tid ENQ_TXN_ID,
cast(FROM_TZ(p.deq_time, '00:00')
at time zone sessiontimezone as date) DEQ_TIME, cast(FROM_TZ(p.deq_time,
'00:00')
at time zone sessiontimezone as timestamp)
DEQ_TIMESTAMP, p.deq_uid DEQ_USER_ID, p.deq_tid DEQ_TXN_ID, p.retry_count
RETRY_COUNT, p.exception_qschema EXCEPTION_QUEUE_OWNER, p.exception_queue
EXCEPTION_QUEUE, p.user_data USER_DATA, cast (null as
RAW(16))PROPAGATED_MSGID,p.sender_name SENDER_NAME, p.sender_address
SENDER_ADDRESS, p.sender_protocol SENDER_PROTOCOL, cast (null as RAW(16))
ORIGINAL_MSGID, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_NAME, cast (null
as VARCHAR2(30)) ORIGINAL_QUEUE_OWNER, decode(b.state, 10, 'TIME_EXPIRATION',
NULL) EXPIRATION_REASON, s.name CONSUMER_NAME, s.address ADDRESS, s.protocol
PROTOCOL FROM "AQ$_KUPC$DATAPUMP_QUETAB_1_P" p, SYS.qt40238_BUFFER
b, "AQ$_KUPC$DATAPUMP_QUETAB_1_S" s, all_queues q WHERE
b.subscriber_id = s.subscriber_id AND bitand(s.subscriber_type, 8) != 8 AND
bitand(b.state ,2) = 2 AND p.msgid = b.msgid AND q.qid = b.queue_id and
p.q_name = q.name WITH READ ONLY
and
dbkedDefDump():
Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=406stjn3n5qvg) -----
CREATE OR REPLACE VIEW
"SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" AS SELECT /*+
NO_MERGE (qo) USE_NL(iot) */ qt.q_name Q_NAME, qt.rowid ROW_ID, qt.msgid
MSGID, qt.corrid CORRID, qt.priority PRIORITY, qt.state STATE,
cast(FROM_TZ(qt.delay, '00:00') at time zone sessiontimezone as timestamp)
DELAY, qt.expiration EXPIRATION, cast(FROM_TZ(qt.enq_time, '00:00') at time
zone sessiontimezone as timestamp) ENQ_TIME, qt.enq_uid ENQ_UID, qt.enq_tid
ENQ_TID, cast(FROM_TZ(qt.deq_time, '00:00') at time zone sessiontimezone as
timestamp) DEQ_TIME, qt.deq_uid DEQ_UID, qt.deq_tid DEQ_TID, qt.retry_count
RETRY_COUNT, qt.exception_qschema EXCEPTION_QSCHEMA, qt.exception_queue
EXCEPTION_QUEUE, qt.cscn CSCN, qt.dscn DSCN, qt.chain_no CHAIN_NO,
qt.local_order_no LOCAL_ORDER_NO, cast(FROM_TZ(qt.time_manager_info, '00:00')
at time zone sessiontimezone as timestamp) TIME_MANAGER_INFO, qt.step_no
STEP_NO, qt.user_data USER_DATA, qt.sender_name SENDER_NAME,
qt.sender_address SENDER_ADDRESS, qt.sender_protocol SENDER_PROTOCOL,
qt.dequeue_msgid DEQUEUE_MSGID, 'PERSISTENT' DELIVERY_MODE, 0 SEQUENCE_NUM, 0
MSG_NUM, qo.qid QUEUE_ID, qt.user_prop USER_PROP, iot.subscriber#
SUBSCRIBER_ID, iot.name SUBSCRIBER_NAME, iot.queue# QUEUE_EVTID FROM
"KUPC$DATAPUMP_QUETAB_1" qt,
"AQ$_KUPC$DATAPUMP_QUETAB_1_I" iot, SYS.ALL_INT_DEQUEUE_QUEUES qo
WHERE qt.msgid=iot.msgid and qt.q_name = qo.name AND qo.owner = 'SYS' AND
iot.msg_enq_time = qt.enq_time AND iot.msg_step_no = qt.step_no AND
iot.msg_local_order_no = qt.local_order_no AND iot.msg_chain_no = qt.chain_no
UNION ALL SELECT qo.name Q_NAME, cast (null as rowid) ROW_ID, b.msgid MSGID,
b.corrid CORRID, b.priority PRIORITY, decode(b.state, 4, 0, 5, 8, 12, 10, 0)
STATE, cast (null as TIMESTAMP) DELAY, b.expiration EXPIRATION,
cast(FROM_TZ(b.enq_time, '00:00') at time zone sessiontimezone as timestamp)
ENQ_TIME, b.enq_uid ENQ_UID, cast(null as varchar2(30)) ENQ_TID, cast(null as
timestamp) DEQ_TIME, cast (null as number) DEQ_UID, cast (null as
varchar2(30)) DEQ_TID, 0 RETRY_COUNT, b.exceptionq_schema
EXCEPTION_QUEUE_OWNER, b.exceptionq_name EXCEPTION_QUEUE, 0 CSCN, 0 DSCN, 0
CHAIN_NO, 0 LOCAL_ORDER_NO, cast (null as TIMESTAMP) TIME_MANAGER_INFO, 0
STEP_NO, sys.dbms_aq_bqview.get_adt_payload(b.queue_id, b.msg_num,
"SYS"."KUPC$_MESSAGE"(NULL, NULL)) USER_DATA,
b.sender_name SENDER_NAME, b.sender_address SENDER_ADDRESS, b.sender_protocol
SENDER_PROTOCOL, cast(null as raw(16)) DEQUEUE_MSGID, 'BUFFERED'
DELIVERY_MODE, b.sequence_num SEQUENCE_NUM, b.msg_num MSG_NUM, qo.qid
QUEUE_ID, null USER_PROP, b.subscriber_id SUBSCRIBER_ID, cast (null as
varchar2(30)) SUBSCRIBER_NAME, 0 QUEUE_EVTID FROM SYS.qt40238_BUFFER b,
sys.all_int_dequeue_queues qo WHERE bitand(b.state, 4) = 4 AND qo.qid
=b.queue_id UNION ALL SELECT p.q_name Q_NAME, cast (null as rowid) ROW_ID,
p.msgid MSGID, p.corrid CORRID, p.priority PRIORITY, p.state STATE,
cast(FROM_TZ(p.delay, '00:00') at time zone sessiontimezone as timestamp)
DELAY, p.expiration EXPIRATION, cast(FROM_TZ(p.enq_time, '00:00') at time
zone sessiontimezone as timestamp) ENQ_TIME, p.enq_uid ENQ_UID, p.enq_tid
ENQ_TID, cast(FROM_TZ(p.deq_time, '00:00') at time zone sessiontimezone as
timestamp) DEQ_TIME, p.deq_uid DEQ_UID, p.deq_tid DEQ_TID, p.retry_count
RETRY_COUNT, p.exception_qschema EXCEPTION_QSCHEMA, p.exception_queue
EXCEPTION_QUEUE, p.cscn CSCN, p.dscn DSCN, p.chain_no CHAIN_NO,
p.local_order_no LOCAL_ORDER_NO, cast(FROM_TZ(p.time_manager_info, '00:00')
at time zone sessiontimezone as timestamp) TIME_MANAGER_INFO, p.step_no
STEP_NO, p.user_data USER_DATA, p.sender_name SENDER_NAME, p.sender_address
SENDER_ADDRESS, p.sender_protocol SENDER_PROTOCOL, cast (null as RAW(16)) DEQUEUE_MSGID,
'BUFFERED' DELIVERY_MODE, b.sequence_num SEQUENCE_NUM, b.msg_num MSG_NUM,
qo.qid QUEUE_ID, null USER_PROP, b.subscriber_id SUBSCRIBER_ID, cast (null as
varchar2(30)) SUBSCRIBER_NAME, 0 QUEUE_EVTID FROM
"AQ$_KUPC$DATAPUMP_QUETAB_1_P" p, SYS.qt40238_BUFFER b,
sys.all_int_dequeue_queues qo WHERE bitand(b.state, 2) = 2 AND p.msgid =
b.msgid AND qo.qid = b.queue_id and p.q_name = qo.name WITH READ ONLY
Supplemental Logging is enabled in database leading to the errors.
You can validate that Supplemental Logging is enabled using the
following query:
select SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI,
SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL,
SUPPLEMENTAL_LOG_DATA_PL from V$DATABASE;
This behavior was logged in bug:19078725 which seems to
affect the 11.2.0.3 and 11.2.0.4 releases.
This bug is still being worked by development.
Until a fix is made available, you can use the Workaround of disabling
supplemental log data:
SQL> alter database drop
supplemental log data;
And then perform the import.
BUG:19078725 - IMPDP ERRORS
AFTER ENABLING SUPPLEMENTAL LOGGING
|
