Oracle stream 測試____Oracle

主要資料庫 
IP :192.168.2.100  
DB : RDBMS 11.2.0.4
oracle_sid : test 
global_name: test 


從資料庫
IP :192.168.2.14 
DB : RDBMS 11.2.0.4
oracle_sid : orcl 

global_name: orcl 

1 為 test ，orcl庫開啟歸檔
2 配置test，orcl庫的相關參數

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=25M scope=spfile;  alter system set utl_file_dir='*' scope=spfile;   alter system set open_links=4 scope=spfile;  

3 為test，orcl庫配置streams使用者

create tablespace streams_tbs datafile '/u01/app/oracle/oradata/test/streams_tbs01.dbf'   size 100m autoextend on   next 1m maxsize unlimited;  create tablespace streams_tbs datafile '/u01/app/oracle/oradata/orcl/streams_tbs01.dbf'   size 100m autoextend on   next 1m maxsize unlimited; create user stradmin identified by oracle default tablespace streams_tbs;grant connect, resource, dba, aq_administrator_role to stradmin;  begin      dbms_streams_auth.grant_admin_privilege(          grantee => 'stradmin',          grant_privileges => true      );  end; 

4 配置監聽

--listnern.ora SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = test)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)      (SID_NAME = test)    )  )LISTENER =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.100)(PORT = 1521))  )ADR_BASE_LISTENER = /u01/app/oracleSID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (global_dbname = orcl)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)      (SID_NAME = orcl)    )  )LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    )    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = test02)(PORT = 1521))    )  )ADR_BASE_LISTENER = /u01/app/oracle--tnsnames.oraORCL =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = test02)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )TEST =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.100)(PORT = 1521))    )    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = test)    )  )  

5 在主庫上啟用supplemental log

alter database force logging;alter database add supplemental log data;SELECT SUPPLEMENTAL_LOG_DATA_MIN,      SUPPLEMENTAL_LOG_DATA_PK,       SUPPLEMENTAL_LOG_DATA_UI,      SUPPLEMENTAL_LOG_DATA_ALL   FROM V$DATABASE;

6 建立dblink

-- 主庫上conn stradmin/oracle create database link orcl connect to stradmin identified by oracle using 'orcl';-- 從庫上conn stradmin/oracle create database link test connect to stradmin identified by oracle using 'test';

7 建立隊列流

-- 主庫conn stradmin/oracleexec dbms_streams_adm.set_up_queue(queue_table => 'test_queue_table', queue_name => 'test_queue')--從庫conn stradmin/oracleexec dbms_streams_adm.set_up_queue(queue_table => 'orcl_queue_table', queue_name => 'orcl_queue')  

8 建立樣本使用者用於測試

-- 在主庫操作，建立資料表空間create tablespace bb datafile '/u01/app/oracle/oradata/test/bb.dbf'   size 100m autoextend on   next 1m maxsize unlimited;-- 在主庫建立使用者bbcreate user bb identified by oracle default tablespace bb;grant connect,resource to bb;-- 在從庫上建立資料表空間  create tablespace bb datafile '/u01/app/oracle/oradata/orcl/bb.dbf'   size 100m autoextend on   next 1m maxsize unlimited;-- 在從庫上建立使用者 create user bb identified by oracle default tablespace bb;grant connect,resource to bb;

9 在主庫上建立捕獲進程

conn stradmin/oracle begin      dbms_streams_adm.add_schema_rules(          schema_name => 'bb',          streams_type => 'capture',          streams_name => 'capture_test',          queue_name => 'stradmin.test_queue',          include_dml => true,          include_ddl => true,           include_tagged_lcr => false,          source_database => null,          inclusion_rule => true      );  end;  

10  執行個體化複製資料庫

匯出主庫bb的資料exp userid=bb/oracle@test file='/home/oracle/bb1.dmp' object_consistent=y rows=y  [oracle@test admin]$ exp userid=bb/oracle@test file='/home/oracle/bb1.dmp' object_consistent=y rows=yExport: Release 11.2.0.4.0 - Production on Thu Feb 1 21:42:43 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion). exporting pre-schema procedural objects and actions. exporting foreign function library names for user BB . exporting PUBLIC type synonyms. exporting private type synonyms. exporting object type definitions for user BB About to export BB's objects .... exporting database links. exporting sequence numbers. exporting cluster definitions. about to export BB's tables via Conventional Path .... exporting synonyms. exporting views. exporting stored procedures. exporting operators. exporting referential integrity constraints. exporting triggers. exporting indextypes. exporting bitmap, functional and extensible indexes. exporting posttables actions. exporting materialized views. exporting snapshot logs. exporting job queues. exporting refresh groups and children. exporting dimensions. exporting post-schema procedural objects and actions. exporting statisticsExport terminated successfully without warnings.匯入imp userid=bb/oracle@orcl file='/home/oracle/bb1.dmp' ignore=y commit=y log='/home/oracle/bb.log' streams_instantiation=y fromuser=bb touser=bb  [oracle@test02 ~]$ imp userid=bb/oracle@orcl file='/home/oracle/bb1.dmp' ignore=y commit=y log='/home/oracle/bb.log' streams_instantiation=y fromuser=bb touser=bbImport: Release 11.2.0.4.0 - Production on Thu Feb 1 21:44:04 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathimport done in US7ASCII character set and AL16UTF16 NCHAR character setimport server uses WE8MSWIN1252 character set (possible charset conversion)Import terminated successfully without warnings.

11  建立傳播進程  在主庫 

conn stradmin/oracle begin       dbms_streams_adm.add_schema_propagation_rules(           schema_name => 'bb',           streams_name => 'test_to_orcl',           source_queue_name => 'stradmin.test_queue',           destination_queue_name => 'stradmin.orcl_queue@orcl',           include_dml => true,           include_ddl => true,           include_tagged_lcr => false,           source_database => 'test',           inclusion_rule => true      );   end;  -- 修改propagation休眠時間為5，表示即時傳播LCR(logical change recoder)。begin      dbms_aqadm.alter_propagation_schedule(          queue_name => 'test_queue',          destination => 'orcl',          destination_queue => 'orcl_queue',          latency => 5      );  end;  

12  建立應用進程 ，在從庫上

conn stradmin/oracle begin       dbms_streams_adm.add_schema_rules(           schema_name => 'bb',           streams_type => 'apply',           streams_name => 'apply_orcl',           queue_name => 'stradmin.orcl_queue',           include_dml => true,           include_ddl => true,           include_tagged_lcr => false,           source_database => 'test',           inclusion_rule => true      );   end;   

13  啟動stream  

conn stradmin/oracleexec dbms_apply_adm.start_apply(apply_name => 'apply_orcl')   --主庫,啟動捕捉進程conn stradmin/oracle exec dbms_capture_adm.start_capture(capture_name => 'capture_test');

14 測試
-- 在主庫

conn bb/oracle@orcl create table t1(id number, name varchar2(10));  insert into t1 values(1, 'AAA'); insert into t1 values(2, 'BBB');  insert into t1 values(3, 'CCC');  commit;

--在從庫上 

conn bb/oracle desc t1

15 停止stream 

-- 主庫上conn stradmin/oracleexec dbms_capture_adm.stop_capture(capture_name => 'capture_test') -- 從庫上conn stradmin/oracle exec dbms_apply_adm.stop_apply(apply_name => 'apply_orcl')

16 清除所有配置

-- 主庫上conn stradmin/oracleexec DBMS_STREAMS_ADM.remove_streams_configuration(); -- 從庫上conn stradmin/oracleexec DBMS_STREAMS_ADM.remove_streams_configuration();

END  2018-02-01  已經測試