| How to Centalize the data from remote database [message #75414] |
Mon, 24 November 2003 03:34  |
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  |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
