8 Replies Latest reply: Mar 1, 2013 6:01 AM by JohnWatson RSS

    Logical Standby

    monoj
      Dear All,

      My database version is 10.2.0.1.0(production) and Operating System     Linux x86-64 OS Version     Red Hat Enterprise 4

      Below is my configuration details:

      Rprimary Database:Two Nodes RAC

      StandBy Database LOCAL:Standalone database

      Standby Database Remote:Two Node RAC

      So,my question is, i want my local physical DR to convert into logical standby and

      Remote DR will operate as physical standby as it IS.



      Again,another important point is that my database is having ROWID datatype

      SQL> SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED
      2 ORDER BY OWNER,TABLE_NAME;

      OWNER TABLE_NAME
      ------------------------------ ------------------------------
      EMPLOYEE WB_RT_ERRORS
      EMPLOYEE WB_RT_EXCEPTIONS
      EMPLOYEE WB_RT_FEEDBACK
      EMPLOYEE WB_RT_POSTLOAD_EXCEPTIONS
      ENDLAYER WB_RT_ERRORS
      ENDLAYER WB_RT_EXCEPTIONS
      ENDLAYER WB_RT_FEEDBACK
      ENDLAYER WB_RT_POSTLOAD_EXCEPTIONS


      SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
      2 WHERE OWNER='ENDLAYER' and table_name='WB_RT_ERRORS';

      COLUMN_NAME
      ------------------------------
      DATA_TYPE
      --------------------------------------------------------------------------------
      RTE_ROWID
      ROWID



      and Another important is that the below query return following result

      SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
      2 WHERE (OWNER, TABLE_NAME) NOT IN
      3 (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
      4 AND BAD_COLUMN = 'Y';

      OWNER TABLE_NAME
      ------------------------------ ------------------------------
      TSMSYS SRS$
      ASSAMDP PEN_APPLICATION_BK_08012010
      TREASURY EUL5_PLAN_TABLE
      DEC_REP BS_CLUSTERS
      DEC_REP BS_DOCS
      DEMODISC EUL5_PLAN_TABLE
      TREASURY PLAN_TABLE
      ENDLAYER CREATE$JAVA$LOB$TABLE
      DEC_REP BS_MVIEWS
      DEC_REP BS_SCRIPTS
      DEC_REP BS_TYPES

      OWNER TABLE_NAME
      ------------------------------ ------------------------------
      DEC_REP BS_TRIGGERS
      DEC_REP BS_VIEWS
      EMPLOYEE PLAN_TABLE
      BUDGET PLAN_TABLE

      15 rows selected.

      SQL>


      Is it possible to configure the same.please help me out to configure the same.


      Thanks and Regards
      Monoj Das
        • 1. Re: Logical Standby
          JohnWatson
          You need to add primary keys where possible to those tables listed in DBA_LOGSTDBY_NOT_UNIQUE. Then you'll need to mark any of those tables for which you cannot create a PK, and all the tables with ROWID columns, to be skipped by the apply processes. And if you want to propagate changes to those tables, do it by some other means: a periodic export/import, perhaps.
          But this is only a work-around. The best solution would be to investigate why those tables contain ROWIDs and do not have primary keys, and fix them accordingly.
          --
          John Watson
          Oracle Certified Master DBA
          http://skillbuilders.com
          • 2. Re: Logical Standby
            monoj
            Sir,Thank you so much for your reply..

            Sir,I am also confuse about the statement,
            Add a disabled primary key rely constraint.

            ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;


            Do i have to execute the above statement for all the table in my database OR just for those tables my below query returns.

            What if i don't run the statement ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;



            SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
            2 WHERE (OWNER, TABLE_NAME) NOT IN
            3 (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
            4 AND BAD_COLUMN = 'Y';

            OWNER TABLE_NAME
            ------------------------------
            TSMSYS SRS$
            ASSAMDP PEN_APPLICATION_BK_08012010
            TREASURY EUL5_PLAN_TABLE
            DEC_REP BS_CLUSTERS
            DEC_REP BS_DOCS
            DEMODISC EUL5_PLAN_TABLE
            TREASURY PLAN_TABLE
            ENDLAYER CREATE$JAVA$LOB$TABLE
            DEC_REP BS_MVIEWS
            DEC_REP BS_SCRIPTS
            DEC_REP BS_TYPES

            OWNER TABLE_NAME
            ------------------------------
            DEC_REP BS_TRIGGERS
            DEC_REP BS_VIEWS
            EMPLOYEE PLAN_TABLE
            BUDGET PLAN_TABLE

            15 rows selected.


            Thanks and Regards
            Monoj
            • 3. Re: Logical Standby
              JohnWatson
              It seems that you may be missing the point of this.
              SQL Apply needs a primary key to identify rows. If there is no primary key, SQL Apply will fail. If you define primary keys with RELY DISABLE, the SQL Apply will not fail but it will corrupt your data (unless your application software handles the situation).
              Perhaps some outside assistance for this project would be advisable.
              --
              John Watson
              Oracle Certified Master DBA
              http://skillbuilders.com
              • 4. Re: Logical Standby
                monoj
                Sir,thank you so much ones again for you reply.


                ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

                Sir,do i have to execute the above statement for all the tables in my database.

                Bit confuse,please guide me.


                Thanks and regards
                Monoj
                • 5. Re: Logical Standby
                  monoj
                  Sir,Do i have to execute ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;
                  just against below tables or For all the tables in my database that doesn't have primary key.


                  SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
                  2 WHERE (OWNER, TABLE_NAME) NOT IN
                  3 (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
                  4 AND BAD_COLUMN = 'Y';

                  OWNER TABLE_NAME
                  TSMSYS SRS$
                  ASSAMDP PEN_APPLICATION_BK_08012010
                  TREASURY EUL5_PLAN_TABLE
                  DEC_REP BS_CLUSTERS
                  DEC_REP BS_DOCS
                  DEMODISC EUL5_PLAN_TABLE
                  TREASURY PLAN_TABLE
                  ENDLAYER CREATE$JAVA$LOB$TABLE
                  DEC_REP BS_MVIEWS
                  DEC_REP BS_SCRIPTS
                  DEC_REP BS_TYPES

                  OWNER TABLE_NAME
                  DEC_REP BS_TRIGGERS
                  DEC_REP BS_VIEWS
                  EMPLOYEE PLAN_TABLE
                  BUDGET PLAN_TABLE

                  Thanks in advance.


                  Thanks and Regrds
                  Monoj
                  • 6. Re: Logical Standby
                    UweHesse
                    -duplicate

                    Edited by: Uwe Hesse on 01.03.2013 12:37
                    • 7. Re: Logical Standby
                      UweHesse
                      You said:
                      >
                      SQL Apply needs a primary key to identify rows. If there is no primary key, SQL Apply will fail.
                      >

                      That is not correct. SQL Apply will work in the absence of a Primary Key also - as long as the datatypes of the table are supported.

                      The LGWR on the Primary DB will in this case include all columns of the rows that have been modified into the Redo Stream, though.
                      That is why it is desirable to have Primary/Unique Keys on the Primary because it avoids these additional entries in the Online Logs.

                      Kind regards
                      Uwe Hesse

                      "Don't believe it, test it!"
                      http://uhesse.com
                      • 8. Re: Logical Standby
                        JohnWatson
                        Uwe Hesse wrote:
                        You said:
                        >
                        SQL Apply needs a primary key to identify rows. If there is no primary key, SQL Apply will fail.
                        >

                        That is not correct. SQL Apply will work in the absence of a Primary Key also - as long as the datatypes of the table are supported.
                        Well, maybe. I'm not about to test it.
                        As far as I can remember (10g was some time ago) my experience, with Streams more than logical standby, was that in these circumstances you can create an unconditional supplemental log group with all the columns, and it looks OK but eventually it will fail because the impossibility of identifying a unique row means that the data diverges.
                        So everything works for an hour, a day, or a month - but eventually it falls over.
                        But right or wrong, not having a PK is a seriously bad idea.