本文共 4170 字,大约阅读时间需要 13 分钟。
先是在主库上对justin执行DML操作,涉及数据不到两万条,却因此导致备库延迟;
备库上对应的justin的上没有任何索引,而该表有600多万数据,生成库上的操作到此就演化为对该表全表扫描将近两万次,无怪乎备库会延迟;当时为了避免这种状况再次发生,在备库执行了create index indx_justin_number on justin(justin_number)online却因为基于该表的DML没有结束而排队等待,最后将此创建索引操作kill;问题来了,明明该列没有索引,再次执行却报以下错误SQL> create index indx_justin_number on justin(justin_number) online; create index indx_justin_number on justin(justin_number) online ORA-01408: such column list already indexedSQL> drop table justin; drop table justin ORA-08104: this index object 405828 is being online built or rebuilt此时要停止逻辑备库应用也被阻塞了Tue Sep 20 16:47:25 CST 2011alter database stop logical standby applyTue Sep 20 16:47:25 CST 2011ALTER DATABASE STOP LOGICAL STANDBY APPLYTue Sep 20 16:47:25 CST 2011LOGSTDBY: Shutdown acknowledgedTue Sep 20 16:48:51 CST 2011RFS LogMiner: Client enabled and ready for notificationTue Sep 20 16:48:51 CST 2011Primary database is in MAXIMUM PERFORMANCE modeRFS[6]: Successfully opened standby log 15: '/data/oracle/oradata/justin/stdby_redo15.log'Tue Sep 20 16:48:53 CST 2011Primary database is in MAXIMUM PERFORMANCE modeRFS[23]: Successfully opened standby log 26: '/data/oracle/oradata/justin/stdby_redo26.log'Tue Sep 20 16:48:55 CST 2011RFS LogMiner: Registered logfile [/data/oracle/oradata/justin/arch/2_6591_657561562.dbf] to LogMiner session id [1]Tue Sep 20 16:48:55 CST 2011RFS LogMiner: Client enabled and ready for notificationTue Sep 20 16:48:56 CST 2011RFS LogMiner: Registered logfile [/data/oracle/oradata/justin/arch/4_2617_657561562.dbf] to LogMiner session id [1]Tue Sep 20 16:52:10 CST 2011alter database stop logical standby applyTue Sep 20 16:52:10 CST 2011ALTER DATABASE STOP LOGICAL STANDBY APPLY找到目前正在更新表justin的session,将其kill后方才成功将逻辑备库停掉; 再说说ORA-08104这是rebuild online被手工kill常见的现象,解决方案很简单。先找到该索引的object_idSQL> select object_id from dba_objects where object_name ='IND_justin_NUMBER'; OBJECT_ID---------- 405828然后通过sys用户执行SQL> declare 2 v_clean boolean; 3 begin 4 v_clean := FALSE; 5 v_clean := dbms_repair.online_index_clean(405828,dbms_repair.lock_wait); 6 end; 7 /PL/SQL procedure successfully completed.
此时以上错误便可解决,可再度在此列上创建索引关于ora-8104,metalink有很多文章介绍
Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1]________________________________________ 修改时间 29-APR-2010 类型 PROBLEM 状态 MODERATED In this Document Symptoms Cause Solution References________________________________________Platforms: 1-914CU; This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.6 to 11.1.0.6 - Release: 9.2 to 11.1Information in this document applies to any platform.SymptomsWhile running an online index rebuild your session was killed or otherwise terminated abnormally. You are now attempting to run the index rebuild again and is throwing the error:ORA-08104: this index object ##### is being online built or rebuilt
CauseA session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not.SolutionThe dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. Use the dbms_repair.online_index_clean function to resolve the issue.* Please note if you are unable to run the dbms_repair.online_index_clean function it is due to the fact that you have not installed the patch for Bug 3805539 or are not running on a release that includes this fix. The fix for this bug is a new function in the dbms_repair package called dbms_repair.online_index_clean, which has been created to cleanup online index [[sub]partition] [re]builds. New functionality is not allowed in patchsets; therefore, this is not available in a patchset but is available in 10gR2.- Check your patch list to verify the database is patched for Bug 3805539 using the following command and patch for the bug if it is not listed: opatch lsinventory -detail来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-707970/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-707970/