源库和目标库搭建了一个stream的单向流测试环境，在源库的t20表中加入数据后，目标库中t20表能正常接收到数据。
尝试源库重新导入业务数据后，测试stream的应用情况。
1.停止源库、目标库的捕获应用进程：
SQL> exec dbms_apply_adm.stop_apply('APPLY_STANDY');
PL/SQL procedure successfully completed
1.停止源库、目标库的捕获应用进程：
SQL> exec dbms_apply_adm.stop_apply('APPLY_STANDY');
PL/SQL procedure successfully completed
SQL> exec dbms_capture_adm.stop_capture('LIRHZ_CAPTURE');
PL/SQL procedure successfully completed
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();
dbms_apply_adm.set_schema_instantiation_scn@kmeddb(
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');
PL/SQL procedure successfully completed
SQL> exec dbms_apply_adm.start_apply('APPLY_STANDY');
PL/SQL procedure successfully completed
7.启动源库的捕获
SQL> exec dbms_capture_adm.start_capture('LIRHZ_CAPTURE');
PL/SQL procedure successfully completed
8.观察目标库的alert日志出现：
[oracle@rac ~]$ tail -f /u02/app/oracle/diag/rdbms/kmeddb/kmeddb/trace/alert_kmeddb.log
[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
Sun Nov 21 21:57:18 2010
Streams Apply Server AS02 for APPLY_STANDY with pid=39 OS id=9448 stopped
Sun Nov 21 21:57:18 2010
Streams Apply Server AS03 for APPLY_STANDY with pid=40 OS id=9450 stopped
Sun Nov 21 21:57:18 2010
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
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
Sun Nov 21 21:57:18 2010
Streams Apply Server AS02 for APPLY_STANDY with pid=39 OS id=9448 stopped
Sun Nov 21 21:57:18 2010
Streams Apply Server AS03 for APPLY_STANDY with pid=40 OS id=9450 stopped
Sun Nov 21 21:57:18 2010
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
Unix process pid: 9456, image: oracle@rac
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
Unix process pid: 9456, image: oracle@rac
*** 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
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=LIRHZ
knlldmm: objn=74947
knlldmm: objv=1
knlldmm: scn=2629293307
-------------
The previous warning indicates that required information for this Logical
Change Record is not available in the Streams dictionary.
knlldmm: gdbnm=LIRHZ
knlldmm: objn=74947
knlldmm: objv=1
knlldmm: scn=2629293307
-------------
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.
* 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:
above, query dba_objects to determine the offending table:
select owner, object_name from dba_objects
where object_id = <objn specified above>;
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.
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:
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#;
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:
prepared correctly:
select count(*) from dba_capture_prepared_tables
where table_owner=<object owner obtained above>
and table_name = <object name obtained above>;
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:
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>;
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.
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
Tue Nov 23 14:05:16 2010
LOGMINER: session#=41, builder MS01 pid=41 OS id=196 sid=396 started
Tue Nov 23 14:05:16 2010
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
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
Tue Nov 23 14:05:16 2010
LOGMINER: session#=41, builder MS01 pid=41 OS id=196 sid=396 started
Tue Nov 23 14:05:16 2010
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的提示信息，如下：
knlldmm: gdbnm=LIRHZ
knlldmm: objn=74947
knlldmm: objv=1
knlldmm: scn=2629293307
knlldmm: objn=74947
knlldmm: objv=1
knlldmm: scn=2629293307
源库中又有显示 Begin mining logfile D:\APP\ADMINISTRATOR\ARCHIVE\ARC0000001972_0728499328.0001提示的。
尝试挖一下ARC0000001972_0728499328.0001 日志的信息。
尝试挖一下ARC0000001972_0728499328.0001 日志的信息。
在源库操作：
SQL> exec sys.dbms_logmnr.add_logfile(LogFileName => 'D:\app\Administrator\archive\ARC0000001972_0728499328.0001');
PL/SQL procedure successfully completed
SQL> exec dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed
SQL> exec sys.dbms_logmnr.add_logfile(LogFileName => 'D:\app\Administrator\archive\ARC0000001972_0728499328.0001');
PL/SQL procedure successfully completed
SQL> exec dbms_logmnr.start_logmnr(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed
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' )
捕获进程的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;
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 ' ||
' :dml.get_object_name() <> ''DBMS_TABCOMP_TEMP_UNCMP'' and :dml.get_object_name() <> ''DBMS_TABCOMP_TEMP_CMP''' ||' ) '
);
end;
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 ' ||
' :dml.get_object_name() <> ''DBMS_TABCOMP_TEMP_UNCMP'' and :dml.get_object_name() <> ''DBMS_TABCOMP_TEMP_CMP''' ||' ) '
);
end;
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
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，如需转载请自行联系原作者