Oracle Streams errors

Encountered Streams errors

A non exhaustive list of Oracle streams errors I encountered when performing my testing…

ORA-01341

If you get “Capture process aborts with error : ORA-01341: LogMiner out-of-memory”, then issue:

EXEC dbms_capture_adm.set_parameter(capture_name => 'CAPTURE_STREAM', parameter => '_SGA_SIZE', VALUE => '20');
 
EXEC dbms_capture_adm.start_capture(capture_name => 'CAPTURE_STREAM');

The above changes space for named capture process : ‘STRMADMIN_CAPTURE’ from 10M -> 20M (increase further only if you still get the error). Can also be done with Grid Control

Capture forever in DICTIONARY INITIALIZATION state

You can see it with Grid Control or (DBA_CAPTURE does not provide this information):

SELECT * FROM v$streams_capture;
SQL> SELECT TO_CHAR(CURRENT_SCN) FROM v$database;
 
TO_CHAR(CURRENT_SCN)
----------------------------------------
8921108567059
 
SQL> SELECT TO_CHAR(dbms_flashback.get_system_change_number) FROM dual;
 
TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE
----------------------------------------
8921108567113

Most probably is it because the needed archive log file to restart the capture process has been deleted from disk. As explained in Metalink note (313279.1 and 843267.1) there is NO OPTION to recover this situation than recreating from scratch the whole Streams process !!!.

So better to use script provided in Metalink note 290143.1 to know which archived log files you can delete or not.

Remark:

delete noprompt archivelog all;

RMAN command DOES NOT take this into account !!).

MISSING Streams multi-version data dictionary!!!

If you see in alert log file of your database:

knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=DB1.WORLD
knlldmm: objn=211550
knlldmm: objv=1
knlldmm: scn=8921144706068

And if by issuing:

SELECT * FROM dba_objects WHERE object_id=211550;

You get nothing then see Capture / Knlc_processmvdd-2: Missing Streams Multi-Version Data Dictionary in Alert.log file [ID 760875.1], you are probably hitting bug 7605621. Unless go and correct the problem using Resolving the MISSING Streams Multi-version Data Dictionary Error [ID 212044.1]

On the other hand if you get:

Mon Apr 26 15:59:46 2010
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=DB1.WORLD
knlldmm: objn=211610
knlldmm: objv=1
knlldmm: scn=8921183090873
See trace file for more information

then, as explained in alert.log you will see, generated at the same time, a trace file that contains what to do. At the same time you surely encounter issue with replication on one of your objects:

server1{root}# more /ora_db2/dump/db2/db2_ora_9410.trc
/ora_db2/dump/db2/db2_ora_9410.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /ora_db2/software
System name:    HP-UX
Node name:      server1
Release:        B.11.23
Version:        U
Machine:        ia64
Instance name: db2
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 9410, image: oracle@server1
 
*** ACTION NAME:() 2010-04-26 15:59:46.327
*** MODULE NAME:(oracle@server2 (TNS V1-V3)) 2010-04-26 15:59:46.327
*** SERVICE NAME:(SYS$USERS) 2010-04-26 15:59:46.327
*** SESSION ID:(172.671) 2010-04-26 15:59:46.327
*** 2010-04-26 15:59:46.327
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=DB1.WORLD
knlldmm: objn=211610
knlldmm: objv=1
knlldmm: scn=8921183090873
-------------
 The previous warning indicates that required information for this Logical
 Change Record is not available in the Streams dictionary.
 
 This typically occurs because of one of the following reasons:
 * The table was not properly prepared using either
   DBMS_STREAMS_ADM.ADD_%_RULES or
   DBMS_CAPTURE_ADM.PREPARE_%_INSTANTIATION.
 * The information to update the Streams dictionary was filtered out by
   the rules of a propagation or apply process.  This is most likely due
   to the improper ordering of adding rules.
 * The Streams dictionary tablespace has been reset by running
   DBMS_LOGMINER_D.SET_TABLESPACE after Streams was configured.
 
 To begin diagnosing this problem, on the database specified by 'gdbnm'

Solve it with (on source database):

db1> SELECT * FROM dba_objects WHERE object_id=211610;
 
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED              LAST_DDL_TIME
------------------------------ ---------- -------------- ------------------- -------------------- --------------------
TIMESTAMP           STATUS  T G S
------------------- ------- - - -
STRMADMIN
TEST1
                                   211610         211610 TABLE               21-apr-2010 18:07:49 26-apr-2010 16:03:24
2010-04-21:18:07:49 VALID   N N N
db1> EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name=>'strmadmin.test1', supplemental_logging=>'keys');
 
PL/SQL PROCEDURE successfully completed.

Propagation disabled after 16 consecutive errors

Propagation Schedule for (STRMADMIN.STREAMS_QUEUE, DB2.WORLD) disabled due to 16 consecutive errors.

