-------------------------------------------------------------------------------
主数据库: 操作系统: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:orclalter 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;
begindbms_streams_adm.add_schema_rules( schema_name => 'hytera', --捕获用户名 非sys或者systemstreams_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
BEGINDBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'capture_stream');END;/停止捕获进程
BEGINDBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => 'capture_stream');END;/查看捕获进程状态
select capture_name,status from dba_capture;表示实时传播lcr实时复制
begindbms_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;begindbms_streams_adm.add_schema_rules( schema_name => 'hytera', --捕获用户名 非sys或者systemstreams_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;BEGINDBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(source_schema_name => 'hytera',source_database_name => 'ORCL',instantiation_scn => 1097715);END;/启动目的端的Apply应用进程相当于启动OGG里面的rep1
BEGINDBMS_APPLY_ADM.START_APPLY(apply_name => 'target_apply_stream');END;/停止apply
BEGINDBMS_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里面的ext1BEGINDBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'capture_stream');END;/停止捕获进程
BEGINDBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => 'capture_stream');END;/ 启动目的端的Apply应用进程相当于启动OGG里面的rep1BEGINDBMS_APPLY_ADM.START_APPLY(apply_name => 'target_apply_stream');END;/停止apply
BEGINDBMS_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 问题BEGINDBMS_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;