【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库（二）
之前发布过一步一步搭建 oracle 11gR2 rac + dg，这里的dg为物理dg，但是实际自己使用过程中发现需要开3个虚拟机，机器特卡，所以决定在同一台机器上再搭建一台物理和逻辑dg。
一步一步搭建 oracle 11gR2 rac + dg 之前传（一） http://blog.itpub.net/26736162/viewspace-1290405/
一步一步搭建oracle 11gR2 rac+dg之环境准备(二) http://blog.itpub.net/26736162/viewspace-1290416/
一步一步搭建oracle 11gR2 rac+dg之共享磁盘设置(三) http://blog.itpub.net/26736162/viewspace-1291144/
一步一步搭建 oracle 11gR2 rac+dg之grid安装(四) http://blog.itpub.net/26736162/viewspace-1297101/
一步一步搭建oracle 11gR2 rac+dg之database安装(五) http://blog.itpub.net/26736162/viewspace-1297113/
一步一步搭建11gR2 rac+dg之安装rac出现问题解决(六) http://blog.itpub.net/26736162/viewspace-1297128/
一步一步搭建11gR2 rac+dg之DG 机器配置(七) http://blog.itpub.net/26736162/viewspace-1298733/
一步一步搭建11gR2 rac+dg之配置单实例的DG(八) http://blog.itpub.net/26736162/viewspace-1298735/
一步一步搭建11gR2 rac+dg之DG SWITCHOVER功能(九) http://blog.itpub.net/26736162/viewspace-1328050/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库（一）： http://blog.itpub.net/26736162/viewspace-1448197/
本篇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