See these topics for information about supported and unsupported database objects.

• Supported Datatypes in a Logical Standby Database

• Unsupported Datatypes in a Logical Standby Database

The Extended Datatype Support (EDS) feature provides a mechanism for logical standbys to support certain data types that lack native redo-based support.

For example, tables with SDO_GEOMETRY columns can be replicated using EDS. (Source tables must have a primary key.)

You can query the DBA_LOGSTDBY_EDS_SUPPORTED view to find out which tables are candidates for EDS.

Oracle Data Guard SQL Apply can be used to provide data protection for a primary database with Transparent Data Encryption (TDE) enabled.

Consider the following when using a logical standby database to provide data protection for applications with advanced security requirements:

• Tables with Transparent Data Encryption using server held keys are replicated on a logical standby database when both the primary and the standby databases are running at a compatibility level of 11.1 or higher.

• Transparent Data Encryption in the context of Hardware Security Modules is supported for logical standby databases in Oracle Database 11g Release 2 (11.2) and later.

You must consider the following restrictions when, in the context of a logical standby database, you want to replicate tables that have encrypted columns:

1. To translate encrypted redo records, SQL Apply must have access to an open wallet containing the Transparent Data Encryption keys. Therefore, you must copy the wallet containing the keys from the primary database to the standby database after it has been created.

2. The wallet must be copied from the primary database to the logical standby database every time the master key is changed.

3. Oracle recommends that you not rekey the master key at the logical standby database while the logical standby database is replicating encrypted tables from the primary database. Doing so may cause SQL Apply to halt when it encounters an encrypted redo record.

4. You can rekey the encryption key of a replicated table at the logical standby database. This requires that you lower the guard setting to NONE before you issue the rekey command.

5. Replicated encrypted tables can use a different encryption scheme for columns than the one used in the primary database. For example, if the SALARY column of the HR.EMPLOYEES table is encrypted at the primary database using the AES192 encryption algorithm, it can be encrypted at the logical standby using the AES256 encryption algorithm. Or, the SALARY column can remain unencrypted at the logical standby database.

Oracle Data Guard SQL Apply can be used to provide data protection for a primary database that has tablespace encryption enabled.

In such a case, restrictions 1, 2, and 3 listed in Support for Transparent Data Encryption (TDE) apply.

Encryption, re-keying, or decryption of a tablespace on a primary does not trigger the need for the same operation on a logical standby. However, a logical standby must have the capability of re-keying as well.

SQL> DBMS_LOGMNR_D.SET_TABLESPACE(NEW_TABLESPACE => 'ENCRYPTED_LOGMNR_TS'); 

As of Oracle Database 11g, logical standby can automatically replicate the security environment provided through the DBMS_RLS and DBMS_FGA PL/SQL packages.

This support simplifies management of security considerations when a server fails over to the standby since the security environment is transparently maintained. It also ensures that access control policies applied to the primary data can be automatically forwarded to the standby, and the standby data transparently given the same level of protection. If a standby server is newly created with 11g, this replication is enabled by default; otherwise it has to be enabled by the DBA at an appropriate time.

Support for the replication of these PL/SQL packages requires that both the primary and the standby be running with a compatibility setting of 11.1 or higher.

It also requires that the table referenced be a Logical Standby maintained object. For example, a table with a rowid column does not have its data maintained by Logical Standby, so DBMS_RLS and DBMS_FGA calls referencing that table are not maintained.

As of Oracle Database 12c Release 2 (12.2), you can upgrade databases that use Oracle Label Security (OLS) to new Oracle Database releases and patch sets using Oracle Data Guard database rolling upgrades with a transient logical standby database and the PL/SQL package, DBMS_ROLLING.

Logical standby databases do not fully support an Oracle E-Business Suite implementation because there are tables that contain unsupported data types.

However, using SKIP rules, it is possible for you to replicate a subset of the E-Business Suite schemas and tables to offload applications to the logical standby.

Logical standby databases support several table storage types.

If a table contains only these datatypes, then logical standby does not support it.

Keep these considerations in mind regarding supported and unsupported PL/SQL supplied packages.

• Supported PL/SQL Supplied Packages

• Unsupported PL/SQL Supplied Packages

• Handling XML and XDB PL/SQL Packages in Logical Standby

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 and tables on the primary database are automatically skipped by SQL Apply on the logical standby database. Moreover, no error message is returned.

There are three types of objects on a database, from the perspective of logical standby support:

• Objects that are explicitly maintained by SQL Apply

• Objects that are implicitly maintained by SQL Apply

• Objects that are not maintained by SQL Apply

Some schemas that ship with the Oracle database (for example, SYSTEM) contain objects that are implicitly maintained by SQL Apply. However, if you put a user-defined table in SYSTEM, then it is not maintained even if it has columns of supported data types. To discover which objects are not maintained by SQL Apply, you must run two queries. The first query is as follows:

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

