October 23, 2009 at 10:51 am
I hear you can replicate Oracle to SQL Server 2008 Enterprise. Does it have to be the entire DB or can it be only one table?
Thanks
December 8, 2009 at 11:40 am
You can replicate a single table if you want - even a subset of a single table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 19, 2010 at 7:46 am
What tools or product do you use to replicate a table or groups of tables from Oracle to SQL Server. I need to do this daily for a datamart from Oracle to SQL Server 2008.
February 19, 2010 at 2:41 pm
Here... http://msdn.microsoft.com/en-us/library/ms152481.aspx
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 13, 2010 at 12:12 am
Hello,
I am using SQL Server 2008 on XP machine and want to Replicate a database to an Oracle Database on a Unix machine. The two machines are connected to each other on network. I am successfully able to perform snapshot replication for small tables. But there is one table with a 2 columns of datatype VARCHAR(5000) and few other columns of datatype of VARCHAR(smaller than 4000). This table has over 1 million recrods. The distribution agent fails to apply the snapshot on the Oracle database for this table.
It says:
" Error 20203: There is not Enough Storage to Complete this Operation". I tried to repair my MDAC as well and update it, that too didn't worked.
Can some please help me.
I have posted the same issue here:
http://qa.sqlservercentral.com/Forums/Topic900274-1044-1.aspx
Have got a few response, but were of little help.
Thanks and Regards
Sameer Kumar
Associate DBA
May 13, 2010 at 12:39 pm
Oracle does not supports VARCHAR(5000)
The highest you can go is VARCHAR2(4000)
What's the data type and size of the Oracle side column?
What version of Oracle is it?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 13, 2010 at 9:39 pm
Oracle version is 10g release 2. As far as data type is concerned, SQL Server follows a standard of Converting any varchar column of length more 4000 to clob, while replicating to an Oracle DB. I have a wild guess, it may be an issue of Undo tablespace or rollback segments. I am yet to explore the physical structure of database. Any further inputs on this is welcome. Let me know if you need more information.
May 14, 2010 at 3:01 pm
sameer.kasi200x (5/13/2010)
it may be an issue of Undo tablespace or rollback segments.
Pretty easy to validate... start the job and ask your Oracle DBA to monitor undo tablespace as well as rbs behavior.
Once process abends ask your Oracle DBA to check alert log looking for any ORA-1630 and/or ORA-1652 errors affecting rbs/undo
No ORA-1630 and no ORA-1652 means this is not a space related issue at rbs or tablespace level.
sameer.kasi200x (5/13/2010)
Let me know if you need more information.
Yes. What's the datatype/size of the targetted column on Oracle side?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 19, 2010 at 10:58 pm
One of my seniors had tried it out earlier (checking for ORA-1630 and no ORA-1652). There is no clue about this error in Alert Log on my Unix machine, where the Oracle is running.
>>Yes. What's the datatype/size of the targetted column on Oracle side?
As I said, the VARCHAR columns with length greater than 4000 are made to be CLOB and VARCHAR columns with length lesser than are replicated as VARCHAR2.
I have selected CREATE REPLACE option for replication and hence I can not control this behavior of SQL Server.
Though I had tried to change the replication properties to TRUNCATE. I had created a target table in Oracle DB with LOB column (instead of CLOB) before starting the replication. That too did not work.
May 20, 2010 at 10:03 am
sameer.kasi200x (5/19/2010)
One of my seniors had tried it out earlier (checking for ORA-1630 and no ORA-1652). There is no clue about this error in Alert Log on my Unix machine, where the Oracle is running.
Well... this means it is not a "space" issue 😉
sameer.kasi200x (5/19/2010)
As I said, the VARCHAR columns with length greater than 4000 are made to be CLOB and VARCHAR columns with length lesser than are replicated as VARCHAR2.
In the Oracle world, LOB columns have to be handled via system package DBMS_LOB when attempting to insert, update, etc meaning they can not be handled by just naming a column in an insert statement.
I would open a ticket with Microsoft asking them: "How does Microsoft Replication to Oracle handles LOBs when data size is larger than 4000?"
Alternatives are:
1... Microsoft replication only handles LOBs when data ssize is less than 4000 - and you are in serious troubles.
2... Microsoft replication does handles LOBs when data size is larger than 4000 - and further research is needed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 21, 2010 at 2:10 am
Please refer to the following link on MSDN for Mapping Datatypes of Oracle and MS SQL Server for an Oracle Subscriber:
May 22, 2010 at 4:13 pm
sameer.kasi200x (5/21/2010)
http://technet.microsoft.com/en-us/library/ms151738.aspx%5B/quote%5D
Thank you for pointing at the right document but, please note poster already stated SQL Server varchar(5000) is mapped to an Oracle CLOB, which is correct.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 8, 2010 at 7:35 am
Nicely worded but spam anyway - isn't it?
Reported.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 29, 2010 at 6:37 am
Does anyone have experience about replication between Oracle 10gR2 database to SQL server 2008 database, if so could you please share it with us about the tool or method you used for that?
October 29, 2010 at 7:23 am
Michal222 (10/29/2010)
Does anyone have experience about replication between Oracle 10gR2 database to SQL server 2008 database, if so could you please share it with us about the tool or method you used for that?
Nothing beats official documentation, here... http://msdn.microsoft.com/en-us/library/ms152481.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply