This content has been marked as final. Show 12 replies
its errors and aborts the stream service on the DB2 database.post error message
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?
a) use OMF instead of specifying datafile names1 person found this helpful
b) set up a custom DDL handler
Hi, thanks. OMF isn't an option. But how can I create a custom DDL handler?
Thanks for any assistance
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; /
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
of course it is just a simple example, and you need to work out the details (filter for command type, make it robust, etc.)
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
Edited by: Balazs Papp on Sep 24, 2012 3:26 PM
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?
how did you set up Streams?
by default DDL replication is disabled, and you have to explicitly specify if you want to enable it
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.
Sorry, my bad. I have just noticed that i forgot to include the most important line in the DDL handler above:
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.)
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 :)
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.