源庫和目标庫搭建了一個stream的單向流測試環境，在源庫的t20表中加入資料後，目标庫中t20表能正常接收到資料。
嘗試源庫重新導入業務資料後，測試stream的應用情況。
1.停止源庫、目标庫的捕獲應用程序：
SQL> exec dbms_apply_adm.stop_apply('APPLY_STANDY');
PL/SQL procedure successfully completed
SQL> exec dbms_capture_adm.stop_capture('LIRHZ_CAPTURE');
2.源庫truncate table業務表。
通過腳本來批量完成。
3.源庫通過imp導入新的業務資料。
4.源端準備執行個體SCN 。
begin
dbms_capture_adm.prepare_schema_instantiation(
schema_name => 'hz',
supplemental_logging => 'keys');
end;
5.目标庫做應用scn。
declare
iscn number;
begin
iscn:=dbms_flashback.get_system_change_number();
source_schema_name => 'hz',
source_database_name => 'lirhz',
instantiation_scn => iscn,
recursive => true);
end;
6.重新啟動目标庫的應用。
SQL> exec dbms_apply_adm.start_apply('APPLY_STANDY');
PL/SQL procedure successfully completed
7.啟動源庫的捕獲
SQL> exec dbms_capture_adm.start_capture('LIRHZ_CAPTURE');
8.觀察目标庫的alert日志出現：
[oracle@rac ~]$ tail -f /u02/app/oracle/diag/rdbms/kmeddb/kmeddb/trace/alert_kmeddb.log
Propagation Receiver (CCA) for Streams Capture LIRHZ_CAPTURE and Apply APPLY_STANDY with pid=36, OS id=9456, objnum=78023 started.
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=LIRHZ
knlldmm: objn=74947
knlldmm: objv=1
knlldmm: scn=2629293307
See trace file for more information
Sun Nov 21 21:57:18 2010
Streams Apply Reader AS01 for APPLY_STANDY with pid=38 OS id=9446 stopped
Streams Apply Server AS02 for APPLY_STANDY with pid=39 OS id=9448 stopped
Streams Apply Server AS03 for APPLY_STANDY with pid=40 OS id=9450 stopped
Streams Apply Server AS04 for APPLY_STANDY with pid=41 OS id=9452 stopped
Sun Nov 21 21:57:19 2010
Streams Apply Server AS05 for APPLY_STANDY with pid=42 OS id=9454 stopped
Sun Nov 21 21:57:22 2010
Streams APPLY AP01 for APPLY_STANDY with pid=37, OS id=9444 stopped
9.檢視日志中生成的trc檔案.
[oracle@rac trace]$ vi kmeddb_ora_9456.trc
Trace file /u02/app/oracle/diag/rdbms/kmeddb/kmeddb/trace/kmeddb_ora_9456.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u02/app/oracle/product/11.2.0/db1
System name: Linux
Node name: rac
Release: 2.6.9-78.ELsmp
Version: #1 SMP Fri Jul 25 00:04:28 EDT 2008
Machine: i686
Instance name: kmeddb
Redo thread mounted by this instance: 1
Oracle process number: 36
*** 2010-11-21 21:57:13.422
*** SESSION ID:(53.76) 2010-11-21 21:57:13.422
*** CLIENT ID:() 2010-11-21 21:57:13.422
*** SERVICE NAME:(kmeddb) 2010-11-21 21:57:13.422
*** MODULE NAME:(STREAMS) 2010-11-21 21:57:13.422
*** ACTION NAME:(STREAMS Propagation Receiver CCA) 2010-11-21 21:57:13.422
-------------
The previous warning indicates that required information for this Logical
Change Record is not available in the Streams dictionary.
This typically occurs because of one of the following reasons:
* The table was not properly prepared using either
DBMS_STREAMS_ADM.ADD_%_RULES or
DBMS_CAPTURE_ADM.PREPARE_%_INSTANTIATION.
* The information to update the Streams dictionary was filtered out by
the rules of a propagation or apply process. This is most likely due
to the improper ordering of adding rules.
* The Streams dictionary tablespace has been reset by running
DBMS_LOGMINER_D.SET_TABLESPACE after Streams was configured.
To begin diagnosing this problem, on the database specified by 'gdbnm'
above, query dba_objects to determine the offending table:
select owner, object_name from dba_objects
where object_id = <objn specified above>;
With this owner and name, verify that the table changes should be captured
by Streams. If the table owner and name are for an uninteresting table,
you have likely encountered a bug.
If the above query returned no rows, it is possible that a DDL has been
performed either renaming or removing the table since this warning has
been issued. If this is the case perform the following query to obtain
the table owner and table name:
select u.name owner, o.name from system.logmnr_obj$ o, user$ u
where o.obj# = <objn above> and o.objv# = <objv above>
and o.owner# = u.user#;
If the table changes should be captured, make sure that the object was
prepared correctly:
select count(*) from dba_capture_prepared_tables
where table_owner=<object owner obtained above>
and table_name = <object name obtained above>;
If the above query returns 0, on the 'gdbmn' site, execute the following
PL/SQL to properly prepare the table:
exec dbms_capture_adm.prepare_table_instantiation('<owner obtained above>.<object name obtained above>')
If none of the above errors are found to be the cause of this error, on
the database on which this warning is raised check the Streams dictionary
for information regarding this object:
select count(*) from logmnrc_gtlo o, logmnrc_dbname_uid_map m
where m.logmnr_uid=o.logmnr_uid
and ownername=<object owner obtained above>
and lvl0name=<object name obtained above>;
If the above query returns 0, execute PL/SQL mentioned above to prepare
the table for instantiation.
10.根據trace檔案的提示，在源端及目标庫查詢以下2個sql語句，都沒有記錄。
SQL> select owner, object_name from dba_objects where object_id = 94947;
OWNER OBJECT_NAME
------------------------------ --------------------------------------------------------
SQL>
SQL> select u.name owner, o.name from system.logmnr_obj$ o, user$ u
2 where o.obj# = 94947 and o.objv# = 1 and o.owner# = u.user#;
OWNER NAME
------------------------------ ------------------------------
11.再查詢源庫的日志：
D:\app\Administrator\diag\rdbms\lirhz\lirhz\trace\alert_lirhz.log
Streams CAPTURE CP01 for LIRHZ_CAPTURE with pid=40, OS id=3272 is in combined capture and apply mode.
Capture LIRHZ_CAPTURE is handling 1 applies.
Starting persistent Logminer Session with sid = 41 for Streams Capture LIRHZ_CAPTURE
LOGMINER: Parameters summary for session# = 41
LOGMINER: Number of processes = 3, Transaction Chunk Size = 1
LOGMINER: Memory Size = 15M, Checkpoint interval = 1000M
LOGMINER: SpillScn 0, ResetLogScn 947455
LOGMINER: summary for session# = 41
LOGMINER: StartScn: 2629293121 (0x0000.9cb7d441)
LOGMINER: EndScn: 0
LOGMINER: HighConsumedScn: 2629293121 (0x0000.9cb7d441)
LOGMINER: session_flag 0x0
LOGMINER: LowCkptScn: 2610870692 (0x0000.9b9eb9a4)
LOGMINER: HighCkptScn: 2610870693 (0x0000.9b9eb9a5)
LOGMINER: SkipScn: 2610870692 (0x0000.9b9eb9a4)
Tue Nov 23 14:05:16 2010
LOGMINER: session#=41, reader MS00 pid=37 OS id=3156 sid=395 started
LOGMINER: session#=41, builder MS01 pid=41 OS id=196 sid=396 started
LOGMINER: session#=41, preparer MS02 pid=42 OS id=3672 sid=777 started
LOGMINER: Begin mining logfile for session 41 thread 1 sequence 1972, D:\APP\ADMINISTRATOR\ARCHIVE\ARC0000001972_0728499328.0001
Tue Nov 23 14:05:47 2010
knlbmEnq: all subscribers are inactive - stop enqueuing
12.考慮到目标庫日志裡有knlldmm: scn=2629293307的提示資訊，如下：
源庫中又有顯示 Begin mining logfile D:\APP\ADMINISTRATOR\ARCHIVE\ARC0000001972_0728499328.0001提示的。
嘗試挖一下ARC0000001972_0728499328.0001 日志的資訊。
在源庫操作：
SQL> exec sys.dbms_logmnr.add_logfile(LogFileName => 'D:\app\Administrator\archive\ARC0000001972_0728499328.0001');
SQL> exec dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
SQL> col SQL_REDO format a35;
SQL> col SEG_TYPE_NAME format a15;
SQL> col SEG_NAME format a15;
SQL> select scn,timestamp,seg_owner,seg_name,SEG_TYPE_NAME,sql_redo from v$logmnr_contents where scn=2629293307;
SCN TIMESTAMP SEG_OWNER SEG_NAME SEG_TYPE_NAME SQL_REDO
---------- ----------- ---------- --------------- --------------- -------------------------------
2629293307 2010-11-23 HZ PLAN_TABLE TABLE truncate table hz.PLAN_TABLE;
可以看到SCN 2629293307 号對應的就是PLAN_TABLE 表的記錄了。這樣就有點奇怪了，捕獲規則裡已經明确設定了PLAN_TABLE表不捕獲了，不知道為啥還會有這個提示？
捕獲程序的dml規則如下：
:dml.get_object_owner()='HZ' AND :dml.is_null_tag() ='Y' AND :dml.get_source_database_name()= 'LIRHZ' and (:dml.get_object_name() <> 'DB_SYSLOG' and :dml.get_object_name() <> 'DB_OPLOGS' and :dml.get_object_name() <> 'PLAN_TABLE' and :dml.get_object_name() <> 'DBMS_TABCOMP_TEMP_UNCMP' and :dml.get_object_name() <> 'DBMS_TABCOMP_TEMP_CMP' )
設定傳播規則，過濾PLAN_TABLE表。
sour> begin
dbms_rule_adm.alter_rule(
rule_name => 'strmadmin.member_pro_dml',
condition => ':dml.get_object_owner()=''HZ'' AND ' ||
' :dml.is_null_tag() =''Y'' AND '||
' :dml.get_source_database_name()= ''LIRHZ'' and ' ||
' (:dml.get_object_name() <> ''DB_SYSLOG'' and :dml.get_object_name() <> ''DB_OPLOGS'' and ' ||
' :dml.get_object_name() <> ''PLAN_TABLE'' and ' ||
' :dml.get_object_name() <> ''DBMS_TABCOMP_TEMP_UNCMP'' and :dml.get_object_name() <> ''DBMS_TABCOMP_TEMP_CMP''' ||' ) '
);
end;
begin
dbms_rule_adm.alter_rule(
rule_name => 'strmadmin.member_pro_ddl',
condition => '(:ddl.get_object_owner()=''HZ'' OR ' ||
' :ddl.get_base_table_owner() =''TEST02'') AND ' ||
' :ddl.is_null_tag() =''Y'' AND '||
' :ddl.get_source_database_name()= ''LIRHZ'' and ' ||
' (:dml.get_object_name() <> ''DB_SYSLOG'' and :dml.get_object_name() <> ''DB_OPLOGS'' and ' ||
' :dml.get_object_name() <> ''PLAN_TABLE'' and ' ||
13.重新啟動應用，應用就不報錯了，測試源庫在t20表加1條記錄，目标庫沒有錯誤，也沒有接收到，觀察目标庫的日志資訊，等了很長時間後，出現了redo日志切換的資訊，證明日志庫已經開始應用源庫的日志了，（因為源庫做imp導入時産生了很多的日志資訊）
Propagation Receiver (CCA) for Streams Capture LIRHZ_CAPTURE and Apply APPLY_STANDY with pid=31, OS id=10244, objnum=78023 started.
Sun Nov 21 22:54:49 2010
Propagation Receiver (CCA) for Streams Capture LIRHZ_CAPTURE and Apply APPLY_STANDY with pid=31, OS id=10316, objnum=78023 started.
Sun Nov 21 22:55:48 2010
Thread 1 advanced to log sequence 52 (LGWR switch)
Current log# 1 seq# 52 mem# 0: +DATA/kmeddb/redo01.log
Sun Nov 21 22:55:53 2010
Archived Log entry 209 added for thread 1 sequence 51 ID 0xe6d453cb dest 2:
Sun Nov 21 22:55:59 2010
Starting background process SMCO
Sun Nov 21 22:56:00 2010
SMCO started with pid=46, OS id=10321
Sun Nov 21 22:56:19 2010
Thread 1 advanced to log sequence 53 (LGWR switch)
Current log# 2 seq# 53 mem# 0: +DATA/kmeddb/redo02.log
.......
Sun Nov 21 23:01:53 2010
Archived Log entry 218 added for thread 1 sequence 60 ID 0xe6d453cb dest 2:
Sun Nov 21 23:02:35 2010
Thread 1 cannot allocate new log, sequence 62
Checkpoint not complete
Current log# 1 seq# 61 mem# 0: +DATA/kmeddb/redo01.log
Thread 1 advanced to log sequence 62 (LGWR switch)
Current log# 2 seq# 62 mem# 0: +DATA/kmeddb/redo02.log
最後應用完所有的源端日志後，查詢目标庫t20表，久違的記錄可以應用了。
SQL> select * from hz.t20;
ID NAME
---------- --------------------
1 guangzhou
2 foshan
4 shenzhen
5 beijing
有個疑問，按理源庫在重新導入資料前，已經停掉了捕獲和應用程序，源庫導入資料後目标庫重新做了scn應用。應該從導入後開始捕獲的，不知為啥會把導入時生成的archive 檔案要再次應用？
本文轉自 gjm008 51CTO部落格，原文連結：http://blog.51cto.com/gaoshan/430381，如需轉載請自行聯系原作者
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)