ORA-12514 in Oracle Streams

I recently got stuck on setting up streams (downstream approach) with the target database in a RAC environment (I'll detail the steps of doing this in a later blog entry).

Anyway, here's the error that was showing up on the downstream database --

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Propagation Schedule for (STRMDBA.STRM_CAPTURE_Q, "STRMDBA"."STRM_APPLY_Q"@PERG) disabled due to 16 consecutive errors.

Here's what I did to fix this error (also note that earlier my "global_name" was also not set correctly, so it is quite likely that the service name was initially picked from the wrong global_name entry) --

PERG1_SQL > show parameter service

NAME TYPE VALUE
------------------------------------ -------- ------------------------------
service_names string SYS$STRMDBA.STRM_APPLY_Q.PERG.REGRESS.RDBMS.DEV.US.ORACLE.COM, PERG

PERG1_SQL > alter system set service_names='SYS$STRMDBA.STRM_APPLY_Q.PERG, PERG';

System altered.

PERG1_SQL > show parameter service

NAME TYPE VALUE
------------------------------------ -------- ------------------------------
service_names string SYS$STRMDBA.STRM_APPLY_Q.PERG, PERG

The propagation was still giving errors (as it potentially recorded the wrong service_names entry in the dictionary), so I had to drop and recreate the propagation rules. After that, all went fine.