一：最大效能轉最大可用（預設DG是最大效能模式）

1.確認主備資料庫模式

1）主庫：

SYS@ORA11GR2>select protection_mode,protection_level from v$database;

 

PROTECTION_MODE      PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

 

SYS@ORA11GR2>

SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;

 

NAME      PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS

--------- -------------------- ---------------- --------------------

ORA11GR2  MAXIMUM PERFORMANCE  PRIMARY          SESSIONS ACTIVE

 

SYS@ORA11GR2>

 

2）備庫：

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

 

NAME      PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS

--------- -------------------- ---------------- --------------------

ORA11GR2  MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

 

2.主庫重啟到mount

SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

SYS@ORA11GR2>startup mount;

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

Database mounted.

SYS@ORA11GR2>

 

3.修改引數

SYS@ORA11GR2>alter system set LOG_ARCHIVE_DEST_2='SERVICE=ocm LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OCMU';

 

System altered.

 

SYS@ORA11GR2>alter database set standby database to maximize availability;

 

Database altered.

 

 

4.開啟主庫驗證：

SYS@ORA11GR2>alter database open;

 

Database altered.

 

SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;

 

NAME      PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS

--------- -------------------- ---------------- --------------------

ORA11GR2  MAXIMUM AVAILABILITY PRIMARY          SESSIONS ACTIVE

 

5.備庫確認：

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

 

NAME      PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS

--------- -------------------- ---------------- --------------------

ORA11GR2  MAXIMUM AVAILABILITY PHYSICAL STANDBY NOT ALLOWED

 

——最好把備庫也改一下，以便在主備切換時，依舊是最大可用：

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ora lgwr sync VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA11GR2';

 

System altered.

 

二：最大可用模式切換為最大保護模式

（因為最大可用模式和最大保護模式對應的引數都是LGWR和SYNC,所以不需要再改引數了）

 

1.主庫操作：

SYS@ORA11GR2>alter database set standby database to maximize protection;

 

Database altered.

 

——檢視狀態：

SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;

 

NAME      PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS

--------- -------------------- ---------------- --------------------

ORA11GR2  MAXIMUM PROTECTION   PRIMARY          SESSIONS ACTIVE

 

2.檢視備庫狀態：

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

 

NAME      PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS

--------- -------------------- ---------------- --------------------

ORA11GR2  MAXIMUM PROTECTION   PHYSICAL STANDBY NOT ALLOWED

 

備庫修改引數，與主庫相同，上面已經修改過了；

 

三：最大保護模式轉最大效能模式

（最大效能引數問LGWR、SYNC或者ASYNC或者ARCH、SYNC，因為上面已經改為LGWR、SYNC，所以不需要再設定了，直接轉換）

 

1.主庫：

SYS@ORA11GR2>alter database set standby database to maximize performance;

 

Database altered.

 

SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;

 

NAME      PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS

--------- -------------------- ---------------- --------------------

ORA11GR2  MAXIMUM PERFORMANCE  PRIMARY          SESSIONS ACTIVE

 

SYS@ORA11GR2>

 

2.備庫：

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

 

NAME      PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER_STATUS

--------- -------------------- ---------------- --------------------

ORA11GR2  MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

 

最好把備庫也改一下，以便在主備切換時，依舊是最大效能。