Solved

oracle data replication b/w oracle standard and enterprise edition

Posted on 2011-09-07
12
603 Views
Last Modified: 2012-06-27
Ok here is the thing

I have two databases, one is oracle 11.1.0.6.0 standard editions which is also our production RAC.
The other one is also the same oracle version (11g R1) but enterprise edition and single instance.

We use enterprise edition for our oracle BI reporting using oracle discoverer.we dont allow them to connect to the production (standard edition RAC),
therefore, all of our BI users get connected to enterprise edition


Nightly, we run bunch of scripts that moves over the data from production to warehouse. The scripts are some thing like
that and are plenty of them


Insert into warehouse.table
select col_names from production.table1, production.table2

where condition = 1
and condition = 2
and condition = 3

UNION

select col_names from production.table3, production.table4

where condition = 1
and condition = 2
and condition = 3

UNION

select col_names from production.table5, production.table6

where condition = 1
and condition = 2
and condition = 3


Pl. advise me the best possible soultion for replicating these kind of scripts mentioned above. I have tested out Oracle 11g
dbms_comparision package that replicated the data from source to destination tables. What else can be done? Pl. dont advise
streams as the source is standard edition also MVs also dont offer full featurs in standard edition.
Pl. let me know if any additional detail is required
Thanks
0
Comment
Question by:nocinfospan
  • 5
  • 4
  • 3
12 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36502210
About the only other thing I can think of that you haven;t already mentioned is  export/import (classic or datapump)?  You can specify a QUERY clause which is basically a WHERE for the tables being exported.


0
 
LVL 5

Expert Comment

by:anand_20703
ID: 36502459
1) In destination,create a database link to production(dblinkPROD) so that u can select data
2) By sitting on destination,
Insert into warehouse.table
select col_names from production.table1@dblinkPROD, production.table2@dblinkPROD
.
Use this approach as on required on batch programs.

This is the simplest way.
So no need to use other features and worry about standard/enterprise.
0
 
LVL 1

Author Comment

by:nocinfospan
ID: 36502879
@anand, this is what is our current system, the problem with this is that the users get access to the data which is a day older as we run those scripts nightly.

@slightwv, I have considered imp/exp option but we need some thing real time. I mean we can not expect real time until we use strams or golden gate which is not the option due to standard edition and licencing. therefore, after doing some research and testing I came accross oracle dbms_comparision package that does the data replication from source to destination tables using db links. I can schedule to run the dbms_comparison in cron in mornings, afternoons and evenings. This way the user will have 3 to 4 hours delay in data instead of 24 hours.

My question is that is there any real time data replication work around exists may be in the form of standby or something ..
you advise is appriciated
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36502909
Sorry.  I missed the real-time aspect to the question.

In 11g there is a new feature called active standby where your standby database can be opened read-only.

Never heard if it is compatible between Standard and Enterprise.  I cannot think of a reason why it wouldn't be.

The only other thing I can think of is triggers set up to do this.
0
 
LVL 1

Author Comment

by:nocinfospan
ID: 36504792
Not sure about active standby either, however, I tried using active database duplication using RMAN from standard to enterprise and it didnt work, but same set of scripts work b/w enterprise to enterprise in my test environment.
Unfortunately there is'nt much documentation available for standard edition thats is why i have been spending  serveral hours on differenet features and then realizing its not possible in standard.
I am going to test the active standby too but not very hopful about that either..
meanwhile, if you come across any sol. would really appriciate...all I need a direction that is possiblee b/w standard to enterprise replication and I'll take it from there.
will try the active standby and let you know if it works!!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36505079
Did you try with triggers?

Have you reached out to Oracle Support on this one?  They will know what is and isn't possible.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 5

Expert Comment

by:anand_20703
ID: 36508437
Friends, Standard Edition does not support Standby/Active Standby Feature.better not waste time in this direction.
If dbms_comparision works well, that could be ur best solution. Else using inserts with dblink would ease ur task. Can be used as many times as you want to refresh the data.In the same direction , little more sophisticated option is to use MERGE sql option(INSERT & UPDATE). This will also make the task easy for field level complexity.

