逻辑standby用的很少，特别是在11g，物理standby也可以只读打开并实时应用，物理standby也可以做滚动升级.不过，最近有一个用户需
逻辑standby用的很少，特别是在11g，物理standby也可以只读打开并实时应用，物理standby也可以做滚动升级.不过，最近有一个用户需求，要建一个报表系统数据库，想到可以试试逻辑standby去实现，同步生产库的基础数据用户，并在逻辑standby上建bi相关的用户，，用做数据统计。这样可以避免直接通过dblink或物化视图抓取数据对生产库的性能影响，又比利用goldengate实现同步在维护性上方便。
一.创建逻辑standby官方文档及注释:
step by step guide on how to create logical standby (文档 id 738643.1)
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.
please refer following documentations for creating physical standby database:
 for 10.2:
 oracle? data guard concepts and administration 10g release 2 (10.2)
for 11.1:
 oracle? data guard concepts and administration 11g release 1 (11.1)
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'
4.2. set log_archive_dest_3 for logs which will received on standby from primary
log_archive_dest_3= 'location=/u01/arch/standby/ valid_for=(standby_logfiles,standby_role) db_unique_name=prim1'
 log_archive_dest_state_3=enable
note: log_archive_dest_3 only takes effect when the primary database is transitioned to the logical standby role.
step 5 build a dictionary in the redo data on primary database
--在主库上生成logminer字典信息
sql> execute dbms_logstdby.build;
the dbms_logstdby.build procedure waits for all existing transactions to complete. long-running transactions executed on the primary database will affect the timeliness of this command.
step 6 convert to a logical standby database
--转换物理standby到逻辑standby,db_name是要指定一个新的逻辑standby 的db_name
sql> alter database recover to logical standby ;