DG：三种模式切换

应用归档日志方式进行数据同步
SQL> alter system set log_archive_dest_2='SERVICE=standby arch noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=spfile;

应用在线日志方式进行数据同步
SYNC：
SQL> alter system set log_archive_dest_2='SERVICE=standby lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=spfile;

ASYNC:
SQL> alter system set log_archive_dest_2='SERVICE=standby lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=standby';

DG切换模式
最大性能模式--切换到--&gt最大高可用 (MAXIMUM PERFORMANCE——>MAXIMUM AVAILABILITY)

1.192.168.163.6：
SQL> shutdown immediate

2.192.168.163.18
SQL> alter database recover managed standby database cancel;

3.192.168.163.6
SQL>
SQL>
SQL> startup mount

SQL> alter database set standby database to maximize availability;

SQL> alter system set log_archive_dest_2='SERVICE=PROD_STD LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STD' scope=spfile;

System altered.

4.192.168.163.18
SQL> startup nomount;

SQL> alter database mount standby database;

SQL> alter system set log_archive_dest_2='SERVICE=PROD_STD LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STD' scope=spfile;

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

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

OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM AVAILABILITY PHYSICAL STANDBY NOT ALLOWED

5.192.168.163.6
SQL> shutdown immediate

SQL> startup

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=PROD_STD LGWR SYNC VAL
ID_FOR=(ONLINE_LOGFILES,PRIMAR
Y_ROLE) DB_UNIQUE_NAME=PROD_ST
D
6.192.168.163.18
SQL> alter database open;

SQL> alter database recover managed standby database using current logfile disconnect from session;

最大高可用--切换到--&gt最保护能模式 (MAXIMUM AVAILABILITY——>MAXIMUM PROTECTION)
（因为最大可用模式和最大保护模式对应的参数都是LGWR和SYNC,所以不需要再改参数了）

1.192.168.163.6
SQL> shutdown immediate

SQL> startup mount

SQL> alter database set standby database to maximize protection;

SQL> show parameter log_archive_dest_2

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

OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PROTECTION PHYSICAL STANDBY NOT ALLOWED

最大保护模式转最大性能模式 (MAXIMUM PROTECTION——>MAXIMUM PERFORMANCE)
（最大性能参数问LGWR、SYNC或者ASYNC或者ARCH、SYNC，因为上面已经改为LGWR、SYNC，可以不需要再设置了，直接转换）
本文进行了转换将LGWR SYNC转换为LGWR ASYNC，最大性能模式

1.192.168.163.6
SQL> shutdown immediate

2.192.168.163.18
SQL> alter database recover managed standby database cancel;

SQL> shutdown immediate

3.192.168.163.6
SQL> alter system set log_archive_dest_2='SERVICE=PROD_STD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STD' scope=spfile;

4.192.168.163.18
SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter system set log_archive_dest_2='SERVICE=PROD_STD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD_STD' scope=spfile;

5.192.168.163.6
SQL> shutdown immediate

SQL> startup

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

OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ WRITE MAXIMUM PERFORMANCE PRIMARY SESSIONS ACTIVE

6.192.168.163.18
SQL> shutdown immediate

SQL> startup

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

OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED

DG：三种模式切换

推荐阅读