oracle 11g bi,通过Oracle 11g 逻辑standby实现BI的需求

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]

Information in this document applies to any platform.

***Checked for relevance on 21-Sep-2012***

*** Reviewed for Relevance 16-Jul-2015 ***

Goal

Step by Step Guide on How to Create Logical Standby

Solution

Prerequisite

--必要条件

1 : Before setting up a logical standby database, ensure the logical

standby database can maintain the data types and tables in your primary

database. See Appendix C of the dataguard documentation for a complete

list of data type and storage type considerations.

--确定数据库中的数据类型和存储类型支持

2 : Ensure Table Rows in the Primary Database Can Be Uniquely Identified.

--确定表中各行的唯一性,尽量有主键或唯一索引

2.1 : Find Tables Without Unique Logical Identifier in the Primary Database.

Use following query to display a list of tables that SQL Apply may not be able to uniquely identify:

SQL> SELECT OWNER,

TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN

(SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND

BAD_COLUMN = 'Y'

2.2 : If your application ensures the rows in a

table are unique, you can create a disabled primary key RELY constraint

on the table. Use ALTER TABLE command to add a disabled primary-key

RELY constraint.

The following example creates a disabled RELY

constraint on a table named mytab, for which rows can be uniquely

identified using the id and name columns:

SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

Creating a Logical Standby Database:

--创建逻辑standby

Step 1 Create a Physical Standby Database

--建逻辑standby,要先建一个物理standby,然后再进行转换Create a Physical Standby Database and make sure that there is no error in remote archiving to Standby from Primary Database.