oracle用户数据库导错,Oracle数据库迁移遭遇AQ队列表创建报ORA-24001错误

前两天,客户在迁移一个数据库系统遭遇AQ队列表创建的报错故障.

A.上来诊断时,大致了解到数据库系统迁移过程如下:

1. 安装新数据库软件,并且跨版本,源数据库是: 9.2.0.6,目标数据库是:

10.2.0.5;因而新数据库是全新安装RDBMS和创建新数据库,采用用户级别的数据迁移方式.

2. 迁移用户数据时,并没有采用EXP/IMP方式,而是通过net8网络,在数据库内创建DB

LINK方式,把源数据库用户下的所有对象创建一便:

有数据的表使用CTAS,其他对象通过DBMS_METADATA获取创建语句重新跑一便.

3.系统采用了Oracle AQ队列功能来进行应用程序之间的信息传送分发,导致把原来创建的队列表也通过CTAS方式给创建了.

B. 处理过程大致如下:

1. 开始着手创建队列,首先采用: exec

dbms_aqadm.drop_queue_table(’QUE_XXX_TAB’,true,true);来清理可能存在此队列表,报错:

ORA-24002.

2. 初始创建报错,是正常才对,于是就开始创建:

exec dbms_aqadm.create_queue_table(

queue_table=>’QUE_XXX_TAB’,

queue_payload_type=>’SYS.AQ$_JMS_TEXT_MESSAGE’,

multiple_consumers => false,comment

=> ‘Queue Table For PS Connection Factory’);

报: ORA-24001错误.

3. 检查用户下的对象情况

select object_name,object_type from user_objects where

object_name= ‘QUE_XXX_TAB’;

发现存在此表对象,于是咨询现场工程师,是如何做的数据迁移,获知是通过DB LINK方式,具体也不太清楚.

4. 到此,只能判断是这个表对象占用了命名空间,由此想到把它重命名走先,再进行创建.

rename QUE_XXX_TAB to QUE_XXX_TAB_BAK;

exec dbms_aqadm.create_queue_table(

queue_table=>’QUE_XXX_TAB’,

queue_payload_type=>’SYS.AQ$_JMS_TEXT_MESSAGE’,

multiple_consumers => false,comment

=> ‘Queue Table For PS Connection Factory’);

还是报: ORA-24002错误.

5. 这下一时不知道什么问题引起,于是检查了几个方面: 系统权限,队列信息

a. 权限

SQL>select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE

XXX_USER SYS DBMS_AQ SYS EXECUTE

XXX_USER SYS DBMS_AQADM SYS EXECUTE

XXX_USER SYS DBMS_AQIN SYS EXECUTE

XXX_USER SYS DBMS_AQJMS SYS EXECUTE

SQL>select * from user_role_privs;

no rows selected

b. 用户队列任务

SQL> select qname,start_date,process_name from

user_queue_schedules;

no rows selected

c. 用户队列

SQL> select

name,queue_table,queue_type,enqueue_enabled,dequeue_enabled from

user_queues;

no rows selected

d. 用户队列表

SQL> SELECT * FROM user_queue_tables;

AQ_XXX_QT

QUE_XXX_TAB

看着这些信息,就判断可能是AQ管理权限角色未分配,导致删除不掉队列表所致,于是进行授权

SQL>grant aq_administrator_role to XXX_USER;

6. 授权后,继续进行原来的队列表清理

SQL>exec

dbms_aqadm.drop_queue_table(’QUE_XXX_TAB’,true,true);

还是报: ORA-24002错误.

7. 看着这前后貌似矛盾的状况,有些不解.

8. 现场DBA也在诊断,开始在我诊断的基础上,进行分析后,判断是否还缺少其他权限,于是他执行另一个授权

SQL>grant create any table to XXX_USER;

授权重新清理,一样不行.

9.

最后,终于在前面的诊断分析中的rename对象上引起了思考,结合报错信息,就去看了被重名的对象表上的属性,发现还有两个索引在其上;

于是就手工把这两个索引删除.

10. 删除索引后,重建队列表成功,队列启动正常,应用启动后,验证一切OK.

注解:

此次故障,是迁移忽略了AQ队列表问题,引起.

处理时,因队列表都不是特别熟悉,因而在诊断处理时,到了点上,却没有完全理解透队列的相关特性,而致多走弯路,浪费时间.

从这也了解到ORACLE队列表创建,命名空间和用户模式下的其他对象是共享的,创建队列表及其附属索引,不是随机地采用系统名,而是用约定俗称的名给对象命名的,因此后续重建,需要清理其相关对象.