本文详细介绍了在Oracle环境下，从主机oracle.sannet.net创建hdb和itods两个数据库，配置主备库间HR数据同步的完整流程。包括数据库归档、追加日志启用、参数调整、表空间创建、用户授权、tnsnames配置、DBlink建立、队列创建、传播和捕捉进程设置等关键步骤，并最终实现主备库间的实时数据同步。
环境准备
一台主机oracle.sannet.net上创建hdb,itods两个数据库,配置hdb上HR至itods上HR的Stream.
Stream创建
1-5 sys用户下操作
1,主，备库启用归档
SQL>startup mount;
SQL>alter database arhivelog;
SQL>alter database open;
2,主库启用追加日志
SQL>alter database add supplemental log data;
3,主，备库修改global_names,global_name参数
global_name=true
global_name
主:hdb.oracle.sannet.net
备:itods.oracle.sannet.net
4,主，备库几个参数修改
主:
global_names=true
aq_tm_process=2
job_queue_processes=10
logmnr_max_persistent_sessions=1(默认为1,需确认)
open_links=4
db_domain=oracle.sannet.net
service_names=hdb.oracle.sannet.net
备:
global_names=true
aq_tm_process=2
job_queue_processes=10
logmnr_max_persistent_sessions=1(默认为1,需确认)
open_links=4
db_domain=oracle.sannet.net
service_names=itods.oracle.sannet.net
5,主，备库创建表空间，用户,并授权。
SQL>create tablespace stream_ts datafile '/u01/app/oracle/oradata/hdb/stream_ts.ora' size 25m reuse autoextend on maxsize unlimited;
SQL>create user strmadmin identified by strmadmin default tablespace stream_ts quota unlimited on stream_ts;
SQL>grant dba to strmadmin;
6,主，备库配置tnsnames.ora使得strmadmin可以正常访问两者.
[oracle@oracle ~]$ less $ORACLE_HOME/network/admin/tnsnames.ora
hdb.oracle.sannet.net =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.sannet.net)(PORT = 1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hdb.oracle.sannet.net)
(INSTANCE_NAME = hdb)
)
)
itods.oracle.sannet.net =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.sannet.net)(PORT = 1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = itods.oracle.sannet.net)
(INSTANCE_NAME = itods)
)
)
主:sqlplus strmadmin/strmadmin@itods.oracle.sannet.net
备:sqlplus strmadmin/strmadmin@hdb.oracle.sannet.net
以下在strmadmin用户下操作
7,主库创建DB link
SQL> connect strmadmin/strmadmin
SQL> create database link itods.oracle.sannet.net connect to strmadmin identified by strmadmin using 'itods.oracle.sannet.net';
SQL> select * from user_db_links;
DB_LINK USERNAME PASSWORD HOST CREATED
------------------------------ ------------------------------ ---------- ------------------------ -----------------
ITODS.ORACLE.SANNET.NET STRMADMIN itods.oracle.sannet.net 17-FEB-12
SQL> select * from global_name@itods.oracle.sannet.net;
GLOBAL_NAME
----------------------------------------
ITODS.ORACLE.SANNET.NET
8,主，备库创建队列queue
SQL> exec dbms_streams_adm.set_up_queue();
PL/SQL procedure successfully completed.
默认情况下的队列名为STREAMS_QUEUE,queue_table为STREAMS_QUEUE_TABLE
SQL> select owner,queue_table,name from dba_queues where owner='STRMADMIN';
OWNER QUEUE_TABLE NAME
------------------------------ ------------------------------ ------------------------------
STRMADMIN STREAMS_QUEUE_TABLE AQ$_STREAMS_QUEUE_TABLE_E
STRMADMIN STREAMS_QUEUE_TABLE STREAMS_QUEUE
SQL> conn hr/hr
Connected.
注意:主备库均创建.
9,主库创建propagation进程
SQL> begin
2 dbms_streams_adm.add_schema_propagation_rules(
3 schema_name => 'hr',
4 streams_name => 'hr_hdb_to_itods',
5 source_queue_name => 'strmadmin.streams_queue',
6 destination_queue_name => 'strmadmin.streams_queue@itods.oracle.sannet.net',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'hdb.oracle.sannet.net',
10 inclusion_rule => true,
11 queue_to_queue => true);
12 end;
13 /
PL/SQL procedure successfully completed.
查看创建的propagation
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
------------------- -------------------- ------------------------- ---------------------------- --------
HR_HDB_TO_ITODS STREAMS_QUEUE STREAMS_QUEUE ITODS.ORACLE.SANNET.NET ENABLED
#######################################################
源库的propagation有异常disable的话,开启.
SQL> select PROPAGATION_NAME,status from dba_propagation;
PROPAGATION_NAME STATUS
------------------------------ --------
HR_HDB_TO_ITODS DISABLED
SQL> begin dbms_propagation_adm.start_propagation(PROPAGATION_NAME => 'HR_HDB_TO_ITODS');
2 end;
3 /
#######################################################
10,主库创建capture进程
SQL> begin
2 dbms_streams_adm.add_schema_rules(
3 schema_name => 'hr',
4 streams_type => 'capture',
5 streams_name => 'capture_hdb',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> select capture_name,queue_name,start_scn,status,capture_type from dba_capture;
CAPTURE_NAME QUEUE_NAME START_SCN STATUS CAPTURE_TY
------------------------------ ------------------------------ ---------- -------- ----------
CAPTURE_HDB STREAMS_QUEUE 1364525 DISABLED LOCAL
SQL> select * from all_capture_prepared_schemas;
SCHEMA_NAME TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME
------------------------------ --------- -------- -------- -------- --------
HR 17-FEB-12 IMPLICIT IMPLICIT IMPLICIT NO
11,将主库HR方案下的数据导入到备库itods中
通过db link将数据从hdb导入到itods
在备库创建db link
SQL> create public database link hdb.oracle.sannet.net connect to system identified by system using 'hdb.oracle.sannet.net';
Database link created.
SQL> select * from global_name@hdb.oracle.sannet.net;
GLOBAL_NAME
--------------------------------------------------------------------------------
HDB.ORACLE.SANNET.NET
在备库上导入,请准备好hr方案涉及的用户和表空间.
[oracle@oracle ~]$ impdp system/system network_link=hdb.oracle.sannet.net schemas=hr
12,在主库上设置备库HR方案的instantiation scn
SQL> show user
USER is "STRMADMIN"
SQL> select name from v$database;
NAME
---------
HDB
SQL> declare
2 iscn number;
3 begin
4 iscn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_schema_instantiation_scn@itods.oracle.sannet.net(
6 source_schema_name => 'hr',
7 source_database_name => 'hdb.oracle.sannet.net',
8 instantiation_scn => iscn,
9 recursive => true);
10 end;
11 /
PL/SQL procedure successfully completed.
13,备库上创建apply进程
SQL> conn strmadmin/strmadmin
Connected.
SQL> begin
2 dbms_streams_adm.add_schema_rules(
3 schema_name => 'hr',
4 streams_type => 'apply',
5 streams_name => 'hr_itods_apply',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'hdb.oracle.sannet.net',
10 inclusion_rule => true);
11 end;
12 /
PL/SQL procedure successfully completed.
查看apply
SQL> select apply_name,queue_name,status from dba_apply;
APPLY_NAME QUEUE_NAME STATUS
------------------------------ ------------------------------ --------
HR_ITODS_APPLY STREAMS_QUEUE DISABLED
14,备库启动apply进程
SQL> show user
USER is "STRMADMIN"
SQL> begin
2 dbms_apply_adm.set_parameter(
3 apply_name => 'hr_itods_apply',
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 => 'hr_itods_apply');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select apply_name,queue_name,status from dba_apply;
APPLY_NAME QUEUE_NAME STATUS
---------------------------- ------------------------------ --------
HR_ITODS_APPLY STREAMS_QUEUE ENABLED
15,主库上启动capture进程
SQL> begin
2 dbms_capture_adm.start_capture(
3 capture_name => 'CAPTURE_HDB');
4 end;
5 /
PL/SQL procedure successfully completed.
16,验证stream的结果
主:插入一条记录
SQL>conn hr/hr
SQL>select * from mytable_1;
ID NAME
---------- ------------------------------
1 user01
2 user02
3 user03
4 user04
SQL>insert into mytable_1 values(5,'user_strm');
SQL>commit;
SQL>select * from mytable_1;
ID NAME
---------- ------------------------------
1 user01
2 user02
3 user03
4 user04
5 user_strm
备:查询主库插入的记录
SQL>conn hr/hr
SQL>select * from mytable_1;
ID NAME
---------- ------------------------------
1 user01
2 user02
3 user03
4 user04
5 user_strm
配置成功.
17,附录常见问题
(1),需要在queue_name中指定schema,queue_name='strmadin.source_queue'
SQL> begin
2 dbms_streams_adm.add_schema_rules(
3 schema_name => 'hr',
4 streams_type => 'capture',
5 streams_name => 'source_capture_stream',
6 queue_name => 'source_queue',
7 include_dml => true,
8 include_ddl => true,
9 include_tagget_lcr => false,
10 source_database => null,
11 inclusion_rule => true);
12 end;
13 /
dbms_streams_adm.add_schema_rules(
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'ADD_SCHEMA_RULES'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
(2),正常配置状态中,清除stream配置信息,先停止capture和apply进程.
停止capture,apply进程
主:
sqlplus strmadmin/strmadmin
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture名称');
end;
/
备:
sqlplus strmadmin/strmadmin
begin
dbms_apply_adm.stop_apply(
apply_name => 'apply名称');
end;
/
清除配置
sqlplus strmadmin/strmadmin
exec dbms_streams_adm.remove_streams_configuration();
备:
sqlplus strmadmin/strmadmin
exec dbms_streams_adm.remove_streams_configuration();
(3),如果主备机有异常,清除stream如下http://docs.oracle.com/cd/B19306_01/server.102/b14229/whatsnew.htm#i971129
删除propagation
exec DBMS_PROPAGATION_ADM.DROP_PROPAGATION(propagation_name=>'HR_HDB_TO_ITODS');
删除capture
exec DBMS_CAPTURE_ADM.DROP_CAPTURE(capture_name=>'CAPTURE_HDB');
删除apply
exec DBMS_APPLY_ADM.drop_apply(apply_name=>'HR_ITODS_APPLY');
删除队列,会自动删除queue_table
begin
dbms_streams_adm.remove_queue(
queue_name => '对列名');
end;
/
(4),诊断视图
dba_capture,dba_apply,dba_queue,dba_queue_tables
(5),另外对不同schema进行流的应用.
用DBMS_STREAMS_ADM.RENAME_SCHEMA 这个包
参阅网友文档
http://trailblizer.blog.163.com/blog/static/5963036420100136149244/
http://blog.itpub.net/post/42426/501576
http://wenku.baidu.com/view/d8cfb864783e0912a2162ae3.html
http://tech.it168.com/db/o/2006-05-26/200605262248749.shtml
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
