【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结构图：

 

1. 由物理备库转化为逻辑备库

  --主库上的信息  

 

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

 

1. 停用备库MRP进程  

  对于将物理备库切换到逻辑备库，我们需要在主库构建LogMiner字典及启用补充日志，因此应先停用逻辑备库的MRP进程，避免产生额外的redo apply, 逻辑备用数据库在后台使用LogMiner来提取生成SQL Apply事务必须的重做数据，在创建Log Miner字典之前，我们必须停止备用数据库上的管理恢复，以确保我们只应用包含LogMiner字典的重做数据：

  如果正在使用Broker管理现有的物理备库，应先在Broker中禁用目标数据库。  

    

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

SQL>

 

1. 修改主库参数(搭建物理备库已建做过，略过)

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;

 

1. 主库构建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>

 

 

1. 把物理备库恢复为逻辑备库

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>

 

 

1. 修改备库参数、打开逻辑备用数据库、启用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>

 

至此逻辑备用数据库以搭建完成。

 

1. 备库执行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