ITPub博客

首页 > 数据库 > Oracle > stream环境的日常管理

stream环境的日常管理

原创 Oracle 作者:531968912 时间:2016-01-12 15:11:10 0 删除 编辑
第一部分  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 : <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
 
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
这几个过程在流复制的故障诊断中非常有用,记录于此。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25462274/viewspace-1975338/,如需转载,请注明出处,否则将追究法律责任。

上一篇: SYSAUX 说明
下一篇: streams 总结
请登录后发表评论 登录
全部评论

注册时间:2014-09-24

  • 博文量
    574
  • 访问量
    877263