OraFAQ Forum: Replication » Oracle replication challenges for unique needs

Home » Server Options » Replication » Oracle replication challenges for unique needs (Oracle 11g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Oracle replication challenges for unique needs [message #576729]

Thu, 07 February 2013 10:11

Perc
Messages: 4
Registered: February 2013

Junior Member

Hi All,

We have a unique situation and I was wondering if some of you could chime in. I am no way close to DBA and that's why I am here.

Let me illustrate what we are trying to achieve.

Let's say we have Table - A and we would like to replicate specific row transaction to Table B.

Here are the rows in *Table A*
Time: Lets say 15:00

A1 Just Updated @15:00
A2 Just inserted @15:01
A3
B1 - Daily Delete Row -i.e just deleted a while back - Non scheduled process --executed by application @15:02
B2 -
B3 - Daily Delete Row - i.e just deleted a while back -- Non Schduled process --executed by application @15:05

B4 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B5 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10

B6 -Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10

Current Data in Table B (Before Replication)
@15:00

A1 (without updates)
A3
B1
B2
B3
B4
B5
B6

Expected rows in Table B (via replication/snapshot/materialized view / or any other method)

*Replication at 15:30*
Table B - Read Only

Expected rows after replication-

A1 -- Newly updated details
A2 -- Newly inserted row
A3
B1 - Daily delete row is expected to be replicated
B2
B3 - Daily delete row is expected to be replicated

***Note row B4 is not expected to be replicated to table B.

Questions:

1) How can we get updates, inserts and daily deletes replicated while ignore large purges?
2) How can large purge changes be reflected in replicated tables as well without deleting daily deletes?

Well, if anyone of you experts can give further direction , it will very highly appreciated and remembered Smile

Report message to a moderator

Re: Oracle replication challenges for unique needs [message #576734 is a reply to message #576729]

Thu, 07 February 2013 10:28

Michel Cadot
Messages: 54718
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is not clear what is in your table data.
Does "Just Updated @15:00" and "Daily Delete Row" part of it or just "A1", "A2"...?

Quote:

Expected rows after replication

Expected rows where? In the read only table?
It is not clear what you want.

Post CREATE TABLE and INSERT statements for your current table and data.
Post EXACT situation after replication and CLEARLY explain the rules from before to after.

Regards
Michel

Report message to a moderator

Re: Oracle replication challenges for unique needs [message #576740 is a reply to message #576734]

Thu, 07 February 2013 11:06

Perc
Messages: 4
Registered: February 2013

Junior Member

Thanks Michael. Appreciate your quick feedback. The rows are expected in Table B (which is Read Only).
Currently, we don't have rules set-up for replication. The illustration was to understand whether Table B (which is Read only) can get only specific rows based on following rules:

Table A to Table B Replication Rules:

1) All updates and inserts
2) All daily deletes to be replicated(i.e the daily delete rows should exist in Table B)
3) All 180 day large scale purges should not be replicated

Hope this brings some clarification. Again, I am looking at this from a business perspective and my DB knowledge is limited.

Report message to a moderator

Re: Oracle replication challenges for unique needs [message #576743 is a reply to message #576740]

Thu, 07 February 2013 11:25

Michel Cadot
Messages: 54718
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can create a trigger on table A which implements your requirements, they are not too complex:
- on INSERT, insert into the table B
- on UPDATE, update into table B
- on DELETE, do nothing (do not create the trigger for this event)

For the purge, if it is a massive DELETE, then nothing has to be done as you do nothing on DELETE.

Regards
Michel

Report message to a moderator

Re: Oracle replication challenges for unique needs [message #576748 is a reply to message #576743]

Thu, 07 February 2013 11:59

Perc
Messages: 4
Registered: February 2013

Junior Member

Thanks again Michael. However, I am not sure if the suggestion would also include deleted rows i.e does the solution cater to rule #2 as mentioned below.

So, if I understand correctly, the table B (the read table) would not retain daily deleted rows. Say 10 rows are deleted in Table A as part of daily process (not the mega purges), would the proposed solution have the capability to have the deleted 10 rows also replicated to table B?

Please correct me if I am wrong.

Table A to Table B Daily Replication Rules:

1) All updates and inserts
2) All daily deletes to be replicated(i.e the daily delete rows should exist in Table B)
3) All 180 day large scale purges should not be replicated

Report message to a moderator

Re: Oracle replication challenges for unique needs [message #576749 is a reply to message #576748]

Thu, 07 February 2013 12:07

Michel Cadot
Messages: 54718
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Thanks again Michael. However, I am not sure if the suggestion would also include deleted rows i.e does the solution cater to rule #2 as mentioned below.

#2 is covered by #1: a row is inserted before being deleted, so when you delete it it is already in table B.

Quote:

would the proposed solution have the capability to have the deleted 10 rows also replicated to table B?

Yes, because they have been inserted at the same time than in table A.

Regards
Michel

Report message to a moderator

Re: Oracle replication challenges for unique needs [message #576765 is a reply to message #576749]

Thu, 07 February 2013 14:44

Perc
Messages: 4
Registered: February 2013

Junior Member

Thanks Michael. I am hoping that this could be worked upon as expected Smile

Report message to a moderator

Previous Topic:	update database on daily basis..
Next Topic:	About Replication

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 20 00:51:40 CDT 2013

Total time taken to generate the page: 0.12220 seconds