I have a Oracle database that i want cache its tables in two Timesten databases operated in two different PCs in a local network. There are all kinds of cache groups. When some changes are applied in
TimesTen databases, propagation to oracle tables fails. This problem occur on global and SWT cache groups. My guess is something wrong about the TimesTen grid.
Error is: [TimesTen][TimesTen 220.127.116.11.0 ODBC Driver][TimesTen]TT5025: Commit failure in Oracle. Transaction must be rolled back in TimesTen. -- file "sqlAPI.c", lineno 3277, procedure
OS: windows xp sp3
Timesten version: 18.104.22.168.0
My Oracle: 11g
Edited by: Shahrokh on Jun 13, 2012 8:54 PM
The error message seems to imply that there is a problem committing the transaction in the Oracle database. First test the connectivity to Oracle with the following in TimesTen as your cache manager user in ttIsql:
Command> passthrough 3;
Command> SELECT * FROM V$VERSION;
Command> passthrough 0;
if that returns with no error then try:
Command> passthrough 3;
Command> +<Your SQL that failed>+
Command> passthrough 0;
That might give an indication of why the transaction is failing in Oracle.
Just one general comment, I'm interested to know your reason for using SWT cache groups rather than AWT cache groups? Typically people are building high performance applications with TimesTen, so most people tend to go with AWT cache groups. SWT cache groups are of course a valid configuration but as the changes are in lockstep between TimesTen and Oracle it is not very performant.
You're right, SWT is not optimum in caching tables but the order of propagating data into oracle by TimesTen clients is important for us. When you use AWT cache groups you can't be sure about validity of the data. Look, we wanna keep synchron both Oracle and TimesTen grids, what is your idea to accomplish this??? One more question, I read that TimesTen doesn't support Grid feature on Windows platform, is it correct? what is solution for it?
Edited by: Shahrokh on Jun 11, 2012 6:16 AM
If you're not concerned with regards to performance then SWT is fine. With AWT the transaction order is maintained, the committed transactions are taken form the transaction log buffer/file and then applied in the Oracle database.
Grid on windows is coming in a future release.
If connection between Oracle and one of Timesten clients cut off, you can't be sure about about the order of propagating data in AWT cache groups because the pending changes will be
committed after the connection reestablished. By the way, i still don't get my answer. If one of Timesten change cached tables data(in Oracle), how Oracle can alert the other Timesten clients to
execute the proper command (REFRESH or LOAD) on their databases to get the last version of data?
Assume Timesten databases have overlap on each other.
There are 2 ways you could possibly look to achieve this:
As you are considering SWT then update performance is perhaps not critical. You could use Read-Only cachegroups and use PASSTHROUGH so that any updates in TimesTen are passed through to the Oracle database executed/committed there, then at the next AUTOREFRESH these updates are refreshed into both TimesTen nodes. This is only appropriate if you have a relatively low update rate from TimesTen.
The other way is to use AWT cachegroups with a TimesTen Grid of 2 nodes. This way the data is only ever in one TimesTen node. So if you were to update the data in TT node 1 the update would be propagated to the Oracle database and then if accessed/updated from TT node 2, node 2 would then take ownership of the row and then service the application.
To achieve HA each TimesTen node would be an Active/Standby replicated pair managed by Oracle clusterware.
READ_ONLY cache groups aren't appropriate for us because we want Timesten be able to change data directly. Since we want to have the same data on both nodes, for speed reasons, we don't
want to use Grid neither. What do you think we can do? Can't we use Oracle features like triggers? The fact that data changes on Oracle tables aren't refreshed automatically on Timesten database
is vulnerable point for it.
Perhaps you could expand a bit more on your use case? How many updates/reads/inserts a second you have? Volume of data etc? Do you have any possibility of node affinity with regards to data access?
Also have you tested the performance of Grid to see if it meets your needs?
The amount of updates and also the volume of database are not an important issue. We have two Timesten clients that they have access to read/write data directly and they cover the same data
and tables because we need to answer to queries fast that's why Grid feature is not a good option. Now, the main issue is how to synchronize the common data between oracle and Timesten
clients. How can we execute changes on oracle by one of Timesten to the other?? Is there any way to do automatically this(beside executing manually REFRESH and LOAD statement)?? Can Oracle
alert Timesten about applied changes??
It difficult to give you a recommendation without knowing the detail.
If you have some level of node affinity in the TimesTen clients then Grid is the perfect solution. How do you know it's not fast enough without testing it?
If you only have occasional updates to the data in the TimesTen clients then using Read-Only cache groups with PASSTHROUGH is the perfect solution. Updates happen on the TimesTen connection but are passed through to Oracle where at the next AUTOREFRESH they are propagated from Oracle to both TimesTen clients. But if you have a large volume of updates this is not a practical solution. If you do have a large volume of updates then you could send the updates directly to Oracle but this of course requires the application to have a connection to TimesTen and a connection to Oracle.
Without more detail I suggest you try these 2 simple approaches to see which one works for you.
What you are asking for is to replicate a change from a TimesTen database to an Oracle database then to another TimesTen database. This is very difficult to achieve with any replication technology. Others in this forum may have other ideas?
Thank you very much for your patience. I wanted to do this issue with TimesTen and oracle potential and use their integrated connectivity but i think we have to solve our problem with other
options and use some auxiliary application in both Oracle and TimesTen. We can create a manager that monitor Oracle tables and message to TimesTen agents and wait for their responses. I just
wanted to know if there is a better and faster way but i disappointed.
As Tim has mentioned, our 'out of the box' solution for this use case is Cache Grid. If that doesn't meet your needs for some reason (and it not being supported on Windows yet could be a good reason) then you will have to build something yourself. At the general level, what you describe is essentially 3-way multi-master replication between two TimesTen databases and Oracle DB. That's potentially complex and any solution you engineer will need to ensure that data consistency is preserved everywhere at all times. As this is very hard to do in the general case while also maintaining high-performance that is why we do not provide an ouit of the box solution for this use case. Solutions may be easier for specific use cases where there are additional constraints that reduce or eliminate the potential data consistency issues.