17,377
社区成员
发帖
与我相关
我的任务
分享数据同步问题
有两个数据库A，B 数据库的结构都一样
两个数据库同时在办公，每周末要对A、B
两数据库的数据进行维护，使A、B两个库
的数据一样（即两个表的增加相加）
oracle有这样的维护机制吗？
两个数据库同时在办公，每周末要对A、B
两数据库的数据进行维护，使A、B两个库
的数据一样（即两个表的增加相加）
oracle有这样的维护机制吗？
...全文
请发表友善的回复…
发表回复
zhuomuniao 2002-11-01
- 打赏
- 举报
up
zero_wgh 2002-11-01
- 打赏
- 举报
基于你的这种情况，我建议你还是用oracle自身的multimaster replication机制来解决，你的要求是在A和B数据库同时更新的情况下来做同步，oracle可以设置同步的调度，而且这种multimaster是可以做成双向的，另外还有冲突机制来保证数据的一致性.
Michaelyfj 2002-11-01
- 打赏
- 举报
to KingSunSha(弱水三千) 我原来也用过一段时间的snapshot，数据的维护
基本正常。但我只是会用，一旦出现异常时我就束手无策，还想你能把
这些东西整理一下贴出来，为大家做点贡献是最快乐，不知道您是否这样
认为
基本正常。但我只是会用，一旦出现异常时我就束手无策，还想你能把
这些东西整理一下贴出来，为大家做点贡献是最快乐，不知道您是否这样
认为
Michaelyfj 2002-11-01
- 打赏
- 举报
我对multimaster replication专门发了一贴，请大家参与讨论：
http://expert.csdn.net/Expert/topic/1140/1140921.xml?temp=.2825128
http://expert.csdn.net/Expert/topic/1140/1140921.xml?temp=.2825128
Michaelyfj 2002-11-01
- 打赏
- 举报
zero_wgh(traffic_light) 请指教，oracle8i版本的multimaster replication是独立软件，还是oracle的安装盘有啊
KingSunSha 2002-10-31
- 打赏
- 举报
非常惭愧,对于同步,我们一直用公司自己开发的工具,叫做IDRS.基本原理我整理一下贴出来.但是一直没有机会好好研究一下SNAPSHOT等工具,如果有空的话,我测试一下看看.
black_snail 2002-10-31
- 打赏
- 举报
Overview
Another feature of Oracle that needs administration is the snapshot (also known as a materialized view.) Snapshots are copies of either an entire single table or set of its rows (simple snapshot) or a collection of tables, views, or their rows using joins, grouping, and selection criteria (complex snapshots). Snapshots are very useful in a distributed environment where remote locations need a queriable copy of a table from the master database. Instead of paying the penalty for using the network to send out the query and get back the data, the query is against a local table image and is thus much faster. With later versions of Oracle7 and in Oracle8 and Oracle8i, snapshots can be made updateable.
Snapshots and materialized views are asynchronous in nature; they reflect a table's or a collection's state at the time the snapshot was taken. A simple snapshot or materialized view can be periodically refreshed by either use of a snapshot log containing only the changed rows for the snapshot (fast refresh), or a totally new copy (complete refresh). In most cases, the fast refresh is quicker and just as accurate. A fast refresh can only be used if the snapshot or materialized view has a log, and that log was created prior to the creation or last refresh of the snapshot. For a complex snapshot or materialized view, a complete refresh is required. It is also possible to allow the system to decide which to use, either a fast or complete refresh.
One problem with a snapshot or materialized view log is that it keeps a copy of each and every change to a row. Therefore, if a row undergoes 200 changes between one refresh and the next, there will be 200 entries in the snapshot or materialized view log that will be applied to the snapshot at refresh. This could lead to the refresh of the snapshot or materialized view taking longer than a complete refresh. Each snapshot or materialized view should be examined for the amount of activity it is seeing and if this is occurring with any of them, the snapshot or materialized view log should be eliminated or the refresh mode changed to COMPLETE.
A materialized view is simply a snapshot that is contained in the current instance instead of a remote instance. Other than the keyword MATERIALIZED VIEW the CREATE SNAPSHOT and CREATE SNAPSHOT LOG commands are identical to the CREATE MATERIALIZED VIEW and CREATE MATERIALIZED VIEW LOG commands. Since the CREATE MATERIALIZED VIEW command creates a view, table and an index to maintain the materialized view you must have the CREATE VIEW, CREATE TABLE, CREATE INDEX and CREATE MATERIALIZED VIEW or CREATE SNAPSHOT privileges to create a materialized view. If you wish query rewrite to be available on the materialized views created, the owner of the underlying tables and the materialized view must have QUERY REWRITE or, the creator of the materialized view must have GLOBAL QUERY REWRITE privilege.
In a data warehousing situation a materialized view can be used by Oracle to re-write queries on the fly that the optimizer determines would profit from using the materialized view rather than the base tables. You should take this into consideration when the concurrency of the data is important since a materialized view is only as current as its last refresh.
Snapshot Types
w Simple - consists of either an entire single table, or, a simple select of rows from a single table.
w Complex - consists of joined tables, views, grouped or complex select statement queries.
Restrictions
w Snapshots cannot be created on tables owned by the SYS user.
w Data can only be selected from a snapshot; no updates, inserts or deletions are allowed.
w To create a simple snapshot, the snapshot's defining query cannot contain the following SQL attributes:
distinct or aggregate functions
GROUP BY or CONNECT BY clauses
joins (other than the allowed types of subqueries)
set operations
When a snapshot definition uses any of the above attributes, the snapshot is a complex snapshot. Oracle cannot use a snapshot log to perform fast refreshes for a complex snapshot.
Special Requirements for Primary Key Snapshots (Oracle8)
By default, Oracle creates a snapshot log to support primary key snapshots. Therefore, the master table must contain a valid PRIMARY KEY constraint before you can create a snapshot log.
Naming
A snapshot's name cannot exceed 19 characters in length. This is because Oracle adds its own suffixes to the table name used for the snapshot. An Oracle table's name cannot exceed 30 characters. As with other Oracle objects, it pays to make the snapshot name meaningful.
Indexing
When you create a snapshot, Oracle automatically creates a table in the replicated database. The table name has the prefix SNAP$_. You will often want to index the snapshot table differently to the table being replicated from. This is especially true if the main database is being used for different purposes, for example, the database being replicated from being used for OLTP and the database being replicated to being used for batch reporting. Oracle will also create its own index on the ROWID column if the snapshot is created using the rowid option and will be on the primary key columns if the snapshot is created with the primary key columns.
Recommendations
w Use snapshots in a distributed environment where remote locations need a queriable copy of a table from the master database. Instead of paying the penalty for using the network to send out the query and get back the data, the query is against a local table image and is thus much faster.
w Snapshot size should mirror those for the source table. If the source table is stable, a large initial extent with smaller subsequent extents should be used. Since snapshots will most likely be on slow growth tables, set PCTINCREASE to zero in most cases.
Using DBMS_JOB to Supplement Snapshot Replication
The dbms_job facility can be thought of in much the same manner as the Unix utility cron: a simple scheduling tool. Exploiting the dbms_job functionality allows us to build a much more sophisticated form of data replication. Instead of being limited by Oracle's own snapshot replication restrictions, it is a very simple task to construct your own business data replication engine.
Another feature of Oracle that needs administration is the snapshot (also known as a materialized view.) Snapshots are copies of either an entire single table or set of its rows (simple snapshot) or a collection of tables, views, or their rows using joins, grouping, and selection criteria (complex snapshots). Snapshots are very useful in a distributed environment where remote locations need a queriable copy of a table from the master database. Instead of paying the penalty for using the network to send out the query and get back the data, the query is against a local table image and is thus much faster. With later versions of Oracle7 and in Oracle8 and Oracle8i, snapshots can be made updateable.
Snapshots and materialized views are asynchronous in nature; they reflect a table's or a collection's state at the time the snapshot was taken. A simple snapshot or materialized view can be periodically refreshed by either use of a snapshot log containing only the changed rows for the snapshot (fast refresh), or a totally new copy (complete refresh). In most cases, the fast refresh is quicker and just as accurate. A fast refresh can only be used if the snapshot or materialized view has a log, and that log was created prior to the creation or last refresh of the snapshot. For a complex snapshot or materialized view, a complete refresh is required. It is also possible to allow the system to decide which to use, either a fast or complete refresh.
One problem with a snapshot or materialized view log is that it keeps a copy of each and every change to a row. Therefore, if a row undergoes 200 changes between one refresh and the next, there will be 200 entries in the snapshot or materialized view log that will be applied to the snapshot at refresh. This could lead to the refresh of the snapshot or materialized view taking longer than a complete refresh. Each snapshot or materialized view should be examined for the amount of activity it is seeing and if this is occurring with any of them, the snapshot or materialized view log should be eliminated or the refresh mode changed to COMPLETE.
A materialized view is simply a snapshot that is contained in the current instance instead of a remote instance. Other than the keyword MATERIALIZED VIEW the CREATE SNAPSHOT and CREATE SNAPSHOT LOG commands are identical to the CREATE MATERIALIZED VIEW and CREATE MATERIALIZED VIEW LOG commands. Since the CREATE MATERIALIZED VIEW command creates a view, table and an index to maintain the materialized view you must have the CREATE VIEW, CREATE TABLE, CREATE INDEX and CREATE MATERIALIZED VIEW or CREATE SNAPSHOT privileges to create a materialized view. If you wish query rewrite to be available on the materialized views created, the owner of the underlying tables and the materialized view must have QUERY REWRITE or, the creator of the materialized view must have GLOBAL QUERY REWRITE privilege.
In a data warehousing situation a materialized view can be used by Oracle to re-write queries on the fly that the optimizer determines would profit from using the materialized view rather than the base tables. You should take this into consideration when the concurrency of the data is important since a materialized view is only as current as its last refresh.
Snapshot Types
w Simple - consists of either an entire single table, or, a simple select of rows from a single table.
w Complex - consists of joined tables, views, grouped or complex select statement queries.
Restrictions
w Snapshots cannot be created on tables owned by the SYS user.
w Data can only be selected from a snapshot; no updates, inserts or deletions are allowed.
w To create a simple snapshot, the snapshot's defining query cannot contain the following SQL attributes:
distinct or aggregate functions
GROUP BY or CONNECT BY clauses
joins (other than the allowed types of subqueries)
set operations
When a snapshot definition uses any of the above attributes, the snapshot is a complex snapshot. Oracle cannot use a snapshot log to perform fast refreshes for a complex snapshot.
Special Requirements for Primary Key Snapshots (Oracle8)
By default, Oracle creates a snapshot log to support primary key snapshots. Therefore, the master table must contain a valid PRIMARY KEY constraint before you can create a snapshot log.
Naming
A snapshot's name cannot exceed 19 characters in length. This is because Oracle adds its own suffixes to the table name used for the snapshot. An Oracle table's name cannot exceed 30 characters. As with other Oracle objects, it pays to make the snapshot name meaningful.
Indexing
When you create a snapshot, Oracle automatically creates a table in the replicated database. The table name has the prefix SNAP$_. You will often want to index the snapshot table differently to the table being replicated from. This is especially true if the main database is being used for different purposes, for example, the database being replicated from being used for OLTP and the database being replicated to being used for batch reporting. Oracle will also create its own index on the ROWID column if the snapshot is created using the rowid option and will be on the primary key columns if the snapshot is created with the primary key columns.
Recommendations
w Use snapshots in a distributed environment where remote locations need a queriable copy of a table from the master database. Instead of paying the penalty for using the network to send out the query and get back the data, the query is against a local table image and is thus much faster.
w Snapshot size should mirror those for the source table. If the source table is stable, a large initial extent with smaller subsequent extents should be used. Since snapshots will most likely be on slow growth tables, set PCTINCREASE to zero in most cases.
Using DBMS_JOB to Supplement Snapshot Replication
The dbms_job facility can be thought of in much the same manner as the Unix utility cron: a simple scheduling tool. Exploiting the dbms_job functionality allows us to build a much more sophisticated form of data replication. Instead of being limited by Oracle's own snapshot replication restrictions, it is a very simple task to construct your own business data replication engine.
Michaelyfj 2002-10-31
- 打赏
- 举报
三千兄说的没错，主要难点是两个数据库的同一个对象都在同时更新。
我也想自己做一个维护工具，但具体的思路不是很清楚，还望三千兄
能指导一下，在下谢谢了
我也想自己做一个维护工具，但具体的思路不是很清楚，还望三千兄
能指导一下，在下谢谢了
Michaelyfj 2002-10-31
- 打赏
- 举报
black_snail(●○)老大，你在这方面应该比较有经验吧，
能否告知一二
能否告知一二
langlang_2000 2002-10-30
- 打赏
- 举报
black_snail,我想知道具体的步聚，能否告知一二!
KingSunSha 2002-10-30
- 打赏
- 举报
这个问题不是很容易,因为两边的数据都在更新.
首先你要从业务逻辑上确定如果有相同纪录的话,以哪个条件为更新的标准(通常是时间点).我的习惯是自己写代码做数据交换,从一端导出上次同步以后更新过的数据到文本文件,传送到另一端用脚本倒入. 当然所有的操作都能做成定时处理的.这样的方式虽然繁琐一点,但是最容易控制,也最容易维护.
首先你要从业务逻辑上确定如果有相同纪录的话,以哪个条件为更新的标准(通常是时间点).我的习惯是自己写代码做数据交换,从一端导出上次同步以后更新过的数据到文本文件,传送到另一端用脚本倒入. 当然所有的操作都能做成定时处理的.这样的方式虽然繁琐一点,但是最容易控制,也最容易维护.
black_snail 2002-10-30
- 打赏
- 举报
1.snapshot
2.Replication
2.Replication
xxy802 2002-10-30
- 打赏
- 举报
你可以使用link我前一段作的项目和这个差不多
