本篇blog结构图：
由物理备库转化为逻辑备库
--主库上的信息
SQL> select name,open_mode,database_role,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
ORA11G READ WRITE PRIMARY MAXIMUM PERFORMANCE
--备库上的信息
SQL> select name,open_mode,database_role,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
ORA11G READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
停用备库MRP进程
对于将物理备库切换到逻辑备库，我们需要在主库构建LogMiner字典及启用补充日志，因此应先停用逻辑备库的MRP进程，避免产生额外的redo apply, 逻辑备用数据库在后台使用LogMiner来提取生成SQL Apply事务必须的重做数据，在创建Log Miner字典之前，我们必须停止备用数据库上的管理恢复，以确保我们只应用包含LogMiner字典的重做数据：
如果正在使用Broker管理现有的物理备库，应先在Broker中禁用目标数据库。
SQL> alter database recover managed standby database cancel;
Database altered.
SQL>
修改主库参数(搭建物理备库已建做过，略过)
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=ora11g valid_for=(ALL_LOGFILES,ALL_ROLES)';
alter system set log_archive_dest_state_1=enable;
主库构建LogMiner字典
SQL> create tablespace logmnrtbs datafile '/u01/app/oracle/oradata/ora11g/logmnrtbs1.dbf' size 100m;
Tablespace created.
SQL> execute dbms_logmnr_d.set_tablespace('logmnrtbs');
PL/SQL procedure successfully completed.
SQL> exec dbms_logstdby.build;
PL/SQL procedure successfully completed.
SQL>
把物理备库恢复为逻辑备库
SQL> select name,open_mode,database_role,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
ORA11G READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 335547696 bytes
Database Buffers 71303168 bytes
Redo Buffers 8466432 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY ora11gdb parallel 10;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 335547696 bytes
Database Buffers 71303168 bytes
Redo Buffers 8466432 bytes
Database mounted.
SQL>
修改备库参数、打开逻辑备用数据库、启用SQL应用
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=ora11gdg' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_1=enable scope=both;
System altered.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> alter database start logical standby apply immediate;
Database altered.
----可以看到name自动改变，为读写模式，日志序列也从1开始
SQL> select name,open_mode,database_role,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
ORA11GDB READ WRITE LOGICAL STANDBY MAXIMUM PERFORMANCE
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>
至此逻辑备用数据库以搭建完成。
备库执行DML操作
在logical standby环境下，备库基本上是一个独立的库，如果要在备库，以非sys用户对备库的数据进行DML修改，就会报错 ora-16224
[oracle@rhel6_lhr orclasm]$ oerr ora 16224
16224, 00000, "Database Guard is enabled"
// *Cause: Operation could not be performed because database guard is enabled
// *Action: Verify operation is correct and disable database guard
You have new mail in /var/spool/mail/oracle
[oracle@rhel6_lhr orclasm]$
SQL> conn lhr/lhr
Connected.
SQL> delete from lhr.test;
delete from lhr.test
*
ERROR at line 1:
ORA-16224: Database Guard is enabled
SQL> alter database guard none;
Database altered.
SQL> delete from lhr.test;
5669 rows deleted.
SQL> rollback;
Rollback complete.
SQL> alter database guard standby;
Database altered.
SQL> delete from lhr.test;
delete from lhr.test
*
ERROR at line 1:
ORA-16224: Database Guard is enabled
SQL> select guard_status from v$database;
GUARD_S
-------
STANDBY