Solution for R/W Splitting on Oracle with Data Guard

Question

Our company is using Oracle11g database and Data Guard. Our DBA told us that the secondary database (backup by Data Guard) is read only.

The issue is we have some reports which need write data into temporary table, so our DBA told us we can't let the reports ran on the secondary database since secondary database is read only. But we don't want let the reports ran on the primary database because we have some online transactions. The reports will impact our OLTP performance.

We just want to R/W Splitting on the Oracle database, are there any solutions?

Answer 1

Your DBA is correct, with Active Data Guard, the standby database can only be used for read-only access.

There are solutions for active-active high availability. One is Golden Gate, information here.

Answer 2

1. You can switch db connections at client side. Depends on the technology you use, there're many methods to do so, but at least there're a simple method which you can get connection to master and slave by your code.

2. dblinks. Create dblinks in slave DB, then you can access master DB in slave DB. So your code connect to slave DB for reading, and write to master DB if you use dblinks, for example: insert ... into schema.test@masterDB