DB Links from Readonly databases.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: DB Links from Readonly databases.

Hybrid View

  1. #1
    Join Date
    May 2007
    Posts
    1

    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.

  2. #2
    Join Date
    Nov 2001
    Posts
    335
    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!

  3. #3
    Join Date
    Jun 2006
    Posts
    259
    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.

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

  5. #5
    Join Date
    Jun 2006
    Posts
    259
    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.
    Last edited by ixion; 06-01-2007 at 02:24 PM.

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    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.

  7. #7
    Join Date
    Jun 2006
    Posts
    259
    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.

  8. #8
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width