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

 

 

 

"errors ora-31623 and ora-600 [kwqbgqc: bad state] during datapump export or import (文档 id 754401.1)"原文如下所示：

 

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

 

解决方法：

 

1：关闭数据库实例，然后以约束方式启动（startup restrict)

 

sql> connect / as sysdba

sql> shutdown immediate

sql> startup restrict

 

2: 删除queue表

 

sql> exec dbms_aqadm.drop_queue_table (queue_table => 'sys.kupc$datapump_quetab', force => true);

pl/sql procedure successfully completed.

 

官方文档提示，如果遇到ora错误，可以按下面方式处理，如果没有遇到任何错误，直接跳过下面内容。

 

- 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：执行下面脚本，重建queue。此脚本位于$oracle_home/rdbms/admin目录下的catdpb.sql脚本中

 

sql> 

sql> begin

  2  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');

  3  

  4  exception

   when others then

  5    6        if sqlcode = -24001 then null;

  7        else raise;

  8        end if;

  9  end;

 10  /

pl/sql procedure successfully completed.

 

 

4：重启数据库实例

 

重启数据库后已经没有遇到“error 942 occured during initialization of bufq kupc$s_1_20181023155636”这样的错误了

 

sql> shutdown immediate;

sql> startup

 

5：重编译失效对象

 

sql> @$oracle_home/rdbms/admin/utlrp.sql

 

6：测试验证数据导出功能

 

 

 

参考资料：

 

errors ora-31623 and ora-600 [kwqbgqc: bad state] during datapump export or import (文档 id 754401.1)

error 942 occured during initialization of bufq kupc$s_<number>' starting database (文档 id 1384131.1)