环境:
OS:Red Hat Linux As 5
DB:10.2.0.4
主库:HostA
备库:HostB
Oracle包dbms_streams_adm中提供了global级(视图dba_streams_global_rules),schema级(dba_streams_schema_rules),table级(dba_streams_table_rules)的流复制,以下是针对某个schema而实现的流复制.
1.HostA和HostB必须在归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/duplicate/
Oldest online log sequence 19
Next log sequence to archive 21
Current log sequence 21
------ 以下操作需要在sys下执行--------
2.HostA 配置supplemental log
SQL>alter database add supplemental log data;
3.HostA和HostB 配置相应参数
SQL> alter system set job_queue_processes=4 scope=both;
SQL> alter system set aq_tm_processes=4 scope=both;
SQL> alter system set global_names=true scope=both;
4.HostA和HostB配置global_name
HostA:
SQL> alter system set global_names=true scope=both;
SQL> alter database rename global_name to hxl.com;
HostB:
SQL> alter system set global_names=true scope=both;
SQL> alter database rename global_name to standby.com;
5.HostA和HostB创建表空间以及相应用户
HostA:
SQL>create tablespace streams_tbs datafile '/u01/app/oracle/oradata/oracl/streams_tbs01.dbf' size 1024M;
SQL>create user strmadmin identified by strmadmin
default tablespace streams_tbs
quota unlimited on streams_tbs;
SQL>grant dba,select_catalog_role to strmadmin;
HostB:
SQL>create tablespace streams_tbs datafile '/u02/app/oracle/oradata/streams_tbs01.dbf' size 1024M;
SQL>create user strmadmin identified by strmadmin
default tablespace streams_tbs
quota unlimited on streams_tbs;
SQL>grant dba,select_catalog_role to strmadmin;
6.HostA和HostB上给用户strmadmin赋予权限
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;
7.配置listener.ora和tnsnames.ora
HostA：tnsping STANDBY
HostB：tnsping TEST
确保以上能够连通.
-------- 以下操作在strmadmin用户下执行---------
8.HostA 创建dblink
SQL>create database link standby.com connect to strmadmin identified by strmadmin using 'standby';
注意这里dblink的名必须是HostB配置的global_name.
9.HostA和HostB上创建队列
begin
dbms_streams_adm.set_up_queue();
end;
默认创建queue为streams_queue, queue_table为 streams_queue_table.
相关系统视图:dba_queues,dba_queue_tables.
10.HostA 上创建Stream propagation
Begin
Dbms_Streams_Adm.Add_Schema_Propagation_Rules
(Schema_Name => 'scott',
Streams_Name => 'TEST_to_STANDBY',
Source_Queue_Name => 'strmadmin.streams_queue',
Destination_Queue_Name => 'strmadmin.streams_queue@STANDBY.com',
Include_Dml => True,
Include_Ddl => True,
Source_Database => 'hxl.com',
Inclusion_Rule => True,
Queue_To_Queue => True
);
End;
相关视图:dba_propagation
11.HostA上创建Capture进程
BEGIN
dbms_streams_adm.add_schema_rules
(
schema_name => 'scott',
streams_type => 'capture',
streams_name => 'capture_test',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true
);
END;
相关视图:dba_capture,dba_rules,dba_rule_set_rules
12.将HostA上的scott schema数据导到HostB上
HostB:
SQL>drop user scott cascade;
SQL> create public database link hxl.com connect to strmadmin identified by strmadmin using 'TEST';
$impdp strmadmin/strmadmin network_link=hxl.com schemas=scott;
13.在 HostA 上设置STANDBY上scott schema的Instantiation SCN;
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@STANDBY.com
(
source_schema_name => 'scott',
source_database_name => 'hxl.com',
instantiation_scn => iscn,
recursive => true
);
END;
14.在HostB上创建apply进程apply_STANDBY:
BEGIN
dbms_streams_adm.add_schema_rules(
schema_name => 'scott',
streams_type => 'apply',
streams_name => 'apply_STANDBY',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'hxl.com',
inclusion_rule => true);
END;
15.在HostB上启动capture和apply
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_STANDBY',
parameter => 'disable_on_error',
value => 'n');
END;
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_STANDBY');
END;
16 在HostA上启动capture process
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name =>'capture_test');
END;
17.测试
HostA:
SQL>update emp set ENAME='HXLLI' where empno=7902;
SQL>commit;
HostB:
SQL> select empno,ENAME from emp where empno=7902;
EMPNO ENAME
---------- ----------
7902 HXLLI
Streams配置和管理相关package
dbms_steams_adm
dbms_capture_adm
dbms_propagation_adm
dbms_apply_adm
dbms_rule_adm
Streams主要相关数据字典
主库:
dba_capture;
dba_capture_prepared_tables;
dba_capture_parameters;
dba_capture_extra_attributes;
dba_capture_prepared_schemas;
dba_streams_administrator
dba_propagation
dba_registered_archived_log
dba_rules
dba_rule_sets
dba_rule_set_rules
v$streams_capture
v$streams_pool_advice
v$streams_transaction
v$propagation_receiver
v$propagation_sender
v$streams_apply_coordinator
v$streams_apply_reader
v$streams_apply_server
v$buffered_publishers
备库:
dba_apply
dba_apply_confict_columns
dba_apply_dml_handlers
dba_apply_enqueue
dba_apply_error
dba_apply_execute
dba_apply_parameters
dba_apply_progress
排错:
主库和备库都重启后发现之前创建的流复制不起作用了,问题找了很久,最后发现是PROPAGATION因为之前的错误而停止了,重新启动该PROPAGATION,问题解决.
1.主库的capture没问题
SQL> SELECT status FROM DBA_CAPTURE t Where t.capture_name = 'CAPTURE_TEST';
STATUS
--------
ENABLED
2.备库的apply也没问题
SQL> select status from dba_apply t Where t.apply_name ='APPLY_STANDBY';
STATUS
--------
ENABLED
3.发现主库的PROPAGATION失效
SQL> SELECT status FROM DBA_PROPAGATION t Where t.propagation_name = 'TEST_TO_STANDBY';
STATUS
--------
DISENABLED
4.重启动PROPAGATION,主库的数据就能传输到备库了.
Begin
Dbms_Propagation_Adm.Start_Propagation(Propagation_Name => 'TEST_TO_STANDBY');
End;
-- The End --
