C Data Type and DDL Support on a Logical Standby Database

C.1 Datatype Considerations

The following sections list the supported and unsupported database objects:

• Supported Datatypes in a Logical Standby Database

• Unsupported Datatypes in a Logical Standby Database

C.2 Support for Data Types That Lack Native Redo-Based Support

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.

C.3 Support for Transparent Data Encryption (TDE)

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.

C.4 Support for Tablespace Encryption

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 Section C.3, "Support for Transparent Data Encryption (TDE)" will apply.

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

C.5 Support For Row-level Security and Fine-Grained Auditing

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 will transparently be 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 will not have its data maintained by Logical Standby, in which case DBMS_RLS and DBMS_FGA calls referencing that table will also not be maintained.

DBMS_LOGSTDBY.Skip (
stmt => 'PL/SQL',
schema_name => 'SYS',
object_name =>'DBMS_RLS',
use_like => FALSE);

DBMS_LOGSTDBY.Skip (
stmt => 'PL/SQL',
schema_name => 'SYS',
object_name =>'DBMS_RLS.ADD_POLICY',
use_like => FALSE);

DBMS_RLS.DROP_POLICY(
object_schema => 'SCOTT, 
object_name  => 'EMP',
policy_name => 'MYPOLICY');

C.6 Oracle Label Security

Logical standby databases do not support Oracle Label Security. If Oracle Label Security is installed on the primary database, SQL Apply fails on the logical standby database with an internal error during startup.

C.7 Oracle E-Business Suite

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.

C.8 Supported Table Storage Types

Logical standby databases support the following table 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).

• Advanced row compression and basic table compression. Both of these options require that the compatibility setting of the primary database be set to 11.1.0 or higher.

• Tables containing LOB columns stored as SecureFiles, when compatibility is set to 11.2 or higher.

• Tables using Hybrid Columnar Compression, when compatibility is set to 11.2.0.2 or higher.

  See Also:

  • Oracle Database Concepts for more information about Hybrid Columnar Compression

• Tables with virtual columns (provided the table has no other columns or properties not supported by logical standby)

• If there is no primary key and no non-null unique constraint or index, then all columns with a declared maximum length of 4000 bytes are logged as part of the UPDATE statement to help identify the modified row. Note that for the purpose of row identification, logical standby requires that a table have at least one visible (not virtual) column of one of the following datatypes:

  • CHAR

  • VARCHAR

  • VARCHAR2 (with a declared column length <= 4000 bytes)

  • NVARCHAR

  • NVARCHAR2 (with a declared column length <= 4000 bytes)

  • NUMBER

  • DATE

  • RAW

  • BINARY FLOAT

  • BINARY DOUBLE

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

  • INTERVAL YEAR TO MONTH

  • INTERVAL DAY TO SECOND

  See Also:

  • Section 4.1.2, "Ensure Table Rows in the Primary Database Can Be Uniquely Identified"

C.9 Unsupported Table Storage Types

Logical standby does not support tables that contain only the following datatypes:

• LOB (CLOB, NCLOB, BLOB)

• LONG

• LONG RAW

• OBJECT TYPE

• COLLECTIONS

• XML

• VARCHAR2 (with a declared column length > 4000 bytes)

• NVARCHAR2 (with a declared column length > 4000 bytes)

• RAW (with a declared column length > 4000 bytes)

C.10 PL/SQL Supplied Packages Considerations

This section discusses the following considerations regarding PL/SQL supplied packages:

• Supported PL/SQL Supplied Packages

• Unsupported PL/SQL Supplied Packages

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

SQL> SELECT OWNER, PKG_NAME FROM DBA_LOGSTDBY_PLSQL_SUPPORT -
> where support_level = 'ALWAYS';

SQL> SELECT OWNER, PKG_NAME FROM DBA_LOGSTDBY_PLSQL_SUPPORT -
> where support_level = 'DBMS_ROLLING';

select status, event from dba_logstdby_events
          where commit_scn >= (select applied_scn from dba_logstdby_progress) and
          status_code = 16265
          order by commit_scn desc;
 
