签到成功

知道了

CNDBA社区CNDBA社区

DG 3种保护模式切换

2017-07-09 18:34 2490 0 原创 Oracle 11g
作者： leo
本文链接：http://www.cndba.cn/cndba/leo1990/article/2011
最大保护模式

这种保护模式确保如果主数据库故障不会发生数据丢失。要提供这种级别的保护，恢复每个事务所需的重做数据必须在事务提交之前同时
写到本地联机重做日志和至少一个备数据库上的备重做日志。要确保不发生数据丢失，如果故障导致主数据库无法写重做流到至少一个事务一致性备数据
库的备重做日志时，主数据库会关闭。

最大可用性模式 

这种保护模式提供了可能的最高级别的数据保护，而不用与主数据库的可用性相折衷。与最大保护模式相同，在恢复事务所需的重做写
到本地联机重做日志和至少一个事务一致性备数据库上的备重做日志之前，事务将不会提交。与最大保护模式不同的是，如果故障导致主数据库无法写重
做流到异地备重做日志时，主数据库不会关闭。替代地，主数据库以最大性能模式运行直到故障消除，并且解决所有重做日志文件中的中断。当所有中断
解决之后，主数据库自动继续以最大可用性模式运行。
这种模式确保如果主数据库故障，但是只有当第二次故障没有阻止完整的重做数据集从主数据库发送到至少一个备数据库时，不发生数据丢失。

最大性能模式
 
这种保护模式（默认）提供了可能的最高级别的数据保护，而不影响主数据库的性能。这是通过允许事务在恢复该事务所需重做数据在
写到本地联机重做日志后立即提交而实现的。主数据库的重做数据流也写到至少一个备数据库，但是那个重做流相对于创建重做数据的事务是异步写的。
当所用的网络连接有足够的带宽，这种模式提供了近似于最大可用性模式的数据保护级别，并且对主数据库性能的影响最小。
最大保护和最大可用性模式需要备重做日志文件配置在配置中的至少一个备数据库上。所有三种保护模式需要在LOG_ARCHIVE_DEST_n 初始化参数上
指定特定的日志传输属性以发送重做数据到至少一个备数据库。

--在切换保护模式之前关闭MRP 
SQL> alter database recover managed standby database cancel;

Database altered.
--保护模式切换之后再备库开启MRP 
SQL> alter database recover managed standby database disconnect from session;

Database altered.
当前模式 DG在最大性能模式MAXIMUM PERFORMANCE
--主库 
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE  PRIMARY	      MAXIMUM PERFORMANCE  READ WRITE
--备库 
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE  PHYSICAL STANDBY MAXIMUM PERFORMANCE  READ ONLY WITH APPLY
DG最大性能切换最大可用性模式Maximum availability 
--主库操作
SQL> show parameter log_archive_dest_2

NAME				     TYPE	 	VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		 string		service=orcl_pd valid_for=(onl
									ine_logfiles,primary_role) db_
									unique_name=orcl_pd

SQL> alter system set log_archive_dest_2='service=orcl_pd lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl_pd';

System altered.

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		 string	 service=orcl_pd lgwr sync affi
						 rm valid_for=(online_logfiles,
						 primary_role) db_unique_name=o
						 rcl_pd
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size		    2259840 bytes
Variable Size		  889193600 bytes
Database Buffers	  146800640 bytes
Redo Buffers		    5632000 bytes
Database mounted.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.

SQL> alter database open;

Database altered.

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM AVAILABILITY PRIMARY	      MAXIMUM AVAILABILITY READ WRITE

--备库操作
SQL> alter system set log_archive_dest_2='service=orcl_st lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl_st'; 

System altered.

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		 string	 service=orcl_st lgwr sync affi
						 rm valid_for=(online_logfiles,
						 primary_role) db_unique_name=o
						 rcl_st

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY READ ONLY WITH APPLY


验证最大可用模式：
--主库：
SQL> insert into nancy select * from all_users;

36 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from nancy;

  COUNT(*)
----------
       108
--备库查看
SQL> select count(*) from nancy;

  COUNT(*)
----------
       108
