Prior to that, we need to create the Physical Standby.

Logical Standby Database : SQL Apply Architecture

We need to perform following steps before creating Logical Standby Database :

1. Primary Database must be in Archive log mode.

Check your database is in archive log mode or not. If not enable it for archive log.

2. Unsupported Objects :

We should be aware of non-supported objects in the Logical Standby database.

Following are the list of Unsupported objects in the Logical Standby database.

  1. Tables and sequences in SYS schema.
  2. Tables used to support materialized view.
  3. Global temporary tables.
  4. Tables with unsupported data types.

If your primary database contains any of the above objects then log apply service automatically excludes these objects while applying redo to the logical standby database.

Now, See few examples of unsupported data types  :

  1. BFILE, ROWID, and UROWID
  2. User-defined types
  3. Multimedia data types (Spatial, Image and Oracle Text)

We can view unsupported tables from dba_logstdby_unsupported_tables.

A result of above query contains the list of tables which are not supported by log apply process.

To view the list of unsupported data types, we can query dba_logstdby_unsupported table.

It contains the list of data types with object name which is unsupported in a logical standby environment.

3. Unsupported DDL commands :

We need to be aware of DDL command that does not execute on a logical standby database.

Below is the list of few examples of DDL commands that do not execute on Logical Standby Database with log apply process :

4. Unsupported PLSQL Supplied Packages :

PLSQL supplied packages which modifies metadata are not supported by SQL apply process. Oracle PLSQL supplied packages which do not modify system metadata but may modify user data are supported by SQL apply process if they do not contain any unsupported data types listed in the 3rd section.For eg: DBMS_LOB, DBMS_SQL

Below is List of few examples of Unsupported PL/SQL supplied packages include :

4: Ensuring Unique Row Identifiers :

Primary database and standby database rowid might not be the same so we need to apply the different mechanism to ensure efficient SQL apply process for data updates to the logical standby database.

And Primary key or Unique key works efficiently with this.

We can query a dba_logstdby_not_unique table to check tables that do not contain any primary or unique key.

Now we are all set to create Logical Standby Database. In next article, we will see a creation of Logical Standby Database.

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.