nocinfospan
asked on
oracle data replication b/w oracle standard and enterprise edition
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
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
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.
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@dblinkPR
.
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.
2) By sitting on destination,
Insert into warehouse.table
select col_names from production.table1@dblinkPR
.
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.
ASKER
@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
@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
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.
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.
ASKER
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!!
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!!
Did you try with triggers?
Have you reached out to Oracle Support on this one? They will know what is and isn't possible.
Have you reached out to Oracle Support on this one? They will know what is and isn't possible.
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,re
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
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,re
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
ASKER
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..
@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..
>>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.
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.
ASKER
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
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 ?
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
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 ?
>>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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.