ORA-01410 INVAILD ROWID 无解之题啊

标题的错误代码帮你改了下...这个错误是发生在备库吗？还是主库
在standby上做查询操作时触发的，然后flush standby的buffer cache，并在primary重建索引解决的？

发生在备库。在主库重建了索引。在备库flush buffer cache

看起来和Bug 8740993很接近啊Here is the scenario:

(1) Standby queries table T1, which brings buffer X into the buffer cache.
(2) Primary drops/truncates/shrinks table T1.
(3) Standby applies the redo to drop/truncates/shrinks T1 as well.
(4) DROP case: Primary creates a new table T2 and inserts into T2,
    which uses the space that was previously used by T1.
    TRUNCATE/SHRINK case: Primary inserts more rows into T1,
    which uses the space that was previously used by T1.
(5) Standby applies the redo in step (4).
(6) A query is run to scan T2/T1, which finds buffer X in the buffer
    cache (provided that buffer X has not yet aged out).  It leads to
    ORA-1410.检查下是否发生了drop/truncates/shrinks
11.2.0.1的bug有点多，可以的话，升级到11.2.0.3

我碰上过的这种错误基本上是和索引的重建有关，比如说在一条SQL正在执行未完成的时候，它所使用的索引被rebuild online完成，这时候就会报这个错误。你可以先在备库上按last_ddl_time逆序查询看是不是有新ddl产生导致的错误，如果是的话，也许可以在应用程序层面解决。

是的很接近！这个库运行的比较久远了。上面有6个SCHMA 最近把其中一个交易量大的用户迁走了。过了几周后，我把该用户给DROP USER CCPS 。而发生ORA的是CCPS1，另外还有个CCPS2用户并没有发生问题。
不会是我DROP USER引起来的吧！

谢谢提醒，第一次发生在7月23日然后我在主库重建了索引。再一次发生在一周后！我FLASH BUFFER CACHE了
不知道能不能找到原因所在？

OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_TIME
CCPS1 CCPS_MAXMIND_OUTPUTS TABLE 2014-07-30 1124:0I:44
CCPS1 CCPS_CREDITINFO TABLE 2014-07-30 1024:58I:01
CCPS1 IX_CI_EMAIL INDEX 2014-07-30 1024:57I:59
CCPS1 IX_CI_DATETIME INDEX 2014-07-30 1024:57I:59
CCPS1 PK_CCPS_CREDITINFO INDEX 2014-07-30 1024:57I:05
CCPS1 CCPS_MER_TEL_VALIDATION_SEQ SEQUENCE 2014-07-29 1724:14I:25
CCPS1 IX_TV_MER_NO INDEX 2014-07-29 1724:12I:18
CCPS1 IX_TV_GW_NO INDEX 2014-07-29 1724:12I:18
CCPS1 PK_CCPS_MER_TEL_VALIDATION INDEX 2014-07-29 1724:12I:18
CCPS1 CCPS_MER_TEL_VALIDATION TABLE 2014-07-29 1724:12I:18
CCPS1 CCPS_INTERFACE_PARAMNAME_SEQ SEQUENCE 2014-07-23 1524:17I:59
CCPS1 PK_CCPS_INTERFACE_PARAMNAME INDEX 2014-07-23 1524:17I:58
CCPS1 CCPS_INTERFACE_PARAMNAME TABLE 2014-07-23 1524:17I:58
CCPS1 SYS_SMS TABLE 2014-07-22 2124:32I:00
CCPS1 SYS_SMS_SEQ SEQUENCE 2014-07-22 2124:31I:12
CCPS1 IX_TR_BANKORDERNO INDEX 2014-07-03 1624:56I:38
CCPS1 CCPS_TRADERECORD TABLE 2014-07-03 1624:56I:38

如果是这个bug，按文档描述里说的，buffer cache flush过了以后应该不会再和删除用户有关系了
重建索引有效果的话，按4楼说的，检查下索引是否进行了相关操作

