Re: Weird read-only error Solaris 10, 10.2.0.2

From: Yong Huang <yong321_at_yahoo.com>
Date: Fri, 9 Oct 2009 09:57:51 -0700 (PDT)
Message-ID: <105646.97343.qm_at_web80602.mail.mud.yahoo.com>



> We have a daily clone that we open in read-only mode. User reports the
> following error:
>
> select * from t_sap_cd
>
> ORA-00604: error occurred at recursive SQL level 1
>
> ORA-16000: database open for read-only access

With some private emails, Chris told me what happened and I think it's interesting to everybody. The database is open read only. But the status of the synonym is still invalid (beginning with 10g, a synonym can become invalid), due to an earlier specification change of the object the synonym points at. When you later use the synonym, as in select * from this_synonym or even desc this_synonym, the status must be changed to valid because the translated object perfectly exists. But because the database is read only, its status in the system tablespace is frozen, hence the error.

If I were at it, I would probably alter session to enable SQL trace (not set autotrace on) and systematically troubleshoot the issue, unless I already thought of it. The trace file should have "err=16000" in it.

Yong Huang       

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 09 2009 - 11:57:51 CDT

Original text of this message