Skip to Main Content
  • Questions

  • Can we have extra tables in Active Data guard replicated DB

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ashwin.

Asked: April 11, 2016 - 6:46 pm UTC

Last updated: April 27, 2016 - 4:16 am UTC

Version: 11g R2 EXADATA

Viewed 1000+ times

You Asked

Hi,

we have 11g R2 Oracle DB with ExaData. We want to create a separate Reporting Layer with real time replication of data.
Suppose we use Oracle Active Data Guard Can we have some 20 extra tables in the replicated DB along with our replicated OLTP tables and views?

Regards
Ashwin

and Connor said...

From the docs:

"Redo generation on a read-only database is not allowed"

so you're out of luck.

Once you get to 12c, then you have global temporary tables in the DG database, which might be of assistance to you.

A potential workaround would have to:

- have the 20 tables stored in a 'normal' database on the dataguard node
- have a database link in the primary that points to that db, which hence will be mirrored into the standby.
- incorporate those 20 tables into standby queries using the database links.

I stress "potential" because joins across database links are most definitely not the same a joins within the same database.

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Need clarification

Ashwin Shah, April 25, 2016 - 11:04 am UTC

Hi Connor,

http://www.oracle-wiki.net/startdocsdataguardfeatures
http://www.databasejournal.com/features/oracle/article.php/3860251/Leveraging-Logical-Standby-Databases-in-Oracle-11g-Data-Guard.htm

I have been reading few blogs on Oracle data Guard 11g version. The above blogs says that we can have new schema and tables created in Oracle DG replicated environment if you use Logical Standby Database.

Can you please confirm whether using Logical Standby Database for Oracle Active data guard 11g R2 will it allow us to create new schema or few tables or materialized views in the Oracle Active data guard replicated Data base server?

Regards
Ashwin Shah
Connor McDonald
April 27, 2016 - 4:16 am UTC

OK, different technologies in use here.

Active Dataguard provides a read-only copy of your data, that is continuously recovered to be in alignment with your primary database. It is read-only. In 12c, you can have read/write global temporary tables in there, but that's about it. That's because the recovery is being done in "normal" fashion, that is, applying redo logs to the standby database.

Logical Standby is more like a *replica* of your primary. (Conceptually) we use the redo logs, to recreate the SQL's that were run against the primary, and run them against this second database (the logical standby). Because of this, the logical standby is actually a distinct database - it can be read/write, and diverge from the primary...So you get read/write, but there are also more restrictions and more complexity to setup and maintain.

So you can choose what best suits you.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.