Home » Server Options » Streams & AQ » Oracle Streams 11g , not working (Oracle Linux 5.4 , Oracle 11g )
Oracle Streams 11g , not working [message #562874] Mon, 06 August 2012 08:50 Go to next message
RAY_HT
Messages: 150
Registered: May 2005
Location: Giza
Senior Member

Dears ,

i'm trying to set up streams between 2 databases , all parameters set on SRC database and then i execute wizard of stream from SRC enterprise manager , it's done successfully and submit the job , but finally i got error , here is the script log (Note: DB link is working well) , please help

SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 6 17:00:17 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> SQL> SQL> Executing ..
SQL> connect STREAMS_ADM/******
SQL> Connected.
SQL> SQL> Executing ..
SQL> DROP DATABASE LINK ORCL
SQL>
Database link dropped.

SQL> 1* DROP DATABASE LINK ORCL
SQL> SQL> Executing ..
SQL> CREATE DATABASE LINK ORCL connect to STREAMS_ADM identified by ****** using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=stream.localdomain)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)(server=DEDICATED))) '
SQL>
Database link created.

SQL> Executing ..
SQL> COMMIT
SQL>
Commit complete.

SQL> Executing ..
SQL> connect STREAMS_ADM/******@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=stream.localdomain)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)( server=DEDICATED)))'
SQL> Connected.
SQL> SQL> Executing ..
SQL> DROP DATABASE LINK SRC
SQL>
Database link dropped.

SQL> SQL> Executing ..
SQL> CREATE DATABASE LINK SRC connect to STREAMS_ADM identified by ****** using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=master.localdomain)(PORT=1521)))(CONNECT_DATA=(SID=src)(server=DEDICATED)))'
SQL>
Database link created.

SQL> Executing ..
SQL> COMMIT
SQL>
Commit complete.

SQL> Executing ..
SQL> connect STREAMS_ADM/******
SQL> Connected.
SQL> SQL> Executing ..
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> SQL> 1* COMMIT
SQL> Executing ..
SQL> DECLARE
SQL> schemas DBMS_UTILITY.UNCL_ARRAY
SQL> BEGIN
SQL> schemas(1) := '"DF"'
SQL> schemas(2) := '"DMS"'
SQL> schemas(3) := '"NMIS"'
SQL> schemas(4) := '"SA2"'
SQL> schemas(5) := '"SCOTT"'
SQL> DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
SQL> schema_names => schemas,
SQL> source_directory_object => 'ORA_EM_STRM_DPUMP_10205',
SQL> destination_directory_object => 'ORA_EM_STRM_DPUMP_10205',
SQL> source_database => 'SRC',
SQL> destination_database => 'ORCL',
SQL> perform_actions => TRUE,
SQL> script_name => 'streams_setup_2012_8_6_16_58_53_966.sql',
SQL> script_directory_object => 'ORA_EM_STRM_DPUMP_10205',
SQL> dump_file_name => 'streams_setup_2012_8_6_16_58_53_966.dmp',
SQL> capture_name => 'SRC$CAP',
SQL> capture_queue_table => 'SRC$CAP_QT',
SQL> capture_queue_name => 'SRC$CAP_Q',
SQL> capture_queue_user => 'STREAMS_ADM',
SQL> propagation_name => 'PROPAGATION$_1',
SQL> apply_name => 'APPLY$_SRC_1',
SQL> apply_queue_table => 'STREAMS_ADM.APPLY$_SRC_1_QT',
SQL> apply_queue_name => 'STREAMS_ADM.APPLY$_SRC_1_Q',
SQL> apply_queue_user => 'STREAMS_ADM',
SQL> log_file => 'streams_setup_2012_8_6_16_58_53_966.log',
SQL> bi_directional => false,
SQL> include_ddl => true)
SQL> END
SQL> /
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 DECLARE
*
ERROR at line 1:
ORA-23616: Failure in executing block 14 for script
C69A4C4212032B38E0430100007F85EC with
ORA-04052: error occurred when looking up remote object
STREAMS_ADM.V$PARAMETER@SRC
ORA-00604: error occurred at recursive SQL level 2
ORA-12170: TNS:Connect timeout occurred
ORA-06512: at "SYS.DBMS_R
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 8002
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2706
ORA-06512: at line 9
Re: Oracle Streams 11g , not working [message #562885 is a reply to message #562874] Mon, 06 August 2012 11:42 Go to previous messageGo to next message
Littlefoot
Messages: 19608
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Note: I don't know Streams.

RAY_HT wrote on Mon, 06 August 2012 15:50
Note: DB link is working well

ORA-04052: error occurred when looking up remote object
STREAMS_ADM.V$PARAMETER@SRC

It seems that SRC isn't accessible as you thought it was. What did you mean by the "note"? How did you verify that it is working well? The fact that a database link was created doesn't mean that it is CORRECTLY created. Have a look: I'll create a dummy database link which looks into "nothing" and then try to select from a dual over that database link:
SQL> create database link src
  2    connect to blabla
  3    identified by lafjlksjf
  4    using 'ora10';

Database link created.

SQL> select * from dual@src;
select * from dual@src
                   *
ERROR at line 1:
ORA-12545: Connect failed because target host or object does not exist


SQL>

As I said: I don't know Streams and error you got isn't really ORA-12545, but - perhaps you should verify SRC really works.
Re: Oracle Streams 11g , not working [message #562892 is a reply to message #562874] Mon, 06 August 2012 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-12170: TNS:Connect timeout occurred


Usually occurs when a firewall is in place.

Regards
Michel
Re: Oracle Streams 11g , not working [message #562898 is a reply to message #562874] Mon, 06 August 2012 12:22 Go to previous message
John Watson
Messages: 4557
Registered: January 2010
Location: Global Village
Senior Member
Have you given your streams administrator enough privileges? He does need the DBA role.
Previous Topic: AQ messages are in ready state - Not dequeued
Next Topic: Streams propagate error
Goto Forum:
  


Current Time: Wed Sep 17 17:31:49 CDT 2014

Total time taken to generate the page: 0.12482 seconds