Asynchronous Distributed HotLog - CDC Part IV

Part I, II and III.

For the time being I am throwing in the towel. I managed to patch my 10.2.0.1 version up to 10.2.0.3 thinking that might help, but it did not.

I'm still leaning to something on the 9.2.0.6 side, the only reason being is that in the trace file generated by the 10g instance, I see a "Alter9iSource" line in there...but there is nothing in the alert log on the 9i instance.

So I'm packing it up for a little while, hopefully the time away will do me some good and help me regain focus.

I did find a great example of the manual setup by Lewis Cunningham; that may be the route in the future, just a bit more work to maintain.

Asynchronous Distributed HotLog - CDC Part III

This is the third installment (one and two)of my attempts to configure Oracle's CDC from a 9.2.0.6 source (highest release for windows) to a 10.2.0.1 (ditto) target.

After being appropriately set up with privileges (DBA's trusted me with the DBA role!) on our actual environments, 9.2.0.7 source and 10.2.0.3 target (Sun Solaris), I've moved back to trying to just get a simple proof of concept working on my machine.

That's had it's own difficulties, though I can safely say that I can manually create 9i and 10g databases manually.

Getting the databases configured properly has been my biggest challenge. I had eliminated most possibilities down to either Net Services or something to do with the JVM. I used my local listener and ruled out Net Services. It was down to the JVM. I kept getting this strange error:

ERROR at line 1:
ORA-00600: internal error code, arguments: [qccsrc_createHLSource-3], [], [],
[], [], [], [], []
ORA-06512: at "SYS.DBMS_CDC_IPUBLISH", line 133
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 226
ORA-06512: at line 2

A trace file was generated (with the above error) but it didn't really tell me anything. I googled the phrase, nothing. I went to Metalink, nothing. It looked like a Java call though, so I tore the database down and rebuilt everything. Just in case, I added XDB, Data Mining and InterMedia in case there was some reference there.

Installing InterMedia was a pain. I kept getting a failure of a particular jar (ORA-23542?), could not resolve the class.

I googled one of the classes that failed to see if anyone had run across this before. There was only one record found...and it was mine from about a year and half ago! I found that very amusing.

So I went into the \ord\im directory and found a readme.txt. As I am scanning through it I see CLASSPATH. So I add the environment variable and voila! Perfect install. That would have solved my problem from before too. I followed the docs on the install to a T. Nothing in there about CLASSPATH. Perhaps I should write them and ask for it to be added?

Anyway, thinking the best, since I had a perfectly clean installation, I tried again:

SQL> BEGIN
  2    dbms_cdc_publish.create_hotlog_change_source
  3      ( change_source_name => 'T',
  4        description => 'Test',
  5        source_database => 'ORANINE' );
  6  END;
  7  /

And I waited for an agonizing minute thinking I had it this time...

BEGIN
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "SYS.DBMS_CDC_IPUBLISH", line 133
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 226
ORA-06512: at line 2

Barnacles!

At least the ORA-0600 is gone, but I am back to where I started.

Another good thing is that I am really learning how to pour through trace files and tkprof files.

All the examples I have found make it seem so easy...why can't I get this dang thing to work?!

Asynchronous Distributed HotLog - CDC Part II

In my first installment, I talked about my task to configure CDC.

I've set up both the source and target databases as detailed in the documents.

I create a simple table:

CREATE TABLE t ( x NUMBER, y DATE );

From the target database I issue the following:

BEGIN
  dbms_cdc_publish.create_hotlog_change_source
    ( change_source_name => 'T_CHANGE',
      description => 'Test',
      source_database => 'CDC_APP' );
END;
/

and shortly thereafter receive the following error:

BEGIN
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "SYS.DBMS_CDC_IPUBLISH", line 133
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 226
ORA-06512: at line 2


Elapsed: 00:00:57.23

So I set up tracing and I find the following in the trace file:

PARSING IN CURSOR #13 len=52 dep=0 uid=12 oct=47 lid=12 tim=342736069442 hv=1029988163 ad='3414b300'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #13:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=342736069435
BINDS #13:
kkscoacd
 Bind#0
  oacdty=123 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=4000 off=0
  kxsbbbfp=08460bd8  bln=4000  avl=00  flg=15
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=0844cf58  bln=22  avl=22  flg=05
  value=###
  An invalid number has been seen.Memory contents are :

The way I read that is "###" is being passed to dbms_output.get_lines where it should be expecting a number.

Again, the source database is 9.2.0.7 and the target is 10.2.0.1. I uninstalled the JVM because I thought that the wrong version of java was used to load it (not that I can find any reference to it being used). Changed my path, set up an ORACLE_HOME environment variable (yes, windows XP).

I'm hoping I just mangled the configuration of my local database. Tomorrow one of the DBAs is going to walk me through this in either our DEV or TEST environments (as it should be).

Fun...

Asynchronous Distributed HotLog - Change Data Capture

I am currently researching the feasibility of getting Asynchronous Distributed HotLog CDC working for a Proof of Concept in our organization. The source database is a 9.2.0.7 on Sun Solaris and our target database is a 10gR2 on the same OS.

I have been trying, since I arrived, to get them to use more features provided by Oracle. From what I have found so far, all of our Oracle databases are severely under utilized.

I will post my findings here along with a full How To when I have completed it and hopefully the results of whether it was implemented or not.

I did want to post some of the sources I have used so far though:

Oracle Documentation - This is where I started. Most of the other links reference the docs.
Mark Rittman - 10g to 10g. Great example but from what I can tell so far, it's almost identical to the documentation.
Oracle How To - I just found this one and am very interested in reading through it.

If you have any examples, feel free to post the link.