逻辑 standby

逻辑备库中的表必须用非空唯一索引或者主键
原因：因为逻辑备库日志里记录的rowid都是主库产生的，不能用来确定逻辑备库的，所以oracle逻辑备库使用主键或者唯一约束索来确定逻辑备库修改的的行
If a table has a primary key defined, then the primary key is logged along with the modified columns as part of the UPDATE statement to identify the modified row.
If there is no primary key, then the shortest nonnull unique-constraint/index is logged along with the modified columns as part of the UPDATE statement to identify the modified row.
If there is no primary key and no nonnull unique constraint/index, then all columns of bounded size are logged as part of the UPDATE statement to identify the modified row. All columns are logged except the following: LONG, LOB, LONG RAW, object type, and collections.
A function-based index, even though it is declared as unique, cannot be used to uniquely identify a modified row. However, logical standby databases support replication of tables that have function-based indexes defined, as long as modified rows can be uniquely identified.






oracle 指出，你在主库添加主键或者非空唯一索引时，确定在逻辑备库能够应用update 的日志
           （1）在主库查询没有主键的表
                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）对于没有主键的表添加约束
                         当你确定此表上的某列是唯一的，可以添加disable 约束
SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;
为了增加逻辑备库的性能，添加主键后来确定一行，那么在删除或者update时sql应用就不会全表扫描了
   （3）创建物理standby
   （4）停止日志应用
           在新建的物理备库上，可以应用日志到任何时候，但是在转变成逻辑备库前，必须停止日志的应用，因为 avoid applying changes past the redo that contains the LogMiner dictionary
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

   （5）准备主库角色的转换
      Change the VALID_FOR attribute in the original LOG_ARCHIVE_DEST_1 destination to archive redo data only from the online redo log and not from the standby redo log.
              Include the LOG_ARCHIVE_DEST_3 destination on the primary database. This parameter only takes effect when the primary database is transitioned to the logical standby role.
                    
                           LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_3='LOCATION=/arch2/chicago/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=chicago' LOG_ARCHIVE_DEST_STATE_3=ENABLE
（6） Build a Dictionary in the Redo Data
               SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
            在  11.2之前的版本，主库打开附加日志但是备库没有，需要手动开启附加日志，但是11。2之后，主库开启附加日志，备库也会开启
                备库:   SQL>  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
(7) 转换成逻辑standby
           如果是rac 物理standby，那么需要关闭所有实例，只在一个实例上执行
      SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT EXCLUSIVE; 
          SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;
（8）调整逻辑备库的参数
    如果是rac要执行；SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE; 
SQL> SHUTDOWN;
SQL> STARTUP MOUNT;

对于逻辑备库有自己不同的日志online redo log files, archived redo log files, and standby redo log files，逻辑备库是open状态，要有不同的归档地方
LOG_ARCHIVE_DEST_1：存放逻辑备库产生的日志
LOG_ARCHIVE_DEST_1：存放主库产生的归档
修改参数
LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/boston/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2= 'SERVICE=chicago ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_3='LOCATION=/arch2/boston/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
            （9）打开数备库
                        SQL> ALTER DATABASE OPEN RESETLOGS;


                   应用日志
                      SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;