This is expected behavior (to re-phrase Oracle 😉 ) and links to Oracle scheduler (DBA_JOBS), if after 16 retry of the propagation job and not been able to propagate to target database then it falls down with error (standard number of times Oracle is trying to restart a job before setting it to broken).

Capture process in Paused For Flow Control

SQL> SELECT CAPTURE_NAME, STATE FROM V$STREAMS_CAPTURE;
 
CAPTURE_NAME                   STATE
------------------------------ -------------------------------------------------------------------------
CAPTURE_STREAM                 PAUSED FOR FLOW CONTROL
 
SQL> SELECT PROPAGATION_NAME,ERROR_MESSAGE FROM dba_propagation;
 
PROPAGATION_NAME               ERROR_MESSAGE
------------------------------ ------------------------------------------------------------
DB1_TO_DB2                     ORA-25307: Enqueue rate too high, flow control enabled
 
SQL> SELECT queue_schema, queue_name, unbrowsed_msgs, overspilled_msgs, memory_usage, publisher_state
FROM V$BUFFERED_PUBLISHERS;
 
QUEUE_SCHEMA                   QUEUE_NAME                     UNBROWSED_MSGS OVERSPILLED_MSGS MEMORY_USAGE
------------------------------ ------------------------------ -------------- ---------------- ------------
PUBLISHER_STATE
-----------------------------------------------------------
STRMADMIN                      STREAMS_QUEUE                             819              819           66
IN FLOW CONTROL: INSUFFICIENT MEMORY AND UNBROWSED MESSAGES

Then if you go on target database the apply process is disabled:

SQL> SELECT APPLY_NAME,STATUS FROM dba_apply;
 
APPLY_NAME                     STATUS
------------------------------ --------
APPLY_STREAM                   ABORTED

then looking further in alert.log of target database:

ORA-1688: unable to extend table SYS.STREAMS$_APPLY_SPILL_MSGS_PART partition P1 by 512 in                 tablespace SYSAUX
Streams Apply Reader P000 pid=13 OS id=23041 stopped
Thu Apr 22 13:06:16 2010
Errors in file /ora_db2/dump/db2/db2_p000_23041.trc:
ORA-01688: unable to extend table SYS.STREAMS$_APPLY_SPILL_MSGS_PART partition P1 by 512 in tablespace SYSAUX
Streams Apply Server P001 pid=14 OS id=23043 stopped
Thu Apr 22 13:06:16 2010
Errors in file /ora_db2/dump/db2/db2_p001_23043.trc:
ORA-10388: parallel query server interrupt (failure)
Thu Apr 22 13:06:18 2010
Streams APPLY A001 with pid=42, OS id=17196 stopped
Thu Apr 22 13:06:18 2010
Errors in file /ora_db2/dump/db2/db2_a001_17196.trc:
ORA-12801: error signaled in parallel query server P000
ORA-01688: unable to extend table SYS.STREAMS$_APPLY_SPILL_MSGS_PART partition P1 by 512 in tablespace SYSAUX

So increase SYSAUX (or whatever Streams tablespace you have chosen) and restart apply process. Every transaction that have more than TXN_LCR_SPILL_THRESHOLD (10,000 be default in 10.2.0.4) messages are automatically spilled on disk so you may need big Streams tablespace to store STREAMS$_APPLY_SPILL_MSGS_PART table.

You can also message the Streams latency with:

SELECT apply_name,
(apply_time-applied_message_create_time)*86400 "latency_in_seconds",
(sysdate-apply_time)*86400"latency_in_seconds",
TO_CHAR(applied_message_create_time,'hh24:mi:ss mm/dd/yy') "event_creation",
TO_CHAR(apply_time,'hh24:mi:ss mm/dd/yy') "apply_time"
FROM dba_apply_progress;
 
APPLY_NAME                     latency_in_seconds latency_in_seconds event_creation    apply_time
------------------------------ ------------------ ------------------ ----------------- -----------------
APPLY_STREAM                                   34              16349 11:48:05 04/23/10 11:48:39 04/23/10
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_NAME                   LATENCY_SECONDS LAST_STATUS CAPTURE_TIME      CREATE_TIME
------------------------------ --------------- ----------- ----------------- -----------------
CAPTURE_STREAM                           60903           0 11:15:39 04/23/10 18:20:36 04/22/10

DBSNMP sessions consuming lots of CPU on monitored server

If you see many sessions consuming lots of CPU on server consuming CPU do not hesitate to apply latest Grid Control PSU (10.2.0.5.3). Solved my problem…

References

  • Capture / Knlc_processmvdd-2: Missing Streams Multi-Version Data Dictionary in Alert.log file [ID 760875.1]
  • Resolving the MISSING Streams Multi-version Data Dictionary Error [ID 212044.1]
  • Troubleshooting Streams Capture when status is Paused For Flow Control [ID 746247.1]
This entry was posted in Oracle and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>