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 ;

?For db_name, specify a database name to identify the new logical standby database. If you are using a spfile for standby, then command will update the db_name parameter otherwise it will issues a message reminding you to set the name of the DB_NAME parameter after shutting down the database.

Step 7 Create a New Password File for Logical Standby Database
--新建密码文件,这个在10g需要执行,11g原有的就可以

$ORAPWD FILE= PASSWORD= ENTRIES=

?This step is required in 10.2 only and should not be performed in 11g.

Step 8 Shutdown and Startup Logical Standby Database in Mount Stage
--关闭逻辑standby并开户到mount状态

SQL> SHUTDOWN;
?SQL> STARTUP MOUNT;

Step 9 Adjust Initialization Parameter on Logical Standby Database
--转换为逻辑standby后,修改原有的日志归档目录
?

LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logstd1'
?LOG_ARCHIVE_DEST_2= 'SERVICE=prim1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim1'
?LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=logstd1'
?LOG_ARCHIVE_DEST_STATE_1=ENABLE
?LOG_ARCHIVE_DEST_STATE_2=ENABLE
?LOG_ARCHIVE_DEST_STATE_3=ENABLE

Step 10 Open the Logical Standby Database
--以resetlogs方式打开数据库
?

SQL> ALTER DATABASE OPEN RESETLOGS;

Step 11 Start Logical Apply on Standby
--开启sql apply,这里的immediate是要实时应用,需要有standby redo log支持,如果不需要实时应用,可不加immediate.
?

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

二.逻辑standby创建完成后的一些设置
--在逻辑standby上将guard设置为standby,实现能在逻辑standby创建BI相关的用户
SQL>alter database guard standby;
--在逻辑standby上使用dbms_logstdby.skip跳过不需要同步的用户的表
SQL>alter database stop logical standby apply;
?SQL>exec dbms_logstdby.skip(stmt=>'SCHEMA_DDL',SCHEMA_NAME=>'HR',OBJECT_NAME=>'%');
SQL>exec dbms_logstdby.skip(stmt=>'DML',SCHEMA_NAME=>'HR',OBJECT_NAME=>'%');
?SQL>alter database start logical standby apply;
? ? 如果将来需要将跳过的表重新添加回来
? SQL>alter database stop logical standby apply;
? ? 通过exec dbms_logstdby.unskip添加回来
? 通过dbms_logstdby.instantiate_table同步,这里需要建dblink
? ? SQL>alter database start logical standby apply;