8 Replies Latest reply: Aug 9, 2013 2:22 PM by mseberg RSS

    Table Modifications in Logical Standby

    DBA Dude

      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

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

              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

                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

                  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


                    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

                      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

                        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 )