逻辑standby用的很少，特别是在11g，物理standby也可以只读打开并实时应用，物理standby也可以做滚动升级.不过，最近有一个用户需求，要建一个报表系统数据库，想到可以试试逻辑standby去实现，同步生产库的基础数据用户，并在逻辑standby上建BI相关的用户，用做数据统计。这样可以避免直接通过dblink或物化视图抓取数据对生产库的性能影响，又比利用goldengate实现同步在维护性上方便。

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.

Please refer following documentations for creating physical standby database:

?For 10.2:
?Oracle? Data Guard Concepts and Administration 10g Release 2 (10.2)
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#i63561

For 11.1:
?Oracle? Data Guard Concepts and Administration 11g Release 1 (11.1)
http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/create_ps.htm#i63561

Step 2 Make Sure that Physical Standby is in Sync with Primary Database
--在物理standby上执行,查看跟主库的同步情况
Use following query on Standby to check:

SQL>SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
?FROM?
?(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,?
?(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL?
?WHERE?
?ARCH.THREAD# = APPL.THREAD#?
?ORDER BY 1;
?/*
?Thread? ? Last Sequence Received? ? Last Sequence Applied
?1? ? 60? ? 60
?*/

There should not be any difference in Last Seq Received and Last Seq Applied on Physical Standby.

Step 3 Stop Redo Apply on the Physical Standby Database
--停止物理standby的redo应用
?

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 4 Set Parameters for Logical Standby in Primary
--设定主库的日志归档目录,LOG_ARCHIVE_DEST_3的设定是为了主库切换后使用
4.1. Change VALID_FOR in LOG_ARCHIVE_DEST_1 on Primary to (ONLINE_LOGFILES,ALL_ROLES)

LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim1'