Database replication can be surprisingly complex. Whether setting up duplicate Oracle databases or synching with SQL Server, Oracle experts Karen Morton and Brian Peasland admit that many decisions factor into setting up a replication job. This Ask the Experts compilation acts as a starting point for anyone working on the task.
I need to put a database on two servers, and I want them to be the same: If I change one of them, the second one should take this change, for example, adding columns, removing and whatever else.
Brian Peasland: What you are asking to do is to replicate your database between two different servers. Oracle sells an additional product called Advanced Replication. With replication, you can set up one database to be the MASTER with all changes propagated to the other. You can even have two-way replication if you desire. But keep in mind that you will have to license the database and replication on both servers. This cost can add up quickly!
I want to set up replication between two databases (Solaris/Oracle 8.1.7). Please tell me what procedure I should follow.
Brian Peasland: Setting up replication isn't the easiest thing to do. There are many factors to consider. Each factor can force a decision or two. And there is quite a lot of steps to perform, many more steps than can be easily portrayed in this forum. I strongly suggest reading the Oracle9i Replication Management API Reference documentation. It details how to set up replication.
How can I use replication in Oracle8i when I have to update three remote locations simultaneously?
Karen Morton: Replication is a big topic and not really something that can be explained quickly in this limited forum. Please see the following links for all the details:
I want to do a replication from SQL Server to Oracle. How can I do it?
Brian Peasland: Your replication solution will depend on your replication requirements. Do you need data replicated in near-real time, or will you only need data updated every so often? If you need data replicated in real time, then you might wish to look at SQL Server's solution to link Oracle databases. (I'm not a SQL Server expert so I can't give you any guidance there). If the data needs to be replicated every so often, then you can use SQL Server's Data Transformation Services (DTS) to push data to Oracle through OLE DB. Alternatively, you can connect Oracle directly to a SQL Server database, through ODBC, using an Oracle product called Heterogeneous Services.
I want to replicate data between SQL Server 2000 and Oracle. The dataset is huge, and the replication has to occur every five minutes. Is it possible, and if not, what is the alternative? Both the servers are well-settled servers and cannot be disturbed. They carry huge amounts of business data.
Brian Peasland: This is quite a task when you consider that you have two different vendors' RDBMS platforms. They don't always talk to each other very well. If both databases used the same RDBMS software, then this chore would be easier.
From an Oracle perspective, you can make the SQL Server database appear as if it is an Oracle database by using a product called Heterogeneous Services. This let's you connect the Oracle database to SQL Server through an ODBC driver. You can then use triggers in the Oracle database to push the data to the SQL Server database when the data changes in Oracle. This way, data is replicated instantly!!
Another method is to use database triggers to populate a local table with the underlying table's changes. For instance, let's assume I have an EMP table. Then I can create a EMP_CHANGES table. Also create a trigger on the EMP table that populates EMP_CHANGES with all of the changes that occurred to EMP. Now have the other database query this table on a regular basis (5 minutes?) and only query the EMP_CHANGES table. This greatly reduces the burden on the EMP table!
I am setting up an Oracle replication using two different instances, and it works with no problem. I tried to set up using the same instances, but it doesn't work. I already tried configuring the database links, but I still cannot work it out. Can Oracle support replicating two of the same instances? How can I configure it in database links? I am using Oracle 7.3.4.
Brian Peasland: Oracle replication does not work with two instances that share the same SID, at least not without a little help. Oracle replication requires that each instance be able to be uniquely identified. That means that the combination of DB_NAME and DB_DOMAIN must be unique. So if you don't want to change your DB_NAME for one of your instances, try changing the DB_DOMAIN instead.
For more information
- Tips and more expert advice on database replication
- More Oracle answers from Brian Peasland
- More Oracle answers from Karen Morton