DB Links from Readonly databases.
I have this issue with DB links from a readonly standby database.
tried to select from another DB through a link,but it errors with the follwoing message:
ERROR at line 5:
ORA-00372: file 1 cannot be modified at this time
ORA-01110: data file 1: '/exxx_dataxxx/xxxx/system_01.dbf'
This is a 18.104.22.168.0 database.
The same happens with a 10G R1 instance as well,but the error message is :
ERROR at line 1:
ORA-16000: database open for read-only access
I have verified that the links are created properly.
Is it true that we cant query a remote DB from a readonly DB using DB links?
What could the errors mean?
Thanks in advance.
Even single query like select 'xxx' from dual@DBLINK does not work?
One, who thinks that the other one who thinks that know and does not know, does not know either!
I think the reason is that the DB is read only. And your select is attempting to modify the database.
A distributed query naturally starts a transaction for read consistent purposes.
Try logging on locally and run the select without the db link.
Originally Posted by ixion
Out of interest: is that transaction local (in the DB where the transaction is issued), or remote (the DB the link points to)? Because this will generate REDO, no?
The transaction is local, where the session is located, not on the db link database.
Originally Posted by JMac
Yes it does generate a little bit of redo since a transaction is started. However the acutall select does not generate any redo. Its always a good idea to issue a commit after this type of thing to release the transaction. I was very surprised by this behavior, a select statement starting a transaction.
Last edited by ixion; 06-01-2007 at 02:24 PM.
As an addendum:
It also does a manipulation of sorts on the DB link target as well. This data is not visible from a simple select from v$transaction. I found this through direct interegation of the internal structures.
Yes that is TRUE, you can not use a remote query from a readonly DB using DB links!
Originally Posted by ranju_bk
The reason is as I posted above, the query begins a transaction on your DB which is in read only mode. Thus the query fails.
Create a read-write database with nothing in it but database links to the read only database and appropriate synonyms if needed (to mask the database link from the user).
Assistance is Futile...
Click Here to Expand Forum to Full Width