描述:
原来在两个数据库服务器直接配置了Streams的单向传输,
原来的源数据库为两台集群64位HP-UNIX的Oracle11gR2数据库,
原来的目标数据库为1台64位Linux的Oracle11gR2的数据库,
现在上面的基础上,把1台64位Linux的Oracle11gR2的当作源数据库,把两台集群64位HP-UNIX的Oracle11gR2数据库当作目标数据库,
安装Streams的单向传输步骤进行配置,这样就变成了两个数据库服务器之间的Steams的双向传输。
10.2.2.1:为源数据库 10.1.1.1及10.1.1.2 集群数据库为目标数据库
1、源数据库归档模式
源:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 420
Next log sequence to archive 422
Current log sequence 422
2、在源数据库启用追加日志
启用辅助日志
SQL> alter database add supplemental log data;
Database altered.
3、需要传输testUser 用户的数据,在源数据库创建source队列
> sqlplus strmadmin/strmadmin
SQL> BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(
2 queue_table=>'SOURCE_QUEUE_TABLE',
3 queue_name=>'SOURCE_QUEUE',
4 queue_user=>'strmadmin');
5 END;
6 /
PL/SQL procedure successfully completed.
或者:
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
该命令会创建一个队列缺省名:streams_queue,队列表缺省是:STREAMS_QUEUE_TABLE
队列存储的object类型是anaydata
移除队列:
exec dbms_streams_adm.remove_queue(
queue_name => 'streams_queue',
cascade => true,
drop_unused_queue_table => true);
可以用查询dba_queues,dba_queue_tables来检查:
SQL> select owner,queue_table,name from dba_queues where wner='STRMADMIN';
OWNER QUEUE_TABLE NAME
----------------- ------------------------- ------------------
STRMADMIN SOURCES_QUEUE_TABLE SOURCES_QUEUE
STRMADMIN SOURCES_QUEUE_TABLE AQ$_SOURCES_QUEUE_TABLE_E
SQL>select owner,queue_table,object_type from dba_queue_tables where wner='STRMADMIN';
OWNER QUEUE_TABLE OBJECT_TYPE
-------------- --------------------- ------------------
STRMADMIN SOURCES_QUEUE_TABLE SYS.ANYDATA
4、在目标数据库创建接收队列
$ sqlplus strmadmin/strmadmin
SQL> BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(
2 queue_table=>'TARGET_QUEUE_TABLE',
3 queue_name=>'TARGET_QUEUE',
4 queue_user=>'strmadmin');
5 END;
6 /
PL/SQL procedure successfully completed.
5、在源数据库创建capture进程
CBDBS01-> sqlplusstrmadmin/strmadmin@CBOMS
SQL> BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
2 schema_name=>'testUser',
3 streams_type=>'capture',
4 streams_name=>'capture_stream',
5 queue_name=>'strmadmin.SOURCE_QUEUE',
6 include_dml=>true,
7 include_ddl=>true,
8 SOURCE_DATABASE=>'bhoms',
9 include_tagged_lcr=>false,
10 inclusion_rule=>true);
11 END;
12 /
PL/SQL procedure successfully completed.
可以通过dba_capture查看:
SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;
SQL> select * from ALL_CAPTURE_PREPARED_SCHEMAS;
6、在源数据库继续创建传播进程
CBDBS01-> sqlplus strmadmin/strmadmin
SQL> BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
2 schema_name=>'testUser',
3 streams_name=>'source_to_target',
4 source_queue_name=>'strmadmin.SOURCE_QUEUE',
5 destination_queue_name=>'strmadmin.TARGET_QUEUE@CBOMS',
6 include_dml=>true,
7 include_ddl=>true,
8 source_database=>'bhoms',
9 inclusion_rule=>true,
10 queue_to_queue=>true);
11 END;
12 /
PL/SQL procedure successfully completed.
重新启动propagation process
查看propagation process名称
select * from all_propagation;
可以通过dba_propagations查看propagation是否启动
SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,DESTINATION_QUEUE_NAME,DESTINATION_DBLINK,STATUS from dba_propagation
如果status不是“ENABLED”,而是“DISABLED”
可以启动
SQL>exec dbms_propagation_adm.start_propagation('source_to_target'); (关闭为exec dbms_propagation_adm.stop_propagation('source_to_target');)
7、在目标数据库创建apply进程
$ sqlplus strmadmin/strmadmin
SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
3 schema_name=>'testUser',
4 streams_type=>'apply',
5 streams_name=>'target_apply_stream',
6 queue_name=>'strmadmin.TARGET_QUEUE',
7 include_dml=>true,
8 include_ddl=>true,
9 include_tagged_lcr=>false,
10 source_database=>'bhoms',
11 inclusion_rule=>true);
12 END;
13 /
PL/SQL procedure successfully completed.
可以通过:
dba_apply
v$streams_apply_reader
v$streams_apply_coordinator
v$streams_apply_server
8、直接设置SCN的方式进行实例化
源:
获取源库互置用户的SCN
sqlplus strmadmin/strmadmin
SQL> set serveroutput on
SQL> DECLARE
2 iscn NUMBER;
3 BEGIN
4 iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_OUTPUT.PUT_LINE('Instantiation SCN is :'||iscn);
6 END;
7 /
Instantiation SCN is :5475315
PL/SQL procedure successfully completed.
在目标数据库:
设置为目标库互置用户的SCN
sqlplus strmadmin/strmadmin
SQL> BEGIN
2 DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
3 source_schema_name=>'testUser',
4 source_database_name=>'bhoms',
5 instantiation_scn=>&iscn);
6 END;
7 /
Enter value for iscn: 5475315
old 5: instantiation_scn=>&iscn);
new 5: instantiation_scn=>5475315);
PL/SQL procedure successfully completed.
9、在目标数据库启动Apply进程
SQL> BEGIN
2 DBMS_APPLY_ADM.SET_PARAMETER(
3 apply_name => 'target_apply_stream',
4 parameter=>'disable_on_error',
5 value=>'n');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_APPLY_ADM.START_APPLY(
3 apply_name=>'target_apply_stream');
4 END;
5 /
PL/SQL procedure successfully completed.
附注:
1、#停止Apply进程
SQL>begin
dbms_apply_adm.stop_apply(
apply_name => 'target_apply_stream');
end;
/
2、启动Apply进程
begin
dbms_apply_adm.start_apply(
apply_name => 'target_apply_stream');
end;
/
查看Apply状态
SQL> select apply_name,queue_name,status from dba_apply;
3、启动传播进程
exec dbms_propagation_adm.start_propagation('source_to_target');
4、在源数据库上启动capture
SQL> BEGIN
2 DBMS_CAPTURE_ADM.START_CAPTURE(
3 capture_name=>'capture_stream');
4 END;
5 /
PL/SQL procedure successfully completed.
5、#停止Capture进程
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_stream');
end;
/
begin
dbms_capture_adm.start_capture(
capture_name => 'capture_stream');
end;
/
查看Capture状态:
SQL> select capture_name,status from dba_capture;
7、把数据库的归档日志空间设置成20GB
show parameter recover
alter system set db_recovery_file_dest_size=20G scope=both;
如果空间太小,还需要写个rman的脚本删除多余的归档日志。
8、在目标数据库没有实例化错误ORA-26687:
在集群的select * from dba_apply_error
ORA-26687: no instantiation SCN provided for "testUser"."TEST11" in source database "BHOMS"
在目标数据库查看初始化数据是否成功:
select source_database,source_object_name,instantiation_scn from dba_apply_instantiated_objects;
如果查询出来的对象所对应的source_database为源数据库的数据库名称则是已经实例化了
解决:
如果查询出来的对象所对应的source_database不是源数据库的数据库名,则可以修改数据库的数据字典(需要超级用户sys进行操作)
select source_database,source_object_name,instantiation_scn from dba_apply_instantiated_objects order by instantiation_scn
for update
把对象所对应的source_database不是源数据库的数据库名的数据查询出来
select * from dba_apply_instantiated_objects s where s.source_database='CBOMS'
把下面的这些表对象的source_database值更改为源数据库的数据库名
update dba_apply_instantiated_objects s set s.source_database='BHOMS'
where s.source_database='CBOMS' and s.source_object_owner='testUser'
and s.source_object_name not like 'T%'
这样可以解决了。
查看数据库表的对应的scn
SELECT * FROM DBA_CAPTURE_PREPARED_TABLES order by scn
启动capture,propagation,apply并测试: select * from dba_capture; select * from dba_propagation; select * from dba_apply; select * from dba_apply_error; select * from dba_apply_instantiated_objects; select * from dba_rule_set_rules; select * from dba_rule_sets; select * from dba_rules;