Streams Service Review

General problems in the Streams Environment

• Listener problems @destination sites (mainly Tier1 databases)
  • ORA-12514: TNS: listener does not currently know of service requested in connect descriptor
  • ORA-12520: TNS: listener could not find available handler for requested type of server
  • propagation job disabled after 16 consecutive errors -> Tier 1 administrators will receive an email from the monitoring tool
  • sqlplus connection may still be possible
  • actions for Tier 1 administrators:
    • check the “lsnrctl services” output
      • must show service related to the Streams queue: Service "SYS$STRMADMIN.STREAMS_QUEUE_ONL_AP.ATLR.CERN.CH" has 1 instance(s)
      • databases running LFC and LHCb Streams must have 2 services
    • fix the problem: listener configuration
    • re-start the propagation job using "strmprop" user (connect to the downstream database): exec dbms_propagation_adm.start_propagation('%propagation_name%');

• Running out of space in the archive log area
  • increase size shared storage
  • recommendation: use monitoring and alarms

• Network outage
  • send us an email with estimated resolution time

• User-defined types not supported by streams -> capture process aborted
  • use filter rules in order to skip those tables

• User error encountered while applying
  • run procedure ‘print_errors.sql’ shows details transactions in the error queue (strmadmin user)
  • causes: human errors, streams misconfiguration, ….
    • ORA-01403: no data found
    • ORA-00955: name is already used by an existing object
    • analyzed with the help of experiment’s administrators
  • other causes: database administration related
    • ORA-01652: unable to extend tablespace
    • ORA-00060: deadlock detected while waiting for resource
  • actions for Tier 1 administrators (only in case of database administration related cause):
    • fix the problem: increase tablespace size, eliminate deadlock, ....
    • re-apply transaction in the error queue (connect as strmadmin user): exec dbms_apply_adm.execute_all_errors('%apply_name%');
    • re-start apply process (connect as strmadmin user): exec dbms_apply_adm.start_apply('%apply_name%');

• ORA-12801, ORA-04039 when query gv$views
  • database is up but monitoring shows it down
  • adjust memory parameters

• knllgobjinfo: MISSING Streams multi-version data dictionary!!!
  • Warning messages reported when creating tables with constraints whether these are replicated or not.
  • As they are one-time only errors reported on table creation and they do not affect Streams replication they can be safely ignored.
  • Metalink note 471858.1

Known Streams bugs

List of the known bugs related to Oracle Streams, workarounds and fix patches.

In addition, check Metalink Note:437838.1 - Recommended Patch for Streams

Oracle version 10.2.0.4

• ORA-600 [KWQBMCRCPTS101] after dropping Propagation job
  • Apply patches: 7263055 and 7480651 (check patch post-installation steps)

• ORA-00600: [KWQPCBK179], memory leak from propagation job
  • Apply patch: 5933656

• ORA-26687 when “drop table” and 2 Streams setups between the same source and destination databases
  • Apply patch: 6452375

• *ORA-600: [KNLQDQM2USR:4], apply aborted, after installing 10.2.0.4 patchset
  • Apply patch: 7033630 (check patch post-installation steps)

Oracle version 10.2.0.3

• Capture process is aborted with error ORA-01280: Fatal LogMiner
  • capture process running out of memory
  • MLR for Streams and Logminer bugs 6081550
  • re-start capture process

• ORA-600 [KWQBMCRCPTS101] after dropping Propagation job
  • Streams queue is not usable => replication stops
  • workaround: remove the complete Streams setup and re-configure Streams without problematic propagation
  • fixed on 11g, fix patch available on 10.2.0.4

• ORA-00600: [KWQPCBK179], memory leak from propagation job
  • observed after applying the fix patch
  • new patch on top 10.2.0.4

• OEM agent blocks Streams processes
  • stop_propagation procedure hangs
  • fix patch 5330663

• CREATE VIEW ddl is replicated when run from a schema with select privilege on the replicated schema table
  • new BUG: 6641199
  • no fix patch available

• Bug recyclebin=on; after child table dropped: ORA-26687 on parent
  • Metalink note 412449.1
  • workaround: use “purge” option or disable constraints
  • fixed on 10.2.0.4 and 11g

• ORA-26687 when “drop table” and 2 Streams setups between the same source and destination databases
  • table is not removed @destination
  • workaround: define schema rules in the apply processes
  • fix patch available on top 10.2.0.4

• APPLY gets stuck: APPLY SERVER WAITING FOR EVENT
  • generic performance issue on RAC
  • apply processes stuck on different wait events like 'library cache lock' and 'cursor: pin S wait on X’, …
  • apply the fixes for the following bugs: 5964485, 5907779 and 5977546