Solved

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

Posted on 2014-04-11
9
1,386 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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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]
Comment Utility
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
 

Author Comment

by:John Parker
Comment Utility
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
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.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
Comment Utility
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
Comment Utility
Thanks again, gentlemen.  Your input is much appreciated!
0

Featured Post

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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now