This returns all schemas that are considered to be internal. User tables placed in these schemas are not replicated on a logical standby database and do not show up in the DBA_LOGSTDBY_UNSUPPORTED view. Tables in these schemas that are created by Oracle are maintained on a logical standby, if the feature implemented in the schema is supported in the context of logical standby.

The second query you must run is as follows. It returns tables that do not belong to internal schemas and are not maintained by SQL Apply because of unsupported data types:

SQL> SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED -
> 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 -
> 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.

By default, certain SQL statements are automatically skipped by SQL Apply.

The DBMS_LOGSTDBY.SKIP procedure has several optional keywords.

GRANT
REVOKE
ANALYZE TABLE
ANALYZE INDEX
ANALYZE CLUSTER

AUDIT CLUSTER
CREATE CLUSTER
DROP CLUSTER
TRUNCATE CLUSTER

CREATE CONTEXT
DROP CONTEXT

CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP DATABASE LINK
DROP PUBLIC DATABASE LINK

ALTER DIMENSION
CREATE DIMENSION
DROP DIMENSION

CREATE DIRECTORY
DROP DIRECTORY

ALTER INDEX
CREATE INDEX
DROP INDEX

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

ALTER PROFILE
CREATE PROFILE
DROP PROFILE

CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK

CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM

ALTER ROLE
CREATE ROLE
DROP ROLE
SET ROLE

ALTER ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT

ALTER SEQUENCE
CREATE SEQUENCE
DROP SEQUENCE

CREATE PUBLIC SYNONYM
CREATE SYNONYM
DROP PUBLIC SYNONYM
DROP SYNONYM

AUDIT SQL_statements
NOAUDIT SQL_statements

CREATE TABLE
ALTER TABLE
DROP TABLE
TRUNCATE TABLE

CREATE TABLESPACE
DROP TABLESPACE
ALTER TABLESPACE

ALTER TRIGGER
CREATE TRIGGER
DISABLE ALL TRIGGERS
DISABLE TRIGGER
DROP TRIGGER
ENABLE ALL TRIGGERS
ENABLE TRIGGER

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

ALTER USER
CREATE USER
DROP USER

CREATE VIEW
DROP VIEW

^Footnote 1

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

You can perform distributed transactions using a couple different methods.

• Modify tables in multiple databases in a coordinated manner using database links.

• Use the XA interface, as exposed by the DBMS_XA package in supplied PL/SQL packages or via OCI or JDBC libraries. The XA interface implements X/Open Distributed Transaction Processing (DTP) architecture.

Changes made to the primary database during a distributed transaction using either of these two methods are replicated to the logical standby database.

However, the distributed transaction state is not replicated. The logical standby database does not inherit the in-doubt or prepared state of such a transaction, and it does not replicate the changes using the same global transaction identifier used at the primary database for the XA transactions. As a result, if you fail over to a logical standby database before committing a distributed transaction, the changes are rolled back at the logical standby. This rollback occurs even if the distributed transaction on the primary database is in a prepared state and has successfully completed the first phase of the two-phased commit protocol. Switchover operations wait for all active distributed transactions to complete, and are not affected by this restriction.

XA transactions can be performed in two ways:

• tightly coupled, where different XA branches share locks

• loosely coupled, where different XA branches do not share locks

Replication of changes made by loosely coupled XA branches is supported regardless of the COMPATIBLE parameter value. Replication of changes made by tightly coupled branches on an Oracle RAC primary (introduced in 11g Release 1) is supported only with COMPATIBLE=11.2 or higher.

SecureFiles LOBs are supported when the database compatibility level is set to 11.2 or higher.

Transparent Data Encryption and data compression can be enabled on SecureFiles LOB columns at the primary database.

Deduplication of SecureFiles LOB columns and SecureFiles Database File System (DBFS) operations are fully supported.

If SQL Apply encounters redo generated by unsupported operations, it stops with an ORA-16211: Unsupported record found in the archived redo log error. To continue, add a skip rule for the affected table using DBMS_LOGSTDBY.SKIP and restart SQL Apply.

The Database File System (DBFS) creates a standard file system interface on top of files and directories that are stored in database tables, which makes it easier for you to access and manage files stored in the database.

Logical standby supports the Database File System (DBFS). See Oracle Database SecureFiles and Large Objects Developer's Guide for more information about DBFS.

There are considerations to keep in mind regarding character sets.

• It is not supported to have a Data Guard configuration in which the primary database and logical standby database have different character sets.

• Configurations in which a multitenant container database (CDB) has a mixed character set are only supported when using DBMS_ROLLING for rolling upgrades. A mixed character set means that the CDB$ROOT and one or more of the CDB’s pluggable databases (PDBs) have different character sets.

Replication of certain packages is available only in the context of rolling upgrades performed using the DBMS_ROLLING package.