【DG】基于同一个主机建立物理备库和逻辑备库（二）

本篇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

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

更多相关文章

随机推荐

【DG】 基于同一个主机建立物理备库和逻辑备库（二）

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

更多相关文章

随机推荐

【DG】基于同一个主机建立物理备库和逻辑备库（二）