Solved

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

Posted on 2014-04-11
9
1,519 Views
Last Modified: 2014-04-14
Experts,

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?

Thanks,

John
0
Comment
Question by:John Parker
[X]
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
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39994754
Based on the docs here:
http://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_repcont.htm#TDPII220

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:
http://docs.oracle.com/cd/E11882_01/license.112/e10594/editions.htm#DBLIC117
0
 

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?
0
 
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...

http://www.dbvisit.com/products/dbvisit_replicate_real_time_oracle_database_replication/

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

Cheers,
Alex
0
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!
0
 
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.
0
 
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):
http://docs.oracle.com/cd/E16655_01/server.121/e17642/deprecated.htm#BABEAJJE
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 ;-)
0
 

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.

Thanks!

John
0
 
LVL 77

Accepted Solution

by:
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.
0
 

Author Comment

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

Featured Post

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle database T-1 Setup 7 45
Password_rules_securitty.. 12 46
Oracle Join issue. 3 48
Dbms_job.change procedure 16 37
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

734 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