创建logical standby步骤

10.2.0.5 on windows 2008 64bit

1 创建一个physical standby

2 停止physical 上的recover service

alter database recover  managed standby database cancel;

3 调整primary 参数

 因为logical standby有两种redo log，其中online redo log在physical standby里面是没有的。所以这两种redo log的archived log要分开路径存放

- online redo log

- standby redo log

例子：增加standby log的archive路径

LOG_ARCHIVE_DEST_3=
'LOCATION=/arch2/chicago/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) 
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_3=ENABLE

另外之前的LOG_ARCHIVE_DEST_1是valid for (ALL_LOGFILES,ALL_ROLES)， 需要修改为(ONLINE_LOGFILES,ALL_ROLES)

4 在primary database建立dictionary 

EXECUTE DBMS_LOGSTDBY.BUILD

5 将physical standby转化为logical standby 

alter database recover to logical standby test_s

select database_role from v$database; 

 LOGICAL STANDBY

6 重建PASSWORD FILE

orapwd file=C:\oracle\product\10.2.0\db_1\database\pwdtestdgs.ora password=oracle

注意密码一定要与primary database一致

7 修改standby database的log_archive_dest参数

 LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/boston/
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=chicago LGWR 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

8 open resetlogs

alter database open resetlogs;

alter database start logical stdandby apply;