基于用户的流复制配置 案例
主数据库:
操作系统: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_schema_rules(
SCHEMA_NAME=> 'hr',
STREAMS_TYPE=>'capture',
STREAMS_NAME=>'capture_db1',
QUEUE_NAME=>'strmadmin.db1_queue',
INCLUDE_DML=>true,
INCLUDE_DDL=>true,
SOURCE_DATABASE=> 'db1',
INCLUDE_TAGGED_LCR=>false,
INCLUSION_RULE=> true);
end;
/
PL/SQL procedure successfully completed.
SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;
CAPTURE_NAMEQ UEUE_NAME START_SCN STATUS CAPTURE_TYPE
---------- ------------------------ ---------------------------------------------
CAPTURE_DB1 DB1_QUEUE 4010410 ENABLED LOCAL
1.10.创建传播进程(对用户进行同步)
connect strmadmin/oracle
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'hr',
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,
include_tagged_lcr => false,
source_database => 'db1',
inclusion_rule => true,
queue_to_queue=>true);
end;
/
#修改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_schema_rules(
schema_name => 'hr',
streams_type => 'apply',
streams_name => 'apply_db2',
queue_name => 'db2_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'db1',
inclusion_rule => true);
end;
/
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
1.12.2 直接设置SCN的方式进行实例化:
---获取源库互置用户的SCN
connect strmadmin/oracle@DB1
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
4428237
---设置为目标库互置用户的SCN
connect strmadmin/oracle@db2
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name => 'hr',
source_database_name => 'db1',
instantiation_scn => 4428237);
END;
/
查询状态:
可以通过:
dba_apply
v$streams_apply_reader
v$streams_apply_coordinator
v$streams_apply_server
查看状态
SQL> select apply_name,queue_name,status from dba_apply;
APPLY_NAME
--------------------------------------------------------------------------------
QUEUE_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
APPLY_DB2
DB2_QUEUE
ENABLED
1.13 启动STREAM
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
#启动Apply进程
execute dbms_apply_adm.start_apply(apply_name => 'apply_db2');
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
#启动Capture进程
execute dbms_capture_adm.start_capture(capture_name => 'CAPTURE_DB1');
1.14 停止STREAM
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
#停止Capture进程
execute dbms_capture_adm.stop_capture(capture_name => 'CAPTURE_DB1');
#以strmadmin身份,登录从数据库。
connect strmadmin/oracle
#停止Apply进程
execute dbms_apply_adm.stop_apply(apply_name => 'APPLY_STREAM');
1.15 清除所有配置信息
要清除Stream配置信息,需要先执行1.14,先停止Stream进程。
#以strmadmin身份,登录主数据库。
connect strmadmin/oracle
exec DBMS_STREAMS_ADM.remove_streams_configuration();
2#方法
exec dbms_streams_adm.remove_queue(queue_name=>'DB1_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=>'DB2_QUEUE',cascade=>true,drop_unused_queue_table=>true);
二、测试数据同步
查询状态:
1.主库
SQL> select capture_name,queue_name,status,captured_scn,applied_scn from dba_capture;
CAPTURE_NAME QUEUE_NAME STATUS CAPTURED_SCN APPLIED_SCN
-------------- ------------------------- ------------------------ ------------ -----------
CAPTURE_DB1 DB1_QUEUE ENABLED 4423352 4423352
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_DB2 YES ENABLED
测试:插入,修改,删除,及建立表,查看目标服务器,数据同步完成。
测试通过。
查询状态SQL
select owner,name from dba_queues where owner='STRMADMIN';
select capture_name,queue_name,rule_set_name,negative_rule_set_name,status from dba_capture;
select propagation_name,source_queue_name,destination_queue_name,destination_dblink,status from dba_propagation;
select capture_name,queue_name,status,captured_scn,applied_scn from dba_capture;
select apply_name,error_number,apply_captured,status from dba_apply;
select queue_name,error_number,error_message,error_creation_time from dba_apply_error;