STATUS
--------------------------------------------------------------------------------
EVENT
--------------------------------------------------------------------------------
ORA-16265: Unsupported PL/SQL procedure encountered
begin
"XDB"."DBMS_XMLSCHEMA"."REGISTERPARAMETER" (
   "NAME" => 'myIndexParam',
 "PARAMETER" => 'PATH TABLE 
 
ORA-16265: Unsupported PL/SQL procedure encountered
begin
"XDB"."DBMS_XMLSCHEMA"."REGISTERPARAMETER" (
   "NAME" => 'myIndexParam',
 "PARAMETER" => 'PATH TABLE 
  
2 rows selected.

alter database start logical standby apply skip failed transaction'

-- Procedures to determine how to handle registerSchema calls
 
-- This procedure extracts the schema URL, or name, from the statement
-- string that is passed into the skip procedure.
 
Create or replace procedure sec_mgr.parse_schema_str(
  statement             in varchar2,
  schema_name      out varchar2)
Is
  pos1 number;
  pos2 number;
  workingstr   varchar2(32767);
Begin
 
-- Find the correct argument
pos1 := instr(statement, '"SCHEMAURL" => ''');
workingstr := substr(statement, pos1 + 16);
 
-- Find the end of the schema name
pos1 := instr(workingstr, '''');
 
-- Get just the schema name
workingstr := substr(workingstr, 1, pos1 - 1);
 
schema_name := workingstr;
 
End parse_schema_str;
/
show errors
 
 
-- This procedure checks if a schema is already registered. If so,
-- it returns the value DBMS_LOGSTDBY.SKIP_ACTION_SKIP to indicate that
-- the PL/SQL should be skipped. Otherwise, the value 
-- DBMS_LOGSTDBY.SKIP_ACTION_SKIP is returned and Logical Standby apply 
-- will halt to allow the DBA to deal with the registerSchema call.
 
Create or replace procedure sec_mgr.skip_registerschema(
  statement             in varchar2,
  package_owner            in varchar2,
  package_name             in varchar2,
  procedure_name                 in varchar2,
  current_user                   in varchar2,
  xidusn                in number,
  xidslt                in number,
  xidsqn                in number, 
  exit_status            in number, 
  skip_action            out number)
Is
  schema_exists number;
  schemastr varchar2(2000);
Begin
 
  skip_action := DBMS_LOGSTDBY.SKIP_ACTION_SKIP;
 
  -- get the schame name from statement
  parse_schema_str(statement, schemastr);
 
  -- see if the schema is already registered
  select count(*) into schema_exists from sys.all_xml_schemas s 
                                     where s.schema_url = schemastr and
                                           s.owner = current_user;
 
  IF schema_exists = 0 THEN
      -- if the schema is not  registered, then we must stop apply
      skip_action := DBMS_LOGSTDBY.SKIP_ACTION_APPLY;     
  ELSE
      -- if the schema is already registered, then we can skip this statement
      skip_action := DBMS_LOGSTDBY.SKIP_ACTION_SKIP;     
  END IF;
 
End skip_registerschema;
/
show errors
 
-- Register the skip procedure to deal with the unsupported registerSchema 
-- PL/SQL.
Begin
   sys.dbms_logstdby.skip(stmt => 'PL/SQL', 
        schema_name => 'XDB', 
                                        object_name   => 'DBMS_XMLSCHEMA.REGISTERSCHEMA', 
                                        proc_name     => 'SEC_MGR.SKIP_REGISTERSCHEMA',
        use_like         => FALSE );
                  End;
    /
show errors

C.11 Unsupported Tables

It is important to identify unsupported database objects on the primary database before you create a logical standby database because changes made to unsupported data types and tables on the primary database will be automatically skipped by SQL Apply on the logical standby database. Moreover, no error message will be 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 will be implicitly maintained by SQL Apply. However, if you put a user-defined table in SYSTEM, it will not be 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 will return all schemas that are considered to be internal. User tables placed in these schemas will not be replicated on a logical standby database and will not show up in the DBA_LOGSTDBY_UNSUPPORTED view. Tables in these schemas that are created by Oracle will be 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 will not be 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.

C.12 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.13 DDL Statements Supported by a Logical Standby Database

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. In addition, any of the SQL statements listed in the sys.audit_actions table (shown in the right column of Table 1-13) are also valid values. Note that keywords are generally defined by database object.

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.

CREATE TABLE tablename AS SELECT * FROM bar@dblink

C.14 Distributed Transactions and XA Support

You can perform distributed transactions using either of the following 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.

C.15 Support for SecureFiles LOBs

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. Fragment operations are only supported via Extended Datatype Support (EDS).

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.

C.16 Support for Database File System (DBFS)

Logical standby supports the Database File System (DBFS). 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. See Oracle Database SecureFiles and Large Objects Developer's Guide for more information about DBFS.

C.17 Character Set Considerations

Configurations are not supported in which the primary database and standby database have different character sets.

C.18 Additional PL/SQL Package Support Available Only in the Context of DBMS_ROLLING Upgrades

Replication of the following packages is available only in the context of rolling upgrades performed using the DBMS_ROLLING package:

• DBFS

  • DBMS_DBFS_CONTENT_ADMIN

  • DBMS_DBFS_SFS

  • DBMS_DBFS_SFS_ADMIN

• Lightweight Security

  • XS_ACL

  • XS_DATA_SECURITY

  • XS_NAMESPACE

  • XS_PRINCIPAL

  • XS_ROLESET

  • XS_SECURITY_CLASS

• Oracle Streams Advanced Queuing (AQ)

  • DBMS_AQ

  • DBMS_AQJMS

  • DBMS_AQADM (except for the following procedures: SCHEDULE_PROPAGATION, RECOVER_PROPAGATION, UNSCHEDULE_PROPAGATION, ALTER_PROPAGATION_SCHEDULE, ENABLE_PROPAGATION_SCHEDULE, and DISABLE_PROPAGATION_SCHEDULE)

• Oracle Text

  • CTX_ADM

  • CTX_ANL

  • CTX_CLS

  • CTX_DDL

  • CTX_DOC

  • CTX_ENTITY

  • CTX_OUTPUT

  • CTX_QUERY

  • CTX_THES

  • CTX_TREE

• Scheduler

  • DBMS_SCHEDULER

• XDB-related

  • DBMS_RESCONFIG

  • DBMS_XDB_CONFIG (Certain procedures are not supported. See Oracle XML DB Developer's Guide for more information.)

  • DBMS_XDB_REPOS

  • DBMS_XDBRESOURCE

  • DBMS_XDB_VERSION

  • DBMS_XDBZ (Certain procedures are not supported. See Oracle XML DB Developer's Guide for more information.)