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:
The following sections list the supported and unsupported database objects:
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 LOB
s 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)
Logical standby databases do not support the following data types:
BFILE
VARRAYS
and nested tables)ROWID
, UROWID
The following sections list the supported and unsupported storage types:
Logical standby databases support the following storage types:
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 packagesOracle 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
.
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.
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.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.
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"
See Also:
Oracle Database PL/SQL Packages and Types Reference for complete information about theDBMS_LOGSTDBY
package and Section 9.4.4, "Setting up a Skip Handler for a DDL Statement"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 |
---|---|
|
Includes DML statements on a table (for example: |
|
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 |
|
All DDL that does not pertain to a particular schema |
|
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 |
|
All DDL statements that create, modify, or drop schema objects (for example: tables, indexes, and columns) |
|
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.
Table C-2 Statement Options for Skipping SQL DDL Statements
Statement Option | SQL Statements and Operations |
---|---|
|
|
|
|
|
|
|
|
|
Execution of any procedure or function or access to any variable, library, or cursor inside a package. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Any statement containing |
|
|
|
|
See Also:
The following sections that provide usage examples of theSKIP
and UNSKIP
options: