1.1.切换保护模式示例
默认DG保护模式是最大性能,在此基础上切换到其他保护模式。
1.1.1.DG最大性能切换最大可用
1)查看主备库当前DG保护模式
查看主库当前保护模式
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
2)修改参数
SQL> alter system set log_archive_dest_2='service=pdbcndba_s sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s';
System altered.
3)切换保护模式
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
Database altered.
4)验证查看主备库保护模式是否切换成功
查看主库保护模式
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
5)备库修改参数
此步骤可以不用做,只是为了方便switchover。
SQL> alter system set log_archive_dest_2='service=pdbcndba_p sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_p';
System altered.
1.1.2.DG最大可用切换到最大保护
1)查看主备库当前保护模式
查看主库保护模式
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
2)切换保护模式
因为最大可用和最大保护可以使用相同的参数这里直接可以做切换
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
Database altered.
3)验证主备库保护模式是否切换成功
主库查看保护模式
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> 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
1.1.3.DG最大保护切换到最大可用
1)查看主备库当前DG保护模式
主库查看保护模式
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> 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
2)切换保护模式
因为最大可用和最大保护可以使用相同的参数这里直接可以做切换
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
Database altered.
4)验证查看主备库保护模式是否切换成功
查看主库保护模式
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
1.1.4.DG最大可用切换到最大性能
1)查看主备库当前DG保护模式
查看主库保护模式
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
2)修改参数
SQL> alter system set log_archive_dest_2='service=pdbcndba_s async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s';
System altered.
3)切换保护模式
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Database altered.
4)验证查看主备库保护模式是否切换成功
查看主库保护模式
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
5)备库修改参数
此步骤可以不用做,只是为了方便switchover。
SQL> alter system set log_archive_dest_2='service=pdbcndba_p async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_p';
System altered.
1.1.5.DG最大性能切换到最大保护
1)查看主备库当前DG保护模式
查看主库保护模式
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
2)修改参数
SQL> alter system set log_archive_dest_2='service=pdbcndba_s sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s';
System altered.
3)切换保护模式
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
根据提示DG最大性能模式切换到最大保护模式是需要主库在MOUNT状态下切换,还有另外一种方法在线可以切换,那就是先切换到最大可用模式在切换到最大保护模式。
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
Database altered.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
Database altered.
4)验证查看主备库保护模式是否切换成功
查看主库保护模式
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> 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
5)备库修改参数
此步骤可以不用做,只是为了方便switchover。
SQL> alter system set log_archive_dest_2='service=pdbcndba_p sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_p';
System altered.
1.1.6.DG最大保护切换到最大性能
1)查看主备库当前DG保护模式
查看主库保护模式
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> 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
2)修改参数
SQL> alter system set log_archive_dest_2='service=pdbcndba_s async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s';
alter system set log_archive_dest_2='service=pdbcndba_s async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16159: Cannot change protected standby destination attributes
3)切换保护模式
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Database altered.
4)再次修改参数
SQL> alter system set log_archive_dest_2='service=pdbcndba_s async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s';
System altered.
5)验证查看主备库保护模式是否切换成功
查看主库保护模式
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
6)备库修改参数
此步骤可以不用做,只是为了方便switchover。
SQL> alter system set log_archive_dest_2='service=pdbcndba_p async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_p';
System altered.