Data Guard Creating a Logical Standby Database Oracle Database Tips by Donald BurlesonDecember 3, 2015

Creating a Logical Standby Database

The logical Oracle instance is a new feature of Oracle9i Data Guard technology. It has been introduced to cater to the demands of DBAs to have a Oracle instance that can simultaneously exist in query and recovery mode. As the name suggests, a logical Oracle instance is not a physical replica of the primary database in that it is not structurally the same as the primary database.

Instead, it is a complete or partial set of schema objects from the primary database. A logical Oracle instance can contain schema objects of its own that are not present in the primary database.

Creating a Logical Standby Database

Logical standby databases are recovered using the LogMiner technology of Oracle RDBMS. The core of logical standby database recovery is the archived redo log file from the primary database. These log files are converted into SQL statements using LogMiner and are applied to the logical Oracle instance using SQL apply service. Like physical standby databases, logical standby databases can participate in switchover and failover operations.

Because a logical database can be put in read-only and recovery mode simultaneously, data available in a logical Oracle instance is always current.  As a result, a logical Oracle instance is a much better option for reporting than is a physical standby database; however, a logical Oracle instance has many limitations which may outweigh its reporting capabilities in some scenarios.

Limitations of the Logical Standby Database

The following limitations apply to logical standby databases:

A logical Oracle instance does not support LONG, LONG RAW, BFILE, ROWID, UROWID, NCLOB and any of the collection data types. If the application contains any of these types, the usability of a logical Oracle instance in that environment should be evaluated. The script, unsup_objects.sql, from the code depot can be used to find the tables in the database that cannot be supported in a logical standby database.

Also see checking log standby unsupported features

Limitations of the Logical Standby Database

• log_parallelism should be set to 1. What does it mean?  The log_parallelism parameter governs the concurrency of the redo allocation latch. If the database is experiencing contention on the redo allocation latch, set this parameter to a higher value to allow a parallel generation of redo. Usually, this contention is observed on high-end servers in the insert/update intensive databases. If the database is not too huge, and the database is not running on a 16 or more processor machine, this limitation should not be a problem.
   

• There are few DDL statements that will be skipped on a logical Oracle instance during the SQL apply operation. Most of these operations are at system or database level. Skipping these statements should not cause any inconsistency in the application data.

Due to these limitations, it is likely that a logical Oracle instance will not make an attractive option for reporting and an even less attractive option for failover operations. Please note that in order to run a logical standby database in this environment, the database software will have to be upgraded to at least the 9.2.0.2 patchset level.

Requirements for the Logical Standby Database

In order to create a logical standby database, some preparatory work on the primary database has to be completed before it can be used for a logical standby database.

Archive Logging and Force Logging

As explained earlier and in much detail in the "Physical Standby Database" section, the core of the standby database are the archived redo logs from the primary database. The primary database must run in the archive log mode and the FORCE LOGGING should be enabled on the primary database to capture all changes made within the database.

See using the reply constraint

Unique Identification of Rows in Tables

In a sample database, there is a schema called REPORTS, which has two tables without any primary keys or unique indexes. The output from script not_unique.sql reveals this:

OWNER                          TABLE_NAME                     B
------------------------------ ------------------------------ -
REPORTS                        ITEMLIST                       N
REPORTS                        ORDERS                         N

The value 'N' in the Bad_Column column shows that the table can be maintained in a logical Oracle instance even though it does not have a primary key/unique index.

If any of the tables contain a CLOB or BLOB, which is supported data type for a logical standby database, but does not have a primary key/unique index defined, the value of Bad_Column will be 'Y'. This means that a primary key/unique index or a disabled primary key RELY constraint needs to be defined using scalar column, i.e. non CLOB or BLOB columns for this table to be maintained in a logical Oracle instance environment.

The primary database should be put in supplemental logging mode before creating a logical standby database. Use the following statement to enable supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

Unique Identification of Rows and Tables

This can be verified from the v$database view using the following query:

SELECT
   SUPPLEMENTAL_LOG_DATA_PK,
   SUPPLEMENTAL_LOG_DATA_UI
FROM V$DATABASE;

The following sample output shows the supplemental logging is enabled for a primary key and unique index:

SUP SUP
--- ---
YES YES

After enabling the supplemental logging on the primary database, use the alter system command to switch to a redo log file. This is required because a logical Oracle instance does not support archived redo logs that contain redo information with and without supplemental logging.

ALTER SYSTEM SWITCH LOGFILE;