--最大保护切换至最大可用 
--主库操作
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM AVAILABILITY PRIMARY	      MAXIMUM AVAILABILITY READ WRITE
--备库查询
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY READ ONLY WITH APPLY
DG最大可用切换至最大保护模式Maximum protection 
--将主库修改为最大保护模式
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size		    2259840 bytes
Variable Size		  889193600 bytes
Database Buffers	  146800640 bytes
Redo Buffers		    5632000 bytes
Database mounted.
--修改为最大保护模式(从最大可用切换至最大保护在主库执行以下命令即可)
SQL> alter database set standby database to maximize protection;

Database altered.

SQL> alter database open;

Database altered.
--Primary端查询状态：
SQL>  select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PROTECTION   PRIMARY	      MAXIMUM PROTECTION   READ WRITE
--Standby端查询状态（切换只需要操作主库，备库不需要动即可）
SQL>  select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PROTECTION   PHYSICAL STANDBY MAXIMUM PROTECTION   READ ONLY WITH APPLY

验证最大保护模式：
--主库
SQL> insert into nancy select * from all_users;

36 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from nancy;

  COUNT(*)
----------
       144
--备库
SQL> select count(*) from nancy;

  COUNT(*)
----------
       144
--最大性能切换至最大保护模式 
--主库操作
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size		    2259840 bytes
Variable Size		  889193600 bytes
Database Buffers	  146800640 bytes
Redo Buffers		    5632000 bytes
Database mounted.
SQL> alter system set log_archive_dest_2='service=orcl_pd lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl_pd';

System altered.

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 service=orcl_pd lgwr sync affi
						 rm valid_for=(online_logfiles,
						 primary_role) db_unique_name=o
						 rcl_pd

SQL> alter database set standby database to maximize protection;

Database altered.

SQL> alter database open;

Database altered.

SQL>  select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PROTECTION   PRIMARY	      MAXIMUM PROTECTION   READ WRITE

--备库执行
SQL> alter system set log_archive_dest_2='service=orcl_st lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl_st';

System altered.

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 service=orcl_st lgwr sync affi
						 rm valid_for=(online_logfiles,
						 primary_role) db_unique_name=o
						 rcl_st

SQL>  select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PROTECTION   PHYSICAL STANDBY MAXIMUM PROTECTION   READ ONLY WITH APPLY
--最大可用、最大保护切换至最大性能 
--主库操作
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size		    2259840 bytes
Variable Size		  889193600 bytes
Database Buffers	  146800640 bytes
Redo Buffers		    5632000 bytes
Database mounted.
SQL> alter system set log_archive_dest_2='service=orcl_pd valid_for=(online_logfiles,primary_role) db_unique_name=orcl_pd';

System altered.

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 service=orcl_pd valid_for=(onl
						 ine_logfiles,primary_role) db_
						 unique_name=orcl_pd
						 
SQL> alter database set standby database to maximize PERFORMANCE;

Database altered.

SQL> alter database open;

Database altered.


SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE  PRIMARY	      MAXIMUM PERFORMANCE  READ WRITE

--备库
SQL> alter system set log_archive_dest_2='service=orcl_st valid_for=(online_logfiles,primary_role) db_unique_name=orcl_st';

System altered.

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE  PHYSICAL STANDBY MAXIMUM PERFORMANCE  READ ONLY WITH APPLY

--验证最大性能模式
--主库执行
SQL> insert into nancy select * from all_users; 

36 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from nancy;

  COUNT(*)
----------
       216
--备库查询
SQL> select count(*) from nancy;

  COUNT(*)
----------
       216
http://www.cndba.cn/cndba/leo1990/article/2011
http://www.cndba.cn/cndba/leo1990/article/2011
http://www.cndba.cn/cndba/leo1990/article/2011
http://www.cndba.cn/cndba/leo1990/article/2011
http://www.cndba.cn/cndba/leo1990/article/2011 http://www.cndba.cn/cndba/leo1990/article/2011 http://www.cndba.cn/cndba/leo1990/article/2011 http://www.cndba.cn/cndba/leo1990/article/2011 http://www.cndba.cn/cndba/leo1990/article/2011 http://www.cndba.cn/cndba/leo1990/article/2011

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
leo

leo

关注

坚持你的坚持

  • 202
    原创
  • 0
    翻译
  • 39
    转载
  • 16
    评论
  • 访问：765153次
  • 积分：1270
  • 等级：核心会员
  • 排名：第8名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