Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    60

    Unanswered: Oracle Replication

    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 ...)

    Thanks for any comment.

  2. #2
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    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.

    HTH,
    Patrick

  3. #3
    Join Date
    Feb 2003
    Posts
    60
    Thank you Patrick for you answer.

    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 ?

  4. #4
    Join Date
    Feb 2003
    Posts
    60

    Error message MGR-11531

    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 ?

  5. #5
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: Error message MGR-11531

    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.

    HTH,
    Patrick


    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 ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •