DB Links from Readonly databases.

Printable View

05-30-2007, 07:08 AM
ranju_bk

DB Links from Readonly databases.

Hi,

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 8.1.7.4.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.
Ranjini.
05-30-2007, 10:35 AM
BV1963

Even single query like select 'xxx' from dual@DBLINK does not work?
05-31-2007, 06:48 PM
ixion

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.
06-01-2007, 05:21 AM
JMac

Quote:

Originally Posted by ixion

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.

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?
06-01-2007, 01:31 PM
ixion

Quote:

Originally Posted by JMac

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.

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.
06-01-2007, 02:13 PM
ixion

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.
06-01-2007, 02:23 PM
ixion

Quote:

Originally Posted by ranju_bk

Hi,
Is it true that we cant query a remote DB from a readonly DB using DB links?

Yes that is TRUE, you can not use a remote query from a readonly DB using DB links!

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.
06-04-2007, 06:05 AM
waitecj

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).