Error 942 occured during Initialization of Bufq KUPC$S_1_20181023155636

一臺ORACLE實例(Oracle Database 10g Release 10.2.0.5.0)啟動時,報“Error 942 occured during Initialization of Bufq KUPC$S_1_20181023155636”錯誤，具體情況如下所示，內容信息來自告警日誌

SMON: enabling cache recovery

Thu Aug 08 18:57:04 CST 2019

Successfully onlined Undo Tablespace 1.

Thu Aug 08 18:57:04 CST 2019

SMON: enabling tx recovery

Thu Aug 08 18:57:04 CST 2019

Database Characterset is UTF8

replication_dependency_tracking turned off (no async multimaster replication found)

Error 942 occured during Initialization of Bufq KUPC$S_1_20181023155636

Starting background process QMNC

QMNC started with pid=80, OS id=1777

Thu Aug 08 18:57:06 CST 2019

Completed: ALTER DATABASE OPEN

Thu Aug 08 18:57:06 CST 2019

db_recovery_file_dest_size of 81920 MB is 0.00% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Thu Aug 08 19:00:57 CST 2019

Thread 1 advanced to log sequence 279278 (LGWR switch)

查了一下metalink官方字元，發現官方文檔有這方面的資料，出現這個問題是因為“ A datapump queue is invalid or it doesn't exist.  The problem occurs during the buffered queue initialization.”，詳情如下所示：

“Errors ORA-31623 And ORA-600 [kwqbgqc: bad state] During DataPump Export Or Import (文檔 ID 754401.1)”原文如下：

APPLIES TO:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later [Release: 10.2 and later ]
Information in this document applies to any platform.

SYMPTOMS

Next ORA-942 is reported in the alert log at database startup:

Starting ORACLE instance (normal)
...
Opening with internal Resource Manager plan
where NUMA PG = 4, CPUs = 2
replication_dependency_tracking turned off (no async multimaster replication found)
Error 942 occured during Initialization of Bufq KUPC$S_2_20081126123353
Starting background process QMNC
QMNC started with pid=19, OS id=1567
Fri Nov 25 07:33:56 2011
Completed: ALTER DATABASE OPEN
...

Trying to do an export, it fails with:

oracle:~ > expdp system/xxx STATUS=60 PARALLEL=4 DIRECTORY=data_pump_dir LOGFILE=expdp.log DUMPFILE=expdp_%u.dmp TABLES=user.table_name TRACE=480300

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 28 November, 2011 13:06:37

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDE-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
ORA-06512: at line 1

At the same time an ORA-600 error is reported an the alert log:

Mon Nov 28 13:06:39 2011
Errors in file /production/ora1/DUPDWH/var/DUPDWH/admin/udump/dupdwh_ora_16238.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []

CAUSE

A datapump queue is invalid or it doesn't exist.?

The problem occurs during the buffered queue initialization.

SOLUTION

To recreate the failing queue, follow the steps from?
Note 754401.1 - ORA-31623 and ORA-600 [kwqbgqc: bad state] During an Export or Import with DataPump

REFERENCES

NOTE:403036.1 - ORA-00600: [Kwqbgqc: Bad State], [1], [1] reported on Enqueue of BUFFERED message to a single or multiple consumer queue
NOTE:754401.1 - ORA-31623 and ORA-600 [kwqbgqc: bad state] During an Export or Import with DataPump

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

An export or import operation using DataPump fails with the following errors:

UDI-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
ORA-06512: at line 1

and the alert log file of the database shows the error:

ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []

CHANGES

CAUSE

A DataPump queue is invalid in data dictionary. This can happen after reruning the catpatch.sql or utlrp.sql scripts.

SOLUTION

1. Shutdown the database cleanly:

SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup restrict

2. Drop the queue table.

SQL> exec dbms_aqadm.drop_queue_table (queue_table => 'SYS.KUPC$DATAPUMP_QUETAB', force => TRUE);

NOTE:

- If the ORA-4020 error is reported, then wait some minutes and try again
- If an ORA-24* error is reported, then it could be necessary to perform a manual cleanup. To perform this, contact with a Support Engineer.

3. Recreate the queue

The SQL is in the?Note.361025.1 or you can pull it from the catdpb.sql script in $ORACLE_HOME/rdbms/admin directory.

-- Create our queue table.
BEGIN
  dbms_aqadm.create_queue_table (queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
         multiple_consumers => TRUE,
         queue_payload_type => 'SYS.KUPC$_MESSAGE',
         comment => 'DataPump Queue Table',
         compatible => '8.1.3');

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -24001 THEN NULL;
  ELSE RAISE;
  END IF;
END;
/

4. Run utlrp.sql to recompile all the database objects.

5. Retry the DataPump operation

測試驗證，使用expdp導出時，確實會遇到ORA-06512錯誤，具體如下所示：

$ expdp system/xxx DIRECTORY=DUMP_DIR LOGFILE=expdp.log DUMPFILE=test.dmp TABLES=xxxx.xxxx 

Export: Release 10.2.0.5.0 - 64bit Production on Friday, 09 August, 2019 17:08:13

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

UDE-00008: operation generated ORACLE error 31623

ORA-31623: a job is not attached to this session via the specified handle

ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886

ORA-06512: at line 1

如果加上參數TRACE=480300， expdp會一直卡死，不出結果。告警日誌也沒有ORA-600錯誤，跟官方文檔“Errors ORA-31623 And ORA-600 [kwqbgqc: bad state] During DataPump Export Or Import (文檔 ID 754401.1)”描述的略有出入。

$ expdp system/xxx DIRECTORY=DUMP_DIR LOGFILE=expdp.log DUMPFILE=test.dmp TABLES=xxxx.xxxx TRACE=480300

解決方法：