This discussion is archived
8 Replies Latest reply: Aug 9, 2013 12:22 PM by mseberg RSS

Table Modifications in Logical Standby

DBA Dude Newbie
Currently Being Moderated

Can I add a new column to a table in a logical standby for a table which is actively synchronized from the primary?  In essence, I would like to add a column to the table in the logical standby, but not add the column to the corresponding table in the primary DB.

  • 1. Re: Table Modifications in Logical Standby
    mseberg Guru
    Currently Being Moderated

    Hello;

     

    My understanding is this is what's not supported:

     

    Tables with virtual columns

    Collections

    User-defined types

    Objects owned by SYS

    BFILE

    Oracle Text

    Spatial

    ROWID

     

     

    Based on that I beleive it will work.

     

    I have not tested, but the test is simple enough.

     

    DBA_LOGSTDBY_UNSUPPORTED is your friend.

     

    Best Regards

     

     

    mseberg

  • 2. Re: Table Modifications in Logical Standby
    DBA Dude Newbie
    Currently Being Moderated
    I don't think that DBA_LOGSTDBY_UNSUPPORTED will help.  I'm talking about making a change to a table only on the logical standby.  Example: PRIMARY DB has table ABC with 2 columns. LOGICAL STANDBY also has table ABC with 2 columns. ABC is synchronized from primary to standby via SQL APPLY technology. We want to add a 3rd column to table ABC on logical standby.  So this new column will not exist on the primary DB.  Just wondering if that type of activity is possible.
  • 3. Re: Table Modifications in Logical Standby
    mseberg Guru
    Currently Being Moderated

    You may be right about DBA_LOGSTDBY_UNSUPPORTED. It may only show tables that contain data types not supported. In any event it a simple test and I would consider performing it myself as odds are it will take much less time than waiting for an answer here.

     

    Best Regards

     

    mseberg

  • 4. Re: Table Modifications in Logical Standby
    DBA Dude Newbie
    Currently Being Moderated

    We don't have a logical standby DB otherwise I would test.  We're trying to determine whether a logical standby would meet our requirements...

  • 5. Re: Table Modifications in Logical Standby
    mseberg Guru
    Currently Being Moderated

    Can you provide the Oracle version 4 digit and what types of columns you might add?

     

    Best Regards

     

    mseberg

  • 6. Re: Table Modifications in Logical Standby
    DBA Dude Newbie
    Currently Being Moderated


    11.2.x.x (we're using 11.2.0.3, but anything 11.2.0.x is fine.

     

    Add a new numeric and varchar2 column.

  • 7. Re: Table Modifications in Logical Standby
    mseberg Guru
    Currently Being Moderated

    OK.

     

    I'm very low on time, but I have several test systems which I can try a test and post the results.

     

    Will setup logical as shown in chapter 3 of this:

     

    http://www.visi.com/~mseberg/data_guard/new_dg_book.html

     

    Status Update

     

    Have built new Primary (PTEST) and Standby (STEST) test system. Will convert Physical Standby to Logical and proceed with your tests.  10am Central Standard Time

     

     

    Best Regards

     

    mseberg

     

    Message was edited by: mseberg

     

    Message was edited by: mseberg

  • 8. Re: Table Modifications in Logical Standby
    mseberg Guru
    Currently Being Moderated

    OK;

     

    So I have convert a Physical Standby to a Logical database.

     

     

    This table has been created:

     

    CREATE TABLE RSTARS

    (

      ID            NUMBER,

      FIRST_NAME    VARCHAR2(50 BYTE),

      LAST_NAME     VARCHAR2(50 BYTE)

    )

    TABLESPACE OTN_TEST

    PCTUSED    40

    PCTFREE    10

    INITRANS   1

    MAXTRANS   255

    STORAGE    (

                INITIAL          64K

                NEXT             1M

                MINEXTENTS       1

                MAXEXTENTS       UNLIMITED

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               )

    LOGGING

    NOCOMPRESS

    NOCACHE

    NOPARALLEL

    MONITORING;

     

     

    ALTER TABLE BIGSHOW.RSTARS ADD (

      CONSTRAINT RSTARS_PK

      PRIMARY KEY

      (ID));

     

    This data exist in the table:

     

    set linesize 200

     

    select * from rstars;

     

     

            ID FIRST_NAME                                         LAST_NAME                                  

    ---------- -------------------------------------------------- --------------------------------------------------

             1 Robert                                             Plant                                      

             2 Jimmy                                              Page                                       

     

    2 rows selected.

     

     

     

     

     

    On the Primary add another row of data

     

    INSERT INTO RSTARS

    (

      ID,

      FIRST_NAME,

      LAST_NAME

    )

    VALUES

    (

      3,

      'Ted',

      'Nugent'

    );

     

    commit;

     

    Connect to the logical Standby and check for it.

     

    set linesize 200

     

    select * from rstars;

     

     

           ID FIRST_NAME                                         LAST_NAME                                  

    ---------- -------------------------------------------------- --------------------------------------------------

             1 Robert                                             Plant                                      

             2 Jimmy                                              Page                                       

             3 Ted                                                Nugent                                     

     

    3 rows selected.

     

     

    MORE TO COME

     

    So I will add the columns one by one and test after each

     

     

    Add varchar2 to the table:

     

     

    SQL> alter database stop logical standby  apply;

     

    Database altered.

     

    SQL> alter session disable guard;

     

    Session altered.

     

    SQL> ALTER TABLE BIGSHOW.RSTARS ADD (CITY  VARCHAR2(15));

     

    Table altered.

     

    SQL> alter session enable guard;

     

    Session altered.

     

    SQL> alter database start logical standby  apply;

     

    Database altered.

     

    SQL>

     

     

    INSERT INTO RSTARS

    (

      ID,

      FIRST_NAME,

      LAST_NAME

    )

    VALUES

    (

      4,

      'Geddy',

      'Lee'

    );

     

    commit;

     

     

    LOGSTDBY Apply process AS02 server id=2 pid=38 OS id=31938 stopped

    Errors in file /u01/app/oracle/diag/rdbms/stest/STEST/trace/STEST_as01_31936.trc:

    ORA-26676: Table 'BIGSHOW.RSTARS' has 3 columns in the LCR and 4 columns in the replicated site

    Fri Aug 09 13:34:18 2013

    LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=34 OS id=31930 sid=34 stopped

     

    So you have good reason to be concerned. Thanks for the question. I have it in my notes, so I would have bet and lost!


    I thinking you can create a child table as a work around.


    You were right about this too:


     

    SQL> select * from DBA_LOGSTDBY_UNSUPPORTED;

     

    no rows selected

     


    ORA-26676 returns zero documents when I search Oracle Support today.


    Best Regards


    mseberg


    How to fix

     

    To correct all I did was this and Oracle added the row:

     

     

    SQL> alter database stop logical standby  apply;

     

    Database altered.

     

    SQL> alter session disable guard;

     

    Session altered.

     

    SQL> ALTER TABLE BIGSHOW.RSTARS DROP COLUMN CITY;

     

    Table altered.

     

    SQL> alter session enable guard;

     

    Session altered.

     

    SQL> alter database start logical standby  apply;

     

    Database altered.

     

    SQL>

     

    set linesize 200

     

    select * from rstars;

     

     

            ID FIRST_NAME                                         LAST_NAME                                     

    ---------- -------------------------------------------------- --------------------------------------------------

             1 Robert                                             Plant                                         

             2 Jimmy                                              Page                                          

             3 Ted                                                Nugent                                        

             4 Geddy                                              Lee                                           

     

    4 rows selected.




     

    Message was edited by: mseberg  ( as you can tell I'm from the 70's )

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points