Oracle Streams Replication Administration
大约 29 分钟
Oracle Streams Replication Administration
Overview of Oracle Streams Replication
企业往往需要集成多个业务数据库的数据来生成报表，有以下几种方式：
- 方式一：以某个业务数据库为主，创建到其他业务数据库的 DBLINK，这种方式在进行报表分析的时候，不可避免会对业务产生影响，且 DBLINK 方式存在性能问题。
- 方式二：使用单独的报表数据库，创建到其他业务数据库物理备库的 DBLINK，这种方式避免了对业务的影响，但还是存在 DBLINK 的性能问题。
- 方式三：使用单独的报表数据库，将数据从业务数据库或者其备库同步过来，这种方式既避免了对业务的影响，也没有 DBLINK 的性能问题。
实际工作中，往往使用方式三来同步大量数据进行报表分析。Oracle 的数据同步产品有：
- Oracle Streams Replication：数据库自带组件，无需额外购买
- Oracle GoldenGate：需要单独购买许可，功能强大
如果预算有限，可使用 Oracle Streams Replication，满足基本的数据同步需求。
Oracle Streams Replication 的基本原理类似于 Logic Standby，数据同步过程如下：
- 捕获：使用捕获进程从源库重做日志中获取并处理 LCR。
- 投递：使用投递进程将 LCR 发送到目标库。
- 应用：使用应用进程将 LCR 应用到目标库。
捕获源库改变的库称为捕获数据库（capture database），可以是源库，目标库或者一个第三方的库。
根据捕获进程和应用进程所在的位置，Oracle Streams Replication 有以下几种应用场景：
- 场景一：捕获进程和应用进程都位于源库（Local capture and apply on one database）
- 场景二：捕获进程位于源库，应用进程位于目标库（Local capture and remote apply）
- 场景三：捕获进程和应用进程都位于目标库（Downstream capture and local apply）
- 场景四：捕获进程位于中间库，应用进程位于目标库（Downstream capture and remote apply）
这是选择最复杂的场景四进行演示，通过将源库的日志传输到中间库，由中间库进行捕获和投递，来将源库数据同步到目标库。可以最大程度降低对源库和目标库的影响。
Configuring Schema Replication with Downstream Capture at Third Database
Planing
（1）主机规划
| 序号 | 操作系统 | 主机名 | 版本 | 用途 |
|---|---|---|---|---|
| 1 | RHEL | rac2 | 6.7-x86_64 | 源库 |
| 2 | RHEL | strmrac | 6.7-x86_64 | 中间库 |
| 3 | RHEL | test | 6.7-x86_64 | 目标库 |
（2）网络规划
| 序号 | Hostname | IP Address | Interface | Resolved by |
|---|---|---|---|---|
| 1 | rac2 | 192.168.247.132 | bond0 | HOST FILE |
| 2 | strmrac | 192.168.247.139 | eth0 | HOST FILE |
| 3 | test | 192.168.247.140 | eth0 | HOST FILE |
（3）名称规划
| 序号 | 主机名 | 实例名称 | DB_UNIQUE_NAME | 数据库名称 |
|---|---|---|---|---|
| 1 | rac2 | stone2 | stone | stone |
| 2 | strmrac | strmrac | strmrac | strmrac |
| 3 | test | test | test | test |
Preparing
（1）源库：使用 Oracle Real Application Clusters Administration 安装的 RAC 数据库，也可以使用一个单实例数据库。
（2）中间库：安装数据库，创建数据库，打开数据库，启动监听。
（3）目标库：安装数据库，创建数据库，打开数据库，启动监听。
Configuring
Source Database
以下操作均在源库进行。
（1）使用 sys 用户连接数据库
[oracle@rac2 ~]$ sqlplus / as sysdba
（2）创建 steams administrator 表空间
SQL> create tablespace streams_tbs;
（3）创建 steams administrator 用户
SQL> create user strmadmin identified by strmadmin default tablespace streams_tbs;
（4）将 logminer 的数据字典从 system 表空间转移到新建的表空间
SQL> execute dbms_logmnr_d.set_tablespace('streams_tbs');
（5）为 streams administrator 用户授予角色
SQL> grant dba to strmadmin;
（6）为 streams administrator 用户授予权限
有一些执行包和查询视图的权限不能通过角色授予，需要使用 grant_admin_privilege 存储过程授予。
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => TRUE);
END;
/
（7）配置到中间库和目标库的连接串
#节点1
[oracle@rac1 ~]$ vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
STRMRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.139)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = strmrac)
)
)
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.140)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
#节点2
[oracle@rac2 ~]$ vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
STRMRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.139)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = strmrac)
)
)
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.140)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
测试网络：
#节点1
[oracle@rac1 ~]$ tnsping test
[oracle@rac1 ~]$ tnsping strmrac
#节点2
[oracle@rac2 ~]$ tnsping test
[oracle@rac2 ~]$ tnsping strmrac
（8）创建到中间库和目标库的 DBLINK
SQL> conn strmadmin/strmadmin
SQL> create database link strmrac connect to strmadmin identified by strmadmin using 'strmrac';
SQL> create database link test connect to strmadmin identified by strmadmin using 'test';
（9）确认源库运行在归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 40
Next log sequence to archive 44
Current log sequence 44
（10）确认源库补充日志
SQL> select name,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME SUPPLEME
--------- --------
STONE YES
（11）设置源库初始化参数
SQL> alter system set global_names=true scope=both;
SQL> select * from global_name;
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(stone,stonedg1,stonedg2,strmrac)' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=defer scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_3='SERVICE=strmrac LGWR ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=strmrac' scope=both;
SQL> select dest_id,dest_name,status,target,destination from v$archive_dest where dest_id <4;
（12）将密码文件拷贝到中间库
[oracle@rac2 ~]$ scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone2 oracle@192.168.247.139:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstrmrac
（13）源库创建目录对象
SQL> conn strmadmin/strmadmin
SQL> create directory data_pump_src as '/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/';
（14）源库创建同步 SCHEMA
SQL> create tablespace test_tbs;
SQL> create user test1 identified by test1 default tablespace test_tbs;
SQL> grant connect,resource to test1;
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where username='TEST1';
SQL> select GRANTEE,GRANTED_ROLE from dba_role_privs where grantee='TEST1';
SQL> conn test1/test1
SQL> create table testtab (id number);
SQL> insert into testtab values(1);
SQL> commit;
Third Database
以下操作均在中间库进行。
（1）使用 sys 用户连接数据库
[oracle@strmrac ~]$ sqlplus / as sysdba
（2）创建 steams administrator 表空间
SQL> create tablespace streams_tbs;
（3）创建 steams administrator 用户
SQL> create user strmadmin identified by strmadmin default tablespace streams_tbs;
（4）将 logminer 的数据字典从 system 表空间转移到新建的表空间
SQL> execute dbms_logmnr_d.set_tablespace('streams_tbs');
（5）为 streams administrator 用户授予角色
SQL> grant dba to strmadmin;
（6）为 streams administrator 用户授予权限
有一些执行包和查询视图的权限不能通过角色授予，需要使用 grant_admin_privilege 存储过程授予。
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => TRUE);
END;
/
（7）配置到源库和目标库的连接串
[oracle@strmrac ~]$ vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
STONE2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.134)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stone)
)
)
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.140)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
测试网络：
[oracle@strmrac ~]$ tnsping stone2
[oracle@strmrac ~]$ tnsping test
（8）创建到源库和目标库的 DBLINK
SQL> conn / as sysdba
SQL> create public database link stone using 'stone2';
SQL> conn strmadmin/strmadmin
SQL> create database link stone connect to strmadmin identified by strmadmin using 'stone2';
SQL> create database link test connect to strmadmin identified by strmadmin using 'test';
（9）确认中间库运行在归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
（10）设置中间库初始化参数
[oracle@strmrac ~]$ mkdir /u01/app/oracle/oradata/STRMRAC/strarclog
[oracle@strmrac ~]$ mkdir /u01/app/oracle/oradata/STRMRAC/archivelog
SQL> alter system set global_names=true scope=both;
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(stone,stonedg1,stonedg2,strmrac)' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_2='location=/u01/app/oracle/oradata/STRMRAC/strarclog valid_for=(STANDBY_LOGFILE,PRIMARY_ROLE)' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
SQL> alter system set fal_server='stone2';
SQL> select dest_id,dest_name,status,target,destination,error from v$archive_dest where dest_id <3;
SQL> alter system set STREAMS_POOL_SIZE=1024M scope=both;
注意：
从主库接收到的日志归档位置不能设置为快速恢复区。
STREAMS_POOL_SIZE 参数需要根据实际情况调整，建议不少于 400 M。
（11）中间库配置 Standby Redo Logs
查询源库日志大小和日志组数量。
SQL> select group#,thread#,bytes/1024/1024/1024 Gb from v$log;
中间库的 Standby Redo Logs 的大小与源库一样或者比源库大，一般和源库一样大，中间库的日志组数量要比源库至少多一组。
SQL> alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata';
SQL> alter database add standby logfile thread 1 group 11 size 50M;
SQL> alter database add standby logfile thread 1 group 12 size 50M;
SQL> alter database add standby logfile thread 1 group 13 size 50M;
SQL> alter database add standby logfile thread 2 group 14 size 50M;
SQL> alter database add standby logfile thread 2 group 15 size 50M;
SQL> alter database add standby logfile thread 2 group 16 size 50M;
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;
Destination Database
以下操作均在目标库进行。
（1）使用 sys 用户连接数据库
[oracle@test ~]$ sqlplus / as sysdba
（2）创建 steams administrator 表空间
SQL> create tablespace streams_tbs;
（3）创建 steams administrator 用户
SQL> create user strmadmin identified by strmadmin default tablespace streams_tbs;
（4）将 logminer 的数据字典从 system 表空间转移到新建的表空间
SQL> execute dbms_logmnr_d.set_tablespace('streams_tbs');
（5）为 streams administrator 用户授予角色
SQL> grant dba to strmadmin;
（6）为 streams administrator 用户授予权限
有一些执行包和查询视图的权限不能通过角色授予，需要使用 grant_admin_privilege 存储过程授予。
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => TRUE);
END;
/
（7）配置到源库和中间库的连接串
[oracle@test ~]$ vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
STONE2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.134)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stone)
)
)
STRMRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.139)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = strmrac)
)
)
测试网络：
[oracle@test ~]$ tnsping stone2
[oracle@test ~]$ tnsping strmrac
（8）创建到源库和中间库的 DBLINK
SQL> conn strmadmin/strmadmin
SQL> create database link stone connect to strmadmin identified by strmadmin using 'stone2';
SQL> create database link strmrac connect to strmadmin identified by strmadmin using 'strmrac';
（8）确认目标库运行在归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
（9）设置目标库初始化参数
SQL> alter system set global_names=true scope=both;
SQL> select * from global_name;
SQL> alter system set STREAMS_POOL_SIZE=1024M scope=both;
注意：
STREAMS_POOL_SIZE 参数需要根据实际情况调整，建议不少于400M。
（10）目标库创建目录对象
SQL> conn strmadmin/strmadmin
SQL> create directory data_pump_dest as '/u01/app/oracle/admin/test/dpdump/';
（11）目标库创建表空间
由于初始化是通过 EXPDP/IMPDP 进行的，那么目标库就需要创建源库同步用户对应的 tablespace。如果源库同步用户使用了自定义的 Profile 和 Role，则目标库也需要创建。
SQL> create tablespace test_tbs;
Creating
（1）使用 strmadmin 用户连接到中间库
[oracle@strmrac ~]$ sqlplus strmadmin/strmadmin
（2）中间库执行创建脚本
BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => 'TEST1',
source_directory_object => 'DATA_PUMP_SRC',
destination_directory_object => 'DATA_PUMP_DEST',
source_database => 'STONE',
destination_database => ‘TEST',
perform_actions => TRUE,
dump_file_name => 'export_TEST1.dmp',
capture_queue_table => 'rep_capture_queue_table',
capture_queue_name => 'rep_capture_queue',
capture_queue_user => NULL,
apply_queue_table => 'rep_dest_queue_table',
apply_queue_name => 'rep_dest_queue',
apply_queue_user => NULL,
capture_name => 'capture_TEST1',
propagation_name => 'prop_TEST1',
apply_name => 'apply_TEST1',
log_file => 'export_TEST1.log',
include_ddl => TRUE,
instantiation => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);
END;
/
其中：
- source_database 为源库的 global_name
- destination_database 为目标库的 global_name
（3）中间库配置实时捕获
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'capture_TEST',
parameter => 'downstream_real_time_mine',
value => 'Y');
END;
/
（4）源库归档当前联机重做日志
SQL> alter system archive log current;
（5）中间库查看接收到的日志
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;
[oracle@strmrac ~]$ ll /u01/app/oracle/oradata/STRMRAC/strarclog
如果参数 log_archive_format 为 %t_%s_%r.dbf，则 %r 表示 resetlogs ID，可以通过下面 SQL 查询出数据库对应的 resetlogs ID，从而知道这个归档日志文件是从哪个数据库来的：
SQL> select resetlogs_id from v$database_incarnation;
（6）目标库查看结果
SQL> conn test1/test1
SQL> select * from testtab;
ID
----------
1
Testing
（1）源库创建表并插入数据
SQL> conn test1/test1
SQL> create table testtab1(id number);
SQL> insert into testtab1 values(1);
SQL> commit;
（2）目标库查看
SQL> select * from testtab1;
ID
----------
1
Managing Oracle Streams Replication
Stopping Streams Replication
（1）使用 strmadmin 用户，登录中间数据库，停止 Capture 进程
SQL> connect strmadmin/strmadmin
SQL> execute dbms_capture_adm.stop_capture(capture_name => 'capture_test1');
sql> select capture_name,queue_name,rule_set_name,negative_rule_set_name,status from dba_capture;
（2）使用 strmadmin 用户，登录目标数据库，停止 Apply 进程
SQL> connect strmadmin/strmadmin
SQL> execute dbms_apply_adm.stop_apply(apply_name => 'apply_test1');
sql> select apply_name, apply_captured, status from dba_apply;
Starting Streams Replication
（1）使用 strmadmin 用户，登录目标数据库，启动 Apply 进程
SQL> connect strmadmin/strmadmin
SQL> execute dbms_apply_adm.start_apply(apply_name => 'apply_test1');
sql> select apply_name, apply_captured, status from dba_apply;
（2）使用 strmadmin 用户，登录中间数据库，启动 Capture 进程
SQL> connect strmadmin/strmadmin
SQL> execute dbms_capture_adm.start_capture(capture_name => 'capture_test1');
sql> select capture_name,queue_name,rule_set_name,negative_rule_set_name,status from dba_capture;
Monitoring a Capture Process
Displaying the Queue, Rule Sets, and Status of Each Capture Process
使用 strmadmin 用户，登录中间数据库，确认捕捉进程是否运行正常。
SQL> conn strmadmin/strmadmin
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15
SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME, STATUS
FROM DBA_CAPTURE;
Capture Capture Capture
Process Process Positive Negative Process
Name Queue Rule Set Rule Set Status
--------------- --------------- --------------- --------------- ---------------
STRM01_CAPTURE STREAMS_QUEUE RULESET$_25 RULESET$_36 ENABLED
- 如果 STATUS 状态是 ENABLED，表示 Capture 进程运行正常
- 如果 STATUS 状态是 DISABLED，表示 Capture 进程处于停止状态，只需重新启动即可
- 如果 STATUS 状态是 ABORTED，表示 Capture 进程非正常停止，查询相应的 ERROR_NUMBER、ERROR_MESSAGE 列可以得到详细的信息；同时 Oracle 会在跟踪文件中记录该信息
Displaying Session Information About Each Capture Process
COLUMN ACTION HEADING 'Capture Process Component' FORMAT A25
COLUMN SID HEADING 'Session ID' FORMAT 99999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 99999999
COLUMN PROCESS HEADING 'Operating System|Process Number' FORMAT A20
COLUMN PROCESS_NAME HEADING 'Process|Name' FORMAT A7
SELECT /*+PARAM('_module_action_old_length',0)*/ ACTION,
SID,
SERIAL#,
PROCESS,
SUBSTR(PROGRAM,INSTR(PROGRAM,'(')+1,4) PROCESS_NAME
FROM V$SESSION
WHERE MODULE ='Streams' AND
ACTION LIKE '%Capture%';
Session
Serial Operating System Process
Capture Process Component Session ID Number Process Number Name
------------------------- ---------- --------- -------------------- -------
EMDBA$CAP - Capture 74 9 10019 CP01
Displaying Change Capture Information About Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A7
COLUMN PROCESS_NAME HEADING 'Capture|Process|Number' FORMAT A7
COLUMN SID HEADING 'Session|ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999
COLUMN STATE HEADING 'State' FORMAT A20
COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo|Entries|Evaluated|In Detail' FORMAT 9999999
COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' FORMAT 9999999999
SELECT c.CAPTURE_NAME,
SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(')+1,4) PROCESS_NAME,
c.SID,
c.SERIAL#,
c.STATE,
c.TOTAL_MESSAGES_CAPTURED,
c.TOTAL_MESSAGES_ENQUEUED
FROM V$STREAMS_CAPTURE c, V$SESSION s
WHERE c.SID = s.SID AND
c.SERIAL# = s.SERIAL#;
Redo
Capture Session Entries Total
Capture Process Session Serial Evaluated LCRs
Name Number ID Number State In Detail Enqueued
------- ------- ------- ------- -------------------- --------- -----------
CAPTURE CP01 954 3 CAPTURING CHANGES 3719085 3389713
_HNS
Displaying State Change and Message Creation Time for Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
COLUMN STATE HEADING 'State' FORMAT A27
COLUMN STATE_CHANGED HEADING 'State|Change Time'
COLUMN CREATE_MESSAGE HEADING 'Last Message|Create Time'
SELECT CAPTURE_NAME,
STATE,
TO_CHAR(STATE_CHANGED_TIME, 'HH24:MI:SS MM/DD/YY') STATE_CHANGED,
TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_MESSAGE
FROM V$STREAMS_CAPTURE;
Capture State Last Message
Name State Change Time Create Time
--------------- --------------------------- ----------------- -----------------
CAPTURE_SIMP CAPTURING CHANGES 13:24:42 11/08/04 13:24:41 11/08/04
Displaying Elapsed Time Performing Capture Operations for Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
COLUMN ELAPSED_CAPTURE_TIME HEADING 'Elapsed|Capture|Time' FORMAT 99999999.99
COLUMN ELAPSED_RULE_TIME HEADING 'Elapsed|Rule|Evaluation|Time' FORMAT 99999999.99
COLUMN ELAPSED_ENQUEUE_TIME HEADING 'Elapsed|Enqueue|Time' FORMAT 99999999.99
COLUMN ELAPSED_LCR_TIME HEADING 'Elapsed|LCR|Creation|Time' FORMAT 99999999.99
COLUMN ELAPSED_PAUSE_TIME HEADING 'Elapsed|Pause|Time' FORMAT 99999999.99
SELECT CAPTURE_NAME,
(ELAPSED_CAPTURE_TIME/100) ELAPSED_CAPTURE_TIME,
(ELAPSED_RULE_TIME/100) ELAPSED_RULE_TIME,
(ELAPSED_ENQUEUE_TIME/100) ELAPSED_ENQUEUE_TIME,
(ELAPSED_LCR_TIME/100) ELAPSED_LCR_TIME,
(ELAPSED_PAUSE_TIME/100) ELAPSED_PAUSE_TIME
FROM V$STREAMS_CAPTURE;
Elapsed Elapsed
Elapsed Rule Elapsed LCR Elapsed
Capture Capture Evaluation Enqueue Creation Pause
Name Time Time Time Time Time
--------------- ------------ ------------ ------------ ------------ ------------
STM1$CAP 1213.92 .04 33.84 185.25 600.60
Displaying Information About Each Downstream Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A15
COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15
COLUMN USE_DATABASE_LINK HEADING 'Uses|Database|Link?' FORMAT A8
SELECT CAPTURE_NAME,
SOURCE_DATABASE,
QUEUE_NAME,
STATUS,
USE_DATABASE_LINK
FROM DBA_CAPTURE
WHERE CAPTURE_TYPE = 'DOWNSTREAM';
Capture Capture Capture Uses
Process Source Process Process Database
Name Database Queue Status Link?
--------------- -------------------- --------------- --------------- --------
STRM03_CAPTURE DBS1.EXAMPLE.COM STRM03_QUEUE ENABLED YES
Displaying the Registered Redo Log Files for Each Capture Process
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 99999
COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A20
COLUMN DICTIONARY_BEGIN HEADING 'Dictionary|Build|Begin' FORMAT A10
COLUMN DICTIONARY_END HEADING 'Dictionary|Build|End' FORMAT A10
SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME,
r.DICTIONARY_BEGIN,
r.DICTIONARY_END
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME;
Capture Dictionary Dictionary
Process Source Sequence Archived Redo Log Build Build
Name Database Number File Name Begin End
--------------- ---------- -------- -------------------- ---------- ----------
STRM02_CAPTURE DBS2.EXAMP 15 /orc/dbs/log/arch2_1 NO NO
LE.COM _15_478347508.arc
STRM02_CAPTURE DBS2.EXAMP 16 /orc/dbs/log/arch2_1 NO NO
LE.COM _16_478347508.arc
STRM03_CAPTURE DBS1.EXAMP 45 /remote_logs/arch1_1 YES YES
LE.COM _45_478347335.arc
STRM03_CAPTURE DBS1.EXAMP 46 /remote_logs/arch1_1 NO NO
LE.COM _46_478347335.arc
STRM03_CAPTURE DBS1.EXAMP 47 /remote_logs/arch1_1 NO NO
LE.COM _47_478347335.arc
Displaying the Redo Log Files that Are Required by Each Capture Process
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 99999
COLUMN NAME HEADING 'Required|Archived Redo Log|File Name' FORMAT A40
SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND
r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN;
Capture Required
Process Source Sequence Archived Redo Log
Name Database Number File Name
--------------- ---------- -------- ----------------------------------------
STRM02_CAPTURE DBS2.EXAMP 16 /orc/dbs/log/arch2_1_16_478347508.arc
LE.COM
STRM03_CAPTURE DBS1.EXAMP 47 /remote_logs/arch1_1_47_478347335.arc
LE.COM
Displaying SCN Values for Each Redo Log File Used by Each Capture Process
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A25
COLUMN FIRST_SCN HEADING 'First SCN' FORMAT 99999999999
COLUMN NEXT_SCN HEADING 'Next SCN' FORMAT 99999999999
COLUMN PURGEABLE HEADING 'Purgeable?' FORMAT A10
SELECT r.CONSUMER_NAME,
r.NAME,
r.FIRST_SCN,
r.NEXT_SCN,
r.PURGEABLE
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME;
Capture
Process Archived Redo Log
Name File Name First SCN Next SCN Purgeable?
--------------- ------------------------- ------------ ------------ ----------
CAPTURE_SIMP /private1/ARCHIVE_LOGS/1_ 509686 549100 YES
3_502628294.dbf
CAPTURE_SIMP /private1/ARCHIVE_LOGS/1_ 549100 587296 YES
4_502628294.dbf
CAPTURE_SIMP /private1/ARCHIVE_LOGS/1_ 587296 623107 NO
5_502628294.dbf
此处的 PURGEABLE 只适用于 Local Capture。
Displaying the Last Archived Redo Entry Available to Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A20
COLUMN LOGMINER_ID HEADING 'LogMiner ID' FORMAT 9999
COLUMN AVAILABLE_MESSAGE_NUMBER HEADING 'Highest|Available SCN' FORMAT 9999999999
COLUMN AVAILABLE_MESSAGE_CREATE_TIME HEADING 'Time of|Highest|Available SCN'
SELECT CAPTURE_NAME,
LOGMINER_ID,
AVAILABLE_MESSAGE_NUMBER,
TO_CHAR(AVAILABLE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY')
AVAILABLE_MESSAGE_CREATE_TIME
FROM V$STREAMS_CAPTURE;
Time of
Capture Highest Highest
Name LogMiner ID Available SCN Available SCN
-------------------- ----------- ------------- -----------------
DB1$CAP 1 1506751 09:46:11 06/29/09
Listing the Parameter Settings for Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A25
COLUMN PARAMETER HEADING 'Parameter' FORMAT A30
COLUMN VALUE HEADING 'Value' FORMAT A10
COLUMN SET_BY_USER HEADING 'Set by|User?' FORMAT A10
SELECT CAPTURE_NAME,
PARAMETER,
VALUE,
SET_BY_USER
FROM DBA_CAPTURE_PARAMETERS;
Capture
Process Set by
Name Parameter Value User?
------------------------- ------------------------------ ---------- ----------
DA$CAP CAPTURE_IDKEY_OBJECTS N NO
DA$CAP CAPTURE_SEQUENCE_NEXTVAL N NO
DA$CAP DISABLE_ON_LIMIT N NO
DA$CAP DOWNSTREAM_REAL_TIME_MINE Y NO
DA$CAP EXCLUDETRANS NO
DA$CAP EXCLUDEUSER NO
DA$CAP EXCLUDEUSERID NO
DA$CAP GETAPPLOPS Y NO
DA$CAP GETREPLICATES N NO
DA$CAP IGNORE_TRANSACTION NO
DA$CAP IGNORE_UNSUPPORTED_TABLE * NO
DA$CAP MAXIMUM_SCN INFINITE NO
DA$CAP MAX_SGA_SIZE INFINITE NO
DA$CAP MERGE_THRESHOLD 60 NO
DA$CAP MESSAGE_LIMIT INFINITE NO
DA$CAP MESSAGE_TRACKING_FREQUENCY 2000000 NO
DA$CAP PARALLELISM 1 NO
DA$CAP SKIP_AUTOFILTERED_TABLE_DDL Y NO
DA$CAP SPLIT_THRESHOLD 1800 NO
DA$CAP STARTUP_SECONDS 0 NO
DA$CAP TIME_LIMIT INFINITE NO
DA$CAP TRACE_LEVEL 0 NO
DA$CAP WRITE_ALERT_LOG Y NO
DA$CAP XOUT_CLIENT_EXISTS N NO
Determining the Applied SCN for All Capture Processes in a Database
COLUMN CAPTURE_NAME HEADING 'Capture Process Name' FORMAT A30
COLUMN APPLIED_SCN HEADING 'Applied SCN' FORMAT 99999999999
SELECT CAPTURE_NAME, APPLIED_SCN FROM DBA_CAPTURE;
Capture Process Name Applied SCN
------------------------------ -----------
CAPTURE_EMP 177154
Determining Redo Log Scanning Latency for Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10
COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN LAST_STATUS HEADING 'Seconds Since|Last Status' FORMAT 999999
COLUMN CAPTURE_TIME HEADING 'Current|Process|Time'
COLUMN CREATE_TIME HEADING 'Message|Creation Time' FORMAT 999999
SELECT CAPTURE_NAME,
((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS,
((SYSDATE - CAPTURE_TIME)*86400) LAST_STATUS,
TO_CHAR(CAPTURE_TIME, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME,
TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME
FROM V$STREAMS_CAPTURE;
Capture Latency Current
Process in Seconds Since Process Message
Name Seconds Last Status Time Creation Time
---------- ------- ------------- ----------------- -----------------
DA$CAP 1 1 12:33:39 07/14/10 12:33:39 07/14/10
Latency in Seconds 是当前时间（SYSDATE）与 Message Creation Time 之间的时间间隔。
Seconds Since Last Status 是当前时间（SYSDATE）与 Current Process Time 之间的时间间隔。
Determining Message Enqueuing Latency for Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10
COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN CREATE_TIME HEADING 'Message Creation|Time' FORMAT A20
COLUMN ENQUEUE_TIME HEADING 'Enqueue Time' FORMAT A20
COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Message|Number' FORMAT 9999999999
SELECT CAPTURE_NAME,
(ENQUEUE_TIME-ENQUEUE_MESSAGE_CREATE_TIME)*86400 LATENCY_SECONDS,
TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME,
TO_CHAR(ENQUEUE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_TIME,
ENQUEUE_MESSAGE_NUMBER
FROM V$STREAMS_CAPTURE;
Capture Latency
Process in Message Creation Message
Name Seconds Time Enqueue Time Number
---------- ------- -------------------- -------------------- -------
CAPTURE 0 10:56:51 03/01/02 10:56:51 03/01/02 253962
Latency in Seconds 是 Enqueue Time 和 Message Creation Time 的时间间隔。
Displaying Information About Rule Evaluations for Each Capture Process
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
COLUMN TOTAL_PREFILTER_DISCARDED HEADING 'Prefilter|Changes|Discarded'
FORMAT 9999999999
COLUMN TOTAL_PREFILTER_KEPT HEADING 'Prefilter|Changes|Kept' FORMAT 9999999999
COLUMN TOTAL_PREFILTER_EVALUATIONS HEADING 'Prefilter|Evaluations'
FORMAT 9999999999
COLUMN UNDECIDED HEADING 'Undecided|After|Prefilter' FORMAT 9999999999
COLUMN TOTAL_FULL_EVALUATIONS HEADING 'Full|Evaluations' FORMAT 9999999999
SELECT CAPTURE_NAME,
TOTAL_PREFILTER_DISCARDED,
TOTAL_PREFILTER_KEPT,
TOTAL_PREFILTER_EVALUATIONS,
(TOTAL_PREFILTER_EVALUATIONS -
(TOTAL_PREFILTER_KEPT + TOTAL_PREFILTER_DISCARDED)) UNDECIDED,
TOTAL_FULL_EVALUATIONS
FROM V$STREAMS_CAPTURE;
Prefilter Prefilter Undecided
Capture Changes Changes Prefilter After Full
Name Discarded Kept Evaluations Prefilter Evaluations
--------------- ---------- ----------- ----------- ----------- -----------
CAPTURE_HNS 927409 3271491 4198900 0 9
Monitoring Oracle Streams Propagations
Displaying the Queues and Database Link for Each Propagation
COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A19
COLUMN SOURCE_QUEUE_NAME HEADING 'Source|Queue|Name' FORMAT A17
COLUMN DESTINATION_DBLINK HEADING 'Database|Link' FORMAT A9
COLUMN DESTINATION_QUEUE_NAME HEADING 'Dest|Queue|Name' FORMAT A15
COLUMN STATUS HEADING 'Status' FORMAT A8
COLUMN QUEUE_TO_QUEUE HEADING 'Queue-|to-|Queue?' FORMAT A6
SELECT PROPAGATION_NAME,
SOURCE_QUEUE_NAME,
DESTINATION_DBLINK,
DESTINATION_QUEUE_NAME,
STATUS,
QUEUE_TO_QUEUE
FROM DBA_PROPAGATION;
Source Dest Queue-
Propagation Queue Database Queue to-
Name Name Link Name Status Queue?
------------------- ----------------- --------- --------------- -------- ------
PROPAGATION$_6 DA$CAPQ DB.EXAMPL DA$APPQ ENABLED TRUE
E.COM
- 如果 STATUS 状态是 ENABLED，表示 Propagation 进程运行正常
- 如果 STATUS 状态是 DISABLED，表示 Propagation 进程处于停止状态，只需重新启动即可
- 如果 STATUS 状态是 ABORTED，表示 Propagation 进程非正常停止，查询相应的 ERROR_MESSAGE 列可以得到详细的信息；同时 Oracle 会在跟踪文件中记录该信息
Determining the Source Queue and Destination Queue for Each Propagation
COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20
COLUMN SOURCE_QUEUE_OWNER HEADING 'Source|Queue|Owner' FORMAT A10
COLUMN 'Source Queue' HEADING 'Source|Queue' FORMAT A15
COLUMN DESTINATION_QUEUE_OWNER HEADING 'Dest|Queue|Owner' FORMAT A10
COLUMN 'Destination Queue' HEADING 'Destination|Queue' FORMAT A15
SELECT p.PROPAGATION_NAME,
p.SOURCE_QUEUE_OWNER,
p.SOURCE_QUEUE_NAME ||'@'||
g.GLOBAL_NAME "Source Queue",
p.DESTINATION_QUEUE_OWNER,
p.DESTINATION_QUEUE_NAME ||'@'||
p.DESTINATION_DBLINK "Destination Queue"
FROM DBA_PROPAGATION p, GLOBAL_NAME g;
Source Dest
Propagation Queue Source Queue Destination
Name Owner Queue Owner Queue
-------------------- ---------- --------------- ---------- ---------------
PROPAGATION$_6 STRMADMIN DA$CAPQ@DA.EXAM STRMADMIN DA$APPQ@DB.EXAM
PLE.COM PLE.COM
Determining the Rule Sets for Each Propagation
COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20
COLUMN RULE_SET_OWNER HEADING 'Positive|Rule Set|Owner' FORMAT A10
COLUMN RULE_SET_NAME HEADING 'Positive Rule|Set Name' FORMAT A15
COLUMN NEGATIVE_RULE_SET_OWNER HEADING 'Negative|Rule Set|Owner' FORMAT A10
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative Rule|Set Name' FORMAT A15
SELECT PROPAGATION_NAME,
RULE_SET_OWNER,
RULE_SET_NAME,
NEGATIVE_RULE_SET_OWNER,
NEGATIVE_RULE_SET_NAME
FROM DBA_PROPAGATION;
Positive Negative
Propagation Rule Set Positive Rule Rule Set Negative Rule
Name Owner Set Name Owner Set Name
-------------------- ---------- --------------- ---------- ---------------
PROPAGATION$_6 STRMADMIN RULESET$_7 STRMADMIN RULESET$_9
Displaying Information About the Schedules for Propagation Jobs
COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN LATENCY HEADING 'Latency|in Seconds' FORMAT 99999
COLUMN SCHEDULE_DISABLED HEADING 'Status' FORMAT A8
COLUMN PROCESS_NAME HEADING 'Process' FORMAT A8
COLUMN FAILURES HEADING 'Failures' FORMAT 999
COLUMN QUEUE_TO_QUEUE HEADING 'Queue|to|Queue'
COLUMN LAST_ERROR_MSG HEADING 'Last Error|Message' FORMAT A15
SELECT p.PROPAGATION_NAME,
s.LATENCY,
DECODE(s.SCHEDULE_DISABLED,
'Y', 'Disabled',
'N', 'Enabled') SCHEDULE_DISABLED,
s.PROCESS_NAME,
s.FAILURES,
p.QUEUE_TO_QUEUE,
s.LAST_ERROR_MSG
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE s.MESSAGE_DELIVERY_MODE = 'BUFFERED'
AND s.DESTINATION LIKE '%' || p.DESTINATION_DBLINK
AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
AND s.QNAME = p.SOURCE_QUEUE_NAME
ORDER BY PROPAGATION_NAME;
Queue
Latency to Last Error
Propagation in Seconds Status Process Failures Queue Message
--------------- ---------- -------- -------- -------- ------ ---------------
PROPAGATION$_6 19 Enabled CS00 0 TRUE
Determining the Total Number of Messages and Bytes Propagated
COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20
COLUMN TOTAL_TIME HEADING 'Total Time|Executing|in Seconds' FORMAT 999999
COLUMN TOTAL_NUMBER HEADING 'Total Messages|Propagated' FORMAT 999999999
COLUMN TOTAL_BYTES HEADING 'Total Bytes|Propagated' FORMAT 9999999999999
SELECT p.PROPAGATION_NAME, s.TOTAL_TIME, s.TOTAL_NUMBER, s.TOTAL_BYTES
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE s.DESTINATION LIKE '%' || p.DESTINATION_DBLINK
AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
AND s.QNAME = p.SOURCE_QUEUE_NAME
AND s.MESSAGE_DELIVERY_MODE = 'BUFFERED';
Total Time
Propagation Executing Total Messages Total Bytes
Name in Seconds Propagated Propagated
-------------------- ---------- -------------- --------------
PROPAGATION$_6 0 432615 94751013
Displaying Information About Propagation Senders
Propagation Senders 将消息从源库发送到目标库。
COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A11
COLUMN SESSION_ID HEADING 'Session ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial Number' FORMAT 9999
COLUMN SPID HEADING 'Operating System|Process ID' FORMAT A24
COLUMN STATE HEADING 'State' FORMAT A16
SELECT p.PROPAGATION_NAME,
s.SESSION_ID,
s.SERIAL#,
s.SPID,
s.STATE
FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
WHERE p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
p.SOURCE_QUEUE_NAME = s.QUEUE_NAME AND
p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
p.DESTINATION_QUEUE_NAME = s.DST_QUEUE_NAME;
Propagation Session Operating System
Name Session ID Serial Number Process ID State
----------- ---------- ------------- ------------------------ ----------------
PROPAGATION 61 17 21145 Waiting on empty
$_6 queue
Displaying Information About Propagation Receivers
Propagation Receivers 在目标库接收 Propagation Senders 发送的消息。
COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A15
COLUMN SESSION_ID HEADING 'Session ID' FORMAT 999999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 999999
COLUMN SPID HEADING 'Operating|System|Process ID' FORMAT 999999
COLUMN STATE HEADING 'State' FORMAT A16
SELECT PROPAGATION_NAME,
SESSION_ID,
SERIAL#,
SPID,
STATE
FROM V$PROPAGATION_RECEIVER;
Session Operating
Propagation Serial System
Name Session ID Number Process ID State
--------------- ---------- ------- ------------------------ ----------------
PROPAGATION$_5 60 5 21050 Waiting for mess
age from propaga
tion sender
Displaying Session Information About Each Propagation
COLUMN ACTION HEADING 'Streams Component' FORMAT A28
COLUMN SID HEADING 'Session ID' FORMAT 99999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999999
COLUMN PROCESS HEADING 'Operating System|Process Number' FORMAT A20
COLUMN PROCESS_NAME HEADING 'Process|Names' FORMAT A7
SELECT /*+PARAM('_module_action_old_length',0)*/ ACTION,
SID,
SERIAL#,
PROCESS,
SUBSTR(PROGRAM,INSTR(PROGRAM,'(')+1,4) PROCESS_NAME
FROM V$SESSION
WHERE MODULE ='Streams' AND
ACTION LIKE '%Propagation%';
Session
Serial Operating System Process
Streams Component Session ID Number Process Number Names
---------------------------- ---------- -------- -------------------- -------
APPLY$_DB_3 - Propagation Re 60 5 21048 TNS
ceiver CCA
PROPAGATION$_6 - Propagation 61 17 21145 CS00
Sender CCA
Monitoring Oracle Streams Apply Processes
Determining the Queue, Rule Sets, and Status for Each Apply Process
使用 strmadmin 用户，登录目标数据库，确认应用进程是否运行正常。
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Apply|Process|Queue' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Apply|Process|Status' FORMAT A15
SELECT APPLY_NAME,
QUEUE_NAME,
RULE_SET_NAME,
NEGATIVE_RULE_SET_NAME,
STATUS
FROM DBA_APPLY;
Apply Apply Apply
Process Process Positive Negative Process
Name Queue Rule Set Rule Set Status
--------------- --------------- --------------- --------------- ---------------
STRM01_APPLY STREAMS_QUEUE RULESET$_36 ENABLED
APPLY_EMP STREAMS_QUEUE RULESET$_16 DISABLED
APPLY STREAMS_QUEUE RULESET$_21 RULESET$_23 ENABLED
- 如果 STATUS 状态是 ENABLED，表示 Apply 进程运行正常;
- 如果 STATUS 状态是 DISABLED，表示 Apply 进程处于停止状态，只需重新启动即可;
- 如果 STATUS 状态是 ABORTED，表示 Apply 进程非正常停止，查询相应的 ERROR_NUMBER、ERROR_MESSAGE 列可以得到详细的信息；同时可以查询 DBA_APPLY_ERROR 视图，了解详细的 Apply 错误信息。
Displaying General Information About Each Apply Process
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN APPLY_CAPTURED HEADING 'Applies Captured LCRs?' FORMAT A22
COLUMN APPLY_USER HEADING 'Apply User' FORMAT A20
SELECT APPLY_NAME, APPLY_CAPTURED, APPLY_USER
FROM DBA_APPLY;
Apply Process Name Applies Captured LCRs? Apply User
-------------------- ---------------------- --------------------
STRM01_APPLY YES STRMADMIN
SYNC_APPLY NO STRMADMIN
Listing the Parameter Settings for Each Apply Process
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN PARAMETER HEADING 'Parameter' FORMAT A30
COLUMN VALUE HEADING 'Value' FORMAT A22
COLUMN SET_BY_USER HEADING 'Set by|User?' FORMAT A10
SELECT APPLY_NAME,
PARAMETER,
VALUE,
SET_BY_USER
FROM DBA_APPLY_PARAMETERS;
Apply Process Set by
Name Parameter Value User?
--------------- ------------------------------ ---------------------- ----------
APPLY$_DB_3 ALLOW_DUPLICATE_ROWS N NO
APPLY$_DB_3 APPLY_SEQUENCE_NEXTVAL N NO
APPLY$_DB_3 COMMIT_SERIALIZATION DEPENDENT_TRANSACTIONS NO
APPLY$_DB_3 COMPARE_KEY_ONLY N NO
APPLY$_DB_3 DISABLE_ON_ERROR Y NO
APPLY$_DB_3 DISABLE_ON_LIMIT N NO
APPLY$_DB_3 GROUPTRANSOPS 250 NO
APPLY$_DB_3 IGNORE_TRANSACTION NO
APPLY$_DB_3 MAXIMUM_SCN INFINITE NO
APPLY$_DB_3 MAX_SGA_SIZE INFINITE NO
APPLY$_DB_3 PARALLELISM 4 NO
APPLY$_DB_3 PRESERVE_ENCRYPTION Y NO
APPLY$_DB_3 RTRIM_ON_IMPLICIT_CONVERSION Y NO
APPLY$_DB_3 STARTUP_SECONDS 0 NO
APPLY$_DB_3 TIME_LIMIT INFINITE NO
APPLY$_DB_3 TRACE_LEVEL 0 NO
APPLY$_DB_3 TRANSACTION_LIMIT INFINITE NO
APPLY$_DB_3 TXN_AGE_SPILL_THRESHOLD 900 NO
APPLY$_DB_3 TXN_LCR_SPILL_THRESHOLD 10000 NO
APPLY$_DB_3 WRITE_ALERT_LOG Y NO
Displaying Session Information About Each Apply Process
COLUMN ACTION HEADING 'Apply Process Component' FORMAT A30
COLUMN SID HEADING 'Session ID' FORMAT 99999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 99999999
COLUMN PROCESS HEADING 'Operating System|Process Number' FORMAT A17
COLUMN PROCESS_NAME HEADING 'Process|Names' FORMAT A7
SELECT /*+PARAM('_module_action_old_length',0)*/ ACTION,
SID,
SERIAL#,
PROCESS,
SUBSTR(PROGRAM,INSTR(PROGRAM,'(')+1,4) PROCESS_NAME
FROM V$SESSION
WHERE MODULE ='Streams' AND
ACTION LIKE '%Apply%';
Session
Serial Operating System Process
Apply Process Component Session ID Number Process Number Names
------------------------------ ---------- --------- ----------------- -------
APPLY$_EMDBB_3 - Apply Coordin 17 3040 9863 AP01
ator
APPLY$_EMDBB_3 - Apply Server 58 52788 9869 AS02
APPLY$_EMDBB_3 - Apply Reader 63 21 9865 AS01
APPLY$_EMDBB_3 - Apply Server 64 37 9872 AS03
APPLY$_EMDBB_3 - Apply Server 67 22 9875 AS04
APPLY$_EMDBB_3 - Apply Server 69 1 9877 AS05
Displaying Information About the Reader Server for Each Apply Process
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN APPLY_CAPTURED HEADING 'Dequeues Captured|Messages?' FORMAT A17
COLUMN PROCESS_NAME HEADING 'Process|Name' FORMAT A7
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_MESSAGES_DEQUEUED HEADING 'Total Messages|Dequeued' FORMAT 99999999
SELECT r.APPLY_NAME,
ap.APPLY_CAPTURED,
SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(')+1,4) PROCESS_NAME,
r.STATE,
r.TOTAL_MESSAGES_DEQUEUED
FROM V$STREAMS_APPLY_READER r, V$SESSION s, DBA_APPLY ap
WHERE r.SID = s.SID AND
r.SERIAL# = s.SERIAL# AND
r.APPLY_NAME = ap.APPLY_NAME;
Apply Process Dequeues Captured Process Total Messages
Name Messages? Name State Dequeued
--------------- ----------------- ------- ----------------- --------------
APPLY_SPOKE YES AS01 DEQUEUE MESSAGES 54
State 可能是：
INITIALIZING- Starting upIDLE- Performing no workDEQUEUEMESSAGES- Dequeuing messages from the apply process's queueSCHEDULEMESSAGES- Computing dependencies between messages and assembling messages into transactionsSPILLING- Spilling unapplied messages from memory to hard diskPAUSED-WAITINGFORDDLTOCOMPLETE- Paused while waiting for a DDL LCR to be applied
Monitoring Transactions and Messages Spilled by Each Apply Process
如果没有将应用进程的 TXN_LCR_SPILL_THRESHOLD 参数设置为 INFINITE，事务的消息数据超过了此参数的设定值，则会将消息写入到磁盘。
COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A20
COLUMN 'Transaction ID' HEADING 'Transaction ID' FORMAT A15
COLUMN FIRST_SCN HEADING 'First SCN' FORMAT 99999999
COLUMN MESSAGE_COUNT HEADING 'Message Count' FORMAT 99999999
SELECT APPLY_NAME,
XIDUSN ||'.'||
XIDSLT ||'.'||
XIDSQN "Transaction ID",
FIRST_SCN,
MESSAGE_COUNT
FROM DBA_APPLY_SPILL_TXN;
Apply Name Transaction ID First SCN Message Count
-------------------- --------------- --------- -------------
APPLY_HR 1.42.2277 2246944 100
COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A15
COLUMN TOTAL_MESSAGES_SPILLED HEADING 'Total|Spilled Messages' FORMAT 99999999
COLUMN ELAPSED_SPILL_TIME HEADING 'Elapsed Time|Spilling Messages' FORMAT 99999999.99
SELECT APPLY_NAME,
TOTAL_MESSAGES_SPILLED,
(ELAPSED_SPILL_TIME/100) ELAPSED_SPILL_TIME
FROM V$STREAMS_APPLY_READER;
Total Elapsed Time
Apply Name Spilled Messages Spilling Messages
--------------- ---------------- -----------------
APPLY_HR 100 2.67
Determining Capture to Dequeue Latency for a Message
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN CREATION HEADING 'Message Creation' FORMAT A17
COLUMN LAST_DEQUEUE HEADING 'Last Dequeue Time' FORMAT A20
COLUMN DEQUEUED_MESSAGE_NUMBER HEADING 'Dequeued|Message Number' FORMAT 9999999999
SELECT APPLY_NAME,
(DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY,
TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') CREATION,
TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE,
DEQUEUED_MESSAGE_NUMBER
FROM V$STREAMS_APPLY_READER;
Latency
Apply Process in Dequeued
Name Seconds Message Creation Last Dequeue Time Message Number
----------------- ------- ----------------- -------------------- --------------
APPLY$_STM1_14 1 15:22:15 06/13/05 15:22:16 06/13/05 502129
Message Creation 是源库重做数据产生的时间。
Last Dequeue Time 是应用进程将消息出队的时间。
Latency in Seconds 是 Message Creation 和 Last Dequeue Time 的时间间隔。
Displaying General Information About Each Coordinator Process
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN PROCESS_NAME HEADING 'Coordinator|Process|Name' FORMAT A11
COLUMN SID HEADING 'Session|ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999
COLUMN STATE HEADING 'State' FORMAT A21
SELECT c.APPLY_NAME,
SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(')+1,4) PROCESS_NAME,
c.SID,
c.SERIAL#,
c.STATE
FROM V$STREAMS_APPLY_COORDINATOR c, V$SESSION s
WHERE c.SID = s.SID AND
c.SERIAL# = s.SERIAL#;
Coordinator Session
Apply Process Process Session Serial
Name Name ID Number State
----------------- ----------- ------- ------- ---------------------
APPLY_SPOKE AP01 944 5 IDLE
State 可能是：
INITIALIZING- Starting upIDLE- Performing no workAPPLYING- Passing transactions to apply serversSHUTTINGDOWNCLEANLY- Stopping without an errorABORTING- Stopping because of an apply error
Displaying Information About Transactions Received and Applied
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN TOTAL_RECEIVED HEADING 'Total|Trans|Received' FORMAT 99999999
COLUMN TOTAL_APPLIED HEADING 'Total|Trans|Applied' FORMAT 99999999
COLUMN TOTAL_ERRORS HEADING 'Total|Apply|Errors' FORMAT 9999
COLUMN BEING_APPLIED HEADING 'Total|Trans Being|Applied' FORMAT 99999999
COLUMN UNASSIGNED_COMPLETE_TXNS HEADING 'Total|Unnasigned|Trans' FORMAT 99999999
COLUMN TOTAL_IGNORED HEADING 'Total|Trans|Ignored' FORMAT 99999999
SELECT APPLY_NAME,
TOTAL_RECEIVED,
TOTAL_APPLIED,
TOTAL_ERRORS,
(TOTAL_ASSIGNED - (TOTAL_ROLLBACKS + TOTAL_APPLIED)) BEING_APPLIED,
UNASSIGNED_COMPLETE_TXNS,
TOTAL_IGNORED
FROM V$STREAMS_APPLY_COORDINATOR;
Total Total Total Total Total Total
Trans Trans Apply Trans Being Unnasigned Trans
Apply Process Name Received Applied Errors Applied Trans Ignored
-------------------- --------- --------- ------ ----------- ---------- ---------
APPLY_FROM_MULT1 81 73 2 6 4 0
APPLY_FROM_MULT2 114 96 0 14 7 4
Determining the Capture to Apply Latency for a Message for Each Apply Process
在应用进程启动的情况下才能使用 V$STREAMS_APPLY_COORDINATOR 视图进行查询。
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A13
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN HWM_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 9999999999
SELECT APPLY_NAME,
(HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Message Creation",
TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
HWM_MESSAGE_NUMBER
FROM V$STREAMS_APPLY_COORDINATOR;
Apply Process Message
Name Latency in Seconds Message Creation Apply Time Number
------------- ------------------ ----------------- ----------------- -----------
APPLY$_DA_2 2 13:00:10 07/14/10 13:00:12 07/14/10 672733
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 9999999999
SELECT APPLY_NAME,
(APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Message Creation",
TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
APPLIED_MESSAGE_NUMBER
FROM DBA_APPLY_PROGRESS;
Applied
Apply Process Message
Name Latency in Seconds Message Creation Apply Time Number
----------------- ------------------ ----------------- ----------------- -------
APPLY$_STM1_14 33 14:05:13 06/13/05 14:05:46 06/13/05 498215
Displaying Information About the Apply Servers for Each Apply Process
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A22
COLUMN PROCESS_NAME HEADING 'Process Name' FORMAT A12
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_ASSIGNED HEADING 'Total|Transactions|Assigned' FORMAT 99999999
COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total|Messages|Applied' FORMAT 99999999
SELECT r.APPLY_NAME,
SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,
r.STATE,
r.TOTAL_ASSIGNED,
r.TOTAL_MESSAGES_APPLIED
FROM V$STREAMS_APPLY_SERVER R, V$SESSION S
WHERE r.SID = s.SID AND
r.SERIAL# = s.SERIAL#
ORDER BY r.APPLY_NAME, r.SERVER_ID;
Total Total
Transactions Messages
Apply Process Name Process Name State Assigned Applied
---------------------- ------------ ----------------- ------------ ---------
APPLY$_DA_2 AS02 IDLE 1012 109190
APPLY$_DA_2 AS03 IDLE 996 107568
APPLY$_DA_2 AS04 IDLE 1006 108648
APPLY$_DA_2 AS05 IDLE 987 10659
State 可能是：
INITIALIZING- Starting up.IDLE- Performing no work.RECORDLOW-WATERMARK- Performing an administrative action that maintains information about the apply progress, which is used in theALL_APPLY_PROGRESSandDBA_APPLY_PROGRESSdata dictionary views.ADDPARTITION- Performing an administrative action that adds a partition that is used for recording information about in-progress transactions.DROPPARTITION- Performing an administrative action that drops a partition that was used to record information about in-progress transactions.EXECUTETRANSACTION- Applying a transaction.WAITCOMMIT- Waiting to commit a transaction until all other transactions with a lower commit SCN are applied. This state is possible only if thecommit_serializationapply process parameter is set to a value other thanDEPENDENT_TRANSACTIONSand theparallelismapply process parameter is set to a value greater than1.WAITDEPENDENCY- Waiting to apply an LCR in a transaction until another transaction, on which it has a dependency, is applied. This state is possible only if thePARALLELISMapply process parameter is set to a value greater than1.WAITFORCLIENT- Waiting for an XStream In client application to request more logical change records (LCRs).WAITFORNEXTCHUNK- Waiting for the next set of LCRs for a large transaction.ROLLBACKTRANSACTION- Rolling back a transaction.TRANSACTIONCLEANUP- Cleaning up an applied transaction, which includes removing LCRs from the apply process's queue.
Displaying Effective Apply Parallelism for an Apply Process
查看自启动 Apply 进程后，应用进程的使用数量。
SELECT COUNT(SERVER_ID) "Effective Parallelism"
FROM V$STREAMS_APPLY_SERVER
WHERE APPLY_NAME = 'APPLY' AND
TOTAL_MESSAGES_APPLIED > 0;
Effective Parallelism
---------------------
2
查看每个应用进程处理的消息数量。
COLUMN SERVER_ID HEADING 'Apply Server ID' FORMAT 99
COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total Messages Applied' FORMAT 999999
SELECT SERVER_ID, TOTAL_MESSAGES_APPLIED
FROM V$STREAMS_APPLY_SERVER
WHERE APPLY_NAME = 'APPLY'
ORDER BY SERVER_ID;
Apply Server ID Total Messages Applied
--------------- ----------------------
1 2141
2 276
3 0
4 0
Checking for Apply Errors
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A11
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
COLUMN LOCAL_TRANSACTION_ID HEADING 'Local|Transaction|ID' FORMAT A11
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A20
COLUMN MESSAGE_COUNT HEADING 'Messages in|Error|Transaction' FORMAT 99999999
SELECT APPLY_NAME,
SOURCE_DATABASE,
LOCAL_TRANSACTION_ID,
ERROR_NUMBER,
ERROR_MESSAGE,
MESSAGE_COUNT
FROM DBA_APPLY_ERROR;
Apply Local Messages in
Process Source Transaction Error
Name Database ID Error Number Error Message Transaction
----------- ---------- ----------- ------------ -------------------- -----------
APPLY$_DB_2 DB.EXAMPLE 13.16.334 26786 ORA-26786: A row wit 1
.COM h key ("EMPLOYEE_ID"
) = (206) exists but
has conflicting col
umn(s) "SALARY" in t
able HR.EMPLOYEES
ORA-01403: no data f
ound
APPLY$_DB_2 DB.EXAMPLE 15.17.540 26786 ORA-26786: A row wit 1
.COM h key ("EMPLOYEE_ID"
) = (206) exists but
has conflicting col
umn(s) "SALARY" in t
able HR.EMPLOYEES
ORA-01403: no data f
ound
如果遇到 Apply Error，可以重新执行失败的事务或者删除失败的事务。重新执行失败的事务之前，需要先找到导致事务失败的原因并解决。
SQL> EXEC DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS ();
SQL> EXEC DBMS_APPLY_ADM.DELETE_ALL_ERRORS ();
Displaying Detailed Information About Apply Errors
Grant Explicit SELECT Privilege on the DBA_APPLY_ERROR View
SQL> GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin;
SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
SQL> connect strmadmin/strmadmin
Create a Procedure that Prints the Value in an ANYDATA Object
CREATE OR REPLACE PROCEDURE print_any(data IN ANYDATA) IS
tn VARCHAR2(61);
str VARCHAR2(4000);
chr VARCHAR2(1000);
num NUMBER;
dat DATE;
rw RAW(4000);
res NUMBER;
BEGIN
IF data IS NULL THEN
DBMS_OUTPUT.PUT_LINE('NULL value');
RETURN;
END IF;
tn := data.GETTYPENAME();
IF tn = 'SYS.VARCHAR2' THEN
res := data.GETVARCHAR2(str);
DBMS_OUTPUT.PUT_LINE(SUBSTR(str,0,253));
ELSIF tn = 'SYS.CHAR' then
res := data.GETCHAR(chr);
DBMS_OUTPUT.PUT_LINE(SUBSTR(chr,0,253));
ELSIF tn = 'SYS.VARCHAR' THEN
res := data.GETVARCHAR(chr);
DBMS_OUTPUT.PUT_LINE(chr);
ELSIF tn = 'SYS.NUMBER' THEN
res := data.GETNUMBER(num);
DBMS_OUTPUT.PUT_LINE(num);
ELSIF tn = 'SYS.DATE' THEN
res := data.GETDATE(dat);
DBMS_OUTPUT.PUT_LINE(dat);
ELSIF tn= 'SYS.TIMESTAMP' THEN
res := data.GETTIMESTAMP(dat);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(dat,'DD-MON-RR HH.MI.SSXFF AM'));
ELSIF tn= 'SYS.TIMESTAMPTZ' THEN
res := data.GETTIMESTAMPTZ(dat);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(dat,'DD-MON-RR HH.MI.SSXFF AM'));
ELSIF tn= 'SYS.TIMESTAMPLTZ' THEN
res := data.GETTIMESTAMPLTZ(dat);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(dat,'DD-MON-RR HH.MI.SSXFF AM'));
ELSIF tn = 'SYS.RAW' THEN
-- res := data.GETRAW(rw);
-- DBMS_OUTPUT.PUT_LINE(SUBSTR(DBMS_LOB.SUBSTR(rw),0,253));
DBMS_OUTPUT.PUT_LINE('BLOB Value');
ELSIF tn = 'SYS.BLOB' THEN
DBMS_OUTPUT.PUT_LINE('BLOB Found');
ELSE
DBMS_OUTPUT.PUT_LINE('typename is ' || tn);
END IF;
END print_any;
/
Create a Procedure that Prints a Specified LCR
CREATE OR REPLACE PROCEDURE print_lcr(lcr IN ANYDATA) IS
typenm VARCHAR2(61);
ddllcr SYS.LCR$_DDL_RECORD;
proclcr SYS.LCR$_PROCEDURE_RECORD;
rowlcr SYS.LCR$_ROW_RECORD;
res NUMBER;
newlist SYS.LCR$_ROW_LIST;
oldlist SYS.LCR$_ROW_LIST;
ddl_text CLOB;
ext_attr ANYDATA;
BEGIN
typenm := lcr.GETTYPENAME();
DBMS_OUTPUT.PUT_LINE('type name: ' || typenm);
IF (typenm = 'SYS.LCR$_DDL_RECORD') THEN
res := lcr.GETOBJECT(ddllcr);
DBMS_OUTPUT.PUT_LINE('source database: ' ||
ddllcr.GET_SOURCE_DATABASE_NAME);
DBMS_OUTPUT.PUT_LINE('owner: ' || ddllcr.GET_OBJECT_OWNER);
DBMS_OUTPUT.PUT_LINE('object: ' || ddllcr.GET_OBJECT_NAME);
DBMS_OUTPUT.PUT_LINE('is tag null: ' || ddllcr.IS_NULL_TAG);
DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE);
ddllcr.GET_DDL_TEXT(ddl_text);
DBMS_OUTPUT.PUT_LINE('ddl: ' || ddl_text);
-- Print extra attributes in DDL LCR
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('serial#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('session#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('thread#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('tx_name');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2());
END IF;
ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('username');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2());
END IF;
DBMS_LOB.FREETEMPORARY(ddl_text);
ELSIF (typenm = 'SYS.LCR$_ROW_RECORD') THEN
res := lcr.GETOBJECT(rowlcr);
DBMS_OUTPUT.PUT_LINE('source database: ' ||
rowlcr.GET_SOURCE_DATABASE_NAME);
DBMS_OUTPUT.PUT_LINE('owner: ' || rowlcr.GET_OBJECT_OWNER);
DBMS_OUTPUT.PUT_LINE('object: ' || rowlcr.GET_OBJECT_NAME);
DBMS_OUTPUT.PUT_LINE('is tag null: ' || rowlcr.IS_NULL_TAG);
DBMS_OUTPUT.PUT_LINE('command_type: ' || rowlcr.GET_COMMAND_TYPE);
oldlist := rowlcr.GET_VALUES('old');
FOR i IN 1..oldlist.COUNT LOOP
IF oldlist(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('old(' || i || '): ' || oldlist(i).column_name);
print_any(oldlist(i).data);
END IF;
END LOOP;
newlist := rowlcr.GET_VALUES('new', 'n');
FOR i in 1..newlist.count LOOP
IF newlist(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('new(' || i || '): ' || newlist(i).column_name);
print_any(newlist(i).data);
END IF;
END LOOP;
-- Print extra attributes in row LCR
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('row_id');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('row_id: ' || ext_attr.ACCESSUROWID());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('serial#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('session#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('thread#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('tx_name');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2());
END IF;
ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('username');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2());
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Non-LCR Message with type ' || typenm);
END IF;
END print_lcr;
/
Create a Procedure that Prints All the LCRs in the Error Queue
CREATE OR REPLACE PROCEDURE print_errors IS
CURSOR c IS
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_NUMBER,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
FROM DBA_APPLY_ERROR
ORDER BY SOURCE_DATABASE, SOURCE_COMMIT_SCN;
i NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgno NUMBER;
msgcnt NUMBER;
errnum NUMBER := 0;
errno NUMBER;
errmsg VARCHAR2(2000);
lcr ANYDATA;
r NUMBER;
BEGIN
FOR r IN c LOOP
errnum := errnum + 1;
msgcnt := r.MESSAGE_COUNT;
txnid := r.LOCAL_TRANSACTION_ID;
source := r.SOURCE_DATABASE;
msgno := r.MESSAGE_NUMBER;
errno := r.ERROR_NUMBER;
errmsg := r.ERROR_MESSAGE;
DBMS_OUTPUT.PUT_LINE('*************************************************');
DBMS_OUTPUT.PUT_LINE('----- ERROR #' || errnum);
DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid);
DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source);
DBMS_OUTPUT.PUT_LINE('----Error in Message: '|| msgno);
DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno);
DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg);
FOR i IN 1..msgcnt LOOP
DBMS_OUTPUT.PUT_LINE('--message: ' || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid);
print_lcr(lcr);
END LOOP;
END LOOP;
END print_errors;
/
执行：
SET SERVEROUTPUT ON SIZE 1000000
EXEC print_errors
Create a Procedure that Prints All the Error LCRs for a Transaction
CREATE OR REPLACE PROCEDURE print_transaction(ltxnid IN VARCHAR2) IS
i NUMBER;
txnid VARCHAR2(30);
source VARCHAR2(128);
msgno NUMBER;
msgcnt NUMBER;
errno NUMBER;
errmsg VARCHAR2(2000);
lcr ANYDATA;
BEGIN
SELECT LOCAL_TRANSACTION_ID,
SOURCE_DATABASE,
MESSAGE_NUMBER,
MESSAGE_COUNT,
ERROR_NUMBER,
ERROR_MESSAGE
INTO txnid, source, msgno, msgcnt, errno, errmsg
FROM DBA_APPLY_ERROR
WHERE LOCAL_TRANSACTION_ID = ltxnid;
DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid);
DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source);
DBMS_OUTPUT.PUT_LINE('----Error in Message: '|| msgno);
DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno);
DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg);
FOR i IN 1..msgcnt LOOP
DBMS_OUTPUT.PUT_LINE('--message: ' || i);
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); -- gets the LCR
print_lcr(lcr);
END LOOP;
END print_transaction;
/
传入事务 ID 并执行：
SET SERVEROUTPUT ON SIZE 1000000
EXEC print_transaction('1.17.2485')
Oracle Streams Restrictions
Capture Process Restrictions
Unsupported Data Types for Capture Processes
捕获进程不支持以下字段类型：
BFILEROWID- User-defined types (including object types,
REFs, varrays, and nested tables) XMLTypestored object relationally or as binary XML- The following Oracle-supplied types:
Anytypes, URI types, spatial types, and media types
Unsupported Table Types for Capture Processes
- temporary tables
- object tables
Changes Ignored by a Capture Process
- The session control statements
ALTERSESSIONandSETROLE. - The system control statement
ALTERSYSTEM. CALL,EXPLAINPLAN, andLOCKTABLEstatements.GRANTstatements on views.- Changes made to a table or schema by online redefinition using the
DBMS_REDEFINITIONpackage. - Changes to sequence values.
- Invocations of PL/SQL procedures, which means that a call to a PL/SQL procedure is not captured.
Operational Requirements for Downstream Capture
对于 Downstream Capture，需要：
- 源库的版本必须 10g 以上，Downstream Capture 数据库版本必须与源库一致或更高。
- 如果配置 Real-Time Downstream Capture，Downstream Capture 数据库版本必须 10gR2 及以上，源库必须 10gR1 及以上。
- 源库和Downstream Capture 数据库的操作系统必须一致。
- 源库和Downstream Capture 数据库所在的硬件架构必须一致。
Apply Process Restrictions
Types of DDL Changes Ignored by an Apply Process
ALTERMATERIALIZEDVIEWALTERMATERIALIZEDVIEWLOGCREATEDATABASELINKCREATESCHEMAAUTHORIZATIONCREATEMATERIALIZEDVIEWCREATEMATERIALIZEDVIEWLOGDROPDATABASELINKDROPMATERIALIZEDVIEWDROPMATERIALIZEDVIEWLOGFLASHBACKDATABASERENAME
特别要注意不能在源库使用 RENAME 重命名表，应该使用 ALTER TABLE ... RENAME TO ...。