Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Oracle - Two Database Replication with Oracle Streams - Can it be done with Oracle Standard Edition

Posted on 2014-04-11
Medium Priority
Last Modified: 2014-04-14

We have a high transaction Oracle Database (running 11g Enterprise) that supports an automated sorting facility.  We are trying to figure out the best way to replicate the data from that database to a separate database (running Oracle 11g Standard) for reporting purposes.  One option that was proposed was using the Oracle Streams feature (tutorial below)...

Tutorial: Configuring Two-Database Replication with a Downstream Capture Process

This sounds promising, but I am no Oracle expert by any means... does anyone know if this setup can be used if the production database is running Enterprise Edition, and the reporting database is running Standard Edition?


Question by:John Parker
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39994754
Based on the docs here:

It implies that it might be possible with Standard Edition:

If you are using Oracle Database 11g Standard Edition, then synchronous capture is the only Oracle Streams component that can capture database changes automatically. To use capture processes, you must have Oracle Database 11g Enterprise Edition.

There might be a cost associated with it.  I'm pretty sure it is only 'free' with Enterprise Edition.

The License docs also say Streams is 'available' in Standard Edition:

Author Comment

by:John Parker
ID: 39995017
Thank you sir!  Have you every delt with this type of replication in a production environment before?  Any thoughts or advise?  The other option we were looking at was doing some type of Data Pump from the production Server to the reporting server once a day, after the operation was complete...  Do you know if that would work between Enterprise and Standard?
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39995199
Before you get "too focused" on Oracle EE, you might want to take a look at this 3rd party solution (just as I/we did recently)... And we were very surprised how easy replication could be (in an Oracle world) ;-) ... compared to using (Advanced) Streams or even Golden Gate... Apart from that, also take a CLOSE look at the costs :-)) plus you're able to use this tool with Oracle SE/SE One...

P.S.: yes, for sure, if I/we had the/too much money left, we surely would love to use Oracle EE, but do you really want to pay that much "just" for using 1 or 2 features that can be replaced as some kind of really good trade off?!?

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:John Parker
ID: 39995277
Thank you, Alex!  I appreciate your input.  Yes, I'm looking at purchasing EE as a last resort... the cost is tremendous and not in our budget.  I'll take a look at the link you provided.  Thanks!
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39995342
I currently use Streams Replication in production and am very happy with it.  However, I'm on Enterprise Edition.

Prior to Streams I used Oracle's Multi-Master Advanced Replication and hated it.  It worked but always seemed to give me problems.

Streams has its moments from time to time but nothing major and typically easily resolved.

I'm not sure I would go with export/import.  The reason for replication is real-time.  If you don't need real time, do you need replication?  If a daily 'feed' will work then maybe exp/imp might work for you and would definitely be a zero cost solution if the Streams Replication with SE (No redo capture) will not work for you.

I trust Alex's opinion so if he says a product is worth looking at, I would look at it.  I've never heard of it before so cannot comment on it or any 3rd party tool.

You might also reach out to your Oracle Account team.  They should be able to offer you suggestions on what options you have available.
LVL 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 800 total points
ID: 39995878
Just as a reminder/footnote: Don't forget to think about "your" future / the future of your company (regarding the use of Oracle DBs):
As you can see, even very nice features/functions like Oracle (Advanced) Streams will be deprecated :-(
And this will become a huge problem if you're depending on such things like replication: 'cause Golden Gate won't come for free neither ;-)

Author Comment

by:John Parker
ID: 39996826
Thank you both for your input!  Your comments are very appreciated.  Steve, one more question for you... are there any performance issues/concerns with the streams replication model if the "reporting" server were to go down?  The challenge I have here is that we don't require replication here... it is a "nice to have", not a "need to have."  At minimum I just need a copy of the data from the production database on the reporting database at the end of the day.  However, having a live "mirror" of the production database would have many benefits.  That is what we're trying to figure out.  Thanks again for your inputs... we contract out our Oracle DBA's, so any input from them costs us $$$ . :-)  If you have any input on performance problems if the mirror server goes down, that would help close some of the loop... what ever solution we choose must cause zero issues on the production server for any reason.  The application it supports is business critical.


LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 1200 total points
ID: 39997925
My Streams experience is based on Enterprise Edition so I have log mining available.  In my case, the remote database can be down for a long time and Streams will go back through the logs to 'catch up'.

Since log mining isn't an option with Standard, my guess is the EE database may hang until it can sync up.

Also, typically replication is two way so if someone deletes data on the reporting database, it will replicate back.

Never tried to set up one-way replication but it might be possible.  You account team should be able to assist with the nuts and bolts (especially if they smell some future sales).

I would just try everything out on your development systems.

Author Comment

by:John Parker
ID: 39999337
Thanks again, gentlemen.  Your input is much appreciated!

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question