ORACLE 11G Dataguard Building Logic Standby

The Oracle Data Guard Logic Repository is a backup of the main library, which first establishes a physical standby and then converts it to a logical standby library. After this, the main library passes the log to the standby repository, which uses Logminer to parse the SQL from the main library and execute it on the repository, so that the data in the master library is logically consistent. Corresponding to the physical reserve, the physical standby is using redo apply, which uses SQL apply. Therefore, the logical repository only guarantees that the data is logically consistent with the main library, so that the logical repository can be in the open state and the corresponding DML operation.

Before converting the physical repository to the logical repository, take a look at the main repository:

Sql> select Name,open_mode,database_role,protection_mode from V$database;

• Primary Library

• Physical Standby Library

1.1 step-by-step instructions for Creating a Logical Standby Database

Because the logical standby is to maintain synchronization with the primary database through the SQL application. There is a big difference between SQL applications and redo applications, where redo applications are actually recover;sql applications on the physical standby side, which is to parse the redo information in the Redo log files and convert them to SQL statements, executed on the logical standby side, and therefore require verifies that the objects and statements of the operation can be logically Standby Support .

• Check if the primary database has objects that are not supported by logical standby

Sql> SELECT * from dba_logstdby_unsupported;

• To view a table in the primary database that does not contain a primary key or unique key index

Sql> Select OWNER, table_name from Dba_logstdby_not_unique 2> WHERE (OWNER, table_name) not in 3> (SELECT distinc T OWNER, table_name from dba_logstdby_unsupported) 4> and bad_column = ' Y ';

Note The Bad_column column value, which has two values:

Y: Indicates that there are fields in the table with big data types, such as long, CLOB, and so on. If some row records in the table except for the log column match exactly, the table cannot be successfully applied to the logical standby. Standby will try to maintain these tables, but you must ensure that the application is not allowed.

N: Indicates that the table has sufficient 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 that in a table you can confirm that the data is unique, but based on efficiency considerations, do not want to create a primary key or UNIQUE constraint for it? It's okay, Oracle. Early on, you can create a disable Primary-key rely constraint.

ADD a disabled Primary-key RELY constraint. (excerpt from June think twice)

Maintaining logical Standby and primary database synchronization is achieved through SQL application, SQL Application transformation SQL statement when executed, for insert fortunately, for the update,delete operation must be able to uniquely locate the database to update the record. If the tables in the primary library are not set properly, you may not be able to confirm unique conditions. Therefore, Oracle recommends creating a primary key or non-null unique index/constraint for the table to ensure that the SQL application can effectively apply the redo data and update the logical standby database as much as possible.

If you can confirm that the rows in the table are unique, you can create a rely primary key for the table, and the rely constraint does not cause the system to maintain the overhead of the primary key, you create a rely constraint on a table, and the system assumes that the rows in the table are unique, providing the performance of the SQL application. However, it is important to note that because rely's primary KEY constraint is only assumed to be unique, it is possible to cause incorrect updates if the actual is not unique.

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

sql> ALTER TABLE mytab ADD PRIMARY KEY (ID, name) RELY DISABLE;

Note : created the rely constraints, Oracle The column is assumed to be unique (give DBA sufficient trust), but the value of the column is not uniquely validated, so the column is unique only by the DBA to proactively maintain.

1.1.1 Create A physical Standby Database

Standby creation of the detailed physical

1.1.2 Stop Redo Apply on the physical Standby Database

Physical to the logical repository, you need to build the Logminer dictionary in the primary library and open the Supplemental log, and before that, you should deactivate the MRP process of the physical repository. In order to avoid applying redo data with Logminer dictionary in advance, when the SQL application does not logminer the redo data of the dictionary data after conversion to logical standby, the normal synchronization between logical standby and primary is affected.

sql> ALTER database RECOVER MANAGED STANDBY database CANCEL;

Standby Recovery transfer Redo:

Sql>  Recover managed Standby database disconnect from session;

1.1.3 Prepare The Primary database to support a Logical Standby database

• The first is to change the Valid_for property in the main library log_archive_dest_1 parameter to just the online redo log, not to include the alternate redo log. when Primary when the master library is used for storing Primary the resulting arch , when Primary after being switched to the standby role, it is used to store itself as the archive generated by the repository .

Log_archive_dest_1= ' Location=use_db_recovery_file_dest valid_for= (online_logfiles,all_roles) DB_UNIQUE_NAME= Primary

• The second is to add a new archive path specifically for the alternate redo log log_archive_dest_3, which means that the online log is separate from the standby log, only if the primary Library is converted to a repository When the Primary library is a standby role, it is used to store the Standby_logfiles received from the primary library.
  

Log_archive_dest_3= ' Location=use_db_recovery_file_dest valid_for= (standby_logfiles,standby_role) DB_UNIQUE_NAME= Primary ' log_archive_dest_state_3=enable

• Generate Logminer Dictionary

Generates a Logminer dictionary so that changes in the redo log can be parsed correctly by the SQL apply component of the Logminer dictionary.

Sql> EXECUTE Dbms_logstdby. BUILD;

The supplemental log of the primary key and the unique constraint/index column is automatically generated, the Supplemental logging information ensures each update contains enough information To logically identify each row this is modified by the Statement. For more than 11.2 versions, the supplemental log will be transferred to the physical repository with the primary library, and the previous version needs to check whether primary libraries and physical repositories generate supplemental logs. If not, the main library is generated from the following statement and then executed switchover or failover to the physical repository.

sql> ALTER DATABASE ADD supplemental LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

1.1.4 Transition to a Logical Standby Database

• Physical Standby Library to logical

If the physical standby is in RAC mode, you need to first:

sql> ALTER SYSTEM SET cluster_database=false scope=spfile; Sql> SHUTDOWN ABORT; sql> STARTUP MOUNT EXCLUSIVE;

Transfer physical to the logical Reserve Library:

Sql> ALTER DATABASE RECOVER to LOGICAL STANDBY db_name;

• Standby parameter Configuration

Sql> SHUTDOWN; Sql> STARTUP MOUNT; Log_archive_dest_1= ' Location=use_db_recovery_file_dest valid_for= (all_logfiles,all_roles) DB_UNIQUE_NAME= Standby

Unlike physical, the logical repository is created with its own redo log (redo) after open, so we need to configure the parameter log_archive_dest_n. For the logical standby, there are three kinds of log files, namely online redo log,archived redo Log,standby Redolog.

Log_archive_dest_3= ' Location=use_db_recovery_file_dest valid_for= (standby_logfiles,standby_role) DB_UNIQUE_NAME= Standby '; Log_archive_dest_state_3=enable

1.1.5 Open the Logical Standby Database

• Because the logical standby does not conform to the primary database transaction, you must specify the RESETLOGS clause when you first open it

sql> ALTER DATABASE OPEN resetlogs;

• Apply Redo Data

sql> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

• Querying database status

1.2 Verify The Logical Standby Database is performing properly Standby

• Primary library NN user T1 table insert data

• Logical Standby Library Query

At this point, ORACLE 11G Dataguard build logic Standby success!

ORACLE 11G Dataguard Building Logic Standby