Home » Server Options » Replication » replicating only in one direction???
replicating only in one direction??? [message #195415] Thu, 28 September 2006 08:43 Go to next message
s-bolz
Messages: 10
Registered: September 2006
Junior Member
Hi,

I want to load all new information from a source schema (in the OLTP db) into a target schema (in the reporting db) - once a day would be enough. If some rows in the OLTP schema are deleted, they mustn't be deleted in the reporting schema as well, because we still want to report on those data sets.

Our productive database is 10g, however my test environment is still 9i.

Can anyone of you guys help me on this? I have just begun my job as junior db developer and don't know to approach this szenario. Which functions / features of Oracle shall I delve into first?

Thanks for your help.
Sebastian
Re: replicating only in one direction??? [message #195665 is a reply to message #195415] Sat, 30 September 2006 13:37 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
For replication you could use materialized view (was snapshot) replication. It is fairly painless to setup, ideally you would have materialized logs on your master database to capture the changes (insert, update, delete). Look at the Advanced Replication manual, http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14226/toc.htm.

There are other options (transport redo logs, etc), I am just not sure if they could be used in your scenario because of the version difference.

Out of curiousity why would you want to replicate from a production to a test environment? At some point your going to want to test something that is affected by these replicated tables and you will have to break replication between prod & test.
Re: replicating only in one direction??? [message #195689 is a reply to message #195665] Sun, 01 October 2006 06:36 Go to previous messageGo to next message
s-bolz
Messages: 10
Registered: September 2006
Junior Member
Hi,

the approach with a materialized view is something I definitely will delve into. Thanks for the suggestion.

I think my first statement was a bit confusing. We have two productive databases, an OLTP database and dome kind of data warehouse (we are not using the star schema though, but we historize the data sets from the OLTP database and that is why deleted data sets on the OLTP mustn't be deleted on the quasi DWH as well). Both are 10g databases. My comment regarding the 9i test systems was meant to clarify that it would be difficult to test 10g specific features. That means that 10g specific features are out of question unless our DBA sets up another 10g test system for us. That was all.

Now you might wonder, why we don't use the same routines for the new schema to load into the DWH as we use for the other schemas which are already loaded into the DWH... (There are other schemas which are already loaded, since both systems are already productive.) The answer to that is quite easy. I was meant to look for new approaches which might be more efficient or faster to implement, since the schema which I want to load into the DWH is relatively small compared to the other schemas which are already extracted.

The approach using the materialized view is definitely an approach I want to try in that case. Maybe it will convince my colleagues...

Thanks again for your help.
Sebastian.
Re: replicating only in one direction??? [message #195700 is a reply to message #195689] Sun, 01 October 2006 09:36 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Ahh, unfortunately MV replication wouldn't work for this scenario. When I read your OP for some reason I read "must be deleted", but it is actually "must not be deleted".

I am not familiar with any mechanism for 9i and earlier that would achieve what you want. If you are transporting redo logs or using MV replication then they will replicate the deletes as well as the updates and inserts.

I am assuming the data that is being deleted is historical information that is purged out of production.

I know that 10g has some relatively new features (Change Data Capture, CDC), but I have not familiarized myself with them. But the problem with any mechanism that is transporting changes (MV Replication, redo logs, CDC, etc) is how do you tell whether it was an intentional delete or not, right? Perhaps the records were removed for a specific reason and you do want it to be represented in your reporting database.
Re: replicating only in one direction??? [message #195716 is a reply to message #195700] Sun, 01 October 2006 13:22 Go to previous messageGo to next message
s-bolz
Messages: 10
Registered: September 2006
Junior Member
Yeah, I know that unfortunately a MV will also replicate the deletions. I thought of using a MV and merging any new and updated data sets into the appropriate target table as a work around. To be honest, we didn't think of seperating intentional deletions from unintentional deletions. We simply wanted no deletion to be carried out in the reporting data base as well. That at least makes things for me easier. I have never heard of CDC, but I think I will have a look at the reference. Maybe this feature is of some assistance.

May I ask you another question concerning MV?

I want to know how reliable this mechanism is, i.e. if an OLTP table is updated while the MV refreshes, is it possible to encounter some errors concerning consistent reading? Will I lose some data? Does the answer to this question depend on the number of parallel transaction while refreshing?
Re: replicating only in one direction??? [message #195719 is a reply to message #195415] Sun, 01 October 2006 15:52 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
I believe what you are asking is what happens when the table is updated on the master and the MV is refreshed on the replica, right? More or less the same thing that happens when you run the query on the master while someone is updating the table.

The MV will capture all of the changed rows on the master from the materialized log, apply the changes locally and commit.

Oracle doesn't do "consistent reads" the way some other databases do with read locks. Oracle uses multi-versioning to "rollback" changed data blocks to the point in time when your query started.

Ask Tom has a discussion on multi-versioning...

http://asktom.oracle.com/pls/ask/f?p=4950:8:17109550103248058412::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:27330770500351
icon14.gif  Re: replicating only in one direction??? [message #195739 is a reply to message #195719] Mon, 02 October 2006 01:51 Go to previous messageGo to next message
s-bolz
Messages: 10
Registered: September 2006
Junior Member
Yes, that is exactly what I meant. Thanks a lot for the link.
I get the ORA-23313 and don't know why... [message #196215 is a reply to message #195739] Wed, 04 October 2006 09:33 Go to previous messageGo to next message
s-bolz
Messages: 10
Registered: September 2006
Junior Member
Hi again,

I have tried to set up a MV Environment on my two test systems. I used the examples from the API reference to do that, but unfortunately I am stuck with ORA-23313 and don't know why...

Can anyone help? Here is what I did:

  • I set up the master and the mv site
  • I created the master group and the master objects as described (on the master site)
  • I generated replication support for each master object
  • I resumed master activity


But when I want to run the DBMS_REPCAT.create_mview_repgroup method on my mv site I get the ORA-23313. In the documentation a hybrid environment is built with 3 master sites, 1 master mv site, and one mv site, but I built only 1 master site (my test OLTP database), and 1 mv site (my test reporting database).

Any help would be appreciated. Thanks.

Sebastian
Re: replicating only in one direction??? [message #196705 is a reply to message #195716] Fri, 06 October 2006 10:54 Go to previous messageGo to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
Do all tables in the source db have last update timestamp column?

-Art
Re: replicating only in one direction??? [message #196710 is a reply to message #195415] Fri, 06 October 2006 12:56 Go to previous messageGo to next message
s-bolz
Messages: 10
Registered: September 2006
Junior Member
No, unfortunately they don't. Embarassed
Re: replicating only in one direction??? [message #196715 is a reply to message #196710] Fri, 06 October 2006 14:09 Go to previous messageGo to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
If the tables are small enought you can MERGE all rows from the source tables into the target tables.
If you must do incremental refreshes then you need to log changes via triggers and then MERGE only those rows.

-Art
icon14.gif  Re: replicating only in one direction??? [message #196929 is a reply to message #196715] Mon, 09 October 2006 03:45 Go to previous message
s-bolz
Messages: 10
Registered: September 2006
Junior Member
Right now, they might be small enough. But that can change. Also, we want to minimize the connections between our OLTP and our reporting databases. So, I will either have to use MVs with scheduled fast refresh and merge the inserted / updated rows into my target tables or i will have to log changes with triggers on the OLTP database and work through that logs when updating the target tables.

Followup:
We have not yet decided on an approach, but it looks like we will use the second alternative, since we have already used it in other cases. My job was to find out, if Oracle 10g offers some new features with which we can accomplish that more easily. Looks, like it doesn't. (Apart from the approach using MVs, which my colleagues will have to evaluate).

Thanks for the help of all you. It has helped me a lot.

Sebastian
Previous Topic: Not able to create snapshot rep group at Snapshot site
Next Topic: How can i monitor replication activity.
Goto Forum:
  


Current Time: Thu Mar 28 10:46:53 CDT 2024