Instructions for creating Oracle DG logical Standby

First, the preparation of logical standby

1, confirm the operation of the object and statement can be logically standby support

Because the logical standby is to maintain synchronization with the primary database through SQL applications. SQL application and redo application is very different, redo application is actually in the physical standby Recover;sql application is to analyze the redo information in the Redo log file and convert it to SQL statement, execute at logical standby end, therefore, The following points need to be noted:

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

The data types supported by the logical standby are:

Binary_double, Binary_float, BLOB, CHAR, CLOB and NCLOB, DATE, INTERVAL year to MONTH, INTERVAL Day to SECOND, long, long RAW, NC HAR, number, NVARCHAR2, RAW, TIMESTAMP,

TIMESTAMP with local TIMEZONE, TIMESTAMP with TIMEZONE, VARCHAR2 and VARCHAR

Description: The following types require attention to compatibility when obtaining standby support:

CLOB, the compatibility level of the primary database needs to run at 10.1 or higher.

The Index organization table (IOT) with LOB fields requires the compatibility level of the primary database to run at 10.2 or higher.

An index organization table (IOT) without LOB fields requires the compatibility level of the primary database to run at 10.1 or higher.

Unsupported data types are:

BFILE, Encrypted Columns, ROWID, Urowid, XmlType, Object type, varrays, nested tables, custom types.

You can also query dba_logstdby_unsupported to determine if the primary database contains unsupported objects

Sql> select * from dba_logstdby_unsupported;

Note: The attributes column of this view shows why objects are not supported by SQL applications.

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

Logical standby can support cluster tables (Cluster tables), index organization tables (index-organized tables), heap organization tables (heap-organized tables), but not segment compression (Segment Compression) storage type.

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

Typically, package that do not modify system metadata (Metadata) are not problematic in practical applications, such as Dbms_output, Dbms_random, Dbms_metadata, and so on.

Package that may modify system metadata are not supported by SQL applications, even if they are executed in primary and successfully transferred to the logical standby end. 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, jobs in the primary database are copied to the logical standby, although they are not executed in the logical standby database.

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

(4) Not all SQL statements can be executed at the logical standby end.

By default, the following SQL statements are automatically skipped by SQL application at the logical standby end:

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 the SQL statements are very flexible, even those DDL statements that can be supported by the SQL application may not be executed on the logical standby side after attaching some special parameters, as the number is not listed here, please refer to the official documentation if you are interested.

(5) Not all DML operations can be applied at the logical standby end of the solid SQL application.

Maintenance logic Standby and primary database synchronization is implemented through SQL application, SQL Application Conversion of SQL statements in the execution, for insert fortunately, for the update, delete operations must be able to locate the database to update the record. The problem is that if the tables in the primary library are improperly set, you may not be able to confirm the unique criteria.

You may say that you can pass ROWID only! Remember, the logical standby, why the logical standby, is because it is logically the same as the primary database, there may be a substantial difference between the physical and primary database. It is important to realize that the physical structure of the logical standby is not the same as the primary (even if the initial logic standby is created through a primary backup).

Therefore, it is obviously not a very convenient way to update by ROWID, and of course it cannot be used as the only condition. Here's a look at the problem.

2. Ensure that the rows of the tables in the primary library can be uniquely identified

Oracle determines the rows in the logical standby database to be updated through a primary key, a supplemental log of unique indexes/constraints (supplemental Logging). When the database is enabled for supplemental logging, each UPDATE statement appends a column value with unique information when it writes redo, for example:

If the table defines a primary key, the primary key column is included with the updated column as part of the UPDATE statement so that the columns should be updated when executed.

If there is no primary key, the Non-empty unique index/constraint will accompany the updated column as part of the UPDATE statement to distinguish which columns should be updated when it is executed, and if the table has multiple unique indexes/constraints, Oracle automatically chooses the one with the shortest length to reduce the generated redo log size.

If the table has neither a primary key nor a unique index/constraint defined, all the columns with a fixed length, along with the updated column, are part of the UPDATE statement. More specifically, a fixed-length column is a column except long, LOB, long RAW, OBJECT type, collection type.

Oracle recommends that you create a primary key or Non-empty unique index/constraint for the table to ensure that the SQL application can efficiently apply redo data and update the logical standby database as much as possible.

More Wonderful content: http://www.bianceng.cn/database/Oracle/

The following statements can be used to check whether the SQL application uniquely identifies a table column and finds unsupported tables:

Sql> SELECT owner, table_name from Dba_logstdby_not_unique WHERE (OWNER, Table_n

AME) not in (SELECT DISTINCT OWNER, table_name from dba_logstdby_unsupported)

and bad_column = ' Y ';

OWNER table_name

----- ---------------

Tsmsy srs$

Tip: For Dba_logstdby_not_unique, this view displays all tables that have neither primary keys nor unique indexes. If the columns in the table contain enough information, you can usually support updates at the logical standby end, and tables that are not supported are usually the result of a column definition that contains unsupported data types.

Note The Bad_column column value, which has two values:

Y: Indicates that the table has fields with large data types, such as long, CLOB. The table cannot be successfully applied to logical standby if some row records in the table except for log columns match exactly. Standby will try to maintain these tables, but you must ensure that the application is not allowed.

N: Indicates that the table has enough information to support updates in the logical standby, but it is still recommended that you create a primary key or unique index/constraint for the table to improve log application efficiency.

Suppose you can confirm that the data is unique in a table, but what if you don't want to create a primary key or a unique constraint for it based on efficiency considerations? Never mind, Oracle had thought of this, you can create a disable Primary-key rely constraint:

Tip: About Primary-key rely constraints.

If the DBA is able to confirm that the rows in the table are unique, then you can create a rely primary key for the table, rely constraints do not cause the system to maintain the primary key overhead, such as you create a rely constraint on a table, the system will assume that the row in the table is unique, which can improve the performance of SQL application. It should be noted, however, that because rely primary key constraints are only assumed to be unique, they may cause incorrect updates if they are not actually unique.

Creating a rely primary KEY constraint is simple, as long as you add rely disable after the standard creation statement, for example:

sql> ALTER TABLE USER ADD PRIMARY KEY (ID) RELY DISABLE;

The table has changed.

Note: After creating the rely constraint, Oracle assumes that the column is unique (sufficient trust to the DBA), but does not uniquely validate the value of the column, so that the column is only actively maintained by the DBA.

Second, the logical standby when the procedure is created

1. Create Physical standby

The first step in creating a logical standby database is to create a physical standby database before converting it to a logical standby database. You can start the Redo application at any time before converting it to a logical standby, but once you decide to convert it to a logical standby, you must stop the redo application of the physical standby to avoid applying logminer data with redo dictionaries in advance, After the conversion to the logical standby, SQL application Logminer the dictionary data is not enough to affect the logical standby and primary normal synchronization.

2. Set up primary database

The corresponding number of initialization parameters were set when the physical standby database was created to primary the role switching between the database and the physical standby, and those parameters were equally useful for the role switching of the logical standby.

Note, however, that if you want the primary database to switch gracefully to the logical standby role, the DBA will need to set the corresponding Log_archive_dest_n initialization parameters when configuring the environment, and note that the parameter's valid_ The For property value needs to be changed to Standby_logfiles,standby_role.