5 Replies Latest reply: Sep 12, 2012 6:14 AM by Balazs Papp RSS

    Stream setup issue

    889124
      I'm trying to set up Streams between to identical Db's (but with different names). These databases are situtated on different servers. Now, I'm setting up Streams via Enterprise manager but ever time the job runs, I'm getting this error message.

      Any idea?

      SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 12 09:51:35 2012

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

      SQL> SQL> SQL> Executing ..
      SQL> connect STREAM/******
      SQL> Connected.
      SQL> SQL> Executing ..
      SQL> DROP DATABASE LINK IFSPROD
      SQL>
      Database link dropped.

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

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

      SQL> Executing ..
      SQL> connect STREAM/******@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=******)(PORT=1521)))(CONNECT_DATA=(SID=SPROD)(server=DEDICATED)))'
      SQL> Connected.
      SQL> SQL> Executing ..
      SQL> DROP DATABASE LINK TEST1
      SQL>
      Database link dropped.

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

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

      SQL> Executing ..
      SQL> connect STREAM/******
      SQL> Connected.
      SQL> SQL> Executing ..
      SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
      SQL> SQL> 1* COMMIT
      SQL> Executing ..
      SQL> BEGIN
      SQL> DBMS_STREAMS_ADM.MAINTAIN_GLOBAL(
      SQL> source_directory_object      =>     'DATA_PUMP_DIR',
      SQL> destination_directory_object     =>     'DATA_PUMP_DIR',
      SQL> source_database           =>     'TEST1',
      SQL> destination_database          =>     'PROD',
      SQL> perform_actions           => TRUE,
      SQL> script_name                         =>     'streams_setup_2012_9_12_9_50_17_760.sql',
      SQL> script_directory_object      => 'DATA_PUMP_DIR',
      SQL> dump_file_name               => 'streams_setup_2012_9_12_9_49_45_62.dmp',
      SQL> capture_queue_user               =>     'STREAM',
      SQL> apply_queue_user          => 'STREAM',
      SQL> log_file                    =>     'streams_setup_2012_9_12_9_49_45_62.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 ==========================================
      Fnd_Session_Util_API.Set_Fnd_User_
      ==========================================
      Foundation User is set to STREAM.
      BEGIN
      *
      ERROR at line 1:
      ORA-29283: invalid file operation
      ORA-06512: at "SYS.UTL_FILE", line 41
      ORA-06512: at "SYS.UTL_FILE", line 512
      ORA-29283: invalid file operation
      ORA-06512: at "SYS.DBMS_STREAMS_MT", line 398
      ORA-06512: at "SYS.DBMS_STREAMS_MT", line 7443
      ORA-06512: at "SYS.DBMS_STREAMS_MT", line 7584
      ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2818
      ORA-06512: at line 2


      Disconnected from Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

      Error from SQLPLUS: 99
        • 1. Re: Stream setup issue
          889124
          Actually, it seems I've got a bit further now.. Now I get this?

          SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 12 09:59:56 2012

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

          SQL> SQL> SQL> Executing ..
          SQL> connect STREAM/******
          SQL> Connected.
          SQL> SQL> Executing ..
          SQL> DROP DATABASE LINK IFSPROD
          SQL>
          Database link dropped.

          SQL> 1* DROP DATABASE LINK IFSPROD
          SQL> SQL> Executing ..
          SQL> CREATE DATABASE LINK IFSPROD connect to STREAM identified by ****** using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ifs-db.ccivalve.com)(PORT=1521)))(CONNECT_DATA=(SID=IFSPROD)(server=DEDICATED)))'
          SQL>
          Database link created.

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

          SQL> Executing ..
          SQL> connect STREAM/******@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ifs-db.ccivalve.com)(PORT=1521)))(CONNECT_DATA=(SID=IFSPROD)(server=DEDICATED)))'
          SQL> Connected.
          SQL> SQL> Executing ..
          SQL> DROP DATABASE LINK IFSTEST1
          SQL>
          Database link dropped.

          SQL> SQL> Executing ..
          SQL> CREATE DATABASE LINK IFSTEST1 connect to STREAM identified by ****** using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=IFS-DBTEST-BRNO.ccivalve.com)(PORT=1521)))(CONNECT_DATA=(SID=IFSTEST1)(server=DEDICATED)))'
          SQL>
          Database link created.

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

          SQL> Executing ..
          SQL> connect STREAM/******
          SQL> Connected.
          SQL> SQL> Executing ..
          SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
          SQL> SQL> 1* COMMIT
          SQL> Executing ..
          SQL> BEGIN
          SQL> DBMS_STREAMS_ADM.MAINTAIN_GLOBAL(
          SQL> source_directory_object      =>     'ORA_EM_STRM_DPUMP_10205',
          SQL> destination_directory_object     =>     'ORA_EM_STRM_DPUMP_10205',
          SQL> source_database           =>     'IFSTEST1',
          SQL> destination_database          =>     'IFSPROD',
          SQL> perform_actions           => TRUE,
          SQL> script_name                         =>     'streams_setup_2012_9_12_9_59_42_543.sql',
          SQL> script_directory_object      => 'ORA_EM_STRM_DPUMP_10205',
          SQL> dump_file_name               => 'streams_setup_2012_9_12_9_59_42_543.dmp',
          SQL> capture_queue_user               =>     'STREAM',
          SQL> apply_queue_user          => 'STREAM',
          SQL> log_file                    =>     'streams_setup_2012_9_12_9_59_42_543.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 ==========================================
          Fnd_Session_Util_API.Set_Fnd_User_
          ==========================================
          Foundation User is set to STREAM.
          BEGIN
          *
          ERROR at line 1:
          ORA-23616: Failure in executing block 4 for script
          153CCC6F12A24D58A676316F61600AEC with
          ORA-00439: feature not enabled: Streams Capture
          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 7983
          ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2818
          ORA-06512: at line 2


          Disconnected from Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

          Error from SQLPLUS: 64
          • 2. Re: Stream setup issue
            Balazs Papp
            to be honest i met this kind of error several times under various circumstances
            you can find more info at DBA_RECOVERABLE_SCRIPT_ERRORS

            mostly it seemed unpredictable, say first i got the error, than in the next try it worked flawlessly
            what i did was just simply recover and start over which in nearly all cases solved the problem
            exec dbms_streams_adm.recover_operation('153CCC6F12A24D58A676316F61600AEC', 'ROLLBACK');
            of course you can try to continue by specifying 'FORWARD' instead of 'ROLLBACK'
            • 3. Re: Stream setup issue
              Balazs Papp
              ok, at first i slipped through this:
              ORA-00439: feature not enabled: Streams Capture
              are you on Standard Edition? there it is expected behaviour:

              *DBMS_STREAMS_ADM.MAINTAIN_TABLES fails with ORA-439 in Standard Edition [ID 1273084.1]*
              • 4. Re: Stream setup issue
                889124
                I think the problem is that I'm using Standard Edition, not enterprise. But I believe Streams can be still used but using synchronous capture?
                • 5. Re: Stream setup issue
                  Balazs Papp
                  Yes, as explain in the above note. Also:

                  http://docs.oracle.com/cd/E11882_01/license.112/e10594/editions.htm#BABDJGGI

                  Oracle Streams Y Y Y SE1/SE: no capture from redo