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.
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
RELY
constraint to the ENAME
column of the table:SQL> ALTER TABLE SCOTT.BONUS ADD PRIMARY KEY (ENAME) RELY DISABLE;
Table altered.
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
RELY
constraints to the rest of the tables above. Now we're ready for the next step, which is creating the logical standby database.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.