创建逻辑dg

逻辑备用DG
  今天是2014-04-29，最近一直忙的事情，也没来的急写点东西。今天继续整理dg的相关内容，要说的是逻辑dg的创建过程和注意事项。
什么是逻辑dg呢？物理dg类似于主库的完整副本，是块对块的重做应用。但是对于逻辑dg，是类似采用日志挖掘技术将主库传来的redo数据，进行构造sql进而在备库进行应用。且备库可以正常打开，并能维护其他不是主库传送的需要维护的对象。
  那么就存在一个问题，如果在备库采用rowid获得的数据，有可能和主库的完全不一致。那么当在主库修改数据的时候，我们建议在所修改的对象上，创建非空索引或是主键条件，那么在传送到备库的时候，继而能够应用这些约束，避免数据更新的错误性。但有时候并不是非要说一定要在主库建立对象表的主键或是非空索引，如果没有创建了，那么将会产生大量的无用redo，这就是在所有字段上都进行了日志补充导致。

The physical organization in a logical standby database is different from that of the primary database, even though the logical standby database is created from a backup copy of the primary database. Thus, ROWIDs contained in the redo records generated by the primary database cannot be used to identify the corresponding row in the logical standby database.

Oracle uses primary-key or unique-constraint/index supplemental logging to logically identify a modified row in the logical standby database. When database-wide primary-key and unique-constraint/index supplemental logging is enabled, eachUPDATE statement also writes the column values necessary in the redo log to uniquely identify the modified row in the logical standby database.

• If a table has a primary key defined, then the primary key is logged along with the modified columns as part of theUPDATE statement to identify the modified row.

• If there is no primary key, then the shortest nonnull unique-constraint/index is logged along with the modified columns as part of theUPDATE statement to identify the modified row.

• If there is no primary key and no nonnull unique constraint/index, then all columns of bounded size are logged as part of the UPDATE statement to identify the modified row. All columns are logged except the following: LONG, LOB, LONG RAW, object type, and collections.

• A function-based index, even though it is declared as unique, cannot be used to uniquely identify a modified row. However, logical standby databases support replication of tables that have function-based indexes defined, as long as modified rows can be uniquely identified.

Oracle recommends that you add a primary key or a nonnull unique index to tables in the primary database, whenever possible, to ensure that SQL Apply can efficiently apply redo data updates to the logical standby database.

那么哪些是sql apply支持的类型和不支持哪些类型呢？查看手册如下：

C.1.1 Supported Datatypes in a Logical Standby Database

Logical standby databases support the following datatypes:

• 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 TIMEZONE

• TIMESTAMP WITH LOCAL TIMEZONE

• VARCHAR and VARCHAR2

• LOBs stored as SecureFiles (requires that the primary database be run at a compatibility of 11.2 or higher.

• XMLType data for all storage models, assuming the following primary database compatibility requirements:

  • XMLType stored in CLOB format requires primary database to run at a compatibility of 11.1 or higher

  • 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