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

Posted on 2014-04-11
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 13

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?!?

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 200 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 300 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

617 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