DG 3种保护模式切换

xiaoxiao2022-06-24 192

最大保护模式

这种保护模式确保如果主数据库故障不会发生数据丢失。要提供这种级别的保护，恢复每个事务所需的重做数据必须在事务提交之前同时

写到本地联机重做日志和至少一个备数据库上的备重做日志。要确保不发生数据丢失，如果故障导致主数据库无法写重做流到至少一个事务一致性备数据

库的备重做日志时，主数据库会关闭。

最大可用性模式

这种保护模式提供了可能的最高级别的数据保护，而不用与主数据库的可用性相折衷。与最大保护模式相同，在恢复事务所需的重做写

到本地联机重做日志和至少一个事务一致性备数据库上的备重做日志之前，事务将不会提交。与最大保护模式不同的是，如果故障导致主数据库无法写重

做流到异地备重做日志时，主数据库不会关闭。替代地，主数据库以最大性能模式运行直到故障消除，并且解决所有重做日志文件中的中断。当所有中断

解决之后，主数据库自动继续以最大可用性模式运行。

这种模式确保如果主数据库故障，但是只有当第二次故障没有阻止完整的重做数据集从主数据库发送到至少一个备数据库时，不发生数据丢失。

最大性能模式

这种保护模式（默认）提供了可能的最高级别的数据保护，而不影响主数据库的性能。这是通过允许事务在恢复该事务所需重做数据在

写到本地联机重做日志后立即提交而实现的。主数据库的重做数据流也写到至少一个备数据库，但是那个重做流相对于创建重做数据的事务是异步写的。

当所用的网络连接有足够的带宽，这种模式提供了近似于最大可用性模式的数据保护级别，并且对主数据库性能的影响最小。

最大保护和最大可用性模式需要备重做日志文件配置在配置中的至少一个备数据库上。所有三种保护模式需要在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

--备库

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

专利

最新回复(0)