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

Part Number B14239-01
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
Feedback

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:


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

C.1.2 Unsupported Data Types in a Logical Standby Database

Logical standby databases do not support the following data types:


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

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:


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

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 not be propagated to the logical standby database. Moreover, no error message will be returned.

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.

C.5 Skipped SQL Statements on a Logical Standby Database

By default, all SQL statements except those in the following list are applied to a logical standby database if they are executed on a primary database:


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

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
NON_SCHEMA_DDL All DDL that does not pertain to a particular schema
SCHEMA_DDL All DDL statements that create, modify, or drop schema objects (for example: tables, indexes, and columns)
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
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
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