Home » Server Options » Replication » How to Centalize the data from remote database
How to Centalize the data from remote database [message #75414] Mon, 24 November 2003 03:34 Go to next message
praveennetha
Messages: 4
Registered: November 2003
Junior Member
we have 300-400 databases(size: low) across remote server.
we want to collect the centralized inforamtion from remote server into centralized oracle database.

1.exporting the centralized data at remote server and
through FTP transfering it to central server and
importing the dump in central database

2.Creating database links and collecting data through
Snapshot

Which is best or Give any suggestion pls
if possible give detail information
Re: How to Centalize the data from remote database [message #75418 is a reply to message #75414] Mon, 01 December 2003 16:40 Go to previous messageGo to next message
Justin Cave
Messages: 8
Registered: December 2003
Junior Member
Using Oracle's replication functionality with materialized views (aka snapshots) will be a lot more efficient and maintainable here. If you configure things correctly, you'll be able to just do incremental refreshes (i.e. you'll only pull the changes across the wire, rather than the full tables). You also don't have to worry about writing and monitoring a bunch of scripts to dump the data, move the files around, and do the import. I'd be hard-pressed, in fact, to come up with a benefit to the export/ import approach.

Setting up snapshot replication is pretty simple.
1) (Optional) On the source databases, create materialized view logs on the table(s) that you want to replicate. This is optional, but is required if you want to be able to do an incremental refresh of the tables.

CREATE MATERIALIZED VIEW LOG ON <
>

2) On the target (central) database, create database links to each of the source databases

CREATE DATABASE LINK dblink_name
CONNECT TO <<user name>> IDENTIFIED BY <<password>>
USING '<<TNS name>>'

3) On the target database, create the materialized views

CREATE MATERIALIZED VIEW view_name
REFRESH FAST
START WITH TRUNC(sysdate+1) + 2/24
NEXT TRUNC(sysdate+1) + 2/24
AS SELECT * FROM <
>@<<dblink_name>>

This will create a materialized view that automatically refreshes every morning at 2 AM pulling incremental data from the source database table.

Justin
Distributed Database Consulting, Inc.
www.ddbcinc.com/askDDBC
Re: How to Centalize the data from remote database [message #75419 is a reply to message #75414] Mon, 01 December 2003 16:42 Go to previous message
Justin Cave
Messages: 8
Registered: December 2003
Junior Member
Using Oracle's replication functionality with materialized views (aka snapshots) will be a lot more efficient and maintainable here. If you configure things correctly, you'll be able to just do incremental refreshes (i.e. you'll only pull the changes across the wire, rather than the full tables). You also don't have to worry about writing and monitoring a bunch of scripts to dump the data, move the files around, and do the import. I'd be hard-pressed, in fact, to come up with a benefit to the export/ import approach.

Setting up snapshot replication is pretty simple.
1) (Optional) On the source databases, create materialized view logs on the table(s) that you want to replicate. This is optional, but is required if you want to be able to do an incremental refresh of the tables.

CREATE MATERIALIZED VIEW LOG ON table name

2) On the target (central) database, create database links to each of the source databases

CREATE DATABASE LINK dblink_name
CONNECT TO user name IDENTIFIED BY password
USING 'TNS name'

3) On the target database, create the materialized views

CREATE MATERIALIZED VIEW view_name
REFRESH FAST
START WITH TRUNC(sysdate+1) + 2/24
NEXT TRUNC(sysdate+1) + 2/24
AS SELECT * FROM table name@dblink_name

This will create a materialized view that automatically refreshes every morning at 2 AM pulling incremental data from the source database table.

Justin
Distributed Database Consulting, Inc.
www.ddbcinc.com/askDDBC
Previous Topic: 1.What version of Linux support Oracle 9i replication? 2.stand by database?
Next Topic: Oracle to SQL Server replication
Goto Forum:
  


Current Time: Fri Mar 29 02:00:06 CDT 2024