Presently I have a setup in Oracle DB in which there are two databases and objects are accessed across these databases via Database Link/Synonyms.
Now I need to replicate this across a Timesten InMemory DB Cache Setup with underlying Oracle Databases which would mean creating a Link between two DSNs kept on two different servers.
Can you guide me on how to go about this.
Currently TimesTen does not support anything like DB links in Oracle nor does it support distributed/remote query of the kind available in Oracle. An application that connects to a Timesten database (what yoiu referred to as a DSN) can access objects stored in the TimesTen database or objects from the underlying Oracle database (using SQL passthrough) but cannot, via SQL, access objects in another TimesTen database.
Perhaps you can elaborate on your use case a little more (for example, are the two Oracle databases in two different Oracle DB instances or are they just different databases/schemas in the same instance - potentially makes a big difference...). Why do you need two separate TimesTen databases? Is it because you need to cache data from two different Oracle instances (in which case you would need two TT databases) or some other reason? What is the application access to the data? Under what circumstances does the application need to access data from the two different databases? Does it need to do things like join tables across the databases? Does it need transatcoons which span databases?
If you can provide this kind of information then we can suggest what options might work best for your use case.
We have two different Oracle Instances (on different servers) hence we would need to setup our TimesTen Database separately. Right now we are trying to integrate TimesTen into a present live system which has these two oracle instances. The application connects independantly on the oracle databases parallelly and accesses tables from each others database (via joins,views,procedures,etc). There are DML operations be carried out though the db link too.
Incase we integrate into one oracle instance would mean application changes which are possible but it should be our last resort.
Unfortunately ther are several constraints here that aren't going to help you:
1. When using the TimesTen 'cache' fucntionality a single TimesTen database can only cache data from a single backend Oracle instance. Only regular tables can be cached (not views, dblinks etc.). So, if you have two Oracle Db instances and you need to cache data from both you will need two TimesTen databases.
2. As mentioned, TimesTen does not support dblinks, remote queries or distributed queries/transactions between different TimesTen databases (well, it does in some circumstances but nothign that will help you I suspect).
3. TimesTen can replicate data between different TimesTen databases but in general one cannot use table level replication with cache tables.
So, to adopt TimesTen in your application I think a significant architecture re-work may be necessary. Only you can be the judge of that of course but when considering this please bear in mind the above constraints.