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 datatypes and tables in your primary database.
The following topics describe the various database objects, storage types, and PL/SQL supplied packages that are supported and unsupported by logical standby databases:
C.1 Datatype Considerations
See these topics for information about supported and unsupported database objects.
C.1.1 Supported Datatypes in a Logical Standby Database
These are the datatypes that logical standby databases support.
Logical standby databases support the following datatypes:
-
Abstract Data Types (ADTs) and ADT tables
-
ADTs cannot contain any data types that are not supported as a top-level column type (for example, nested tables, PKREFs, BFILE, unsupported opaque types).
-
For a table with ADT columns to be supported there must be a primary key (or at least a unique constraint or unique index) that consists solely of scalar top-level columns (scalar ADT attributes cannot be part of such a candidate key).
-
-
BINARY_DOUBLE
-
BINARY_FLOAT
-
BLOB
,CLOB
, andNCLOB
stored as BasicFile and SecureFiles. SecureFiles can be compressed, encrypted, or deduplicated. SecureFiles support requires that the primary database be running at a compatibility of 11.2 or higher. See Support for SecureFiles LOBs -
CHAR
-
DATE
-
INTERVAL YEAR TO MONTH
-
INTERVAL DAY TO SECOND
-
LONG
-
LONG RAW
-
NCHAR
-
NUMBER
-
NVARCHAR2
-
Objects stored as
VARRAY
s (except for Collections) -
Oracle Text
-
RAW
-
Multimedia (See exceptions listed in Unsupported Datatypes in a Logical Standby Database.):
-
ORDAudio
-
ORDDataSource
(internal) -
ORDDicom
-
ORDDoc
-
ORDImage
-
ORDSource
(internal) -
ORDVideo
-
-
Spatial (See exceptions listed in Unsupported Datatypes in a Logical Standby Database.)
-
TIMESTAMP
-
TIMESTAMP WITH TIMEZONE
-
TIMESTAMP WITH LOCAL TIMEZONE
-
VARCHAR
andVARCHAR2
-
XMLType
data for all storage models, assuming the following primary database compatibility requirements:-
XMLType
stored inCLOB
format requires that the primary database be run at a compatibility setting of 11.0 or higher (XMLType
stored asCLOB
is deprecated as of Oracle Database 12c Release 1 (12.1).) -
XMLType
stored in object-relational format or as binary XML requires that the primary database be running Oracle Database 11g Release 2 (11.2.0.3) or higher with a redo compatibility setting of 11.2.0.3 or higher
-
Note:
SQL Apply does not support statements that have function calls that perform DML on ADT, LOB
, or XMLType
columns.
Note:
As of Oracle Database 12c Release 1 (12.1), the maximum size of the VARCHAR2
, NVARCHAR2
, and RAW
datatypes has been increased to 32 KB when the COMPATIBLE
initialization parameter is set to 12.0 or later and the MAX_STRING_SIZE
initialization parameter is set to EXTENDED
. Logical standby databases support this increased size in most cases. See "Ensure Table Rows in the Primary Database Can Be Uniquely Identified" for known restrictions.
C.1.1.1 Compatibility Requirements
SQL Apply support for theses features 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. -
TDE support requires primary database to run at a compatibility of 11.1 or higher.
-
Basic compression and advanced row compression require the primary database to run at a compatibility of 11.1 or higher.
-
Hybrid Columnar Compression support is dependent on the underlying storage system.
See Also:
-
Oracle Database Concepts for more information about Hybrid Columnar Compression
C.1.2 Unsupported Datatypes in a Logical Standby Database
Some data types are not supported by logical standby databases.
If a table contains columns having any of the following unsupported data types, then the entire table is ignored by SQL Apply. (See Support for Data Types That Lack Native Redo-Based Support for information about support for data types that lack native redo-based support.)
-
ROWID
,UROWID
Note that only logical
UROWID
columns are supported. Non-logicalUROWID
are detected at run-time, the DML is skipped, and a trace message is written to the report file. -
Nested tables
-
Objects with nested tables
-
Identity columns
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.
Note:
As of Oracle Database 18c, Extended Datatype Support (EDS) is deprecated. All EDS-supported Oracle data types are now supported natively by logical standbys or Oracle GoldenGate.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:
-
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.
-
The wallet must be copied from the primary database to the logical standby database every time the master key is changed.
-
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.
-
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. -
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 theHR.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, theSALARY
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 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.
Note:
In some cases, when SQL Apply mines and applies redo records for changes made to tables in encrypted tablespaces, records of user data in unencrypted form may be kept for a long period of time. If this is not acceptable, then issue the following command to move all metadata tables pertaining to the mining component of SQL Apply to an encrypted tablespace:
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 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.
C.5.1 Row-level Security
Row-Level Security, also known as Virtual Private Database (VPD), is a feature that enforces security at a fine level of granularity, when accessing tables, views, or synonyms.
When a user directly or indirectly accesses a table, view, or synonym protected with a VPD policy, the server dynamically modifies the SQL statement of the user. The modification creates a WHERE
condition (known as a predicate) returned by a function implementing the security policy. The statement is modified dynamically, transparently to the user, using any condition that can be expressed in, or returned by, a function. VPD policies can be applied to SELECT
, INSERT
, UPDATE
, INDEX
, and DELETE
statements. VPD is implemented by using the DBMS_RLS
package to apply security policies.
When a DBMS_RLS
procedure is executed on the primary, additional information is captured in the redo that allows the procedure call to be logically reconstructed and executed on the standby. Logical Standby supports replication of ancillary objects for VPD such as Contexts, Database Logon Triggers, and their supporting packages. You must ensure that these objects are placed in maintained schemas and that no DDL skips have been configured that would stop their replication.
C.5.2 Fine-Grained Auditing
Fine-grained auditing provides a way to audit select statements.
The DBMS_FGA
package enables all select statements that access a table to be captured, together with what data was accessed. An FGA policy may be applied to a particular column or even to only those select statements that return rows for which a specified predicate returns TRUE
.
When a DBMS_FGA
procedure is executed on the primary, additional information is captured to the redo that allows the procedure call to be logically reconstructed and executed on the standby.
C.5.3 Skipping and Enabling PL/SQL Replication
PL/SQL can be configured with skip and skip_error rules exactly as DDL statements except that the use of wildcards on the package and procedure are not supported.
For example to skip all aspects of VPD, do the following:
DBMS_LOGSTDBY.Skip ( stmt => 'PL/SQL', schema_name => 'SYS', object_name =>'DBMS_RLS', use_like => FALSE);
The schema specified is the schema in which the package is defined. To skip an individual procedure in a package, the syntax is as follows:
DBMS_LOGSTDBY.Skip ( stmt => 'PL/SQL', schema_name => 'SYS', object_name =>'DBMS_RLS.ADD_POLICY', use_like => FALSE);
To skip VPD on certain schemas or tables, a skip procedure must be used. The skip procedure is passed the fully qualified PL/SQL statement that is to be executed, for example:
DBMS_RLS.DROP_POLICY( object_schema => 'SCOTT, object_name => 'EMP', policy_name => 'MYPOLICY');
The procedure could then parse the statement to decide whether to skip it, to apply it, or to stop apply and let the DBA take a compensating action.
Unlike DDL, skip procedures on PL/SQL do not support returning a replacement statement.
C.6 Oracle Label Security
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
.
C.7 Oracle Database Vault
Oracle Data Guard rolling upgrades support databases that use Oracle Database Vault.
As of Oracle Database 12c Release 2 (12.2.0.1), you can upgrade databases that use Oracle Database Vault to new Oracle Database releases and patch sets by using Oracle Data Guard database rolling upgrades with a transient logical standby and the PL/SQL package, DBMS_ROLLING
.
C.8 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.
See Also:
The My Oracle Support note 851603.1 at http://support.oracle.com
for additional information about using Logical standby with Oracle E-Business Suite
C.9 Supported Table Storage Types
Logical standby databases support several 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. 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
-
C.10 Unsupported Table Storage Types
If a table contains only these datatypes, then logical standby does not support it.
-
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.1 Unsupported Tables as a Result of Partitioning
Logical standby does not support tables that use system partitioning or reference partitioning.
When possible, the ATTRIBUTES
column of the DBA_LOGSTDBY_UNSUPPORTED
view displays the reason why a table is not supported by SQL Apply. The ATTRIBUTES
column may be NULL if the table structure itself is not supported by SQL Apply (for example, the table is system-partitioned).
C.11 PL/SQL Supplied Packages Considerations
Keep these considerations in mind regarding 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.11.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_TRACE
, DBMS_METADATA
,
DBMS_CRYPTO
.
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 Supported Datatypes in a Logical Standby Database. Examples of such packages are DBMS_LOB
, DBMS_SQL
, and DBMS_TRANSACTION
.
Oracle Data Guard logical standby supports replication of actions performed through the following packages: DBMS_DDL
, DBMS_FGA
, SDO_META
, DBMS_REDACT
, DBMS_REDEFINITION
, DBMS_RLS
, DBMS_SQL_TRANSLATOR
, DBMS_XDS
, DBMS_XMLINDEX
and DBMS_XMLSCHEMA
.
To identify which packages are supported in logical standby, you can query the DBA_LOGSTDBY_PLSQL_SUPPORT
view. For example, you can run the following query to find out which packages are supported in a generic logical standby:
SQL> SELECT OWNER, PKG_NAME FROM DBA_LOGSTDBY_PLSQL_SUPPORT - > where support_level = 'ALWAYS';
To identify which packages are supported in the context of rolling upgrades done using the DBMS_ROLLING
package, you can query the DBA_LOGSTDBY_PLSQL_SUPPORT
view, as follows:
SQL> SELECT OWNER, PKG_NAME FROM DBA_LOGSTDBY_PLSQL_SUPPORT - > where support_level = 'DBMS_ROLLING';
C.11.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
, and DBMS_AQ
.
Additionally, the DBMS_RESOURCE_MANAGER
package is not supported for physical standby rolling upgrades.
C.11.2.1 Support for DBMS_JOB
Specific support for DBMS_JOB
has been provided. Jobs created on the primary database are replicated on the standby database, but are not run as long as the standby maintains its standby role.
In the event of a switchover or failover, jobs scheduled on the original primary database automatically begin running on the new primary database.
You can also create jobs at the logical standby. These jobs only run as long as the logical standby maintains it standby role.
C.11.2.2 Support for DBMS_SCHEDULER
Specific support for DBMS_SCHEDULER
has been provided to allow jobs to be run on a standby database.
A new attribute of a scheduler job has been created in Oracle Database 11g called database_role
whose contents match the database_role
attribute of V$DATABASE
. When a scheduler job is created, it defaults to the local role, so a job created on the standby defaults to a database_role
of LOGICAL STANDBY
. The job scheduler executes only jobs specific to the current role. On switchover or failover, the scheduler automatically switches to running jobs specific to the new role.
Scheduler jobs are not replicated to the standby, except in the context of a rolling upgrade done using the DBMS_ROLLING
PL/SQL package. However, existing jobs can be activated under the new role by using the DBMS_SCHEDULER.Set_Attribute
procedure. Alternatively, jobs that should run in both roles can be cloned and the copy made specific to the other role. The DBA_SCHEDULER_JOB_ROLES
view shows which jobs are specific to which role.
Scheduler jobs obey the database guard when they run on a logical standby database. Thus, to run jobs that need to modify unmaintained tables, set the database guard to STANDBY
. (It is not possible to use the ALTER SESSION DISABLE GUARD
statement inside a PL/SQL block and have it take effect.)
C.11.3 Handling XML and XDB PL/SQL Packages in Logical Standby
Logical Standby supports XMLType
data for all storage models, with some compatibility requirements.
The requirements are as follows:
-
XMLType
stored inCLOB
format requires that the primary database be run at a compatibility setting of 11.0 or higher (XMLType
stored asCLOB
is deprecated as of Oracle Database 12c Release 1 (12.1).) -
XMLType
stored in object-relational format or as binary XML requires that the primary database be running Oracle Database 11g Release 2 (11.2.0.3) or higher with a redo compatibility setting of 11.2.0.3 or higher
There are several PL/SQL packages used in conjunction with XML that are not fully supported.
The PL/SQL packages and procedures that are supported by Logical Standby only modify in-memory structures; they do not modify data stored in the database. These packages do not generate redo and therefore are not replicated to a Logical Standby.
Certain PL/SQL packages and procedures related to XML and XDB that are not supported by Logical Standby, but that require corresponding invocations at the logical standby database for replication activities to continue, are instrumented such that invocations of these procedures at the primary database generate additional redo records indicating procedure invocation. When SQL Apply encounters such redo records, it stops and writes an error message in the DBA_LOGSTDBY_EVENTS
table, indicating the procedure name. This allows the DBA to invoke the corresponding procedure at the logical standby database at the appropriate time so that subsequent redo records generated at the primary database can be applied successfully at the logical standby database. See The DBMS_XMLSCHEMA Schema through Compensating for Ordering Sensitive Unsupported PL/SQL for more information about dealing with these unsupported procedures.
The following packages contain unsupported procedures:
-
DBMS_XMLSCHEMA
(Supported if compatibility is set to 12.0.0 or higher.) -
DBMS_XMLINDEX
In addition to these packages, Logical Standby does not support any modifications to the XDB schema. The objects within the XDB schema are considered to be system metadata and direct modifications to them are not replicated.
Tables managed by the Oracle XML DB Repository, also known as hierarchy-enabled tables, are not supported by Logical Standby. These tables are used to store XML data and can be accessed using the FTP and HTTP protocols, as well as the normal SQL access. For more information on these tables, refer to the Oracle XML DB Developer's Guide.
C.11.3.1 The DBMS_XMLSCHEMA Schema
Certain procedures within the DBMS_XMLSCHEMA
package are unsupported and cannot be replicated by Logical Standby.
Logical Standby stops when it encounters calls to these procedures to provide the user an opportunity to take a compensating action for these calls. Sections Dealing With Unsupported PL/SQL Procedures through Compensating for Ordering Sensitive Unsupported PL/SQL provide more information on the alternatives available for dealing with these unsupported procedures:
-
COPYEVOLVE
-
INPLACEEVOLVE
-
COMPILESCHEMA
The XDB schema is an Oracle-managed schema. Any changes to this schema are automatically skipped by Logical Standby. The following procedure makes changes to the XDB schema which do not get replicated:
-
GENERATEBEAN
C.11.3.2 The DBMS_XMLINDEX Package
All procedures in the DBMS_XMLINDEX
package are supported except for these.
-
DBMS_XMLINDEX.REGISTERPARAMETER
-
DBMS_XMLINDEX.MODIFYPARAMETER
-
DBMS_XMLINDEX.DROPPARAMETER
C.11.3.3 Dealing With Unsupported PL/SQL Procedures
There are a couple options for dealing with unsupported PL/SQL procedures.
The first option is to allow the Logical Standby apply process to stop and to manually perform some compensating action. The second option is to take a preemptive action and to skip the unsupported PL/SQL either by using Logical Standby skip procedures. Each of these options is discussed in the following sections.
C.11.3.4 Manually Compensating for Unsupported PL/SQL
When Logical Standby encounters something that is unsupported, it stops the apply process and records an error in the DBA_LOGSTDBY_EVENTS
table.
You can query this table to determine what action caused the standby to stop and what action, if any, needs to be taken to compensate.
The following example shows a sample of what this query and its output might look like:
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.
Two rows with the same information are returned because Logical Standby automatically retries the failed transaction. The results show that the standby was stopped when a call to DBMS_XMLSCHEMA.REGISTERSCHEMA
was encountered for the xmlplsqlsch2
schema. You can use this information to transfer any needed files from the primary and register the schema on the standby.
Once the schema has been successfully registered on the standby, the apply process on the Logical Standby can be restarted. This must be performed using the SKIP FAILED TRANSACTION
option, for example:
alter database start logical standby apply skip failed transaction'
Logical Standby skips past the offending transaction and continues applying redo from the primary.
The general procedure for manually replicating unsupported PL/SQL follows these steps:
-
Some unsupported PL/SQL is executed on the primary database.
-
The standby database encounters the unsupported PL/SQL and stops Apply.
-
You examine the
DBA_LOGSTDBY_EVENTS
table to determine what caused Apply to stop. -
You execute some compensating actions on the standby for the unsupported PL/SQL.
-
You restart apply on the standby.
C.11.3.5 Compensating for Ordering Sensitive Unsupported PL/SQL
DBMS_XMLSCHEMA.copyEvolve
. The DBMS_XMLSCHEMA.copyEvolve
procedure evolves, or changes, a schema and can modify tables by adding and or removing columns and it can also change whether or not XML documents are valid.The timing of when this procedure should be executed on the Logical Standby is critical. The only time guaranteed to be safe is when apply has stopped on the Logical Standby when it sees that this procedure was executed on the primary database.
Before evolving a schema, it is also important to quiesce any traffic on the primary that may be using the schema. Otherwise, a transaction that is executed close in time to the evolveSchema
on the primary may be executed in a different order on the Logical Standby because the dependency between the two transactions is not apparent to the Logical Standby. Therefore, when ordering sensitive PL/SQL is involved, you should follow these steps:
-
Quiesce changes to dependent tables on the primary.
-
Execute the
CopyEvolve
on the primary. -
Wait for the standby to stop on the
CopyEvolve
PL/SQL. -
Apply the compensating
CopyEvolve
on the standby. -
Restart apply on the standby.
Example C-1 shows a sample of the procedures that could be used to determine how to handle RegisterSchema
calls.
Example C-1 PL/SQL Skip Procedure for RegisterSchema
-- 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.12 Unsupported Tables
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.
Note:
Sorted hash cluster tables are not supported on logical standby databases.
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.
Note:
For the queries shown in this section, if you are working in a multitenant container database (CDB) environment, then many DBA views have analogous CDB views that you should use instead. For example, you would query the CDB_LOGSTDBY_SKIP
view instead of the DBA_LOGSTDBY_SKIP
view.
C.12.1 Unsupported Tables During Rolling Upgrades
Before you perform a rolling upgrade, determine whether any of the tables involved contain data types that are unsupported on logical standby databases.
To do this, you can query either the DBA_LOGSTDBY_UNSUPPORTED
view or the DBA_ROLLING_UNSUPPORTED
view, depending on the type of rolling upgrade being performed.
If you are performing a rolling upgrade using the DBMS_ROLLING
PL/SQL package, as described in Using DBMS_ROLLING to Perform a Rolling Upgrade , then query the DBA_ROLLING_UNSUPPORTED
view.
If you are not using the DBMS_ROLLING
package, but are instead following the manual process outlined in Using SQL Apply to Upgrade the Oracle Database , then query the DBA_LOGSTDBY_UNSUPPORTED
view.
A rolling upgrade performed using DBMS_ROLLING
supports more object types than a manual rolling upgrade operation. For example, only upgrades performed with DBMS_ROLLING
support queue tables. Additionally, a rolling upgrade performed using DBMS_ROLLING
also supports more PL/SQL packages.
See Also:
-
Using SQL Apply to Upgrade the Oracle Database for more information about performing manual rolling upgrades
-
Using DBMS_ROLLING to Perform a Rolling Upgrade for more information about performing rolling upgrades using the
DBMS_ROLLING
PL/SQL package -
Additional PL/SQL Package Support Available Only in the Context of DBMS_ROLLING Upgrades for information about PL/SQL package support available only in the context of
DBMS_ROLLING
upgrades -
Oracle Database PL/SQL Packages and Types Reference for a description of the
DBMS_ROLLING
PL/SQL package -
Oracle Database Reference for complete information about views
C.12.2 Unsupported Tables As a Result of DML Performed In a PL/SQL Function
If, during an insert or update DML operation on a supported table, an out-of-line column (LOB, XMLType, or ADT) is modified through a PL/SQL function and that function in turn performs DML on another table in the course of its execution, then the redo patterns generated are unsupported by LogMiner.
As a result, redo for such a workload cannot be reliably mined using LogMiner.
C.13 Skipped SQL Statements on a Logical Standby Database
By default, certain SQL statements are automatically skipped by SQL Apply.
The affected statements are as follows:
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
PURGE DBA_RECYCLEBIN
PURGE INDEX
SET CONSTRAINTS
SET ROLE
SET TRANSACTION
All other SQL statements executed on the primary database are applied to the logical standby database.
C.14 DDL Statements Supported by a Logical Standby Database
The DBMS_LOGSTDBY.SKIP
procedure has several optional keywords.
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. Keywords are generally defined by database object.
See Also:
Oracle Database PL/SQL Packages and Types Reference for complete information about the DBMS_LOGSTDBY
package and Setting up a Skip Handler for a DDL Statement
Table C-1 Values for stmt Parameter of the DBMS_LOGSTDBY.SKIP procedure
Keyword | Associated SQL Statements |
---|---|
There is no keyword for this group of SQL statements. |
GRANT REVOKE ANALYZE TABLE ANALYZE INDEX ANALYZE CLUSTER |
|
|
|
|
|
|
|
|
|
|
|
Includes DML statements on a table (for example: |
|
|
|
All DDL that does not pertain to a particular schema Note: |
|
|
|
|
|
|
|
|
|
|
|
|
|
All DDL statements that create, modify, or drop schema objects (for example: tables, indexes, and columns) Note: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Footnote 1
Java schema objects (sources, classes, and resources) are considered the same as procedures for purposes of skipping (ignoring) SQL statements.
See Also:
The following sections that provide usage examples of the SKIP
and UNSKIP
options:
C.14.1 DDL Statements that Use DBLINKS
SQL Apply may not correctly apply DDL statements that reference a database link.
An example of such a statement is as follows:
CREATE TABLE tablename AS SELECT * FROM bar@dblink
This is because the dblink
at the logical standby database may not point to the same database as the primary database. If SQL Apply fails while executing such a DDL statement, then use the DBMS_LOGSTDBY.INSTANTIATE_TABLE
procedure for the table being created, and then restart SQL APPLY operations.
C.14.2 Replication of AUD$ and FGA_LOG$ on Logical Standbys
Auditing and fine-grained auditing are supported on logical standbys.
Changes made to the AUD$
and FGA_AUD$
tables at the primary database are replicated at the logical standby.
Both the AUD$
table and the FGA_AUD$
table have a DBID column. If the DBID value is that of the primary database, then the row was replicated to the logical standby based on activities at the primary. If the DBID value is that of the logical standby database, then the row was inserted as a result of local activities at the logical standby.
After the logical standby database assumes the primary role as a result of a role transition (either a switchover or failover), the AUD$
and FGA_AUD$
tables at the new primary (originally the logical standby) and at the new logical standby (originally the primary) are not necessarily synchronized. Therefore, it is possible that not all rows in the AUD$
or FGA_AUD$
tables at the new primary database will be present in the new logical standby database. However, all rows in AUD$
and FGA_LOG$
that were inserted while the database was in a primary role are replicated and present in the logical standby database.
C.15 Distributed Transactions and XA Support
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.
C.16 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.
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.17 Support for Database File System (DBFS)
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.
C.18 Character Set Considerations
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 theCDB$ROOT
and one or more of the CDB’s pluggable databases (PDBs) have different character sets.
C.19 Additional PL/SQL Package Support Available Only in the Context of DBMS_ROLLING Upgrades
Replication of certain packages is available only in the context of rolling upgrades performed using the DBMS_ROLLING
package.
The affected packages are as follows:
-
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
, andDISABLE_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.)
-
See Also:
-
Oracle Database Real Application Security Administrator's and Developer's Guide for more information about the Lightweight Security packages
-
Oracle Database PL/SQL Packages and Types Reference for more information about
DBMS_SCHEDULER
, XDB-related, and DBFS-related packages