Time for action – checking for the unsupported data types
In order to be aware of what will and will not be replicated, we should check which primary database tables are not supported for the logical standby database.
- Run the following query on the primary database to see the unsupported table names:
SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED_TABLE ORDER BY OWNER,TABLE_NAME; OWNER TABLE_NAME ---------- ------------------------------ IX AQ$_ORDERS_QUEUETABLE_G IX AQ$_ORDERS_QUEUETABLE_H IX AQ$_ORDERS_QUEUETABLE_I IX AQ$_ORDERS_QUEUETABLE_L IX AQ$_ORDERS_QUEUETABLE_S IX AQ$_ORDERS_QUEUETABLE_T IX AQ$_STREAMS_QUEUE_TABLE_C IX AQ$_STREAMS_QUEUE_TABLE_G IX AQ$_STREAMS_QUEUE_TABLE_H IX AQ$_STREAMS_QUEUE_TABLE_I IX AQ$_STREAMS_QUEUE_TABLE_L IX AQ$_STREAMS_QUEUE_TABLE_S IX AQ$_STREAMS_QUEUE_TABLE_T IX ORDERS_QUEUETABLE IX STREAMS_QUEUE_TABLE OE CATEGORIES_TAB OE CUSTOMERS OE PURCHASEORDER OE WAREHOUSES PM ONLINE_MEDIA PM PRINT_MEDIA SH DIMENSION_EXCEPTIONS 22 rows selected.
As mentioned earlier, we use a newly created 11g release 2 database, which only includes built-in example schemas. The unsupported tables are from the
IX,OE,PM, andSHschemas. Now let's check the reasons for which these tables are on the unsupported list. - Run the following query for one of the unsupported tables to check the reason. We're now running
STREAMS_QUEUE_TABLEunder theIXschema:SQL> SELECT DISTINCT(ATTRIBUTES) FROM DBA_LOGSTDBY_UNSUPPORTED WHERE OWNER='IX' and TABLE_NAME = 'STREAMS_QUEUE_TABLE'; ATTRIBUTES ----------------- AQ queue table
We've only queried the
ATTRIBUTEScolumn of theDBA_LOGSTDBY_ UNSUPPORTEDview for a specific table name. TheATTRIBUTEScolumn displays the reason the table is not supported by SQL Apply. If the structure of the table is unsupported, theATTRIBUTEScolumn will show the description for that. In the example we can see thatSTREAMS_QUEUE_TABLEis unsupported because it is anAQ queue table. - If the structure of the table is supported but some columns in the table have unsupported data types, the
ATTRIBUTEcolumn will beNULL. Let's check which columns of which tables haveATTRIBUTEvalueNULL, in other words which tables have unsupported data types on specific columns.SQL> SELECT OWNER, TABLE_NAME, COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED WHERE ATTRIBUTES IS NULL; OWNER TABLE_NAME COLUMN_NAME DATA_TYPE ----- ---------------------- ------------------------ --------- PM ONLINE_MEDIA PRODUCT_PHOTO_SIGNATURE OBJECT PM ONLINE_MEDIA PRODUCT_THUMBNAIL OBJECT PM ONLINE_MEDIA PRODUCT_VIDEO OBJECT PM ONLINE_MEDIA PRODUCT_AUDIO OBJECT PM ONLINE_MEDIA PRODUCT_TESTIMONIALS OBJECT PM ONLINE_MEDIA PRODUCT_PHOTO OBJECT PM PRINT_MEDIA AD_HEADER OBJECT PM PRINT_MEDIA AD_GRAPHIC BFILE OE CUSTOMERS CUST_ADDRESS OBJECT OE CUSTOMERS PHONE_NUMBERS VARRAY OE CUSTOMERS CUST_GEO_LOCATION OBJECT OE WAREHOUSES WH_GEO_LOCATION OBJECT SH DIMENSION_EXCEPTIONS BAD_ROWID ROWID 13 rows selected.
We can see that 5 tables have unsupported columns and will be ignored by SQL Apply like the others, because of their table structure.
Tip
Keep in mind that the changes on the unsupported tables will still be sent by the redo transport service; however, SQL Apply will ignore the changes on the unsupported tables. Another point is the unsupported tables will exist on the logical standby database, because a logical standby is converted from a physical standby database, which is an exact copy of the primary. These tables will exist but will not be updated by SQL Apply on the logical standby database.
What just happened?
We've seen how to query unsupported data for logical standby in the existing database. This information is important in the decision of using logical standby databases.
Now let's search for any table row uniqueness problem in the primary database and how to fix the issue if it exists.