詳解Oracle dg 三種模式切換

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 三種模式切換的相關知識，希望大家喜歡。