Streams Replication Practice: Table Level
--------------------------------------------------------------------------------------------------------------------
配置工作...
1. 首先在源数据库和目标数据上都将相关的参数设置好，（参见http://www.cnblogs.com/fangwenyu/archive/2011/03/07/1970260.html）
Source Database：lo.oracle.com
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
LO.ORACLE.COM
SQL> show parameter streams_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 200M
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.1.0
SQL> show parameter job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1812
Next log sequence to archive 1814
Current log sequence 1814
SQL>
Destination Database: orcl.oracle.com
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.ORACLE.COM
SQL> show parameter streams_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 200M
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.1.0
SQL> show parameter job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
2. 在源数据和目标数据上创建专属于streams的用户, 并赋予相应的权限
SQL> create tablespace streams_tbs
2 datafile 'streams_tbs01.dbf'
3 size 25m;
Tablespace created.
SQL> create user strmadmin identified by strmadmin
2 default tablespace streams_tbs
3 quota unlimited on streams_tbs;
User created.
SQL> grant dba, select_catalog_role to strmadmin;
Grant succeeded.
3. 在source database 上用schema strmamdin 创建连接到target database的db link
-- on source db: lo.oracle.com
SQL> conn strmadmin/strmadmin
Connected.
SQL> create database link orcl.oracle.com
2 connect to strmadmin identified by strmadmin
3 using 'orcl';
Database link created.
SQL> select * from dual@orcl.oracle.com;
D
-
X
4. 分别在source database和target database创建队列
-- source databse: lo.oracle.com
SQL> show user
USER is "STRMADMIN"
SQL> exec dbms_streams_adm.set_up_queue();
PL/SQL procedure successfully completed.
SQL> select * from streams_queue_table;
no rows selected
-- target database: orcl.oracle.com
SQL> show user
USER is "STRMADMIN"
SQL> exec dbms_streams_adm.set_up_queue();
PL/SQL procedure successfully completed.
SQL> select * from streams_queue_table;
no rows selected
5. 在源数据上配置capture streams
SQL> begin
2 dbms_streams_adm.add_table_rules
3 ( table_name => 'scott.emp',
4 streams_type => 'capture',
5 streams_name => 'capture_stream',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
这时候可以查看dba_capture看看capture的状态...
SQL> select capture_name, status, captured_scn, applied_scn from dba_capture;
CAPTURE_NAME STATUS CAPTURED_SCN APPLIED_SCN
------------------------------ -------- ------------ -----------
CAPTURE_STREAM DISABLED
注意这个时候capture process还没有启动，因此状态还是'disbabled'， 而且captured_scn 和 applied_scn都为空。
注意到source database的alert文件的变化...
注意到虽然我没有显示给source database设置supplemental logging, 数据库已经“偷偷”地在database level设置了minimal supplemental logging了...
Tue Mar 01 19:48:11 2011
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
Tue Mar 01 19:48:11 2011
SUPLOG: Updated supplemental logging attributes at scn = 66589705
SUPLOG: minimal = ON, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
Completed: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
Tue Mar 01 19:48:11 2011
ALTER SYSTEM ARCHIVE LOG
Tue Mar 01 19:48:11 2011
Thread 1 cannot allocate new log, sequence 1807
Private strand flush not complete
Current log# 2 seq# 1806 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\LO\REDO02.LOG
Tue Mar 01 19:48:12 2011
Thread 1 advanced to log sequence 1807 (LGWR switch)
Current log# 3 seq# 1807 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\LO\REDO03.LOG
Tue Mar 01 19:48:13 2011
Tue Mar 01 19:48:13 2011
Logminer Bld: Build started
Tue Mar 01 19:48:14 2011
Thread 1 advanced to log sequence 1808 (LGWR switch)
Current log# 1 seq# 1808 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\LO\REDO01.LOG
Tue Mar 01 19:48:14 2011
Tue Mar 01 19:48:14 2011
Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 66589705
Tue Mar 01 19:48:30 2011
ALTER SYSTEM ARCHIVE LOG
Tue Mar 01 19:48:30 2011
Thread 1 cannot allocate new log, sequence 1809
Checkpoint not complete
Current log# 1 seq# 1808 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\LO\REDO01.LOG
Tue Mar 01 19:48:33 2011
Thread 1 advanced to log sequence 1809 (LGWR switch)
Current log# 2 seq# 1809 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\LO\REDO02.LOG
Tue Mar 01 19:48:34 2011
Tue Mar 01 19:48:34 2011
Logminer Bld: Done
6. 在源数据上配置Propagation streams.
SQL> begin
2 dbms_streams_adm.add_table_propagation_rules
3 ( table_name => 'scott.emp',
4 streams_name => 'sour_to_targ',
5 source_queue_name => 'strmadmin.streams_queue',
6 destination_queue_name => 'strmadmin.streams_queue@orcl.oracle.com',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'lo.oracle.com',
10 inclusion_rule => true,
11 queue_to_queue => true);
12 end;
13 /
PL/SQL procedure successfully completed.
也可以查询dba_propagation来查看propagation process的状态信息，
SQL> select propagation_name, status from dba_propagation;
PROPAGATION_NAME STATUS
------------------------------ --------
SOUR_TO_TARG ENABLED
而且这个时候会发现多了一个job,
SQL> select job, what from dba_jobs;
JOB
----------
WHAT
--------------------------------------------------------------------------------
38
next_date := sys.dbms_aqadm.aq$_propaq(job);
21
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
SQL>
7. 切换到目标数据，配置apply streams
SQL> begin
2 dbms_streams_adm.add_table_rules
3 ( table_name => 'scott.emp',
4 streams_type => 'apply',
5 streams_name => 'apply_stream',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'lo.oracle.com',
10 inclusion_rule => true);
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> select apply_name, status from dba_apply;
APPLY_NAME STATUS
------------------------------ --------
APPLY_STREAM DISABLED
8. 给目标数据库设置instantiation scn.
因为source database 和 target database都有表scott.emp, 因此不需要通过exp/imp来进行数据的导出和导入，只需要给target database设置一下instantiation scn就可以了。
首先获得source database的scn,
-- lo.oracle.com
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
66592260
然后在target database上执行如下操作...
-- orcl.oracle.com
SQL> show user
USER is "STRMADMIN"
SQL> begin
2 dbms_apply_adm.set_table_instantiation_scn
3 (source_object_name => 'scott.emp',
4 source_database_name => 'lo.oracle.com',
5 instantiation_scn => 66592260);
6 end;
7 /
PL/SQL procedure successfully completed.
9. 在target数据上启动apply process...
SQL> show user
USER is "STRMADMIN"
SQL> begin
2 dbms_apply_adm.start_apply
3 ('apply_stream');
4 end;
5 /
PL/SQL procedure successfully completed.
这个时候可以在alert文件中看到类似如下内容...
Streams APPLY A001 started with pid=25, OS id=444
Streams Apply Reader started P000 with pid=26 OS id=3316
Streams Apply Server started P001 with pid=27 OS id=3084
10. 在source数据上启动capture process.
SQL> show user
USER is "STRMADMIN"
SQL> begin
2 dbms_capture_adm.start_capture('capture_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
注意这时候dba_capture数据的变化...
SQL> select capture_name, status, captured_scn, applied_scn from dba_capture;
CAPTURE_NAME STATUS CAPTURED_SCN APPLIED_SCN
------------------------------ -------- ------------ -----------
CAPTURE_STREAM ENABLED 66589715 66589715
这个时候除了status变成了enabled, captured_scn和applied_scn也都有值了，而且值是一样的。 而且注意到这个值小于之前得到的scn --66592260, 嘛原因？？
同时注意到alert文件内容的变化...
ue Mar 01 19:58:47 2011
Streams CAPTURE C001 started with pid=40, OS id=1112
Tue Mar 01 19:58:50 2011
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 1
LOGMINER: Memory Size = 10M, Checkpoint interval = 1000M
Tue Mar 01 19:58:50 2011
LOGMINER: krvxpsr summary for session# = 1
LOGMINER: StartScn: 66591231 (0x0000.03f819ff)
LOGMINER: EndScn: 0
LOGMINER: HighConsumedScn: 66589715 (0x0000.03f81413)
LOGMINER: session_flag 0x1
LOGMINER: LowCkptScn: 0 (0x0000.00000000)
LOGMINER: HighCkptScn: 0 (0x0000.00000000)
LOGMINER: SkipScn: 66589715 (0x0000.03f81413)
LOGMINER: session# = 1, reader process P000 started with pid=41 OS id=2616
LOGMINER: session# = 1, builder process P001 started with pid=42 OS id=3740
LOGMINER: session# = 1, preparer process P002 started with pid=43 OS id=4080
Tue Mar 01 19:58:52 2011
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 1808, E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1808_6PSQ8KV8_.ARC
Tue Mar 01 19:59:04 2011
Thread 1 advanced to log sequence 1810 (LGWR switch)
Current log# 3 seq# 1810 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\LO\REDO03.LOG
...............
----------------------------------------------------------------------------------------------------------------------
设置完毕了，下面就可以开始测试了....
1. 测试下DML
确认下源数据...
SQL> select empno, ename, deptno from scott.emp where empno=7900;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7900 JAMES 30
现在在source database:lo.oracle.com做如下改动...
SQL> update scott.emp set deptno=40 where empno=7900;
1 row updated.
SQL> commit;
Commit complete.
现在查看target database:orcl.oracle.com...
SQL> select empno, ename, deptno from scott.emp where empno=7900;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7900 JAMES 40
可以看到orcl.oracle.com的数据被同步了。
2. 现在看看DDL操作...
source database: lo.oracle.com
SQL> alter table scott.emp add (just_4_test number);
Table altered.
SQL> desc scott.emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
JUST_4_TEST NUMBER
target database: orcl.oracle.com
SQL> desc scott.emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
JUST_4_TEST NUMBER
看到表结构也跟着发生了变化。
--------------------------------------------------------------------------------------------------------------------------------
环境清理 Clean Up
source database: lo.oracle.com
SQL> exec dbms_capture_adm.stop_capture('capture_stream');
PL/SQL procedure successfully completed.
SQL> exec dbms_capture_adm.drop_capture('capture_stream');
PL/SQL procedure successfully completed.
SQL> exec dbms_propagation_adm.stop_propagation('sour_to_targ');
PL/SQL procedure successfully completed.
SQL> exec dbms_propagation_adm.drop_propagation('sour_to_targ');
PL/SQL procedure successfully completed.
SQL> select * from dba_capture;
no rows selected
SQL> select * from dba_propagation;
no rows selected
SQL> select job, what from dba_jobs;
JOB
----------
WHAT
--------------------------------------------------------------------------------
21
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
SQL> exec dbms_streams_adm.remove_streams_configuration();
PL/SQL procedure successfully completed.
target database: orcl.oracle.com
SQL> exec dbms_apply_adm.stop_apply('apply_stream');
PL/SQL procedure successfully completed.
SQL> exec dbms_apply_adm.drop_apply('apply_stream');
PL/SQL procedure successfully completed.
SQL> exec dbms_streams_adm.remove_streams_configuration;
PL/SQL procedure successfully completed.
SQL> select * from dba_apply;
no rows selected
最后删除创建的queue/queue table (both source & target database)
SQL> exec dbms_aqadm.stop_queue('streams_queue');
PL/SQL procedure successfully completed.
SQL> exec dbms_aqadm.drop_queue('streams_queue');
PL/SQL procedure successfully completed.
SQL> select * from streams_queue_table;
no rows selected
SQL> exec dbms_aqadm.drop_queue_table('streams_queue_table');
PL/SQL procedure successfully completed.
SQL> select * from streams_queue_table;
select * from streams_queue_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
--------------------------------------
Regards,
FangwenYu
