oracle dg 三大模式切換
===================================
1 最大性能模式MAXIMUM PERFORMANCE ------默認模式
===================================
一 最大性能模式特點
192.168.1.181SQL> select database_role,protection_mode,protection_level from v$database;DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL---------------- -------------------- --------------------PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCESQL> col dest_name for a25SQL> select dest_name,status from v$archive_dest_status;DEST_NAME STATUS------------------------- ---------LOG_ARCHIVE_DEST_1 VALIDLOG_ARCHIVE_DEST_2 VALIDSQL> show parameter log_archiveNAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_config string dg_config=(orcl,db01)log_archive_dest_1 string location=/home/oracle/arch_orc l valid_for=(all_logfiles,all_ roles) db_unique_name=orcllog_archive_dest_2 string service=db_db01 LGWR ASYNC val id_for=(online_logfiles,primar y_roles) db_unique_name=db01SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_orclOldest online log sequence 31Next log sequence to archive 33Current log sequence 33192.168.1.183SQL> select database_role,protection_mode,protection_level from v$database;DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL---------------- -------------------- --------------------PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCESQL> col dest_name for a25SQL> select dest_name,status from v$archive_dest_status;DEST_NAME STATUS------------------------- ---------LOG_ARCHIVE_DEST_1 VALIDLOG_ARCHIVE_DEST_2 VALIDSQL> show parameter log_archiveNAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_config string dg_config=(db01,orcl)log_archive_dest_1 string location=/home/oracle/arch_db0 1 valid_for=(all_logfiles,all_ roles) db_unique_name=db01log_archive_dest_2 string service=db_orcl LGWR ASYNC val id_for=(online_logfiles,primar y_roles) db_unique_name=orclSQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_orclOldest online log sequence 31Next log sequence to archive 33Current log sequence 33192.168.1.181SQL> alter system switch logfile;SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_orclOldest online log sequence 32Next log sequence to archive 34Current log sequence 34192.168.1.183SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_db01Oldest online log sequence 32Next log sequence to archive 0Current log sequence 34
===================================
2 最大性能模式--切換到-->最大高可用 (默認是最大性能模式---MAXIMUM PERFORMANCE)
===================================
192.168.1.181SQL> select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database; DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL---------------- -------------------- --------------------PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCESQL> show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string service=db_db01 LGWR ASYNC val id_for=(online_logfiles,primar y_roles) db_unique_name=db01192.168.1.181SQL> shutdown immediate192.168.1.183SQL> alter database recover managed standby database cancel;SQL> shutdown immediate192.168.1.181SQL> startup mount;SQL> alter database set standby database to maximize availability;SQL> alter system set log_archive_dest_2='service=db_db01 LGWR SYNC valid_for=(online_logfiles,primary_roles) db_unique_name=db01' scope=spfile;192.168.1.183SQL> startup nomountSQL> alter database mount standby database;SQL> alter system set log_archive_dest_2='service=db_orcl LGWR SYNC valid_for=(online_logfiles,primary_roles) db_unique_name=orcl' scope=spfile;SQL> shutdown immediateSQL> startup nomountSQL> alter database mount standby database;192.168.1.181SQL> startupSQL> col dest_name for a25SQL> select dest_name,status from v$archive_dest_status;DEST_NAME STATUS------------------------- ---------LOG_ARCHIVE_DEST_1 VALIDLOG_ARCHIVE_DEST_2 VALIDSQL> show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string service=db_db01 LGWR SYNC vali d_for=(online_logfiles,primary _roles) db_unique_name=db01SQL> select database_role,protection_level,protection_mode from v$database;DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE---------------- -------------------- --------------------PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITYSQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_orclOldest online log sequence 34Next log sequence to archive 36Current log sequence 36192.168.1.183SQL> col dest_name for a25SQL> select dest_name,status from v$archive_dest_status;DEST_NAME STATUS------------------------- ---------LOG_ARCHIVE_DEST_1 VALIDLOG_ARCHIVE_DEST_2 VALIDSQL> show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string service=db_orcl LGWR SYNC vali d_for=(online_logfiles,primary _roles) db_unique_name=orclSQL> select database_role,protection_level,protection_mode from v$database;DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE---------------- -------------------- --------------------PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITYSQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_db01Oldest online log sequence 35Next log sequence to archive 0Current log sequence 36192.168.1.181SQL> alter system switch logfile;SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_orclOldest online log sequence 35Next log sequence to archive 37Current log sequence 37192.168.1.183SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_db01Oldest online log sequence 36Next log sequence to archive 0Current log sequence 37
===================================
3 最大高可用--切換到-->最保護能模式
===================================
DG最大保護模式Maximum protection
192.168.1.181SQL> shutdown immediate192.168.1.183SQL> shutdown immediate192.168.1.181SQL> alter database set standby database to maximize protection;SQL> shutdown immediate192.168.1.183SQL> startup nomountSQL> alter database mount standby database;192.168.1.181SQL> startupSQL> col dest_name for a25SQL> select dest_name,status from v$archive_dest_status;DEST_NAME STATUS------------------------- ---------LOG_ARCHIVE_DEST_1 VALIDLOG_ARCHIVE_DEST_2 VALIDSQL> show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string service=db_db01 LGWR SYNC vali d_for=(online_logfiles,primary _roles) db_unique_name=db01SQL> select database_role,protection_level,protection_mode from v$database;DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE---------------- -------------------- --------------------PRIMARY MAXIMUM PROTECTION MAXIMUM PROTECTIONSQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_orclOldest online log sequence 37Next log sequence to archive 39Current log sequence 39192.168.1.183SQL> col dest_name for a25SQL> select dest_name,status from v$archive_dest_status;DEST_NAME STATUS------------------------- ---------LOG_ARCHIVE_DEST_1 VALIDLOG_ARCHIVE_DEST_2 VALIDSQL> show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string service=db_db01 LGWR SYNC vali d_for=(online_logfiles,primary _roles) db_unique_name=db01SQL> select database_role,protection_level,protection_mode from v$database;DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE---------------- -------------------- --------------------PRIMARY MAXIMUM PROTECTION MAXIMUM PROTECTIONSQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_db01Oldest online log sequence 37Next log sequence to archive 0Current log sequence 39192.168.1.181SQL> alter system switch logfile;SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_orclOldest online log sequence 38Next log sequence to archive 40Current log sequence 40192.168.1.183SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /home/oracle/arch_db01Oldest online log sequence 37Next log sequence to archive 0Current log sequence 40
附：Oracle DG管理模式和只讀模式相互切換
將standby數據庫開啟至只讀模式(用于primary非常忙時，可以在standby跑一些報表)
$sqlplus “/as sysdba”SQL>startup mountSQL>alter database open read only;[@more@]
將只讀模式standby數據庫切換至管理模式
$sqlplus “/as sysdba”SQL>alter database recover managed standby database disconnect from session;
將管理模式的standby數據庫切換至只讀模式
$sqlplus “/as sysdba”SQL>alter database recover managed standby database cancel;SQL>alter database open read only;
以上內容給大家介紹了Oracle dg 三種模式切換的相關知識，希望大家喜歡。
