This discussion is archived
8 Replies Latest reply: Mar 1, 2013 4:01 AM by JohnWatson RSS

Logical Standby

monoj Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    -duplicate

    Edited by: Uwe Hesse on 01.03.2013 12:37
  • 7. Re: Logical Standby
    UweHesse Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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