1. 停止physical standby备库的redo apply
SQL> alter database recover managed standby database cancel;
2. 在主库运行dbms_logstdby.build
SQL> execute dbms_logstdby.build;
3. 在主库上切换物理备库转化为Logical standby
SQL> alter database recover to logical standby ;
4. 修改logical standby的日志归档位置
# mkdir -p /u01/app/oracle/oradata/logical_standby/DGZH
SQL> alter system set log_archive_dest_3='location=/u01/app/oracle/oradata/logical_standby/DGZH valid_for=(standby_logfiles,standby_role) db_unique_name=DGZH';
SQL> alter system set log_archive_dest_state_3=enable;
5. 备库以RETLOGS方式打开logical standby并打开应用
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> alter database start logical standby apply immediate;
6. 检查logical standby的状态
SQL> select APPLIED_SCN,READ_SCN,NEWEST_SCN from dba_logstdby_progress;
APPLIED_SCN READ_SCN NEWEST_SCN
----------- ---------- ----------
1034885 1034888 1034881
7. 测试
<主库>
SQL> create table wugy.tlog (id number primary key, text varchar2(200));
SQL> insert into wugy.tlog (id, text) values (1, 'Test logical standby');
SQL> commit;
<备库>
SQL> select * from wugy.tlog;
ID TEXT
---------- --------------------------------------------------------------------------------
1 Test logical standby