Solved

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

Posted on 2014-04-11
9
1,482 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

828 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