-------------------------------------------------------------------------------

主数据库：

　　操作系统：windows 2012

　　IP地址：192.168.160.122

　　数据库：Oracle 11.2.0.2.0

　　ORACLE_SID：orcl

　　Global_name：orcl

　　从数据库：

　　操作系统：windows 2012

　　IP地址：192.168.160.123

　　数据库：Oracle 11.2.0.2.0

　　ORACLE_SID：orcl

　　Global_name：orcl

alter system set deferred_segment_creation=false scope=both;

alter system set sec_case_sensitive_logon=false scope=both;

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

主数据库配置

archive log list;

select supplemental_log_data_min from v$database;

shutdown immediate;

startup mount;

alter database archivelog;

alter database add supplemental log data;

archive log list;

select supplemental_log_data_min from v$database;

alter database open;

设置参数

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=100M scope=spfile;

　　alter system set utl_file_dir='*' scope=spfile;

　　alter system set open_links=4 scope=spfile;

alter system set statistics_level='TYPICAL' scope=both;

alter system set utl_file_dir='*' scope=spfile;

alter system set "_job_queue_interval"=1 scope=spfile;

alter system set global_names=false scope=both;

shutdown immediate;

startup;

create user hytera identified by hytera account unlock;

grant connect to hytera;

grant RESOURCE to hytera;

grant dba to hytera;

--alter system set logmnr_max_persistent_sessions=1 scope=spfile;

create tablespace streams_tbs datafile 'D:\app\Administrator\oradata\orcl\streams_tbs.dbf' size 100M autoextend on maxsize unlimited;

create user strmadmin identified by strmadmin default tablespace streams_tbs account unlock quota unlimited on streams_tbs;

grant dba to strmadmin;

grant connect to strmadmin;

exec dbms_streams_auth.grant_admin_privilege('strmadmin');

begin

dbms_streams_auth.grant_admin_privilege(

grantee=>'strmadmin',

grant_privileges=>TRUE);

end;

/

tnsnames.ora

SZXTDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.160.123)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

conn strmadmin/strmadmin;

create database link SZXTDB connect to strmadmin identified by strmadmin using 'SZXTDB';

创建Master流队列

#以strmadmin身份，登录主数据库。

connect strmadmin/strmadmin;

begin

dbms_streams_adm.set_up_queue(

queue_table => 'source_queue_table',--队列表

queue_name => 'source_queue',--队列

queue_user =>'strmadmin'); --队列用户

end;

/

select owner,queue_table,name from dba_queues where owner='STRMADMIN';

 

创建数据捕获进程,捕获redo的改变

connect strmadmin/strmadmin;

begin

dbms_streams_adm.add_schema_rules(

schema_name => 'hytera', --捕获用户名 非sys或者system

streams_type => 'capture',

streams_name => 'capture_stream',

queue_name => 'strmadmin.source_queue',

include_dml => true,

include_ddl => true,

include_tagged_lcr => false,

source_database => 'ORCL',

inclusion_rule => true);

end;

/

select capture_name,queue_name,start_scn,status,capture_type from dba_capture;

在源数据库上创建传播进程 相当于ogg里面的datapump数据传递

传播进程需要对应捕获规则分别创建table/schema/global的传播进程。

connect strmadmin/strmadmin;

begin

dbms_streams_adm.add_schema_propagation_rules(

schema_name => 'hytera',

streams_name => 'source_to_target',

source_queue_name => 'strmadmin.SOURCE_QUEUE',

destination_queue_name => 'strmadmin.TARGET_QUEUE@szxtdb',

include_dml => true,

include_ddl => true,

source_database => 'ORCL',

inclusion_rule => true,

queue_to_queue => true);

end;

/

select propagation_name,status from all_propagation;

直接设置SCN的方式进行实例化

查询源端的scn号

select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM dual;

启动捕获进程 相当于OGG里面的ext1

BEGIN

DBMS_CAPTURE_ADM.START_CAPTURE(

capture_name => 'capture_stream');

END;

/

停止捕获进程

BEGIN

DBMS_CAPTURE_ADM.STOP_CAPTURE(

capture_name => 'capture_stream');

END;

/

查看捕获进程状态

select capture_name,status from dba_capture;

表示实时传播lcr实时复制

begin

dbms_aqadm.alter_propagation_schedule(

queue_name => 'strmadmin.SOURCE_QUEUE',

destination => 'szxtdb',

destination_queue => 'TARGET_QUEUE',

latency => 0);

end;

/

-------------------------------------------------------------------------------------------------------------------------

备数据库配置

archive log list;

select supplemental_log_data_min from v$database;

shutdown immediate;

startup mount;

alter database archivelog;

alter database add supplemental log data;

archive log list;

select supplemental_log_data_min from v$database;

alter database open;

设置参数

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=100M scope=spfile;

　　alter system set utl_file_dir='*' scope=spfile;

　　alter system set open_links=4 scope=spfile;

alter system set statistics_level='TYPICAL' scope=both;

alter system set utl_file_dir='*' scope=spfile;

alter system set "_job_queue_interval"=1 scope=spfile;

