Oracle stream 双向复制配置
1 将两个数据库都置于归档模式.
SQL> archive log list
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
2 修改两个库的下列初始化参数
alter system set global_names=true scope=both;
alter system set undo_retention=3600 scope=both;
alter system set job_queue_processes=4 scope=both;
alter system set streams_pool_size= 20m scope=spfile;
alter system set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' scope=spfile;
shutdown immediate;
startup
3 创建流管理员并做相应授权
---at the primary:
create tablespace strepadm datafile 'C:\strepadm01.dbf' size 100m;
create user stream_admin identified by stream_admin default tablespace strepadm temporary tablespace temp;
grant connect, resource, dba, aq_administrator_role to stream_admin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (
grantee => 'stream_admin',
grant_privileges => true);
END;
/
---at the replica:
create tablespace strepadm datafile 'C:\strepadm01.dbf' size 100m;
create user stream_admin identified by stream_admin default tablespace strepadm temporary tablespace temp;
grant connect, resource, dba, aq_administrator_role to stream_admin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE (
grantee => 'stream_admin',
grant_privileges => true);
END;
/
4 修改 tnsnames.ora.
5 创建数据库链接
---at the primary:
connect stream_admin/stream_admin
create database link h10g connect to stream_admin identified by stream_admin using 'h10g';
---at the replica:
connect stream_admin/stream_admin
create database link prod connect to stream_admin identified by stream_admin using 'prod';
6 创建一个用于流复制对象的用户
CONN SYS/SYS AS SYSDBA
CREATE USER WS_APP IDENTIFIED by WS_APP ;
GRANT RESOURCE,CONNECT,CREATE TABLE TO WS_APP;
CONN WS_APP/WS_APP
create table t(a int,b char,constraint pk_woodscrew primary key(a));
7 WS＿APP用户添加附加日志。
Alter table ws_app.t add supplemental log data (ALL) columns;
8 在两个数据库上创建流队列.
---at prod (primary):
connect stream_admin/stream_admin
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'stream_admin.ws_app01_queue_table',
queue_name => 'stream_admin.ws_app01_queue');
END;
/
---At h10g (replica):
connect stream_admin/stream_admin
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'stream_admin.ws_app02_queue_table',
queue_name => 'stream_admin.ws_app02_queue');
END;
/
9 在主数据库 (ORCL)创建捕获进程.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'ws_app',
streams_type =>'capture',
streams_name =>'ws_app01_capture',
queue_name =>'ws_app01_queue',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database
=> NULL,
inclusion_rule => true);
END;
/
10 实例化WS_APP模式。
---AT prod :
exp ws_app/ws_app@prod file=c:\wsapp.dmp log=c:\wsappexp.log object_consistent=y owner=ws_app
---AT h10g:
---创建用户:
CONN SYS/SYS AS SYSDBA
CREATE USER WS_APP IDENTIFIED by WS_APP ;
GRANT RESOURCE,CONNECT,CREATE TABLE TO WS_APP;
CONN WS_APP/WS_APP
imp ws_app/ws_app@h10g file=C:\wsapp.dmp log=C:\wsappimp.log fromuser=ws_app touser=ws_app streams_instantiation=y
11 在主数据库 (prod)中创建传播作业.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name =>'ws_app',
streams_name =>'ws_app01_propagation',
source_queue_name =>'stream_admin.ws_app01_queue',
destination_queue_name=>'stream_admin.ws_app02_queue@h10g',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database =>'prod',
inclusion_rule =>true);
END;
/
12 在从库 (h10g)上创建应用进程.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'ws_app',
streams_type =>'apply',
streams_name =>'ws_app02_apply',
queue_name =>'ws_app02_queue',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database =>'prod',
inclusion_rule =>true);
END;
/
13 在从库（h10g）上设置冲突解决方案
DECLARE
cols DBMS_https://www.doczj.com/doc/d41631323.html,_ARRAY;
BEGIN
cols(1) := 'b';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name =>'ws_app.woodscrew',
method_name =>'OVERWRITE',
resolution_column=>'b',
column_list =>cols);
END;
/
14 在主库 (prod)启用捕获进程.开如捕获变化的SQL语句。
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'ws_app01_capture');
END;
/
15 在从库 (h10g)启用应用进程，开始应用SQL语句的变化。
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'ws_app02_apply');
END;
/
16 测试一个从主库 (prod) 到从库 (h10g)的流复制。
---AT prod:
Conn ws_app/ws_app
insert into t values (123);
更新后做一下归档。
Conn sys/sys as sysdba
Alter system archive log current;
---AT h10g:
connect ws_app/ws_app
select * from t;
如果你完全是按照我上面的脚本来做的话，那么这个查询应该有返回的行，不行你再对照一下我写的脚本检查你是否敲错了哪些字符。
下面将开始从STANDBY向ORCL的流复制环境的搭建工作。主从库互相交换一下。
在STANDBY上启用附加日志。因为在向STANBY导入数据时指定了 STREAMS_INSTANTIATION=Y. 所以不用显示指定附加日志了。当然你可以显示指定一下，只是会收到这样一条提示，说附加日志功能已经开启了，没关系继续下一步就行了。
ORA-32588: supplemental logging attribute all column exists
17 在 库(h10g)上创建捕获进程.
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'ws_app',
streams_type =>'capture',
streams_name =>'ws_app02_capture',
queue_name =>'ws_app02_queue',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database => NULL,
inclusion_rule => true);
END;
/
18 创建从库(h10g)到库(prod)的传播作业。
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name =>'ws_app',
streams_name =>'ws_app02_propagation',
source_queue_name =>'stream_admin.ws_app02_queue',
destination_queue_name=>'stream_admin.ws_app01_queue@prod',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database =>'h10g',
inclusion_rule =>true);
END;
/
19 在库(prod)上创建应用进程。
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name =>'ws_app',
streams_type =>'apply',
streams_name =>'ws_app01_apply',
queue_name =>'ws_app01_queue',
include_dml =>true,
include_ddl =>true,
include_tagged_lcr =>false,
source_database =>'h10g',
inclusion_rule =>true);
END;
/
20 在库（ORCL）上创建冲突的解决方案。
这次我们采用丢弃的办法来处理。（上一次我们采用的是覆盖的办法）
DECLARE
cols DBMS_https://www.doczj.com/doc/d41631323.html,_ARRAY;
BEGIN
cols(1) := 'b';
DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
object_name =>'ws_app.woodscrew',
method_name =>'DISCARD',
resolution_column=>'b',
column_list =>cols);
END;
/
Step 24. 实例化
上一次的实例化是通过exp/imp来完成的。这次我们采用手工实例化两库WS_APP模式下的表。执行下面的语句。
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@ORCL(
source_schema_name =>'ws_app',
source_database_name =>'h10g',
instantiation_scn =>iscn,
recursive =>true);
END;
/
21 启动库（h10g)上的捕获进程.
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'ws_app02_capture');
END;
/
22 启动库 (prod)的应用进程。
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'ws_app01_apply');
END;
/
23 测试一下你是否大功告成了
AT h10g:
SQL> connect ws_app/ws_app
SQL> insert into t values (456)
SQL> conn sys/sys as sysdba
Alter system switch logfile;
AT prod:
SQL> connect ws_app/ws_app
SQL> select * from t;
注意：这个查询应该有两条记录才对。
几个与流相关的数据字典：
DBA_APPLY
DBA_APPLY_ERROR
DBA_CAPTURE
DBA_PROPAGATION
DBA_ROLES
V$BUFFERED_QUEUES
dba_apply_conflict_columns
dba_apply_enqueue等。
可以利用这些字典来管理流，另外可以利用OEM来管理流。利用OEM管理流更加直观。
24 删除streams的配置
1． 停止并删除streams相关进程。
begin
dbms_capture_adm.stop_capture(capture_name =>'capture_na
me');
dbms_capture_adm.drop_capture(capture_name =>'capture_name');
dbms_propagation_adm.stop_propagation(propagation_name =>'propagation_name');
dbms_propagation_adm.drop_propagation(propagation_name =>'propagation_name');
dbms_apply_adm.stop_apply(apply_name =>'apply_name');
dbms_apply_adm.drop_apply(apply_name =>'apply_name');
end;
25 移除streams的整个配置
begin
dbms_streams_adm.remove_streams_configuration;
end;
26 删除streams配置脚本相关数据
--查找相关失败的数据
select*fromdba_recoverable_script_errors
--清除或回滚这些数据
begin
dbms_streams_adm.recover_operation(script_id =>'FCC27F88BDDD4EA59645D08B43C34DC6',operation_mode =>'PURGE');
end;