I am in the process of studying DR solutions for our banking software (oracle 9i on AIX 4.3.3).
Amongst other things, I can see that Oracle 9i provides data replication.
The company that sold us the banking software is a bit ... well ... the database is not normalized ... a few tables don't have a primary key, and I was told (by the seller) this couldn't be changed .... that sucks, but I can't help it.
I know that Informix Replication doesn't work with a database at least 1 table does not have a primary key.
Does anyone know if there is any issue like that one with Oracle ?
If yes, does Oracle provide any other solution ?
(I am talking just about Oracle data, we have a bunch of other solutions - veritas VCS, HACMP, even Oracle RAC ...)
Oracle replication does not require that a primary key be defined on every table. It does require that every row in a table be uniquely identifiable, which sounds a lot like using a primary key.
The catch is that you can define the columns that would make up a primary key during the replication set-up process. So, you do not have to have a primary key constraint on every table, you just have to have a set of columns that would satisfy a primary key constraint.
This sounds confusing, but makes sense after you have set up replication a time or two. Basically, you can define the primary key within the replication process.
Does this operation (defining a primary key within the replication process) involves other things ? I'm thinking in terms of space requirement for example.
Also, if I have to make up a primary key using 5/6 columns, would that affects the performances of the replication process itself ?
I believe the DBMS_REPCAT.SET_COLUMNS procedure is what defines the primary keys for the replication process when a primary key constraint does not exist on the table. This is what I was referring to in my original post.
I have a few tables that are being replicated and that do not have primary key constraints. For these tables, I have three or four columns defined in replication as primary keys. The replication process is extremely quick in our environment, so I have not seen any performance degredation because of this.
I must add that database I am referring to is a low volume database. We are using master to master replication, but only do inserts, updates and deletes on one of the two databases.
Originally posted by prae
I was looking for more information, and found that ORacle error message :
MGR-11531 cannot generate replication support for name. Table has no PRIMARY KEY.
Cause: Replication will not work for tables without a PRIMARY KEY.
Action: Recreate table with a PRIMARY KEY or call procedure DBMS_REPCAT.SET_COLUMNS after CREATE_MASTER_REPOBJECT but before GENERATE_REPLICATION_SUPPORT.
What does the DBMS_REPCAT.SET_COLUMNS procedure does ?