Table of contents
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]
