oracle 11g standby query error: ora-08103 and ora-01410
作者：fcp | 分类： 大话技术 | 标签： 大话技术 | 日期：2008-09-03
范鑫做的测试，我转过来。看起来很简单的一件事情，由于具有偶然性，不能每次都重现，所以特地记录下来，一直报的 ORA-08103: object no longer exists 是由于 standby 上的查询进程导致 ，把standby 激活后查询就正常了
当然这里要交代一下前提，就是在主数据库上有一个job，每天晚上将一个表truncate再插入数据，第二天就发现standby上查询错误，在主数据库上move表之后恢复正常。 但是手工做 truncate却取法重现，重新做个job任务偶尔重现，也不是一定得到这个现象。目前正在进一步测试中，但至少发现了 11g的 standby提供适时查询功能是存在缺陷的。当然不truncate就没问题。
测试在一个已经存在问题的standby上进行，先以standby模式open，再open read only，最后激活open：
oracle@ctr_db1:/home/oracle>export ORACLE_SID=testctrdmsb2
oracle@ctr_db1:/home/oracle>sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Sep 3 10:28:45 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
@>
@>sqlplus /nolog
SP2-0734: unknown command beginning “sqlplus /n…” - rest of line ignored.
@>conn / as sysdba
Connected.
sys@CTRDM_DB1>
sys@CTRDM_DB1>select count(*) from mcc.sync_job_status;
select count(*) from mcc.sync_job_status
*
ERROR at line 1:
ORA-01410: invalid ROWID
sys@CTRDM_DB1>select count(*) from mcc.testd2;
COUNT(*)
———-
1127
sys@CTRDM_DB1>
sys@CTRDM_DB1>
sys@CTRDM_DB1>
sys@CTRDM_DB1>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@CTRDM_DB1>startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 2004340736 bytes
Fixed Size 2145744 bytes
Variable Size 402653744 bytes
Database Buffers 1593835520 bytes
Redo Buffers 5705728 bytes
Database mounted.
sys@CTRDM_DB1>alter database open ;
Database altered.
sys@CTRDM_DB1>select count(*) from mcc.sync_job_status;
select count(*) from mcc.sync_job_status
*
ERROR at line 1:
ORA-08103: object no longer exists
sys@CTRDM_DB1>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@CTRDM_DB1>startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 2004340736 bytes
Fixed Size 2145744 bytes
Variable Size 402653744 bytes
Database Buffers 1593835520 bytes
Redo Buffers 5705728 bytes
Database mounted.
sys@CTRDM_DB1>alter database open read only;
Database altered.
sys@CTRDM_DB1>select count(*) from mcc.sync_job_status;
select count(*) from mcc.sync_job_status
*
ERROR at line 1:
ORA-08103: object no longer exists
sys@CTRDM_DB1>
sys@CTRDM_DB1>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@CTRDM_DB1>
sys@CTRDM_DB1>
sys@CTRDM_DB1>
sys@CTRDM_DB1>startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 2004340736 bytes
Fixed Size 2145744 bytes
Variable Size 402653744 bytes
Database Buffers 1593835520 bytes
Redo Buffers 5705728 bytes
Database mounted.
sys@CTRDM_DB1>alter database open;
Database altered.
sys@CTRDM_DB1>select count(*) from mcc.sync_job_status;
COUNT(*)
———-
25
sys@CTRDM_DB1>
