Manage and monitor streams

Manage and monitor streams
Part 1 routine management of the stream environment 1. capture Process Management-capture Process information set linesize 200 COLUMN CAPTURE_NAME HEADING 'capture | Process | Name 'format A15COLUMN QUEUE_NAME HEADING 'capture | Process | Queue 'format A15COLUMN RULE_SET_NAME HEADING 'Positive | Rule Set 'format A15COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative | Rule Set 'format A15COLUMN status heading' Capture | Process | status' FORMAT A15COLUMN ERROR_MESSAGE HEADING 'captur E | Process | E_MESSAGE 'format A20SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME, STATUS, ERROR_NUMBER, ERROR_MESSAGEFROM DBA_CAPTURE; -- display the statistical information of the capture Process COLUMN CAPTURE_NAME HEADING 'capture | name' FORMAT A7COLUMN PROCESS_NAME HEADING 'capture | Process | Number 'format A7COLUMN sid heading 'session | id' FORMAT 9999 column serial # HEADING 'session | Serial | Number 'format 9999 COLUMN STATE H EADING 'state' FORMAT A27COLUMN 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_ENQUEUEDFROM V $ STREAMS_CAPTURE c, V $ SESSION sWHERE c. SID = s. SID AND C. SERIAL # = s. SERIAL #; -- view the cpture status and the Time when the last message is formed. COLUMN CAPTURE_NAME HEADING 'capture | name' FORMAT A15COLUMN state heading 'state' FORMAT A27COLUMN 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') CR EATE_MESSAGEFROM V $ STREAMS_CAPTURE; -- capture performance View COLUMN CAPTURE_NAME HEADING 'capture | name' FORMAT A15COLUMN comment 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' MAT 99999999.99 COLUMN ELAPSED_PAUSE_TIME HEADING 'elapsed | Pause | Time 'format 99999999.99 SELECT CAPTURE_NAME, (Cost/100) values, (ELAPSED_RULE_TIME/100) ELAPSED_RULE_TIME, (Cost/100) values, (ELAPSED_LCR_TIME/100) ELAPSED_LCR_TIME, (ELAPSED_PAUSE_TIME/100) ELAPSED_PAUSE_TIMEFROM V $ STREAMS_CAPTURE; -- redoCOLUMN CONSUMER_NAME HEADING required for the capture process to restart 'Capture | Process | name' FORMAT A15COLUMN SOURCE_DATABASE HEADING 'source | database' FORMAT A10COLUMN SEQUENCE # HEADING 'sequence | Number 'format 99999 COLUMN Name HEADING 'Required | Archived Redo Log | File NAME 'format A40SELECT r. CONSUMER_NAME, r. SOURCE_DATABASE, r. SEQUENCE #, r. NAMEFROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE cWHERE r. CONSUMER_NAME = c. CAPTURE_NAME android. NEXT_SCN> = c. REQUIRED_CHECKPOI NT_SCN; 2. propagation Process Management -- Queue information COLUMN QUEUE_SCHEMA HEADING 'queue owner' FORMAT A15COLUMN QUEUE_NAME HEADING 'queue name' FORMAT A15COLUMN 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, SP ILL_MSGS, NUM_MSGSFROM V $ BUFFERED_QUEUES; -- display the basic information of each propagation COLUMN PROPAGATION_NAME HEADING 'pagation' FORMAT A17COLUMN SUBSCRIBER_ADDRESS HEADING 'destination | database' FORMAT A11COLUMN CURRENT_ENQ_SEQ HEADING 'current | Enqueued | Sequence 'format 99999999 COLUMN comment 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) & apos; 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_MSGFROM DBA_PROPAGATION p, V $ BUFFERED_SUBSCRIBERS s, V $ BUFFERED_QUEUES qWHERE 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. manage the apply Process -- Basic information of the apply Process COLUMN APPLY_NAME HEADING 'apply Process name' FORMAT A20COLUMN APPLY_CAPTURED HEADING 'Type of Messages Applied 'format A25COLUMN APPLY_USER HEADING 'apply user' FORMAT A30SELECT APPLY_NAME, DECODE (APPLY_CAPTURED, 'yes', 'captured', 'No', 'user-enqueued') APPLY_CAPTURED, APPLY_USERFROM DBA_APPLY; -- apply PARAMETER setting information: COLUMN APPLY_NAME HEADING 'apply Process | name' FORMAT A15COLUMN Parameter HEADING 'parameter 'format A25COLUMN value heading 'value' FORMAT A20COLUMN SET_BY_USER HEADING 'set by User? 'Format A15SELECT APPLY_NAME, PARAMETER, VALUE, SET_BY_USERFROM DBA_APPLY_PARAMETERS; -- reader server Information COLUMN APPLY_NAME HEADING 'apply Process | name' FORMAT A15COLUMN APPLY_CAPTURED HEADING 'apply type' FORMAT A22COLUMN PROCESS_NAME HEADING 'process | name'format A7COLUMN state heading 'state' FORMAT A17COLUMN 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_DEQUEUEDFROM V $ STREAMS_APPLY_READER r, V $ SESSION s, DBA_APPLY apWHERE r. SID = s. SID android. SERIAL # = s. SERIAL # android. APPLY_NAME = ap. APPLY_NAME; --- view the apply latency COLUMN APPLY_NAME HEADING 'apply Process | name' FORMAT A17COLUMN Latency heading 'latency | in | Seconds 'format 9999 column creation heading 'message creation' FORMAT A17COLUMN LAST_DEQUEUE HEADING 'last Dequeue time' FORMAT A20COLUMN comment 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_T IME, 'hh24: MI: ss mm/DD/yy') LAST_DEQUEUE, DEQUEUED_MESSAGE_NUMBERFROM V $ STREAMS_APPLY_READER; -- view the capture parameter set linesize 200 COLUMN CAPUTRE_NAME HEADING 'apply Process | name' FORMAT A15COLUMN Parameter HEADING 'parameter 'format A25COLUMN value heading 'value' FORMAT A20COLUMN SET_BY_USER HEADING 'set by User? 'Format A15SELECT CAPTURE_NAME, PARAMETER, VALUE, SET_BY_USERFROM DBA_CAPTURE_PARAMETERS; 4. clear stream configuration first stop the capture propagation apply process exec dbms_streams_adm.remove_streams_configuration; ######################################## ######################################## # Part 2 stream monitoring tool 1. STRMMON is a small tool used to monitor the running status of stream. For details, see metalink Document ID 290605.1STRMMON. It only supports 10g R2 or a later version. The tool can download % strmmon-interval 3-coun on metalink. T 5-sysdbaSTREAMS Monitor, v 2.5 Copyright Oracle Corp. 2002,200 5. interval = 3, Count = 5 Logon = @ ORACLE 10.2.0.2.0Streams Pool Size = 152 MLOG: NET: Cxxx: MEM: % PRxx: Qx: PSxx: Axxx: flow control in effect: potential bottleneckAR: apply readerAS (n): n number of apply server: xx->: database instance name 2. metalink also provides a check Script for the stream environment. For details about the Health Check Script, see the ID 273674.1 Script which can be downloaded from metalink, This script can generate an html report that contains the performance data of the stream environment. ######################################## ####################### Part 3 Management of archived logs in the stream Environment, you need to pay attention to some issues when cleaning up archived logs. Not all archives can be deleted at will, if the archive log to be read by the capture process is deleted by mistake, the following error occurs: although the capture can be started normally, the status is also enable, but the data is missing and cannot be copied. Note that the REQUIRED_CHECKPOINT_SCN parameter of a capture process indicates the minimum scn number to be scanned when the capture process restarts. You can use this parameter to find the archive logs to be retained for the capture process. Here, we also need to talk about the _ CHEKPOINT_FREQUENCY parameter of the capture process. This parameter indicates the log size that logminer needs to mine for one checkpoint. The unit of this parameter is M, ORACLE officially recommends setting it to 500 M. That is to say, when logminer processes a redo with a size of M, it will perform a logminer checkpoint. After the checkpoint, REQUIRED_CHECKPOINT_SCN will be updated. Therefore, by setting the size of _ chekpoint_frequ, you can control the size of the archive to be retained, you can use dbms_capture_adm.set_parameter to modify the _ CHEKPOINT_FREQUENCY parameter to query the redoCOLUMN CONSUMER_NAME HEADING 'capture | Process | name'format A15CO LUMN SOURCE_DATABASE HEADING 'source | database' FORMAT A10COLUMN SEQUENCE # HEADING 'sequence | Number 'format 99999 column name heading 'Required | Archived Redo Log | File Name 'format A40SELECT r. CONSUMER_NAME, r. SOURCE_DATABASE, r. SEQUENCE #, r. NAMEFROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE cWHERE r. CONSUMER_NAME = c. CAPTURE_NAME android. NEXT_SCN> = c. REQUIRED_CHECKPOINT_SCN ;############################ ######################################## ########## Part 4 fault diagnosis in the stream environment the following section describes errors that occur during the apply process, method for printing detailed error information, for example, when an error occurs during the LCR application: select apply_name, LOCAL_TRANSACTION_ID, failed, Zookeeper; APPLY_NAME LOCAL_TRANSACTION_ID invalid ERROR_MESSAGE ----------- invalid parameter limit APP97_APPLY 5.27.1273 4.46.576 ORA-0140 3: no data found: SQL> GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin; Grant succeededSQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin; grant succeeded then several processes need to be established SQL> connect strmadmin/strmadminConnected 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 VARCH AR2 (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't Export 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) N 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; ex T_attr: = ddllcr. get_extra_attribute ('username'); IF (ext_attr is not null) THEN DBMS_OUTPUT.put_line ('username: '| begin (); 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 ). D ATA); 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 _ I D: '| 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 ('thre Ad # '); 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: '| begin (); 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 statement is cursor c is select local_transaction_id, source_database, message_number, message_count, error_number, error_message FROM limit order by source_database, limit; I NUMBER; txnid VARCHAR2 (30 ); SOURCE VARCH AR2 (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 print_errors; /Now you can use print_errors to print the detailed error information. However, note that if there are many error transactions, this process may take a lot of time: 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_RECORDsource database: TEST201.EYGLE. COMowner: SCOTTobject: DEPTis tag null: Ycommand_type: UPDATEold (1): DEPTNO50old (2): LOCCHINAnew (1 ): LOCCHINAPL/SQL procedure successfully completed the last print_transaction process can be used to print the output details of the specified 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; identifier ('----- 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 print_lcr (LCR); END L OOP; END print_transaction;/now let's take a look at the failed 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_RECORDsource database: TEST201.EYGLE. COMowner: SCOTTobject: DEPTis tag null: Ycommand_type: UPDATEold (1): DEPTNO50old (2): LOCCHINAnew (1): LOCCHINAPL/SQL procedure successfully completed these processes are very useful in the fault diagnosis of stream replication and recorded here.