Home » Server Options » Replication » Oracle streams or MVs or database triggers (Oracle 10gR2 (HP-UX and AIX))
icon5.gif  Oracle streams or MVs or database triggers [message #509389] Fri, 27 May 2011 07:41 Go to next message
aritor
Messages: 2
Registered: May 2011
Location: Norway
Junior Member
Hi,

I have following issue:
We have three unix servers with four databases (10gR2) containing "HP Operation Management Unix" (OMU) server messages for monitoring purpose, and we now want to transfer these data to one new database on a new server for reporting purpose.

The message table in each OMU database keeps the message row until it is "Acknowledged" or for maximum fourteen days, then it is moved to an historic table where it stays for another three days. Keeping data for only seventeen days are a performance issue.
The new "Reporting database" is intended to hold messages data for the last 90 days.

I wonder which method to use to move/replicate data against the databases?:

Materialized view using database link, with view on top of the MVs.
-How to keep rows longer than the master (source) table, avoiding deletion when master row is deleted

Oracle Streams, with local capture and remote apply. How will this influence on the master database performance. There are about 10000 new messages in each OMU database every day. Is it possible having four streams connections against the reporting database ?

Or should I simply use database triggers which fires after insert and update and applies changes to the reporting database using database links ?

Or are there other and better methods ?

Regards
Arild
Re: Oracle streams or MVs or database triggers [message #509440 is a reply to message #509389] Fri, 27 May 2011 11:15 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

If you go by steams / data guard, when ever you do changes on master database it's should be replicate in materialized view site even db link replication also.

but there is some possibility to go by basic replication (read only for materialized view site). If you do the refresh manually then only data's replicate to materialized view site.

Do you think this method will help you?
Re: Oracle streams or MVs or database triggers [message #509650 is a reply to message #509440] Mon, 30 May 2011 15:57 Go to previous message
aritor
Messages: 2
Registered: May 2011
Location: Norway
Junior Member
Thank you for your answer.
I tend towards the basic replication (read-only materialized views) method, which you mention.
After each manual refresh I could merge data to a new historic table on the materialized view site to keep data longer than the master tables.

I am new with this replication methods, so it's always good to get a second opinion from other Oracle people.
Previous Topic: SNAPSHOT Refresh Problem
Next Topic: Golden Gate Login Oddities
Goto Forum:
  


Current Time: Thu Mar 28 11:27:37 CDT 2024