说明下分别发生了2次，同一个用的两个不同的表！
第一个表发生在7月22号 select count(*) from ccps_xxxx  发现是主关键字的索引。然后我在主库重建了索引。
第二个表发生在8月2日是段语句LEFT JOIN table     其上面没有索引，所以在备库上FLASH BUFFER CACHE根据BUG的提示: 对该表做过DROP  TRUCATE SHRINKOWNER    OBJECT_NAME                                               OBJECT_TYPE    LAST_DDL_TIME
CCPS1    CCPS_MAXMIND_OUTPUTS                          TABLE    2014-07-30 1124:0I:44
CCPS1    CCPS_CREDITINFO                                         TABLE    2014-07-30 1024:58I:01
CCPS1    IX_CI_EMAIL                                                       INDEX    2014-07-30 1024:57I:59
CCPS1    IX_CI_DATETIME                                               INDEX    2014-07-30 1024:57I:59
CCPS1    PK_CCPS_CREDITINFO                                INDEX    2014-07-30 1024:57I:05
CCPS1    CCPS_MER_TEL_VALIDATION_SEQ         SEQUENCE    2014-07-29 1724:14I:25
CCPS1    IX_TV_MER_NO                                                INDEX    2014-07-29 1724:12I:18
CCPS1    IX_TV_GW_NO                                                  INDEX    2014-07-29 1724:12I:18
CCPS1    PK_CCPS_MER_TEL_VALIDATION              INDEX    2014-07-29 1724:12I:18
CCPS1    CCPS_MER_TEL_VALIDATION                     TABLE    2014-07-29 1724:12I:18
CCPS1    CCPS_INTERFACE_PARAMNAME_SEQ    SEQUENCE    2014-07-23 1524:17I:59
CCPS1    PK_CCPS_INTERFACE_PARAMNAME       INDEX    2014-07-23 1524:17I:58
CCPS1    CCPS_INTERFACE_PARAMNAME              TABLE    2014-07-23 1524:17I:58
CCPS1    SYS_SMS                                                          TABLE    2014-07-22 2124:32I:00
CCPS1    SYS_SMS_SEQ                                                SEQUENCE    2014-07-22 2124:31I:12
CCPS1    IX_TR_BANKORDERNO                                 INDEX    2014-07-03 1624:56I:38
CCPS1    CCPS_TRADERECORD                                 TABLE    2014-07-03 1624:56I:38
没metlink帐号  无法看到具体的BUG说明。

Bug 8740993  ORA-1410 / ORA-8103 on ADG STANDBY during table scan after DROP/TRUNCATE/SHRINK in PRIMARY
This note gives a brief overview of bug 8740993.
The content was last updated on: 28-JUN-2013
Click here for details of each of the sections below.Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 11.1 but BELOW 12.1
Versions confirmed as being affected •11.2.0.1
•11.1.0.7

Platforms affected Generic (all / most platforms affected)  Fixed:
This issue is fixed in •12.1.0.1 (Base Release)
•11.2.0.2 (Server Patch Set)
•11.1.0.7.8 Database Patch Set Update
•11.1.0.7 Patch 40 on Windows Platforms
Symptoms: Related To:
•Error May Occur
•ORA-1410 / ORA-8103
•Active Dataguard (ADG)
•Space Management (Locally Managed Tablespaces)
•Physical Standby Database / Dataguard
•Truncate
•_query_on_physical
Description
ORA-1410 during full table scan on Active Dataguard (ADG) STANDBY database
during table scan after DROP/TRUNCATE/SHRINK in PRIMARY database

Here is the scenario:

(1) Standby queries table T1, which brings buffer X into the buffer cache.
(2) Primary drops/truncates/shrinks table T1.
(3) Standby applies the redo to drop/truncates/shrinks T1 as well.
(4) DROP case: Primary creates a new table T2 and inserts into T2,
    which uses the space that was previously used by T1.
    TRUNCATE/SHRINK case: Primary inserts more rows into T1,
    which uses the space that was previously used by T1.
(5) Standby applies the redo in step (4).
(6) A query is run to scan T2/T1, which finds buffer X in the buffer
    cache (provided that buffer X has not yet aged out).  It leads to
    ORA-1410.

Note that ORA-1410 is intermittent.

Workaround

  Flush the buffer cache and retry the query

This may also produces an ORA-8103 error when:

- compatible less than 11.0
- a full table scan query fails with:
      ORA-08103: object no longer exists
- the ORA-8103 error is 100% repeatable (so long as the
   table does not change)
- the fix of bug 7650993 is installed but it does not help
- the table is stored in an ASSM tablespace ie the following
- a dump of the segment space metadata shows there is at
   least one unformatted block under the HiHWM. p);

Workaround

In version 11.2 set the static hidden parameter "_query_on_physical=FALSE"
in the standby init.ora and open the standby read only. The previously
failing standby queries should then return the correct results.\

Note that setting "_query_on_physical=FALSE" disables the ADG option and
startup is not allowed if MRP is running.  If startup is attempted while MRP
is running, ORA-16669 is produced (instance cannot be opened because the
Active Data Guard option is disabled).

谢谢版主！
怎么说来
Fixed:
This issue is fixed in •12.1.0.1 (Base Release) 我这个版本已经被FIXED了

吓我一跳，12c都上生产了。你们用的是11.2.0.1，在11.2.0.2中fixed

你知道公司生产运行的什么版本就会一直运行下去。上家的是10G 现在的是11G
稳定的一般都不会升级大版本啊。顶多停机打下补丁。下面的英文我看错了不好意思 12.1.0.1 看成了 11.2.0.1.0 去了。
Fixed:
This issue is fixed in •12.1.0.1 (Base Release)
•11.2.0.2 (Server Patch Set)

调试易

ORA-01410 INVAILD ROWID 无解之题啊

解决方案 »