Skip Headers
Oracle® Data Guard Concepts and Administration
10g Release 2 (10.2)

Part Number B14239-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

C Data Type and DDL Support on a Logical Standby Database

When setting up a logical standby database, you must ensure the logical standby database can maintain the data types and tables in your primary database. This appendix lists the various database objects, storage types, and PL/SQL supplied packages that are supported and unsupported by logical standby databases. It contains the following topics:

C.1 Data Type Considerations

The following sections list the supported and unsupported database objects:

C.1.1 Supported Data Types in a Logical Standby Database

Logical standby databases support the following data types:


BINARY_DOUBLE
BINARY_FLOAT
BLOB
CHAR
CLOB and NCLOB
DATE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
LONG
LONG RAW
NCHAR
NUMBER
NVARCHAR2
RAW
TIMESTAMP
TIMESTAMP WITH LOCAL TIMEZONE
TIMESTAMP WITH TIMEZONE
VARCHAR2 and VARCHAR

Note:

SQL Apply support for the following data types has compatibility requirements on the primary database:
  • Multibyte CLOB support (requires primary database to run at a compatibility of 10.1 or higher).

  • IOT support without LOBs and Overflows (requires primary database to run at a compatibility of 10.1 or higher);

  • IOT support with LOB and Overflow (requires primary database to run at a compatibility of 10.2 or higher)

C.1.2 Unsupported Data Types in a Logical Standby Database

Logical standby databases do not support the following data types:


BFILE
Collections (including VARRAYS and nested tables)
Encrypted columns
Multimedia data types (including Spatial, Image, and Context)
ROWID, UROWID
User-defined types
XMLType

C.2 Storage Type Considerations

The following sections list the supported and unsupported storage types:

C.2.1 Support Storage Types

Logical standby databases support the following storage types:


Cluster tables (including index clusters and heap clusters)
Index-organized tables (partitioned and nonpartitioned, including overflow segments)
Heap-organized tables (partitioned and nonpartitioned

C.2.2 Unsupported Storage Type

Logical standby databases do not support the segment compression storage type.

C.3 PL/SQL Supplied Packages Considerations

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

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about Oracle PL/SQL supplied packages

C.3.1 Supported PL/SQL Supplied Packages

Oracle PL/SQL supplied packages that do not modify system metadata or user data leave no footprint in the archived redo log files, and hence are safe to use on the primary database. Examples of such packages are DBMS_OUTPUT, DBMS_RANDOM, DBMS_PIPE, DBMS_DESCRIBE, DBMS_OBFUSCATION_TOOLKIT, DBMS_TRACE, and DBMS_METADATA.

Oracle PL/SQL supplied packages that do not modify system metadata but may modify user data are supported by SQL Apply, as long as the modified data belongs to the supported data types listed in Section C.1.1. Examples of such packages are DBMS_LOB, DBMS_SQL, and DBMS_TRANSACTION.

C.3.2 Unsupported PL/SQL Supplied Packages

Oracle PL/SQL supplied packages that modify system metadata typically are not supported by SQL Apply, and therefore their effects are not visible on the logical standby database. Examples of such packages are DBMS_JAVA, DBMS_REGISTRY, DBMS_ALERT, DBMS_SPACE_ADMIN, DBMS_REFRESH, DBMS_REDEFINITION, DBMS_SCHEDULER, and DBMS_AQ.

Specific support for DBMS_JOB has been provided. Job execution is suspended on a logical standby database and jobs cannot be scheduled directly on the standby database. However, jobs submitted on the primary database are replicated in the standby database. In the event of a switchover or failover, jobs scheduled on the original primary database will automatically begin running on the new primary database.

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.

Note:

If you determine that the critical tables in your primary database will not be supported on a logical standby database, then you might want to consider using a physical standby database. Physical standby databases do not have any such data type restrictions.

C.5 Skipped SQL Statements on a Logical Standby Database

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


ALTER DATABASE
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW LOG
ALTER SESSION
ALTER SYSTEM
CREATE CONTROL FILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE PFILE FROM SPFILE
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG
CREATE SCHEMA AUTHORIZATION
CREATE SPFILE FROM PFILE
DROP DATABASE LINK
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW LOG
EXPLAIN
LOCK TABLE
SET CONSTRAINTS
SET ROLE
SET TRANSACTION

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 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.

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

Keyword Associated SQL Statements

DML

Includes DML statements on a table (for example: INSERT, UPDATE, and DELETE)

CLUSTER

CREATE CLUSTER
AUDIT CLUSTER
DROP CLUSTER
TRUNCATE CLUSTER

CONTEXT

CREATE CONTEXT
DROP CONTEXT

DATABASE LINK

CREATE DATABASE LINK
DROP DATABASE LINK

DIMENSION

CREATE DIMENSION
ALTER DIMENSION
DROP DIMENSION

DIRECTORY

CREATE DIRECTORY
DROP DIRECTORY

INDEX

CREATE INDEX
ALTER INDEX
DROP INDEX

NON_SCHEMA_DDL

All DDL that does not pertain to a particular schema

PROCEDUREFoot 1 

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

PROFILE

CREATE PROFILE
ALTER PROFILE
DROP PROFILE

PUBLIC DATABASE LINK

CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK

PUBLIC SYNONYM

CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM

ROLE

CREATE ROLE
ALTER ROLE
DROP ROLE
SET ROLE

ROLLBACK STATEMENT

CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT

SCHEMA_DDL

All DDL statements that create, modify, or drop schema objects (for example: tables, indexes, and columns)

SEQUENCE

CREATE SEQUENCE
DROP SEQUENCE

SESSION

Log-ons

SYNONYM

CREATE SYNONYM
DROP SYNONYM

SYSTEM AUDIT

AUDIT SQL_statements
NOAUDIT SQL_statements

SYSTEM GRANT

GRANT system_privileges_and_roles
REVOKE system_privileges_and_roles

TABLE

CREATE TABLE
DROP TABLE
TRUNCATE TABLE

TABLESPACE

CREATE TABLESPACE
DROP TABLESPACE
TRUNCATE TABLESPACE

TRIGGER

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

TYPE

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

USER

CREATE USER
ALTER USER
DROP USER

VIEW

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.

Table C-2 Statement Options for Skipping SQL DDL Statements

Statement Option SQL Statements and Operations

ALTER SEQUENCE

ALTER SEQUENCE

ALTER TABLE

ALTER TABLE

COMMENT TABLE

COMMENT ON TABLE table, view, materialized view

COMMENT ON COLUMN table.column, view.column, materialized_view.column

DELETE TABLE

DELETE FROM table, view

EXECUTE PROCEDURE

CALL

Execution of any procedure or function or access to any variable, library, or cursor inside a package.

GRANT DIRECTORY

GRANT privilege ON directory

REVOKE privilege ON directory

GRANT PROCEDURE

GRANT privilege ON procedure, function, package

REVOKE privilege ON procedure, function, package

GRANT SEQUENCE

GRANT privilege ON sequence

REVOKE privilege ON sequence

GRANT TABLE

GRANT privilege ON table, view, materialized view

REVOKE privilege ON table, view, materialized view

GRANT TYPE

GRANT privilege ON TYPE

REVOKE privilege ON TYPE

INSERT TABLE

INSERT INTO table, view

LOCK TABLE

LOCK TABLE table, view

SELECT SEQUENCE

Any statement containing sequence.CURRVAL or

SELECT TABLE

SELECT FROM table, view, materialized view

REVOKE privilege ON table, view, materialized view

UPDATE TABLE

UPDATE table, view