C Data Type and DDL Support on a Logical Standby Database

C.1 Data Type Considerations

The following sections list the supported and unsupported database objects:

• Supported Data Types in a Logical Standby Database

• Unsupported Data Types in a Logical Standby Database

C.2 Storage Type Considerations

The following sections list the supported and unsupported storage types:

• Support Storage Types

• Unsupported Storage Type

C.3 PL/SQL Supplied Packages Considerations

The following sections list the supported and unsupported PL/SQL supplied packages:

• Supported PL/SQL Supplied Packages

• Unsupported PL/SQL Supplied Packages

C.4 Unsupported Tables, Sequences, and Views

It is important to identify unsupported database objects on the primary database before you create a logical standby database. This is because changes made to unsupported data types, tables, sequences, or views on the primary database will be automatically skipped by SQL Apply on the logical standby database. Moreover, no error message will be returned.

Some schemas that ship with the Oracle database are skipped by SQL Apply. To determine exactly which schemas will be skipped, query the DBA_LOGSTDBY_SKIP view.

SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT = 'INTERNAL SCHEMA';

To determine if the primary database contains unsupported objects, query the DBA_LOGSTDBY_UNSUPPORTED view. See Chapter 16, "Views Relevant to Oracle Data Guard" for more information about views.

For example, use the following query on the primary database to list the schema and table names of primary database tables that are not supported by logical standby databases:

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

OWNER        TABLE_NAME
-----------  --------------------------
HR           COUNTRIES
OE           ORDERS
OE           CUSTOMERS
OE           WAREHOUSES

To view the column names and data types for one of the tables listed in the previous query, use a SELECT statement similar to the following:

SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
  2> WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';

COLUMN_NAME                      DATA_TYPE
-------------------------------  -------------------
CUST_ADDRESS                     CUST_ADDRESS_TYP
PHONE_NUMBERS                    PHONE_LIST_TYP
CUST_GEO_LOCATION                SDO_GEOMETRY

If the primary database contains unsupported tables, SQL Apply automatically excludes these tables when applying redo data to the logical standby database.

C.5 Skipped SQL Statements on a Logical Standby Database

By default, the following SQL statements are automatically skipped by SQL Apply:

All other SQL statements executed on the primary database are applied to the logical standby database.

C.6 DDL Statements Supported by a Logical Standby Database

The following tables list the supported values for the stmt parameter of the DBMS_LOGSTDBY.SKIP procedure and the statement options for skipping SQL DDL statements:

• Table C-1, "Values for stmt Parameter of the DBMS_LOGSTDBY.SKIP procedure"

• Table C-2, "Statement Options for Skipping SQL DDL Statements"

Table C-1 lists the supported values for the stmt parameter of the DBMS_LOGSTDBY.SKIP procedure. The left column of the table lists the keywords that may be used to identify the set of SQL statements to the right of the keyword. Any of the SQL statements in the right column, however, are also valid values. Note that keywords are generally defined by database object.

CREATE CLUSTER
AUDIT CLUSTER
DROP CLUSTER
TRUNCATE CLUSTER

CREATE CONTEXT
DROP CONTEXT

CREATE DATABASE LINK
DROP DATABASE LINK

CREATE DIMENSION
ALTER DIMENSION
DROP DIMENSION

CREATE DIRECTORY
DROP DIRECTORY

CREATE INDEX
ALTER INDEX
DROP INDEX

CREATE FUNCTION
CREATE LIBRARY
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PROCEDURE
DROP FUNCTION
DROP LIBRARY
DROP PACKAGE
DROP PROCEDURE

CREATE PROFILE
ALTER PROFILE
DROP PROFILE

CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK

CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM

CREATE ROLE
ALTER ROLE
DROP ROLE
SET ROLE

CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT

CREATE SEQUENCE
DROP SEQUENCE

Log-ons

CREATE SYNONYM
DROP SYNONYM

AUDIT SQL_statements
NOAUDIT SQL_statements

GRANT system_privileges_and_roles
REVOKE system_privileges_and_roles

CREATE TABLE
DROP TABLE
TRUNCATE TABLE

CREATE TABLESPACE
DROP TABLESPACE
TRUNCATE TABLESPACE

CREATE TRIGGER
ALTER TRIGGER with ENABLE and DISABLE clauses
DROP TRIGGER
ALTER TABLE with ENABLE ALL TRIGGERS clause
ALTER TABLE with DISABLE ALL TRIGGERS clause

CREATE TYPE
CREATE TYPE BODY
ALTER TYPE
DROP TYPE
DROP TYPE BODY

CREATE USER
ALTER USER
DROP USER

CREATE VIEW
DROP VIEW

^Footnote 1 Java schema objects (sources, classes, and resources) are considered the same as procedure for purposes of skipping (ignoring) SQL statements.

Table C-2 lists the statement options for skipping SQL DDL statements.