ORACLE STREAM 演示基于单表的流复制案例
主数据库:
操作系统:REDHAT 5 IP地址:192.168.10.87
数据库:Oracle 11.2.0.3
ORACLE_SID:DB1 Global_name:DB1
从数据库:
操作系统:REDHAT 5 IP地址:192.168.10.88
数据库:Oracle 11.2.0.3
ORACLE_SID:DB2 Global_name:DB2
一、配置
1.1设置初始化参数:
SQL> alter system set aq_tm_processes=2 scope=both;
--alter system set global_names=true scope=both;
alter system set job_queue_processes=10 scope=both;
alter system set parallel_max_servers=20 scope=both;
alter system set undo_retention=3600 scope=both;
alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;
alter system set streams_pool_size=200M scope=spfile;
alter system set utl_file_dir='*' scope=spfile;
alter system set open_links=4 scope=spfile;
System altered.
1.2 检查数据库是否为归档模式:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/oradata/DB1
Oldest online log sequence 313
Next log sequence to archive 315
Current log sequence 315
SQL>
如果不是归档模式,先配置:
startup mount;
alter database archivelog;
alter database open;
1.3 启用追加日志
可以基于Database级别或Table级别,启用追加日志(Supplemental Log)。在建立根据Schema粒度进行复制的Oracle Stream环境中,如果确认Schema下所有Table都有合理的主键(Primary Key),则不再需要启用追加日志。
#启用Database 追加日志
alter database add supplemental log data;
#启用Table追加日志
alter table add supplement log group log_group_name(table_column_name) always;
1.4 建立stream 管理用户
1.4.1创建主环境的Stream专用表空间
create tablespace stream_tbs datafile '/opt/app/oracle/oradata/DB2/stream_tbs01.dbf'
size 100M autoextend on maxsize unlimited segment space management auto;
1.4.2将logminer的数据字典从system表空间转移到新建的表空间,防止撑满system表空间
execute dbms_logmnr_d.set_tablespace('stream_tbs');
1.4.3创建Stream管理用户
create user strmadmin identified by oracle default tablespace stream_tbs temporary tablespace temp;
1.4.4授权Stream管理用户
grant connect,resource,dba,aq_administrator_role to strmadmin;
SQL> begin
2 dbms_streams_auth.grant_admin_privilege(
3 grantee=>'strmadmin',
4 grant_privileges=>true);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
1.5 在从环境中建立stream 管理用户
SQL> archive log file;
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/oradata/DB1
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
SQL> create tablespace stream_tbs datafile '/opt/app/oracle/oradata/DB1/stream_tbs01.dbf'
2 size 100M autoextend on maxsize unlimited segment space management auto;
Tablespace created.
SQL> execute dbms_logmnr_d.set_tablespace('stream_tbs');
PL/SQL procedure successfully completed.
SQL> create user strmadmin identified by oracle default tablespace stream_tbs temporary tablespace temp;
User created.
SQL> grant connect,resource,dba,aq_administrator_role to strmadmin;
Grant succeeded.
SQL> begin
2 dbms_streams_auth.grant_admin_privilege(
3 grantee=>'strmadmin',
4 grant_privileges=>true);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
1.6 配置网络连接
主从数据库(tnsnames.ora)中添加从数据库的配置
DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.87)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB1)
)
)
DB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.10.88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB2)
)
)
1.7 创建DBlink
根据Oracle 10gR2 Stream官方文档,针对主数据库建立的数据库链的名字必须和从数据库的global_name相同。
如果需要修改global_name,执行“alter database rename global_name to xxx”。
1.7.1创建主数据库数据库链
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
#建立数据库链
create database link db2_link connect to strmadmin identified by oracle using 'DB2';
1.7.1创建从数据库数据库链
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
#建立数据库链
SQL> create database link db2_link connect to strmadmin identified by oracle using 'DB2';
Database link created.
SQL> select name from v$database@db2_link;
select name from v$database@db2_link
*
ERROR at line 1:
ORA-02085: database link DB2_LINK connects to DB1
SQL> alter system set global_names=false;
System altered.
#测试连接是否成功
SQL> select name from v$database@db2_link;
NAME
---------------------------
DB2
1.8 创建流队列
1.8.1创建Master流队列
#以strmadmin身份,登录主数据库。
SQL> connect strmadmin/oracle
Connected.
begin
dbms_streams_adm.set_up_queue(
queue_table=>'db1_queue_table',
queue_name=>'db1_queue',
queue_user=>'strmadmin');
end;
/
PL/SQL procedure successfully completed.
查询队列:
SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN';
OWNER QUEUE_TABLE NAME
---------------------------------------------------------
STRMADMIN DB1_QUEUE_TABLE DB1_QUEUE
STRMADMIN DB1_QUEUE_TABLE AQ$_DB1_QUEUE_TABLE_E
1.8.2创建Backup流队列
#以strmadmin身份,登录从数据库。
SQL> connect strmadmin/oracle
Connected.
begin
dbms_streams_adm.set_up_queue(
queue_table=> 'db2_queue_table',
queue_name=>'db2_queue',
queue_user=>'strmadmin');
end;
/
PL/SQL procedure successfully completed.
SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN';
OWNER QUEUE_TABLE NAME
-------------------------------------------------
STRMADMIN DB2_QUEUE_TABLE DB2_QUEUE
STRMADMIN DB2_QUEUE_TABLE AQ$_DB2_QUEUE_TABLE_E
1.9 创建捕获进程 (对单表进行同步)
#以strmadmin身份,登录主数据库。(同样以hr用户做示例)。
connect strmadmin/oracle
begin
dbms_streams_adm.add_table_rules(
table_name => 'HR.TST',
streams_type => 'capture',
streams_name =>'capture_stream',
queue_name => 'STRMADMIN.DB1_QUEUE',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;
PL/SQL procedure successfully completed.
SQL> select capture_name,status from dba_capture;
CAPTURE_NAME STATUS
-------------------- ------------------------
CAPTURE_STREAM DISABLED
1.10.创建传播进程
connect strmadmin/oracle
begin
dbms_streams_adm.add_table_propagation_rules(
table_name =>'hr.tst',
streams_name => 'db1_to_db2',
source_queue_name =>'strmadmin.db1_queue',
destination_queue_name => 'strmadmin.db2_queue@db2_link',
include_dml => true,
include_ddl => true,
source_database =>'db1',
inclusion_rule => true,
queue_to_queue => true);
end;
/
SQL> select propagation_name,source_queue_name,destination_queue_name,destination_dblink,status from dba_propagation;
PROPAGATION_NAME SOURCE_QUEUE_NAME DESTINATION_QUEUE_NAME DESTINATION_DBLINK STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
DB1_TO_DB2 DB1_QUEUE DB2_QUEUE DB2_LINK ENABLED
SQL>
#修改propagation休眠时间为0,表示实时传播LCR。
begin
dbms_aqadm.alter_propagation_schedule(
queue_name =>'db1_queue',
destination=>'db2_link',
destination_queue=>'db2_queue',
latency => 0
);
end;
/
PL/SQL procedure successfully completed.
1.11 创建应用进程
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
begin
dbms_streams_adm.add_table_rules(
table_name => 'hr.tst',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name => 'strmadmin.db2_queue',
include_dml => true,
include_ddl => true,
source_database =>'db1',
inclusion_rule => true);
end;
/
SQL> select apply_name,queue_name,status from dba_apply;
APPLY_NAME
--------------------------------------------------------------------------------
QUEUE_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
DB2_QUEUE
DISABLED
1.12 实例复制
可以使用EXP/IMP 把用户HR 数据导入到从库中。
因我的机子前面做了GGS,这里不再进行实例复制。
exp userid= hr/hr@db1 file='/tmp/hr.dmp'
imp userid=system/sa file='/tmp/hr.dmp' ignore=y fromuser=hr touser=hr
因为只同步一个表,我在DB2中也建立了一个连接。并使用以下SQL 建立一个表:
SQL> create table hr.tst as select * from hr.tst@db1_link;
Table created.
SQL> select * from hr.tst;
no rows selected
SQL>
1.13 直接设置SCN的方式进行实例化:
---获取源库互置用户的SCN
connect strmadmin/oracle@DB1
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
4486128
---设置为目标库互置用户的SCN
connect strmadmin/oracle@db2
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
Source_object_name => 'hr.tst',
source_database_name => 'db1',
instantiation_scn => 4605643);
END;
/
在目标端修改SCN前后查询:
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
4597643
SQL> BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
Source_object_name => 'hr.tst',
source_database_name => 'db1',
instantiation_scn => 4597643);
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
4597431
SQL>
从库启动 APPLY:
SQL> exec dbms_apply_adm.start_apply('apply_stream');
PL/SQL procedure successfully completed.
SQL> select apply_name,status from dba_apply;
APPLY_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
ENABLED
主库启动CAPTURE
SQL> select capture_name,status from dba_capture;
CAPTURE_NAME STATUS
------------------------------------------------------------------------------------------ ------------------------
CAPTURE_STREAM DISABLED
SQL> execute dbms_capture_adm.start_capture(capture_name => 'CAPTURE_STREAM');
PL/SQL procedure successfully completed.
SQL> select capture_name,status from dba_capture;
CAPTURE_NAME STATUS
------------------------------------------------------------------------------------------ ------------------------
CAPTURE_STREAM ENABLED
SQL>
1.13 启动STREAM
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
#启动Apply进程
begin
dbms_apply_adm.start_apply(
apply_name => 'APPLY_STREAM');
end;
/
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
#启动Capture进程
begin
execute dbms_capture_adm.start_capture(capture_name => 'CAPTURE_STREAM');
end;
/
1.14 停止STREAM
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
#停止Capture进程
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_db1');
end;
/
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
#停止Apply进程
begin
dbms_apply_adm.stop_apply(
apply_name => 'APPLY_STREAM');
end;
/
1.15 清除所有配置信息
要清除Stream配置信息,需要先执行1.14,先停止Stream进程。
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
exec DBMS_STREAMS_ADM.remove_streams_configuration();
2#方法
exeucte dbms_streams_adm.remove_streams_queue(queue_name=>'db2_queue',cascade=>true,drop_unused_queue_table=>true);
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
exec DBMS_STREAMS_ADM.remove_streams_configuration();
2#方法
exec dbms_apply_adm.stop_apply(apply_name=>'apply_db2');
exec dbms_streams_adm.remove_queue(queue_name=>'db1_queue',cascade=>true,drop_unused_queue_table=>true);
二、测试数据同步
查询状态:
1.主库
SQL> conn strmadmin/oracle
Connected.
SQL> select capture_name,queue_name,status,captured_scn,applied_scn from dba_capture;
CAPTURE_NAME QUEUE_NAME STATUS CAPTURED_SCN APPLIED_SCN
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------ ------------ -----------
CAPTURE_STREAM DB1_QUEUE ENABLED 4480864 4480864
SQL> select propagation_name,source_queue_name,destination_queue_name,destination_dblink,status from dba_propagation;
PROPAGATION_NAME SOURCE_QUEUE_NAME DESTINATION_QUEUE_NAME DESTINATION_DBLINK STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
DB1_TO_DB2 DB1_QUEUE DB2_QUEUE DB2_LINK ENABLED
SQL> select apply_name,error_number,apply_captured,status from dba_apply;
APPLY_NAME
--------------------------------------------------------------------------------
ERROR_NUMBER APPLY_CAP STATUS
------------ --------- ------------------------
APPLY_STREAM
26714 YES ABORTED
测试:插入,修改,删除,查看目标服务器,数据同步完成。
在源库中删除表HR.TST,目标端出错:alert_db2.log
后,再目标库中重启APPLY进程。看到数据已同步过来了。
SQL> execute dbms_apply_adm.stop_apply(apply_name => 'APPLY_STREAM');
PL/SQL procedure successfully completed.
SQL> exec dbms_apply_adm.start_apply('apply_stream');
PL/SQL procedure successfully completed.
SQL> select apply_name,status from dba_apply;
APPLY_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
ENABLED
DB2_QUEUE
26687
ORA-26687: no instantiation SCN provided for "HR"."" in source database "DB1"
2013-09-08 17:34:31
QUEUE_NAME
--------------------------------------------------------------------------------
ERROR_NUMBER
------------
ERROR_MESSAGE
--------------------------------------------------------------------------------
ERROR_CREATION_TIME
-------------------
DB2_QUEUE
54
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2013-09-08 17:43:11
SQL> select * from hr.tst;
ID
----------
1
主数据库:
操作系统:REDHAT 5 IP地址:192.168.10.87
数据库:Oracle 11.2.0.3
ORACLE_SID:DB1 Global_name:DB1
从数据库:
操作系统:REDHAT 5 IP地址:192.168.10.88
数据库:Oracle 11.2.0.3
ORACLE_SID:DB2 Global_name:DB2
一、配置
1.1设置初始化参数:
SQL> alter system set aq_tm_processes=2 scope=both;
--alter system set global_names=true scope=both;
alter system set job_queue_processes=10 scope=both;
alter system set parallel_max_servers=20 scope=both;
alter system set undo_retention=3600 scope=both;
alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;
alter system set streams_pool_size=200M scope=spfile;
alter system set utl_file_dir='*' scope=spfile;
alter system set open_links=4 scope=spfile;
System altered.
1.2 检查数据库是否为归档模式:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/oradata/DB1
Oldest online log sequence 313
Next log sequence to archive 315
Current log sequence 315
SQL>
如果不是归档模式,先配置:
startup mount;
alter database archivelog;
alter database open;
1.3 启用追加日志
可以基于Database级别或Table级别,启用追加日志(Supplemental Log)。在建立根据Schema粒度进行复制的Oracle Stream环境中,如果确认Schema下所有Table都有合理的主键(Primary Key),则不再需要启用追加日志。
#启用Database 追加日志
alter database add supplemental log data;
#启用Table追加日志
alter table add supplement log group log_group_name(table_column_name) always;
1.4 建立stream 管理用户
1.4.1创建主环境的Stream专用表空间
create tablespace stream_tbs datafile '/opt/app/oracle/oradata/DB2/stream_tbs01.dbf'
size 100M autoextend on maxsize unlimited segment space management auto;
1.4.2将logminer的数据字典从system表空间转移到新建的表空间,防止撑满system表空间
execute dbms_logmnr_d.set_tablespace('stream_tbs');
1.4.3创建Stream管理用户
create user strmadmin identified by oracle default tablespace stream_tbs temporary tablespace temp;
1.4.4授权Stream管理用户
grant connect,resource,dba,aq_administrator_role to strmadmin;
SQL> begin
2 dbms_streams_auth.grant_admin_privilege(
3 grantee=>'strmadmin',
4 grant_privileges=>true);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
1.5 在从环境中建立stream 管理用户
SQL> archive log file;
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/oradata/DB1
Oldest online log sequence 39
Next log sequence to archive 41
Current log sequence 41
SQL> create tablespace stream_tbs datafile '/opt/app/oracle/oradata/DB1/stream_tbs01.dbf'
2 size 100M autoextend on maxsize unlimited segment space management auto;
Tablespace created.
SQL> execute dbms_logmnr_d.set_tablespace('stream_tbs');
PL/SQL procedure successfully completed.
SQL> create user strmadmin identified by oracle default tablespace stream_tbs temporary tablespace temp;
User created.
SQL> grant connect,resource,dba,aq_administrator_role to strmadmin;
Grant succeeded.
SQL> begin
2 dbms_streams_auth.grant_admin_privilege(
3 grantee=>'strmadmin',
4 grant_privileges=>true);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
1.6 配置网络连接
主从数据库(tnsnames.ora)中添加从数据库的配置
DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.87)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB1)
)
)
DB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.10.88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB2)
)
)
1.7 创建DBlink
根据Oracle 10gR2 Stream官方文档,针对主数据库建立的数据库链的名字必须和从数据库的global_name相同。
如果需要修改global_name,执行“alter database rename global_name to xxx”。
1.7.1创建主数据库数据库链
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
#建立数据库链
create database link db2_link connect to strmadmin identified by oracle using 'DB2';
1.7.1创建从数据库数据库链
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
#建立数据库链
SQL> create database link db2_link connect to strmadmin identified by oracle using 'DB2';
Database link created.
SQL> select name from v$database@db2_link;
select name from v$database@db2_link
*
ERROR at line 1:
ORA-02085: database link DB2_LINK connects to DB1
SQL> alter system set global_names=false;
System altered.
#测试连接是否成功
SQL> select name from v$database@db2_link;
NAME
---------------------------
DB2
1.8 创建流队列
1.8.1创建Master流队列
#以strmadmin身份,登录主数据库。
SQL> connect strmadmin/oracle
Connected.
begin
dbms_streams_adm.set_up_queue(
queue_table=>'db1_queue_table',
queue_name=>'db1_queue',
queue_user=>'strmadmin');
end;
/
PL/SQL procedure successfully completed.
查询队列:
SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN';
OWNER QUEUE_TABLE NAME
---------------------------------------------------------
STRMADMIN DB1_QUEUE_TABLE DB1_QUEUE
STRMADMIN DB1_QUEUE_TABLE AQ$_DB1_QUEUE_TABLE_E
1.8.2创建Backup流队列
#以strmadmin身份,登录从数据库。
SQL> connect strmadmin/oracle
Connected.
begin
dbms_streams_adm.set_up_queue(
queue_table=> 'db2_queue_table',
queue_name=>'db2_queue',
queue_user=>'strmadmin');
end;
/
PL/SQL procedure successfully completed.
SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN';
OWNER QUEUE_TABLE NAME
-------------------------------------------------
STRMADMIN DB2_QUEUE_TABLE DB2_QUEUE
STRMADMIN DB2_QUEUE_TABLE AQ$_DB2_QUEUE_TABLE_E
1.9 创建捕获进程 (对单表进行同步)
#以strmadmin身份,登录主数据库。(同样以hr用户做示例)。
connect strmadmin/oracle
begin
dbms_streams_adm.add_table_rules(
table_name => 'HR.TST',
streams_type => 'capture',
streams_name =>'capture_stream',
queue_name => 'STRMADMIN.DB1_QUEUE',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;
PL/SQL procedure successfully completed.
SQL> select capture_name,status from dba_capture;
CAPTURE_NAME STATUS
-------------------- ------------------------
CAPTURE_STREAM DISABLED
1.10.创建传播进程
connect strmadmin/oracle
begin
dbms_streams_adm.add_table_propagation_rules(
table_name =>'hr.tst',
streams_name => 'db1_to_db2',
source_queue_name =>'strmadmin.db1_queue',
destination_queue_name => 'strmadmin.db2_queue@db2_link',
include_dml => true,
include_ddl => true,
source_database =>'db1',
inclusion_rule => true,
queue_to_queue => true);
end;
/
SQL> select propagation_name,source_queue_name,destination_queue_name,destination_dblink,status from dba_propagation;
PROPAGATION_NAME SOURCE_QUEUE_NAME DESTINATION_QUEUE_NAME DESTINATION_DBLINK STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
DB1_TO_DB2 DB1_QUEUE DB2_QUEUE DB2_LINK ENABLED
SQL>
#修改propagation休眠时间为0,表示实时传播LCR。
begin
dbms_aqadm.alter_propagation_schedule(
queue_name =>'db1_queue',
destination=>'db2_link',
destination_queue=>'db2_queue',
latency => 0
);
end;
/
PL/SQL procedure successfully completed.
1.11 创建应用进程
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
begin
dbms_streams_adm.add_table_rules(
table_name => 'hr.tst',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name => 'strmadmin.db2_queue',
include_dml => true,
include_ddl => true,
source_database =>'db1',
inclusion_rule => true);
end;
/
SQL> select apply_name,queue_name,status from dba_apply;
APPLY_NAME
--------------------------------------------------------------------------------
QUEUE_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
DB2_QUEUE
DISABLED
1.12 实例复制
可以使用EXP/IMP 把用户HR 数据导入到从库中。
因我的机子前面做了GGS,这里不再进行实例复制。
exp userid= hr/hr@db1 file='/tmp/hr.dmp'
imp userid=system/sa file='/tmp/hr.dmp' ignore=y fromuser=hr touser=hr
因为只同步一个表,我在DB2中也建立了一个连接。并使用以下SQL 建立一个表:
SQL> create table hr.tst as select * from hr.tst@db1_link;
Table created.
SQL> select * from hr.tst;
no rows selected
SQL>
1.13 直接设置SCN的方式进行实例化:
---获取源库互置用户的SCN
connect strmadmin/oracle@DB1
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
4486128
---设置为目标库互置用户的SCN
connect strmadmin/oracle@db2
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
Source_object_name => 'hr.tst',
source_database_name => 'db1',
instantiation_scn => 4605643);
END;
/
在目标端修改SCN前后查询:
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
4597643
SQL> BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
Source_object_name => 'hr.tst',
source_database_name => 'db1',
instantiation_scn => 4597643);
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
4597431
SQL>
从库启动 APPLY:
SQL> exec dbms_apply_adm.start_apply('apply_stream');
PL/SQL procedure successfully completed.
SQL> select apply_name,status from dba_apply;
APPLY_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
ENABLED
主库启动CAPTURE
SQL> select capture_name,status from dba_capture;
CAPTURE_NAME STATUS
------------------------------------------------------------------------------------------ ------------------------
CAPTURE_STREAM DISABLED
SQL> execute dbms_capture_adm.start_capture(capture_name => 'CAPTURE_STREAM');
PL/SQL procedure successfully completed.
SQL> select capture_name,status from dba_capture;
CAPTURE_NAME STATUS
------------------------------------------------------------------------------------------ ------------------------
CAPTURE_STREAM ENABLED
SQL>
1.13 启动STREAM
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
#启动Apply进程
begin
dbms_apply_adm.start_apply(
apply_name => 'APPLY_STREAM');
end;
/
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
#启动Capture进程
begin
execute dbms_capture_adm.start_capture(capture_name => 'CAPTURE_STREAM');
end;
/
1.14 停止STREAM
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
#停止Capture进程
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_db1');
end;
/
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
#停止Apply进程
begin
dbms_apply_adm.stop_apply(
apply_name => 'APPLY_STREAM');
end;
/
1.15 清除所有配置信息
要清除Stream配置信息,需要先执行1.14,先停止Stream进程。
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
exec DBMS_STREAMS_ADM.remove_streams_configuration();
2#方法
exeucte dbms_streams_adm.remove_streams_queue(queue_name=>'db2_queue',cascade=>true,drop_unused_queue_table=>true);
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
exec DBMS_STREAMS_ADM.remove_streams_configuration();
2#方法
exec dbms_apply_adm.stop_apply(apply_name=>'apply_db2');
exec dbms_streams_adm.remove_queue(queue_name=>'db1_queue',cascade=>true,drop_unused_queue_table=>true);
二、测试数据同步
查询状态:
1.主库
SQL> conn strmadmin/oracle
Connected.
SQL> select capture_name,queue_name,status,captured_scn,applied_scn from dba_capture;
CAPTURE_NAME QUEUE_NAME STATUS CAPTURED_SCN APPLIED_SCN
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------ ------------ -----------
CAPTURE_STREAM DB1_QUEUE ENABLED 4480864 4480864
SQL> select propagation_name,source_queue_name,destination_queue_name,destination_dblink,status from dba_propagation;
PROPAGATION_NAME SOURCE_QUEUE_NAME DESTINATION_QUEUE_NAME DESTINATION_DBLINK STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
DB1_TO_DB2 DB1_QUEUE DB2_QUEUE DB2_LINK ENABLED
SQL> select apply_name,error_number,apply_captured,status from dba_apply;
APPLY_NAME
--------------------------------------------------------------------------------
ERROR_NUMBER APPLY_CAP STATUS
------------ --------- ------------------------
APPLY_STREAM
26714 YES ABORTED
测试:插入,修改,删除,查看目标服务器,数据同步完成。
在源库中删除表HR.TST,目标端出错:alert_db2.log
后,再目标库中重启APPLY进程。看到数据已同步过来了。
SQL> execute dbms_apply_adm.stop_apply(apply_name => 'APPLY_STREAM');
PL/SQL procedure successfully completed.
SQL> exec dbms_apply_adm.start_apply('apply_stream');
PL/SQL procedure successfully completed.
SQL> select apply_name,status from dba_apply;
APPLY_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_STREAM
ENABLED
DB2_QUEUE
26687
ORA-26687: no instantiation SCN provided for "HR"."" in source database "DB1"
2013-09-08 17:34:31
QUEUE_NAME
--------------------------------------------------------------------------------
ERROR_NUMBER
------------
ERROR_MESSAGE
--------------------------------------------------------------------------------
ERROR_CREATION_TIME
-------------------
DB2_QUEUE
54
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2013-09-08 17:43:11
SQL> select * from hr.tst;
ID
----------
1