Time for action – searching for and fixing any table row uniqueness problem

  1. In order to check for any table row uniqueness, we can run the following query on the primary database:
    SQL> SELECT * FROM DBA_LOGSTDBY_NOT_UNIQUE;

OWNER                          TABLE_NAME                     B
------------------------------ ------------------------------ -
SCOTT                          BONUS                          N
SCOTT                          SALGRADE                       N
SH                             SALES                          N
SH                             COSTS                          N
SH                             SUPPLEMENTARY_DEMOGRAPHICS     N

    This query was run on a newly created 11g release 2 database, which only includes built-in example schemas. The output shows that several tables from SCOTT and SH schemas have row uniqueness problem.

    The BAD_COLUMN column has two values, which are Y and N. If you see the rows with BAD_COLUMN=Y, it means that the table column is defined using an unbounded data type, such as LONG or BLOB. If two rows contain the same data except in their LOB columns, the replication will not work properly for this table. If the application ensures the rows are unique, we should consider adding a disabled primary key RELY constraint to these tables. When RELY is used, the system will assume that rows are unique and not validate them on every modification to the table. This method will avoid the overhead of maintaining a primary key on the primary database. However, if there's no such uniqueness, we must add a unique-constraint/index to the columns on the primary database.

    BAD_COLUMN=N means that there is enough column information to maintain the table in the logical standby database; however, the transport and apply services will run more efficiently if you add a primary key to the table. We should again consider adding a disabled RELY constraint to these tables.

  2. Let's add a disabled primary key RELY constraint to the BONUS table in the SCOTT schema. First we check the columns of the table using the following query:
    SQL> DESC SCOTT.BONUS
 Name              Null?    Type
 ----------------- -------- ----------------------------
 ENAME                      VARCHAR2(10)
 JOB                        VARCHAR2(9)
 SAL                        NUMBER
 COMM                       NUMBER
  3. Now we add the disabled RELY constraint to the ENAME column of the table:
    SQL> ALTER TABLE SCOTT.BONUS ADD PRIMARY KEY (ENAME) RELY DISABLE;

Table altered.
  4. We can check the DBA_LOGSTDBY_NOT_UNIQUE view again to see if the BONUS table has disappeared from the list using the following query:
    SQL> SELECT * FROM DBA_LOGSTDBY_NOT_UNIQUE;

OWNER                          TABLE_NAME                     B
------------------------------ ------------------------------ -
SCOTT                          SALGRADE                       N
SH                             SALES                          N
SH                             COSTS                          N
SH                             SUPPLEMENTARY_DEMOGRAPHICS     N
  5. We should add disabled RELY constraints to the rest of the tables above. Now we're ready for the next step, which is creating the logical standby database.

What just happened?

We've just seen the prerequisite steps to create a logical standby database configuration. The first step was checking the unsupported tables that will not be replicated, in order to be aware which data will be missed on the logical standby and to decide whether to use the logical option or not. The next step is searching for and fixing any table row uniqueness problem, for properly working redo transport and SQL Apply services.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.220.202.209