Click here to Skip to main content
15,898,371 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi all

i have 2 orcale server :
1- ser1 have database1
2- ser2 have database2

* datebase1 and daabase2 is the same

i need when i insert , delete or update in database1 dynamically effect in database2 with the same statement

thanks in advance
Posted

1 solution

There are several options for this. One quite easy way is that you create a database link between the databases. See: http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5005.htm[^]

Using that link you can execute statements against the remote server for example from a trigger in the source server.

If you're looking for a solution where the remote server acts as a backup server, then have a look at standby solutions: http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm[^]

[Addition based on comment]
You can create two separate connection objects and execute necessary statements against both connections.

However, if you plan to do the exact same things on both server I believe that this will only succeed generally if there's 1 and only 1 user in both databases at the same time. If they are multi-user databases another application may interfere the logic in the middle of an operation and the results may vary among those databases.

Another thing is that what's going to happen if you execute DML operation against database 1 and then against database 2. What if the second operation isn't succeeding? By definition you should roll back the first operation also but can you do this reliably from the client application. Typically a distributed transaction is used in these kind of cases.
 
Share this answer
 
v2
Comments
marjavic 6-Sep-11 6:27am    
i need solution can translate into c# code like make an event on insert command or use the same command to execute into 2 database i don't know if that possible
Wendelius 6-Sep-11 6:42am    
Answer updated
marjavic 6-Sep-11 7:32am    
thanks alot Mika Wendelius for your help
i already have that solution but that's making overhead on the application couse its dupplicate evry DML operation i was needed to make it in one time.
i make the server ID secondary key so if an operation isn't succeeding i can follow it
Wendelius 6-Sep-11 7:46am    
Not sure if the server id is enough, but I don't have enough information about the scenario and the requirements. Just plan carefully and test your ideas with other people to be sure that you come up with a good solution.

One additional thing. Always execute the statements in the same order concerning the servers. First server 1 then 2. This will simplify things a bit.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900