OracleDG logical Standby creation instructions

Because logical Standby maintains synchronization with the Primary database through SQL applications. There is a big difference between SQL applications and REDO applications. REDO applications are actually on the physical Standby side.

Because logical Standby maintains synchronization with the Primary database through SQL applications. There is a big difference between SQL applications and REDO applications. REDO applications are actually on the physical Standby side.

I. Logical Standby preparation

1. Check whether the operation objects and statements are supported by logical Standby.

Because logical Standby maintains synchronization with the Primary database through SQL applications. There is a big difference between SQL applications and REDO applications. REDO applications actually perform RECOVER on the physical Standby side, while SQL applications analyze REDO information in REDO log files, it is converted to an SQL statement and executed on the logical Standby side. Therefore, pay attention to the following points:

(1) Not all data types can be supported by logical Standby,

Logical Standby supports the following data types:

BINARY_DOUBLE, BINARY_FLOAT, BLOB, CHAR, CLOB and NCLOB, DATE, interval year to month, interval day to second, LONG, long raw, NCHAR, NUMBER, NVARCHAR2, RAW, TIMESTAMP,

Timestamp with local timezone, timestamp with timezone, VARCHAR2 and VARCHAR

Note: compatibility is required when Standby is supported for the following types:

CLOB requires the Primary database to run at the Compatibility Level of 10.1 or higher.

For an index organization table (IOT) containing the LOB field, the compatibility level of the Primary database must run at 10.2 or higher.

For an index organization table (IOT) that does not contain the LOB field, the compatibility level of the Primary database must run at 10.1 or higher.

Unsupported data types include:

BFILE, Encrypted Columns, ROWID, UROWID, XMLType, object type, VARRAYS, nested table, and custom type.

You can also query DBA_LOGSTDBY_UNSUPPORTED to determine whether the primary database contains unsupported objects.

SQL> select * from dba_logstdby_unsupported;

Note: The ATTRIBUTES column of this view shows the reason why the object is not supported by SQL applications.

(2) Not all storage types can be supported by logical Standby.

Logical Standby supports Cluster Tables, Index-Organized Tables, and Heap-Organized Tables, but does not support Segment Compression) storage type.

(3) Not all PL/SQL packages can be supported by SQL applications.

Generally, packages that do not modify system Metadata (Metadata) do not have problems in actual applications, such as DBMS_OUTPUT, DBMS_RANDOM, and DBMS_METADATA.

Packages that may modify the system metadata will not be supported by SQL applications, even if they have been executed in Primary and are successfully transferred to the logical Standby end, they will not be executed. Such as DBMS_JAVA, DBMS_REGISTRY, DBMS_ALERT, DBMS_SPACE_ADMIN, DBMS_REFRESH, DBMS_REDEFINITION, DBMS_SCHEDULER, and DBMS_AQ. With the exception of DBMS_JOB, the jobs of the Primary database will be copied to the logical Standby, but these jobs will not be executed in the logical Standby database.

Description: Metadata is directly understood as the physical definition of an object. For example, for a table, metadata is the table structure or storage attribute of the table.

(4) Not all SQL statements can be executed on the logical Standby side.

By default, the following SQL statements are automatically skipped by the SQL application on the logical Standby side:

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.

Set constraints.

Set role.

Set transaction.

In addition, because SQL statements are flexible, even DDL statements that can be supported by SQL applications may not be executed on the logical Standby end after some special parameters are attached, because of the large quantity, I will not list them here. If you are interested, please refer to the official documentation.

(5) Not all DML operations can be applied in the logical Standby SQL.

Maintain the database synchronization between the logical Standby and Primary through the SQL application. When the SQL statement converted by the SQL application is executed, it is better to say "INSERT, for UPDATE and DELETE operations, you must be able to locate the record to be updated in the database. The problem lies here. If the table in the Primary database is improperly set, the unique condition may not be confirmed.

You may say that it can be unique through ROWID! Remember that logical Standby is called logical Standby because it is logically the same as the Primary database and may be physically quite different from the Primary database. It must be realized that the physical structure of logical Standby is different from that of Primary (even if the initial logical Standby is created through the backup of Primary ).

Therefore, it is obviously difficult to update through ROWID. Of course, it cannot be used as a unique condition. Let's take a look at this problem. See

Related reading:

View Management of Oracle DG logic Standby

Oracle 10g rac Standby automatic deletion script for archiving logs

ORA-01274 issues with Oracle Standby Libraries

Monitoring and management of physical Standby of DataGuard