Database Links and Read Only Databases

One of my colleagues asked me that he gets an error when he tries to query a table on our standby database. We use one of our physical standby database for reporting. It’s Oracle 10g, so we stop log apply and open the database in read only mode.

My colleague tried to run such an query:

He got “ORA-16000 database link read only query fails” error.

At first, I though that it’s about joining tables but after I simpled the query and tried to select from only one table but I still get the same error. Then I found the solution at Tom Kyte’s website.

Distributed stuff starts a transaction “just in case”.

So we should indicate that our transaction will not manipulate any row:

This will not prevent Oracle to create a new transaction, instead it establishes the current transaction as a read-only transaction.

Please share this post Share on Facebook0Share on Google+0Share on LinkedIn0Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

1 Comment

  1. Mohammed

    Hi Team,

    How to Apply archive log file gaps in standby database.

    Let me know the steps.

    Regards,

    Mohammed Siddiq

Leave Comment

Your email address will not be published. Required fields are marked *