streams 日差管理及監控，streams日差監控
第一部分 stream環境的日常管理
1.capture進程管理
--capture進程信息
SET LINESIZE 200
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
COLUMN ERROR_MESSAGE HEADING 'Capture|Process|E_MESSAGE' FORMAT A20
SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME, STATUS,ERROR_NUMBER,ERROR_MESSAGE
FROM DBA_CAPTURE;
--顯示capture進程的統計信息
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 A27
COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo|Entries|Evaluated|In Detail' FORMAT 9999999
COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' FORMAT 999999
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形成的時間
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性能查看
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;
--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;
2.propagation進程管理
--buffer_queues信息
COLUMN QUEUE_SCHEMA HEADING 'Queue Owner' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15
COLUMN MEM_MSG HEADING 'Messages|in Memory' FORMAT 99999999
COLUMN SPILL_MSGS HEADING 'Messages|Spilled' FORMAT 99999999
COLUMN NUM_MSGS HEADING 'Total Messages|in Buffered Queue' FORMAT 99999999
SELECT QUEUE_SCHEMA,
QUEUE_NAME,
(NUM_MSGS - SPILL_MSGS) MEM_MSG,
SPILL_MSGS,
NUM_MSGS
FROM V$BUFFERED_QUEUES;
--顯示各個propagation的基本信息
COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A17
COLUMN SUBSCRIBER_ADDRESS HEADING 'Destination|Database' FORMAT A11
COLUMN CURRENT_ENQ_SEQ HEADING 'Current|Enqueued|Sequence' FORMAT 99999999
COLUMN LAST_BROWSED_SEQ HEADING 'Last|Browsed|Sequence' FORMAT 99999999999999
COLUMN LAST_DEQUEUED_SEQ HEADING 'Last|Dequeued|Sequence' FORMAT 99999999999999
COLUMN NUM_MSGS HEADING 'Number of|Messages|in Queue|(Current)' FORMAT 999999999999
COLUMN TOTAL_SPILLED_MSG HEADING 'Number of|Spilled|Messages|(Cumulative)' FORMAT 9999999999999
set linesize 200
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進程
--apply進程基本信息
COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN APPLY_CAPTURED HEADING 'Type of Messages Applied' FORMAT A25
COLUMN APPLY_USER HEADING 'Apply User' FORMAT A30
SELECT APPLY_NAME,
DECODE(APPLY_CAPTURED,
'YES', 'Captured',
'NO', 'User-Enqueued') APPLY_CAPTURED,
APPLY_USER
FROM DBA_APPLY;
--apply參數設置信息
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN PARAMETER HEADING 'Parameter' FORMAT A25
COLUMN VALUE HEADING 'Value' FORMAT A20
COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A15
SELECT APPLY_NAME,
PARAMETER,
VALUE,
SET_BY_USER
FROM DBA_APPLY_PARAMETERS;
--reader server信息
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN APPLY_CAPTURED HEADING 'Apply Type' FORMAT A22
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,
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延時
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 9999
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 999999
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;
--查看capture的參數
SET LINESIZE 200
COLUMN CAPUTRE_NAME HEADING 'Apply Process|Name' FORMAT A15
COLUMN PARAMETER HEADING 'Parameter' FORMAT A25
COLUMN VALUE HEADING 'Value' FORMAT A20
COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A15
SELECT CAPTURE_NAME,
PARAMETER,
VALUE,
SET_BY_USER
FROM DBA_CAPTURE_PARAMETERS;
4.清除stream配置
首先停止capture propagation apply進程
exec dbms_streams_adm.remove_streams_configuration;
##################################################################################
第二部分
stream監控工具
1.STRMMON是一個監控stream運行狀態的一個小工具，具體參看metalink文檔ID 290605.1
STRMMON只支持10g R2或者更高的版本，工具可以在metalink上下載
% strmmon -interval 3 -count 5 -sysdba
STREAMS Monitor, v 2.5 Copyright Oracle Corp. 2002, 2005.
Interval = 3, Count=5
Logon= @ ORACLE 10.2.0.2.0
Streams Pool Size = 152M
LOG :
NET:
Cxxx:
MEM : %
PRxx:
Qx :
PSxx:
Axxx:
: flow control in effect
: potential bottleneck
AR: apply reader
AS(n): n number of apply server
:
xx->: database instance name
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，ORACLE官方建議設置為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
此後需要建立幾個過程
SQL> connect strmadmin/strmadmin
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as strmadmin
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_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_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;
/
現在就可以使用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
這幾個過程在流復制的故障診斷中非常有用，記錄於此。
