ORA-23616: Failure in executing block 3 for script 删除queue的方法

简介: <div> <div><br></div> <div><span style="color:#cc66cc">ERROR at line 1:</span></div> <div><span style="color:#cc66cc">ORA-<span style="border-bottom-width:1px; border-bottom-style:dashed; borde

ERROR at line 1:
ORA-23616: Failure in executing block 3 for script
E45713597D0EFA08E04010AC280A05F2
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 593
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 616
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 2279
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 7581
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2606
ORA-06512: at line 6


----------------对问题继续追踪.

1 查询错误
 select * from dba_recoverable_script_errors; 
SELECT * FROM DBA_RECOVERABLE_SCRIPT_BLOCKS WHERE BLOCK_NUM=177

2  查询错误信息
  select invoking_package_owner as owner,
  invoking_package as package,
  invoking_procedure as procedure,
  status,
  total_blocks,
  done_block_num
  from dba_recoverable_script
  where script_id=' ';

3 逻辑上步骤 查询第几步的错误信息
  select forward_block,forward_block_dblink
  from dba_recoverable_script_blocks
  where script_id=''  and block_num= ;

4 查询 13步骤的错误具体是什么
  select error_number,error_message
  from dba_recoverable_script_errors
  where script_id='E46B691F109E39F5E 04010AC280A0B83' and block_num=13;

ERROR_NUMBER
------------
ERROR_MESSAGE
--------------------------------------------------------------------------------
      - 39001
ORA- 39001: invalid argument value


5  强制清楚脚本.

begin
 dbms_streams_adm.recover_operation(
   script_id => 'E46B691F109E39F5E 04010AC280A0B83',
   operation_mode => 'PURGE');
end;
/



原因是游标查询返回多条记录,而实际通过select 列明 into 变量 FROM 表A where ···

插入变量的信息是单个变量,不是多条记录,故产生了如下的错误

ORA-01422: exact fetch returns more than requested number of rows


-------------------

以strmadmin用户登录:


一、    抓取进程


SQL> select capture_name,status from dba_capture;


CAPTURE_NAME        STATUS

------------------------------ --------

CAPTURE_BOOK        ENABLED


col status for a10

col QUEUE_NAME for a15

col RULE_SET_NAME for a15

col NEGATIVE_RULE_SET_NAME for a15


SELECT  capture_name,queue_name,RULE_SET_NAME,NEGATIVE_RULE_SET_NAME,status from dba_capture;


SELECT CAPTURE_NAME,QUEUE_NAME,STATUS,CAPTURED_SCN,APPLIED_SCN FROM DBA_CAPTURE;

SELECT apply_name,apply_captured,status FROM dba_apply;


停止

SQL> exec dbms_capture_adm.stop_capture('CAPTURE_BOOK');


PL/SQL procedure successfully completed.

清除

SQL> exec dbms_capture_adm.drop_capture('CAPTURE_BOOK');


PL/SQL procedure successfully completed.

查询

SQL> select capture_name,status from dba_capture;


no rows selected


二、    传播进程


SQL> select propagation_name,status from dba_propagation;


PROPAGATION_NAME        STATUS

------------------------------ --------

BOOK_TO_CISCOSYS        ENABLED

查询

SQL> exec dbms_propagation_adm.stop_propagation('BOOK_TO_CISCOSYS');


PL/SQL procedure successfully completed.

删除

SQL> exec dbms_propagation_adm.drop_propagation('BOOK_TO_CISCOSYS');


PL/SQL procedure successfully completed.

查询

SQL> select propagation_name,status from dba_propagation;


no rows selected


三、    应用进程

SQL> select apply_name,status from dba_apply;


APPLY_NAME        STATUS

------------------------------ --------

APPLY_BOOK        ENABLED

APPLY_BOOK2        ENABLED

APPLY$_CISCOSYS_21        DISABLED


停止

SQL> exec dbms_apply_adm.stop_apply('APPLY_BOOK');

ERROR at line 1:

                     ORA-26663: error queue for apply process APPLY_RACDB_QH must be empty


exec DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name=>'TARGET_APPLY_STREAM')

再次执行可以
 exec dbms_apply_adm.drop_apply('TARGET_APPLY_STREAM');


PL/SQL procedure successfully completed.


删除


SQL> exec dbms_apply_adm.drop_apply('APPLY$_CISCOSYS_21');

PL/SQL procedure successfully completed.


SQL> select apply_name,status from dba_apply;


no rows selected


四、    删除源环境

SQL> exec dbms_streams_adm.remove_streams_configuration;


总之这个清除过程相当复杂,所以类似的操作应当选择在数据库空闲时段来完成,以避免对数据库产生性能冲击,切记切记!



五、   上面只是删除了抓取进程 传播进程 应用进程的名字而已。 

         对于数据库参数、数据库连接、流复制用户、表空间、测试用户、授予的权限都没有删除 

         优点 这个是备用的。以前不懂这些,我直接DBCA干掉库之后重新建,这样就很麻烦。倒是,如果练手的

         还可以,以后对于练手的活,重新装就是了,熟练之后就好了。


