1、conn system/destination_DB as sysdba;alter system set global_names=true scope=spfile;说明streams_pool_size在生产环境中最好 200m参数需要重新启动后才生效Shutdown immediate;Startup;1.3归档模式设置查看是否为归档模式Archive log list源数据库必须处于归档模式shutdown immediate;startup mount;alter database archivelog;alter database open;1.4创建复制表空间Source_DB
2、 :create tablespace streams_tbs datafile /oracle/product/10.2.0/oradata/streams_tbs.dbf size 50M autoextend on;destination_DB :E:ORACLEPRODUCT10.2.0ORADATADESTINATION_DB streams_tbs.dbf1.5创建复制用户源与目标都需要创建CREATE USER strmadmin IDENTIFIED BY strmadminpw DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON
3、 streams_tbs;GRANT connect,resource,dba,aq_administrator_role TO strmadmin;1.6创建数据库链接在这里要做源服务器与目标服务器的tnsnames.ora文件需要设置一致CONNECT strmadmin/strmadmin source_DBCREATE DATABASE LINK destination_DB_Link CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING destination_DB ;CONNECT strmadmin/strmadmin dest
4、ination_DBCREATE DATABASE LINK source_DB_Link CONNECT TO strmadmin IDENTIFIED BY strmadminpwsource_DB 注:dblink的名字最好与目标数据库的名字一样,否则有可能不能被成功复制1.7建立队列CONNECT strmadmin/strmadminpwdestination_DB EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();CONNECT strmadmin/strmadminpwsource_DB 这里的queue_name就默认为strmadmin.streams
5、_queue,queru_talbe就默认为streams_queue_table也可指定名称:DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_name=strmadmin.streams_queue_2, queue_table=streams_queue_table_2)1.8.建立复制替代主键为没有主键的表来需要设置- CONNECT SYS/ORACLEsource_DB AS SYSDBA在源执行 1.8.1复制单表ALTER TABLE hrp270.ss_install ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COL
6、UMNS;1.8.2复制数据库(可以省略):为一个数据库设置复制主键alter database hrp270 add SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;1.8.3 设置追加日志(可以省略)如果复制表上没有主键或者惟一的NOT NULL约束,就需要追加日志。为一个表增加追加日志:Alter table hrp270.gy_ksdm add SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;为一个数据库增加追加日志:alter database hrp270 add SUPPLEMENTAL LOG DA
7、TA ;1.9建立传播队列在源数据建立传播队列,指明从哪里到哪里1.9.1传播表BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( table_name = hrp270.ss_install, streams_name =source_DB _to_destination_DB source_queue_name =strmadmin.streams_queue, destination_queue_name =strmadmin.streams_queuedestination_DB_Link include_dml = true, in
8、clude_ddl = source_database =, - 源SID inclusion_rule = true);END;/1.9.2传播schema DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name =hrp270hrp270_to_bsoftstrmadmin.streams_queue destination_DB_Link include_tagged_lcr = false, 1.10建立捕获在源数据服务器上操作1.10.1捕获表 DBMS_STREAMS_ADM.ADD_TABLE_RULES( strea
9、ms_type =capturecapture_first queue_name =1.10.2捕获schema: DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(streams_queue null,设置capture参数 DBMS_CAPTURE_ADM.SET_PARAMETER( capture_name = parameter =parallelism value =1); -在10G可以是8,9i中必须为11.11建立SCN号同步1.11.1设置表SCN同步: DECLARE iscn NUMBER; - Variable to hold instantiati
10、on SCN value iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCNDESTINATION_DB_LINK ( source_object_name = source_database_name =, - 源服务器sid instantiation_scn = iscn);1.11.2设置Schema级SCN同步: DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN DESTINATION_DB_LINK ( sou
11、rce_schema_name= source_database_name=source_DBinstantiation_scn = iscn,RECURSIVE = TRUE);1.12建立apply联系在目标数据库操作1.12.1表级联系:applyapply_first1.12.2 Schema级联系apply_second strmadmin.streams_queue_2 source_DB 1.12.3 Apply 参数设置是否按业务按源数库的事务执行顺序来执行操作,默认值为,完全按照事务次序执行,NONE表是不按次序来执行 DBMS_APPLY_ADM.SET_PARAMETER
12、( apply_name =APPLY_firstcommit_serializationnone并行处理数高低,默认值是,就是立即处理,如果设置成则表示如果个以上用户来同时处理就需要等待PARALLELISM8设置成如果冲突则自动跳过,n表示跳过,y表示停止,默认值是停止disable_on_errorn2启动与停止复制2.1启动复制2.1.1在源数据库启动传播 DBMS_AQADM.ENABLE_PROPAGATION_SCHEDULE( destination =destination_db_link2.1.2在源数据库启动捕获 DBMS_CAPTURE_ADM.START_CAPTUR
13、E(2.1.3在目标数据库启动apply DBMS_APPLY_ADM.START_APPLY(2.2停止复制CONNECT strmadmin/strmadminpwSource_DB;2.2.1停止捕获 DBMS_CAPTURE_ADM.STOP_CAPTURE(2.2.2停止传播 DBMS_AQADM.DISABLE_PROPAGATION_SCHEDULE( destination_db_link2.2.3停止应用CONNECT strmadmin/strmadminpwdestination_DB; DBMS_APPLY_ADM.STOP_APPLY(3删除复制3.1按明细步骤删除在
14、删除操作前需要先做停止复制操作3.1.1删除传播 DBMS_PROPAGATION_ADM.DROP_PROPAGATION( propagation_name =HRP270_TO_BSOFT drop_unused_rule_sets =3.1.2删除捕获 DBMS_CAPTURE_ADM.DROP_CAPTURE(3.1.3删除应用 DBMS_APPLY_ADM.DROP_APPLY(3.1.4删除队列(源与目标都要删除)Exec DBMS_STREAMS_ADM.REMOVE_QUEUE(queue_name=,cascade=true);3.2快速删除在源数据库与目标数据库可以使用下
15、面命令来删除STREAMSEXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();4信息查看及冲突处理4.1查看信息及错误4.1.1查看apply信息COLUMN APPLY_NAME HEADING Apply Process Name FORMAT A25COLUMN TOTAL_RECEIVED HEADING Total|Trans|Received FORMAT 99999999COLUMN TOTAL_APPLIED HEADING Total|Trans|AppliedCOLUMN TOTAL_ERRORS HEADING Tot
16、al|Apply|Errors FORMAT 9999COLUMN BEING_APPLIED HEADING Total|Trans Being|AppliedCOLUMN TOTAL_IGNORED HEADING Total|Trans|IgnoredSELECT APPLY_NAME, TOTAL_RECEIVED, TOTAL_APPLIED, TOTAL_ERRORS, (TOTAL_ASSIGNED - (TOTAL_ROLLBACKS + TOTAL_APPLIED) BEING_APPLIED, TOTAL_IGNORED FROM V$STREAMS_APPLY_COORD
17、INATOR;4.1.2查看应用apply错误Select * from all_apply_error;4.1.3 显示当前APPLY应用参数Apply Process|Name FORMAT A15COLUMN PARAMETER HEADING Parameter FORMAT A20COLUMN VALUE HEADING ValueCOLUMN SET_BY_USER HEADING Set by User? PARAMETER, VALUE, SET_BY_USER FROM DBA_APPLY_PARAMETERS;4.1.4查看传播情况 传播设置信息SELECT p.DESTINATION_DBLINK, DECODE(s.SCHEDULE_DISABLED,Y, DisabledNEnabled) SCHEDULE_DISABLED, s.PROCESS_NAME, s.FAILURES, s.LAST_ERROR_TIME, s.LAST_ERROR_MSG FROM DBA_QUE
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1