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)

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