一、

         删除抓取进程 传播进程 应用进程的名字而已。 

二、

         对于1数据库参数、2数据库连接、3流复制用户、4表空间、5测试用户、6授予的权限都没有删除

三、   

         用户下的表  级联删除

四、   

         文件的物理路径  /arch/scott.tmp 数据文件表空间物理路径 


五、  已经存在的队列

SQL> col owner for a10

SQL> col name for a24

SQL> col queue_table for a20


                               可以用查询dba_queues,dba_queue_tables来检查:

SQL>  select owner,queue_table,name from dba_queues where owner='STRMADMIN';

OWNER               QUEUE_TABLE              NAME

----------------- ------------------------- ------------------

STRMADMIN          SOURCES_QUEUE_TABLE        SOURCES_QUEUE

STRMADMIN          SOURCES_QUEUE_TABLE        AQ$_SOURCES_QUEUE_TABLE_E

 


SQL>select owner,queue_table,object_type from dba_queue_tables where owner='STRMADMIN';

OWNER            QUEUE_TABLE           OBJECT_TYPE

-------------- --------------------- ------------------

STRMADMIN       SOURCES_QUEUE_TABLE     SYS.ANYDATA


删除 QUEUE_TABLE 的方法

ORA-24005:必须使用DBMS_AQADM.DROP_QUEUE_TABLE删除队列表 的解决方法

 

 SOURCES_QUEUE_TABLE


exec DBMS_AQADM.DROP_QUEUE_TABLE('QHMES_QUEUE_TABLE');


删除queue的方法


exec dbms_aqadm.stop_queue('source_queue');

exec dbms_aqadm.drop_queue('source_queue');



我创建queue和删除queue的方法,如下

SQL code
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--创建queue语法
begin
     dbms_streams_adm.set_up_queue(
         queue_table=> 'source_queue_table' ,
         queue_name => 'source_queue'
     );
end ;
/
--删除queue语法
--首先stop queue, 报错:
SQL>  exec  dbms_aqadm.stop_queue( 'source_queue' );
BEGIN  dbms_aqadm.stop_queue( 'source_queue' );  END ;
 
*
ERROR  at  line 1:
ORA-04063: U_DL_STREAM.SOURCE_QUEUE has errors
ORA-06512:  at  "SYS.DBMS_AQADM_SYS" , line 4913
ORA-06512:  at  "SYS.DBMS_AQADM" , line 240
ORA-06512:  at  line 1


做与此队列相关的操作都会报上面的错误

查询dba_objects发现,此queue的状态为 INVALID


可以通过:

dba_apply

v$streams_apply_reader

v$streams_apply_coordinator

v$streams_apply_server

查看状态


目录
打赏
0
0
0
0
38
分享
相关文章
成功解决“Run-Time Check Failure #2 - Stack around the variable ‘arr‘ was corrupted.“问题
成功解决“Run-Time Check Failure #2 - Stack around the variable ‘arr‘ was corrupted.“问题
439 1
报错modify sync object Modify sync object Failed!
报错modify sync object Modify sync object Failed!
51 1
Install fail! Error: EBUSY: resource busy or locked, symlink
Install fail! Error: EBUSY: resource busy or locked, symlink
137 0
Do Sync Disk 0 Part 0 Failed, code=S3_F42, msg=Sync Failed after retry 5 times
Do Sync Disk 0 Part 0 Failed, code=S3_F42, msg=Sync Failed after retry 5 times
207 1
22-大厂面试题:Con-current Mode Failure如何导致以及解决
上文我们已经介绍了CMS垃圾收集器的工作原理以及流程,本篇我们接着深入说明CMS垃圾收集器的缺点以及所导致的一些问题应该如何解决。
310 0
Redisson BUG: Failed to submit a listener notification task. Event loop shut down?
Redisson BUG: Failed to submit a listener notification task. Event loop shut down?
1564 0
ES报错:“type“=>“cluster_block_exception“, “reason“=>“blocked by: [FORBIDDEN/12/index read-only / allow
ES报错:“type“=>“cluster_block_exception“, “reason“=>“blocked by: [FORBIDDEN/12/index read-only / allow
444 0
ES报错:“type“=>“cluster_block_exception“, “reason“=>“blocked by: [FORBIDDEN/12/index read-only / allow
Error:svn:E155037:Previous operation has not finished; run ‘cleanup‘ if it was interrupted(完美解决)
Error:svn:E155037:Previous operation has not finished; run ‘cleanup‘ if it was interrupted(完美解决)
469 0
Error:svn:E155037:Previous operation has not finished; run ‘cleanup‘ if it was interrupted(完美解决)
[Error] ‘for‘ loop initial declarations are only allowed in C99 or C11 mode 解决方法
[Error] ‘for’ loop initial declarations are only allowed in C99 or C11 mode [Note] use option -std=c99,-std=gnu99,-std=c11 or-std=gnu11 to compile your code
1585 0
[Error] ‘for‘ loop initial declarations are only allowed in C99 or C11 mode 解决方法