一台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)