alter system set global_names=false scope=both;

shutdown immediate;

startup;

--alter system set logmnr_max_persistent_sessions=1 scope=spfile;

create user hytera identified by hytera account unlock;

grant connect to hytera;

grant RESOURCE to hytera;

grant dba to hytera;

create tablespace streams_tbs datafile 'D:\app\Administrator\oradata\orcl\streams_tbs.dbf' size 100M autoextend on maxsize unlimited;

create user strmadmin identified by strmadmin default tablespace streams_tbs account unlock quota unlimited on streams_tbs;

grant dba to strmadmin;

grant connect to strmadmin;

exec dbms_streams_auth.grant_admin_privilege('strmadmin');

begin

dbms_streams_auth.grant_admin_privilege(

grantee=>'strmadmin',

grant_privileges=>TRUE);

end;

/

tnsnames.ora

SZXTDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.160.122)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

conn strmadmin/strmadmin;

create database link SZXTDB connect to strmadmin identified by strmadmin using 'SZXTDB';

创建备库流队列

#以strmadmin身份，登录主数据库。

connect strmadmin/strmadmin;

begin

dbms_streams_adm.set_up_queue(

queue_table => 'target_queue_table',--队列表

queue_name => 'target_queue',--队列

queue_user =>'strmadmin'); --队列用户

end;

/

select owner,queue_table,name from dba_queues where owner='STRMADMIN';

目标端数据库上创建Apply队列应用进程 相当于OGG中的rep1进程

connect strmadmin/strmadmin;

begin

dbms_streams_adm.add_schema_rules(

schema_name => 'hytera', --捕获用户名 非sys或者system

streams_type => 'apply',

streams_name => 'target_apply_stream',

queue_name => 'strmadmin.TARGET_QUEUE',

include_dml => true,

include_ddl => true,

include_tagged_lcr => false,

source_database => 'ORCL',

inclusion_rule => true);

end;

/

select apply_name,queue_name,status from dba_apply;

设置成跟源端一致的SCN号

源端查

select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM dual;

conn strmadmin/strmadmin;

BEGIN

DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(

source_schema_name => 'hytera',

source_database_name => 'ORCL',

instantiation_scn => 1097715);

END;

/

启动目的端的Apply应用进程相当于启动OGG里面的rep1

BEGIN

DBMS_APPLY_ADM.START_APPLY(

apply_name => 'target_apply_stream');

END;

/

停止apply

BEGIN

DBMS_APPLY_ADM.STOP_APPLY(

apply_name => 'target_apply_stream');

END;

/

查看apply进程状态

select apply_name,queue_name,status from dba_apply;

--------------------------------------------------------------

查看捕获进程状态

select capture_name,status from dba_capture;

查看apply进程状态

select apply_name,queue_name,status from dba_apply;

在源数据库上创建传播进程 相当于ogg里面的datapump数据传递

select propagation_name,status from all_propagation;

select capture_name,status from dba_capture;

select apply_name,queue_name,status from dba_apply;

select capture_name,status from dba_capture;

select error_message from DBA_APPLY_ERROR;

源端查询 抓取进程和应用进程的SCN号是否一致,流复制是基于redolog 的scn号的

select capture_name,queue_name,status,captured_scn,applied_scn from dba_capture;

查看应用进程是否有错误

select apply_name,error_number,apply_captured,status from dba_apply;

------------------------------------------------------------------------------

启动捕获进程 相当于OGG里面的ext1

BEGIN

DBMS_CAPTURE_ADM.START_CAPTURE(

capture_name => 'capture_stream');

END;

/

停止捕获进程

BEGIN

DBMS_CAPTURE_ADM.STOP_CAPTURE(

capture_name => 'capture_stream');

END;

/

启动目的端的Apply应用进程相当于启动OGG里面的rep1

BEGIN

DBMS_APPLY_ADM.START_APPLY(

apply_name => 'target_apply_stream');

END;

/

停止apply

BEGIN

DBMS_APPLY_ADM.STOP_APPLY(

apply_name => 'target_apply_stream');

END;

/

--------------------------------------------------------------------------------

create tablespace hytera datafile 'D:\app\Administrator\oradata\orcl\hytera.dbf' size 100M autoextend on maxsize unlimited;

alter user hytera default tablespace hytera;

--------------------------------------------------------------------------------

配置双向流复制

源端执行

解决ORA-26687问题

SELECT * FROM DBA_CAPTURE_PREPARED_TABLES;

BEGIN

DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (

source_object_name => 'hytera.t',

source_database_name => 'ORCL',

instantiation_scn => 1094239);

END;

/

select error_message from DBA_APPLY_ERROR;--流复制错误信息

PURGE recyclebin;

解决 ORA-00942 问题

BEGIN

DBMS_APPLY_ADM.SET_PARAMETER(

apply_name => 'target_apply_stream',

parameter => 'disable_on_error',

value => 'n');

END;

/

--------------------------------------------------------------------------------

drop user hytera cascade;

create user hytera identified by hytera account unlock default tablespace hytera;

grant connect to hytera;

grant RESOURCE to hytera;

grant dba to hytera;