--第一部分 stream环境的日常管理
--1.capture进程管理（一般来说这是个采集进程，simple stream时在主库上，down stream时在负责分发的丛库上）
--capture进程信息
SELECT CAPTURE_NAME,
QUEUE_NAME,
RULE_SET_NAME,
NEGATIVE_RULE_SET_NAME,
STATUS,
ERROR_NUMBER,
ERROR_MESSAGE
FROM DBA_CAPTURE;
--显示 capture进程的统计信息
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#;
--查看cpture状态和最后一个message形成的时间
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 性能查看
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;
--capture进程重启需要的redo
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的参数
SELECT CAPTURE_NAME, PARAMETER, VALUE, SET_BY_USER
FROM DBA_CAPTURE_PARAMETERS;
--2.propagation 进程管理（一般来说这是个分发进程，把采集到的信息分发到各个丛库上去，simple stream时在主库上，down stream时在负责分发的丛库上）
--buffer_queues信息
SELECT QUEUE_SCHEMA,
QUEUE_NAME,
(NUM_MSGS - SPILL_MSGS) MEM_MSG,
SPILL_MSGS,
NUM_MSGS
FROM V$BUFFERED_QUEUES;
--显示各个propagation的基本信息
SELECT p.PROPAGATION_NAME,
s.SUBSCRIBER_ADDRESS,
s.CURRENT_ENQ_SEQ,
s.LAST_BROWSED_SEQ,
s.LAST_DEQUEUED_SEQ,
s.NUM_MSGS,
s.TOTAL_SPILLED_MSG
FROM DBA_PROPAGATION p, V$BUFFERED_SUBSCRIBERS s, V$BUFFERED_QUEUES q
WHERE q.QUEUE_ID = s.QUEUE_ID
AND p.SOURCE_QUEUE_OWNER = q.QUEUE_SCHEMA
AND p.SOURCE_QUEUE_NAME = q.QUEUE_NAME
AND p.DESTINATION_DBLINK = s.SUBSCRIBER_ADDRESS;
--3. 管理apply进程（一般来说这是个信息应用进程，把得到的信息应用到数据库中，不管是simple stream还是down stream，都是在丛库上的）
--apply进程基本信息
SELECT APPLY_NAME,
DECODE(APPLY_CAPTURED, 'YES', 'Captured', 'NO', 'User-Enqueued') APPLY_CAPTURED,
APPLY_USER
FROM DBA_APPLY;
--apply 参数设置信息
SELECT APPLY_NAME, PARAMETER, VALUE, SET_BY_USER FROM DBA_APPLY_PARAMETERS;
--reader server 信息
SELECT r.APPLY_NAME,
DECODE(ap.APPLY_CAPTURED,
'YES',
'Captured LCRS',
'NO',
'User-enqueued messages',
'UNKNOWN') 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延时
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;
---查看apply错误
select * from dba_apply_error ;
--4.清除stream配置
--首先停止capture propagation apply进程
exec dbms_streams_adm.remove_streams_configuration;
--5. stream的一些基本信息
---当前版本对stream的支持与不支持类型
select * from dba_streams_newly_supported;
---一些stream必备的参数状况
show parameter LOG_ARCHIVE;
主要是log_archive_config
log_archive_dest_
log_archive_dest_state_
select * from dba_objects where lower(object_name) like '%rule%'；
---流复制的一些配置规则
--流传送的内容
select * from DBA_STREAMS_RULES;
--传送内容的schema
select * from DBA_STREAMS_SCHEMA_RULES;
--表传送的内容
select * from DBA_STREAMS_TABLE_RULES;
流复制常用的一些存储过程示例：
---检查apply进程的存储过程示例
create or replace procedure prc_check_apply as
v_count number;
v_date date;
v_error varchar2(4000);
begin
select sysdate into v_date from dual;
select count(*)
into v_count
from dba_apply
where apply_name = 'PPS_23_22'
and status = 'ENABLED';
if v_count <> 1 then
dbms_apply_adm.start_apply('PPS_23_22');
insert into log_stream_apply
(up_date, event)
values
(v_date, 'PPS_23_22 HAS SHUTDOWN');
commit;
end if;
EXCEPTION
WHEN OTHERS THEN
v_error := SUBSTR(SQLERRM, 1, 4000);
UPDATE log_stream_apply SET errors = v_error WHERE up_date = v_date;
COMMIT;
end;
--重启capture进程的存储过程示例
create or replace procedure prc_restart_capture is
v_start_time date;
v_error varchar2(4000);
begin
select sysdate into v_start_time from dual;
insert into log_stream_capture (start_time) values (v_start_time);
commit;
dbms_capture_adm.stop_capture('PPS_23_22');
dbms_lock.sleep(60);
dbms_capture_adm.start_capture('PPS_23_22');
update log_stream_capture
set finished_time = sysdate, status = 'OK'
where start_time = v_start_time;
commit;
exception
when others then
v_error := SUBSTR(SQLERRM, 1, 4000);
update log_stream_capture
set status = 'FAILED', error_time = sysdate, errors = v_error
where start_time = v_start_time;
end prc_restart_capture;
--------------------------------------
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.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 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 (255);
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;
------------------------------------------------------------------
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 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 (128);
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;
以下纯转
##################################################################################
第二部分
stream监控工具
1.strmmon一个监控stream运行状态的一个小工具，具体参看metalink文档ID 290605.1
strmmon只支持10g R2或者更高的版本，工具可以在metalink上下载
0 介绍
Note: STRMMON is an OCI program. If you have difficulty using the supplied make file, follow the recommended procedure for compiling and linking OCI programs for your platform (demo_rdbms.mk in the $ORACLE_HOME/rdbms/demo directory). The strmmon.mk file identified above is a copy of the demo_rdbms.mk file from the Oracle 9i Database Release 2 software for the Solaris 32-bit platform.
For Oracle Database 10g Release 2, STRMMON is distributed in the rdbms/demo directory on Release 10G release 2. It is installed when installing "Oracle Database 10G Products" in the Companion CD. To use the latest version of strmmon in Oracle Database 10g Release 2, archive the existing strmmon.c, strmmon.o, and strmmon files in the $ORACLE_HOME/rdbms/demo directory into a backup location. Then, extract the strmmon.c and the strmmon.html files from the STRMMON zip file into the rdbms/demo directory. Use the existing make file (demo_rdbms.mk) in the demo directory to generate the strmmon executable.
1 下载strmmon.zip
metalink登陆: https://metalink2.oracle.com/cgi-bin/cr/getfile.cgi?p_attid=290605.1:STRMMON26
2 安装
$ cd /orahome/soft
$ mkdir strmmon
$ cd strmmon
$ rz (SecureCRT里上传strmmon.zip)
$ unzip strmmon.zip (解压)
$ echo $ORACLE_HOME (确保ORACLE_HOME和LD_LIBRARY_PATH两个环境变量设置正确)
/u01/app/oracle/product/10.2.0/db_1
$ echo $LD_LIBRARY_PATH
/u01/app/oracle/product/10.2.0/db_1/lib
$ make -f strmmon.mk strmmon (编译)
$ pwd
/orahome/soft/strmmon
$ ll (可以看到已经生成了可执行文件strmmon)
total 476
-rwxr-xr-x 1 oracle oinstall 131425 Dec 22 19:14 strmmon
-rw-r--r-- 1 oracle oinstall 131780 Feb 7 2008 strmmon.c
-rw-r--r-- 1 oracle oinstall 45922 Feb 10 2007 strmmon.htm
-rw-r--r-- 1 oracle oinstall 1308 Aug 23 2004 strmmon.mk
-rw-r--r-- 1 oracle oinstall 117568 Dec 22 19:14 strmmon.o
-rw-r--r-- 1 oracle oinstall 31981 Dec 22 19:11 strmmon.zip
3 使用
$ cd /orahome/soft/strmmon
$ ./strmmon (查看帮助)
Usage: strmmon -interval <seconds> -count <number> [-user <user name>]
[-passw <password>] [-dbname <database name>] [-sysdba]
[-long]
$ ./strmmon -interval 3 -count 5 -sysdba (一般用sysdba权限查看即可)
$ ./strmmon -interval 3 -count 5 -sysdba -long (long参数表示获得更加详细的报告)
STREAMS Monitor, v 2.6 Copyright Oracle Corp. 2002, 2005.
Interval = 3, Count=5
Logon= @ ORACLE 10.2.0.1.0
Streams Pool Size = 256M
LOG : <redo generated per sec>
NET: <client bytes per sec> <dblink bytes per sec>
Cxxx: <lcrs captured per sec> <lcrs enqueued per sec> <capture latency>
MEM : <percent of memory used> % <streams pool size>
PRxx: <messages received per sec>
Qx : <msgs enqueued per sec> <msgs spilled per sec>
PSxx: <lcrs propagated per sec> <bytes propaged per sec>
Axxx: <lcrs applied per sec> <txns applied per sec> <dequeue latency>
<F>: flow control in effect
<B>: potential bottleneck
AR: apply reader
AS(n): n number of apply server
<x%I x%F x%xx>: <idle wait events percentage> <flow control wait events percentage> <other wait event percentage and name>
xx->: database instance name
2008-12-23 12:51:02 || WENDING-> | MEM 48 % 256M
2008-12-23 12:51:06 || WENDING-> | LOG 4K | NET 2K 0 | Q15185 0 0 | PS01 0 0 0 <73%I 0%F -> | MEM 48 % 256M
2008-12-23 12:51:09 || WENDING-> | LOG 341 | NET 3K 0 | Q15185 0 0 | PS01 0 0 0 <97%I 0%F -> | MEM 48 % 256M
2008-12-23 12:51:12 || WENDING-> | LOG 0 | NET 3K 0 | Q15185 0 0 | PS01 0 0 0 <97%I 0%F -> | MEM 48 % 256M
2008-12-23 12:51:15 || WENDING-> | LOG 512 | NET 3K 0 | Q15185 0 0 | PS01 0 0 0 <97%I 0%F -> | MEM 48 % 256M
4 各参数详细解释
LOG
Information about the redo log activity is written in this block. The first statistic following the LOG: keyword is the current SCN that has been written to the redo log. This number represents the current activity within the database. If this number does not increase, no activity is occurring on the database. The second statistic is the last block number written in the redo log. Redo blocks are always 512 bytes, so this statistic can be used to calculate the amount of redo generated between intervals. The output for the LOG component under the -long option is always the first entry after the timestamp and the database name on the display and appears as follows:
2005-08-13 18:05:23 || lnxmain-> | LOG 2952151 812574 |
2005-08-13 18:05:23 || lnxmain-> | LOG 2952151 812574 |
2005-08-13 18:05:27 || lnxmain-> | LOG 2952155 812576 |
2005-08-13 18:05:30 || lnxmain-> | LOG 2952156 812577 |
2005-08-13 18:05:33 || lnxmain-> | LOG 2952156 812577 |
In the above example, the current scn that written to the redo log is 2952151 and the last block number is 812574. Since the strmmon command was issued with –interval 3 –count 5, 5 lines of output are displayed with a 3 second interval between them. The activity on this database is very low, as seen by the fact that the current scn increases infrequently (note change between 18:05:23 and 18:05:27 and 18:05:30).
For the default output, the LOG component entry shows the rate at which redo information is generated:
2005-08-13 18:08:51 || lnxmain-> | LOG 1K |
2005-08-13 18:08:54 || lnxmain-> | LOG 341 |
2005-08-13 18:08:57 || lnxmain-> | LOG 170 |
2005-08-13 18:09:00 || lnxmain-> | LOG 0 |
The printed value is the average number of bytes of redo generated per
NET
For the default output, the average number of bytes per second generated over the network by clients and dblinks is reported. For the -long output, the current values for the statistics of client and dblink network activity are reported.
Cxxx
For each capture process configured in the database, a separate block will be displayed. Each block displays the number of lcrs captured per sec, number of lcrs enqueued per sec and the capture latency. If "-long" option is specified, then in each block, the Logminer read scn, the total number of messages captured from the redo log and the most recent scn captured from the redo log are shown. In addition, the number of messages that match the rules specified for the capture process including the most recent message scn enqueued are shown along with the capture latency.
One can also use the difference between successive capture "messages captured" statistics to determine the rate at which capture is mining the redo log. The enqueue scn of capture is an indicator of where the capture process will restart, if capture is stopped and restarted while the database is running. This statistic can also be used for comparison with the appropriate database Apply process high-water mark scn. If these statistics match, the capture and apply are caught up and the data is synchronized.
MEM%
If strmmon is run from the SYS schema connected as SYSDBA, and the database version is 10gR1 or higher, then this displays the percentage of Streams Pool memory currently in use and the size of the Streams Pool. These statistics are reported in both the default and –long output formats.
Qx
For each streams queue in the database, a separate block displays the queue identifier, the cumulative message rate and the spill rate. If "-long" option is specified, then for each streams queue in the database, a separate block will display the queue identifier as well as the number of outstanding messages in the buffered queue, the cumulative number of messages that have been in the queue and the number of messages spilled from memory to disk. In version 9iR2, the number of messages currently spilled is displayed. In 10gR1, the cumulative number of messages spilled is displayed. For 9iR2, the default and the "-long" option display the same data.
The queue identifier (QID) can be used to identify the name of the queue. Use the QID in queries against the DBA_QUEUES view to identify the particular queue in the database. In Oracle Database 9i Release 2, the number of outstanding messages in the buffered queue is only displayed if strmmon is run from the SYS schema as SYSDBA. In 9iR2, if the number of spilled messages becomes non-zero, consider stopping capture temporarily to slow down the flow of data. See Metalink Note 259609.1 for an example flow control script.
PSxx
For each propagation sender, the number of lcrs propagated per sec is displayed. If "-long" option is specified, the for each propagation, the total number of messages and the total number of bytes propagated to the destination site and the total time needed to propagate those messages is displayed.
PRxx
For each propagation receiver, the number of lcrs received per sec is displayed. If "-long" option is specified, the for each propagation, the total number of messages received and the total time to receive those messages is displayed
Axxx
For each apply process, the number of lcrs applied per sec, the number of transactions applied per second, and the apply latency are displayed. If the "-long" option is specified, then for each apply process in the database, the total messages dequeued and the most recent scn dequeued by the apply reader, the dequeue latency, the total number of transactions received, assigned and applied by the coordinator are displayed. The apply high water mark scn along with the apply high water mark latency is also shown. This statistic records the most recent scn from the source site that has been applied at the destination site.
AR
This subcomponent lists the percentage of idle events, flow control events, and the single most significant wait event for the apply reader process if the database version is 10gR1 or higher.
AS(n)
This subcomponent lists the percentage of idle events, flow control events, and the single most significant wait event for the apply server process(es) if the database version is 10gR1 or higher. When apply parallelism is enabled, the values reflect the sum of all associated apply servers in each category: idle, flow control, and wait event.
Flags
Flow Control <F>
This indicates that the capture process is blocked due to flow control.
Potential Bottlenecks <B>
This indicates that the capture or apply process is currently a bottleneck.
<x%I x%F x%xx>
When displayed for a component, x%I indicates the percentage(x%) of idle(I) time for the component. x%F indicates the percentage(x%) of time waiting on flow control events(F). The event with the highest percent (x%) of time waited is indicated(xx)the percentage of time(x%) spent waiting on t<other wait event percentage and name>
Instance <xx>
This indicates the name of the database instance for which the data follows.
2. metalink还提供了一个stream环境的check脚本, Health Check Script 详情请参看ID 273674.1 脚本可以在metalink下载，这个脚本可以生成一个html的报表，报表包含了stream环境的性能数据。
################################################################
第三部分 stream环境归档日志的管理
对于stream复制环境的源数据库一端，对归档日志的清理需要注意一些问题，不是所有的归档都可以随意删除，如果误删了capture进程还需要读取的归档日志就会出现capture虽然能正常启动 status也是enable状态，
但是数据缺无法复制。这里需要注意一个capture进程的参数REQUIRED_CHECKPOINT_SCN这个参数表示capture进程重新启动时需要scan的最小scn号，可以通过这个参数找到需要为capture进程保留的归档日志。这里还需
要讲的一个参数是capture进程的 _CHEKPOINT_FREQUENCY参数这个参数的表示logminer做一次checkpoint需要挖掘的日志大小，这个参数的单位是 M，官方建议设置为500M，也就是说当logminer处理了500M大小的
redo的时候会做一次logminer的 checkpoint，checkpoint之后REQUIRED_CHECKPOINT_SCN被更新，所以通过设 _CHEKPOINT_FREQUENCY的大小，可以控制需要保留的归档的大小，可以使用 dbms_capture_adm.set_parameter过程
修改_CHEKPOINT_FREQUENCY参数
下边的查询可以查出capture如果重启所需要读取的redo
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;
###############################################################################
第四部分 stream环境的故障诊断
下面介绍了在apply过程中出现错误，打印出详细错误信息的方法
比如在LCR应用过程中出现错误：
select apply_name,LOCAL_TRANSACTION_ID,SOURCE_TRANSACTION_ID,ERROR_MESSAGE
from dba_apply_error;
APPLY_NAME LOCAL_TRANSACTION_ID SOURCE_TRANSACTION_ID ERROR_MESSAGE
----------- ---------------------- ---------------------- -------------------------
APP97_APPLY 5.27.1273 4.46.576 ORA-01403: no data found
对复制管理员进行授权：
SQL> GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin;
Grant succeeded
SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
Grant succeeded
现在就可以使用print_errors来打印出详细的错误信息，但是注意，如果错误事务非常多，那么这个过程可能会非常耗时：
SET SERVEROUTPUT ON SIZE 1000000
EXEC print_errors
*************************************************
----- ERROR #1
----- Local Transaction ID: 5.27.1273
----- Source Database: TEST201.EYGLE.COM
----Error in Message: 1
----Error Number: 1403
----Message Text: ORA-01403: no data found
--message: 1
type name: SYS.LCR$_ROW_RECORD
source database: TEST201.EYGLE.COM
owner: SCOTT
object: DEPT
is tag null: Y
command_type: UPDATE
old(1): DEPTNO
50
old(2): LOC
CHINA
new(1): LOC
CHINA
PL/SQL procedure successfully completed
最后创建一个print_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 (128);
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;
/
现在来看看这个失败的事务：
SET SERVEROUTPUT ON SIZE 1000000
EXEC print_transaction('5.27.1273')
----- Local Transaction ID: 5.27.1273
----- Source Database: TEST201.EYGLE.COM
----Error in Message: 1
----Error Number: 1403
----Message Text: ORA-01403: no data found
--message: 1
type name: SYS.LCR$_ROW_RECORD
source database: TEST201.EYGLE.COM
owner: SCOTT
object: DEPT
is tag null: Y
command_type: UPDATE
old(1): DEPTNO
50
old(2): LOC
CHINA
new(1): LOC
CHINA
PL/SQL procedure successfully completed
这几个过程在流复制的故障诊断中非常有用，记录于此。
145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
