This discussion is archived
12 Replies Latest reply: Oct 9, 2012 6:54 AM by user476243 RSS

Oracle Streams Newbie

889124 Newbie
Currently Being Moderated
Right I've create two test db's (Oracle 11gR2) and set up streams between them. This is working correctly as when I set up a user in one of the databases its replicated to the other one.

The problem I have is when I issue a DDL statement. As an example, if I create a table in DB1, Streams tries to replicate the table under DB2 placing the datafile (that will make up this table) in the same location as the one thats create under DB1.

So, what I mean is when I create a table under DB1, the SQL is

CREATE SMALL TABLEPACE ********* 'C:\APP\ORACLEDATA\STREAM1\TEST1' ******

But when it replicates, streams uses the same SQL as above for second database, where it should be doing ...

CREATE SMALL TABLEPACE ********* 'C:\APP\ORACLEDATA\STREAM2\TEST1' ******


When this happens, its errors and aborts the stream service on the DB2 database.

How can I resolve this?
  • 1. Re: Oracle Streams Newbie
    KuljeetPalSingh Guru
    Currently Being Moderated
    its errors and aborts the stream service on the DB2 database.
    post error message

    also check
    http://docs.oracle.com/cd/E11882_01/server.112/e10705/config_simple.htm#CIAJGGBA
  • 2. Re: Oracle Streams Newbie
    889124 Newbie
    Currently Being Moderated
    Hi,

    Right so I've created a test table of STREAM1. I've then logged into the STREAM2 db and via enterprise manager, I've gone into the Manage Replication and noticed the 'Apply' area has a red cross by it.

    This takes me to the APPLY$ STEAM1 3 area and the status is down (State ABORTED). The LCR error states

    ORA-01119: error in creating database file 'C:\APP\ORADATA\STREAM1\test' ORA-27038: created file already exists OSD-04010:<create> option specified, file already exists.

    Which is right, as it does already exisits :). But I wanted it to create the test table under STREAM2?
  • 3. Re: Oracle Streams Newbie
    Balazs Papp Expert
    Currently Being Moderated
    a) use OMF instead of specifying datafile names

    or

    b) set up a custom DDL handler
  • 4. Re: Oracle Streams Newbie
    889124 Newbie
    Currently Being Moderated
    Hi, thanks. OMF isn't an option. But how can I create a custom DDL handler?

    Thanks for any assistance
  • 5. Re: Oracle Streams Newbie
    Balazs Papp Expert
    Currently Being Moderated
    lets say there are 2 databases: ALPHA (source) and BETA (destination)
    datafiles are stored at /oracle/base/oradata/ALPHA and /oracle/base/oradata/BETA

    the below simple (and stupid) ddl handler does nothing else, then replacing ALPHA with BETA in the instruction issued on source site
    CREATE OR REPLACE PROCEDURE datafile_rename(in_any IN SYS.ANYDATA)
    IS
    lcr       SYS.LCR$_DDL_RECORD;
    rc        PLS_INTEGER;
    ddl_text  CLOB;
    BEGIN
    
    rc := in_any.GETOBJECT(lcr);
    DBMS_LOB.CREATETEMPORARY(ddl_text, true);
    lcr.GET_DDL_TEXT(ddl_text);
    
    lcr.set_ddl_text(RPAD(replace(ddl_text, 'ALPHA', 'BETA'), 500));
    
    lcr.EXECUTE(); -- this line was missing
    
    DBMS_LOB.FREETEMPORARY(ddl_text);
    END;
    /
    
    begin
    DBMS_APPLY_ADM.ALTER_APPLY(
       apply_name => 'APPLY$_ALPHA_3',
       ddl_handler => 'STRMADMIN.DATAFILE_RENAME'
    );
    end;
    /
    source alert.log:
    Wed Sep 12 12:04:55 2012
    Propagation Sender (CCA) PROPAGATION$_4 for Streams Capture ALPHA$CAP successfully re-connected to Apply APPLY$_ALPHA_3 (objnum=14146) with pid=55, OS id=3653.
    Wed Sep 12 12:04:55 2012
    knlbmEnq: some subscribers are active - resume to enqueue
    Wed Sep 12 12:05:02 2012
    create tablespace test datafile '/oracle/base/oradata/ALPHA/test01.dbf' size 10M autoextend on next 10M maxsize unlimited
    Completed: create tablespace test datafile '/oracle/base/oradata/ALPHA/test01.dbf' size 10M autoextend on next 10M maxsize unlimited
    destination alert.log:
    Wed Sep 12 12:04:55 2012
    Propagation Receiver (CCA) for Streams Capture ALPHA$CAP and Apply APPLY$_ALPHA_3 with pid=52, OS id=3957, objnum=14146 started.
    Wed Sep 12 12:05:05 2012
    create tablespace test datafile '/oracle/base/oradata/BETA/test01.dbf' size 10M autoextend on next 10M maxsize unlimited
    Completed: create tablespace test datafile '/oracle/base/oradata/BETA/test01.dbf' size 10M autoextend on next 10M maxsize unlimited
    of course it is just a simple example, and you need to work out the details (filter for command type, make it robust, etc.)

    Edited by: Balazs Papp on Sep 24, 2012 3:26 PM
  • 6. Re: Oracle Streams Newbie
    441858 Newbie
    Currently Being Moderated
    Oracle 11gR2

    Hi,

    I had the same issue as the original poster. However, when I created the procedure as you mentioned, the DDL change still did not replicate nor did any other DDL. It's s if it has stopped. Do I need to add additional rules or handlers? If I want the handler to work just for the renaming of the datafile and nothing else, how would I configure that?

    Thanks.
  • 7. Re: Oracle Streams Newbie
    user476243 Newbie
    Currently Being Moderated
    anyone?
  • 8. Re: Oracle Streams Newbie
    Balazs Papp Expert
    Currently Being Moderated
    how did you set up Streams?
    by default DDL replication is disabled, and you have to explicitly specify if you want to enable it
  • 9. Re: Oracle Streams Newbie
    441858 Newbie
    Currently Being Moderated
    I setup streams using the maintain_global procedure and the include_ddl parameter was set to true. As I mentioned, it was working fine...all DDL changes were being captured, propagated and applied. However, when I added the apply rule like in your example, the DDL stopped being propagated.

    Thanks
  • 10. Re: Oracle Streams Newbie
    Balazs Papp Expert
    Currently Being Moderated
    Sorry, my bad. I have just noticed that i forgot to include the most important line in the DDL handler above:

    lcr.EXECUTE();

    without it, the LCR won't get executed, and no DDL will be applied. Fixed it now.

    (Originally my handler did nothing else then logging DDL statements, and I modified that for this example, and that line lost somewhere.)
  • 11. Re: Oracle Streams Newbie
    441858 Newbie
    Currently Being Moderated
    Awesome! That was it!

    I was wondering cause I saw that line in the example in the documentation, but I thought perhaps you were doing it another way. After all, you showed us that it was working :)

    Thanks much
  • 12. Re: Oracle Streams Newbie
    user476243 Newbie
    Currently Being Moderated
    What if we are going from non-ASM to ASM for the datafile/logfile path?

    I know we can do a REPLACE(file_name, '/u03/app/oracle/oradata/orcl/','+DATA/orcl/datafile'). This will work. But what if all our files (datafiles, redologs) are located in one directory (BTW I did not design it that way)? Could we use IF-THEN-ELSE logic here? Because it would not be able to decipher between a logfile and a datafile and it would put it in DATA/orcl/datafile instead of REDO/orcl/onlinelog.

    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points