Other option to use is Table LEvel Replication using the Enterprise Manager GUI console. Using this, this task can certainly be achieved. And Very Easy Administration,seamless,realtime. If the link is good between source and target, refresh will be as instant as possible. Doubt is , not sure whether it will work in STandard/Enterprise Combination. Worth Checking the point.

Assumptions : U cant use Standby / Active STandby because of unsupported Edition
                         Want to refresh as many times as possible
                          not interested to use streams
                         MV's cannot be used because of insuffient functionality by Standard Edition

Happy Tech Journey
                         
0
 
LVL 1

Author Comment

by:nocinfospan
ID: 36510400
Thanks anand and slightwv for your comments...today I am going to device the replication plan based on our discussion, test it and will get back to you by early next week on how it goes. I am sure I will come across some issues and will discuss them furthur...stay tunned

@slighwv...no I havent tried triggers and the reason is the same that ideally I want to avoid the scripting part (since already using inserts) or atleast bring it down to bare min. I dont see any point as yet to replace my current insert scripts with triggers since mothodology of replication is still the same..

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36510435
>>replace my current insert scripts with triggers since mothodology of replication is still the same..

Triggers will fire on insert into to base tables.  This will simulate real-time.

The downside to them is your warehouse database will need to be up or the inserts in the source tables will fail.
0
 
LVL 1

Author Comment

by:nocinfospan
ID: 36511340
Thanks slightwv, ya you are right however, we experience hundred of transactions in a single table and they are around 30 tables that are good replication candidate.
I think this may increase the network traffic even I make the transactions commit on after every 50 or what ever inserts !!. I am not sure the level of impact  this will have on the production performance...??

Also, I know i mentioned that no MV, but was just testing some thing and came across the below mentioned logic to do the replication.

1. replicate standard edition tables to enterprise db using dbms_comparision three times a day i.e. 9 AM, 12 PM and 4 PM. This way the users will have updated reporting data in mornings,  afternoons and so forth.

2. Once the tables data is there in the target enterprise using dbms_comparision, create a MVs in target (EE)  that would replace all the "inserts into from standard.production.tables@db_link" to "select col_names from standard.to.warehouse.replicated.tables .

The reason why I said NO MVs in my first posting because I was thinking about directly replication data using MVs from source to target. And the standard edition doesnt offer MV functionality. But with step 2, since I already have the data from production I can create MVs there.

I did some testing on couple of tables and it works fine. Also my current inserts scripts use a lot of "Union" operator. I didnt know that in MVs "union" can be replaced with "union all" to make them work.
Do you see any potentional problem or performance bottle neck in that approach ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36511567
>>Do you see any potentional problem or performance bottle neck in that approach ?

If DBMS_COMPARISON runs faster than your current scripts then I think it is about the best you can get if you cannot locate any other options.

I would still talk with Oracle directly for other possibilities.

For example:
Can EE read SE log files using logminer?

Even though SE doesn't support Data Guard, can you manually ship the redo logs from SE over to EE and have EE Dataguard process them?
0
 
LVL 5

Accepted Solution

by:
anand_20703 earned 500 total points
ID: 36516105
Do you see any potentional problem or performance bottle neck in that approach ?
.
Any data refresh methodologies like (Replication,MV,DBMS_COMPARISION,streams...) have performance trade off to certain extent.i.e, load on the Database processing and network bandwidth.
Helpful considerations :
-Try refresh schedules at off peak hours
-If enough processes are available, try to parallelize the refresh job
- For all subsequent MV refresh jobs, try using REFRESH FAST instead of REFRESH COMPLETE..... saves significant amount of processing resources and time
And remember , MV refresh is a very much CPU intensive activity..
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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

706 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

17 Experts available now in Live!

Get 1:1 Help Now