Oracle 10 Gb physical standby to logical standby

Oracle 10 Gb physical standby to logical standby

1. Preparations for configuring logical Standby
Before creating a logical standby, first check the status of the primary database to make sure that the primary database has all the preparations for creating the logical standby, for example, whether the archiving is enabled or not, this part can be referred to the preparation work when creating a physical standby. In addition, because logical standby maintains synchronization with the primary database through SQL applications, there is a big difference between SQL applications and redo applications. The redo application is actually a physical standby end for recover, the SQL application analyzes the redo file and converts it into an SQL statement for execution on the logical standby side,
1) check whether the database has objects not supported by logical standby, as shown below:

SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;

OWNER TABLE_NAME COLUMN_NAME DATA_TYPE ATTRIBUTES
------------------------------------------------------------------------------------------
Sh mview $ _ EXCEPTIONS BAD_ROWID ROWID
Oe customers CUST_ADDRESS CUST_ADDRESS_TYP
Oe customers PHONE_NUMBERS PHONE_LIST_TYP
Oe customers CUST_GEO_LOCATION SDO_GEOMETRY
Oe warehouses WAREHOUSE_SPEC XMLTYPE
Oe warehouses WH_GEO_LOCATION SDO_GEOMETRY
PM ONLINE_MEDIA PRODUCT_AUDIO ORDAUDIO
PM ONLINE_MEDIA PRODUCT_TESTIMONIALS ORDDOC
PM ONLINE_MEDIA PRODUCT_PHOTO ORDIMAGE
PM ONLINE_MEDIA PRODUCT_PHOTO_SIGNATURE ORDIMAGESIGNATURE
PM ONLINE_MEDIA PRODUCT_THUMBNAIL ORDIMAGE
PM ONLINE_MEDIA PRODUCT_VIDEO ORDVIDEO
PM PRINT_MEDIA AD_TEXTDOCS_NTAB TEXTDOC_TAB
PM PRINT_MEDIA AD_GRAPHIC BFILE
PM PRINT_MEDIA AD_HEADER ADHEADER_TYP
OE CATEGORIES_TAB CATEGORY_NAME VARCHAR2 Object Table
OE CATEGORIES_TAB CATEGORY_DESCRIPTION VARCHAR2 Object Table
OE CATEGORIES_TAB CATEGORY_ID NUMBER Object Table
OE CATEGORIES_TAB PARENT_CATEGORY_ID NUMBER Object Table

19 rows selected.

Note: DBA_LOGSTDBY_UNSUPPORTED: This view displays the column names of tables that contain unsupported data types and the Data Types of the columns. Note the ATTRIBUTES column of this view, the column value shows why the table is not supported by SQL applications.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Oracle 11g from entry to proficient in PDF + CD source code

Installing Oracle 11g R2 using RHEL6 ASM

2) 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 that for the insert statement, for the update, the delete operation 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, the difference between logical standby and physical standby is that it is logically the same as the primary database, and physically it may be quite different from the primary database. We must realize that, the physical structure of the logical standby is different from that of the primary (even if the initial logical standby is created through the backup of the primary), it is obviously difficult to update the data through rowid, you can solve this problem by using the following methods:
How to ensure that the rows of each table in the primary database can be uniquely identified
Oracle uses the primary key and unique index/constraint to add logs (supplemental logging) to determine the rows in the standby database. When the database enables lemental logging) each update statement adds the unique information of column values when writing redo statements, for example:
If the table defines a primary key, the primary key value will be used as part of the update statement along with the updated column so that other columns in the time zone can be updated;
If there is no primary key, the unique index/constraint that is not null will be updated along with the column to be used as part of the update statement, so that the columns in which the time zone should be updated can be executed, if the table has multiple unique indexes/constraints, oracle automatically selects the shortest one;
If the table does not have a primary key and it does not define a unique index/constraint, all columns with a fixed length, together with the updated column, are part of the update statement, more clearly, the columns with a fixed length refer to those columns except long, lob, long raw, object type, and collection type;
Check whether the Supplemental log is enabled on the primary database. You can query v $ database as follows: SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;
SUP SUPPLEME
--------------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, SUPPLEMENTAL_LOG_DATA_MIN from v $ database;

SUP SUPPLEME
--------------
NO YES

Annotation: SUPPLEMENTAL_LOG_DATA_MIN = YES and IMPLICIT both indicate that the database has started the minimal Supplemental log. YES indicates the minimum supplemental log that is started through the alter database add supplemental log data statement. If the supplemental log for the primary key, unique key, foreign key, and ALL is started, by default, the minimum Supplemental log is enabled. IMPLICIT indicates the minimum Supplemental log opened by enabling support for primary keys, unique keys, foreign keys, or ALL.
Therefore, we recommend that you create a primary key or a unique index/constraint for the table to ensure that the SQL application can effectively apply the redo data update logic to the standby database.
3) run the following statement to check whether the SQL application can uniquely identify the table column and find the unsupported table:
SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
2 WHERE (OWNER, TABLE_NAME) NOT IN
3 (select distinct owner, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
4 AND BAD_COLUMN = 'y ';

OWNER TABLE_NAME
------------------------------------------------------------
Tsmsys srs $ -- this is a system table. First, check whether the user can log on. If the user is locked, we do not need to set it.

SQL> select username, account_status from dba_users where username like '% TSMSYS % ';

USERNAME ACCOUNT_STATUS
--------------------------------------------------------------
Tsmsys expired & LOCKED
Note: DBA_LOGSTDBY_NOT_UNIQUE this view displays tables that contain unsupported data types, that is, tables with neither primary keys nor unique indexes, if the columns in a table contain enough information, the update in logical standby is also supported. Unsupported tables are generally caused by column definitions that contain unsupported data types.
There are two values for the BAD_COLUMN column:
Y: indicates that the table has fields of the big data type, such as LONG and CLOB. If the records of some rows in the table except the log column match completely, 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 enough information to support logical standby updates. However, we recommend that you create a primary key or unique index/constraint for the table to improve log application efficiency;

4) Suppose you can confirm that the data in a table is unique, but you do not want to create a primary key or unique constraint for it due to efficiency considerations. What should we do? It doesn't matter, with this in mind, you can create a disable primary-key rely constraint:
Primary-key RELY constraints:
If you can confirm that the rows in the table are unique, you can create a rely primary key for the table. The RELY constraint does not cause the system to maintain the primary key overhead, if you create a rely constraint on a table, the system assumes that the rows in the table are unique, which can provide performance for SQL applications, because the primary key constraint of rely is only assumed unique, if it is not actually unique, it may cause incorrect updates.
The primary key constraint for creating a rely statement is very simple. You only need to add the rely disable statement after the standard creation statement. The example is as follows:
SQL> alter table mytab add primary key (id, name) rely disable; -- TABLE mytab is the name of the TABLE where I want to create the rely PRIMARY KEY constraint.

For more details, please continue to read the highlights on the next page:

1 2 Next Page