详解Oracle dg 三种模式切换

oracle dg 三大模式切换

===================================
1  最大性能模式maximum performance   ------默认模式
===================================

一 最大性能模式特点

192.168.1.181
sql> select database_role,protection_mode,protection_level from v$database;
database_role  protection_mode   protection_level
---------------- -------------------- --------------------
primary     maximum performance maximum performance
sql> col dest_name for a25
sql> select dest_name,status from v$archive_dest_status;
dest_name         status
------------------------- ---------
log_archive_dest_1    valid
log_archive_dest_2    valid
sql> show parameter log_archive
name                 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=orcl
log_archive_dest_2          string   service=db_db01 lgwr async val
                         id_for=(online_logfiles,primar
                         y_roles) db_unique_name=db01
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_orcl
oldest online log sequence   31
next log sequence to archive  33
current log sequence      33
192.168.1.183
sql> select database_role,protection_mode,protection_level from v$database;
database_role  protection_mode   protection_level
---------------- -------------------- --------------------
physical standby maximum performance maximum performance
sql> col dest_name for a25
sql> select dest_name,status from v$archive_dest_status;
dest_name         status
------------------------- ---------
log_archive_dest_1    valid
log_archive_dest_2    valid
sql> show parameter log_archive
name                 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=db01
log_archive_dest_2          string   service=db_orcl lgwr async val
                         id_for=(online_logfiles,primar
                         y_roles) db_unique_name=orcl
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_orcl
oldest online log sequence   31
next log sequence to archive  33
current log sequence      33
192.168.1.181
sql> alter system switch logfile;
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_orcl
oldest online log sequence   32
next log sequence to archive  34
current log sequence      34
192.168.1.183
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_db01
oldest online log sequence   32
next log sequence to archive  0
current log sequence      34

===================================
2 最大性能模式--切换到-->最大高可用  (默认是最大性能模式---maximum performance)
===================================

192.168.1.181
sql> select database_role,protection_mode,protection_level from v$database; 
database_role  protection_mode   protection_level
---------------- -------------------- --------------------
primary     maximum performance maximum performance
sql> show parameter log_archive_dest_2
name                 type    value
------------------------------------ ----------- ------------------------------
log_archive_dest_2          string   service=db_db01 lgwr async val
                         id_for=(online_logfiles,primar
                         y_roles) db_unique_name=db01
192.168.1.181
sql> shutdown immediate
192.168.1.183
sql> alter database recover managed standby database cancel;
sql> shutdown immediate
192.168.1.181
sql> 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.183
sql> startup nomount
sql> 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 immediate
sql> startup nomount
sql> alter database mount standby database;
192.168.1.181
sql> startup
sql> col dest_name for a25
sql> select dest_name,status from v$archive_dest_status;
dest_name         status
------------------------- ---------
log_archive_dest_1    valid
log_archive_dest_2    valid
sql> show parameter log_archive_dest_2
name                 type    value
------------------------------------ ----------- ------------------------------
log_archive_dest_2          string   service=db_db01 lgwr sync vali
                         d_for=(online_logfiles,primary
                         _roles) db_unique_name=db01
sql> select database_role,protection_level,protection_mode from v$database;
database_role  protection_level   protection_mode
---------------- -------------------- --------------------
primary     maximum availability maximum availability
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_orcl
oldest online log sequence   34
next log sequence to archive  36
current log sequence      36
192.168.1.183
sql> col dest_name for a25
sql> select dest_name,status from v$archive_dest_status;
dest_name         status
------------------------- ---------
log_archive_dest_1    valid
log_archive_dest_2    valid
sql> show parameter log_archive_dest_2
name                 type    value
------------------------------------ ----------- ------------------------------
log_archive_dest_2          string   service=db_orcl lgwr sync vali
                         d_for=(online_logfiles,primary
                         _roles) db_unique_name=orcl
sql> select database_role,protection_level,protection_mode from v$database;
database_role  protection_level   protection_mode
---------------- -------------------- --------------------
physical standby maximum availability maximum availability
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_db01
oldest online log sequence   35
next log sequence to archive  0
current log sequence      36
192.168.1.181
sql> alter system switch logfile;
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_orcl
oldest online log sequence   35
next log sequence to archive  37
current log sequence      37
192.168.1.183
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_db01
oldest online log sequence   36
next log sequence to archive  0
current log sequence      37

===================================
3 最大高可用--切换到-->最保护能模式
===================================

dg最大保护模式maximum protection

192.168.1.181
sql> shutdown immediate
192.168.1.183
sql> shutdown immediate
192.168.1.181
sql> alter database set standby database to maximize protection;
sql> shutdown immediate
192.168.1.183
sql> startup nomount
sql> alter database mount standby database;
192.168.1.181
sql> startup
sql> col dest_name for a25
sql> select dest_name,status from v$archive_dest_status;
dest_name         status
------------------------- ---------
log_archive_dest_1    valid
log_archive_dest_2    valid
sql> show parameter log_archive_dest_2
name                 type    value
------------------------------------ ----------- ------------------------------
log_archive_dest_2          string   service=db_db01 lgwr sync vali
                         d_for=(online_logfiles,primary
                         _roles) db_unique_name=db01
sql> select database_role,protection_level,protection_mode from v$database;
database_role  protection_level   protection_mode
---------------- -------------------- --------------------
primary     maximum protection  maximum protection
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_orcl
oldest online log sequence   37
next log sequence to archive  39
current log sequence      39
192.168.1.183
sql> col dest_name for a25
sql> select dest_name,status from v$archive_dest_status;
dest_name         status
------------------------- ---------
log_archive_dest_1    valid
log_archive_dest_2    valid
sql> show parameter log_archive_dest_2
name                 type    value
------------------------------------ ----------- ------------------------------
log_archive_dest_2          string   service=db_db01 lgwr sync vali
                         d_for=(online_logfiles,primary
                         _roles) db_unique_name=db01
sql> select database_role,protection_level,protection_mode from v$database;
database_role  protection_level   protection_mode
---------------- -------------------- --------------------
primary     maximum protection  maximum protection
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_db01
oldest online log sequence   37
next log sequence to archive  0
current log sequence      39
192.168.1.181
sql> alter system switch logfile;
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_orcl
oldest online log sequence   38
next log sequence to archive  40
current log sequence      40
192.168.1.183
sql> archive log list
database log mode       archive mode
automatic archival       enabled
archive destination      /home/oracle/arch_db01
oldest online log sequence   37
next log sequence to archive  0
current log sequence      40

附：oracle dg管理模式和只读模式相互切换

将standby数据库开启至只读模式(用于primary非常忙时，可以在standby跑一些报表)

$sqlplus “/as sysdba”
sql>startup mount
sql>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 三种模式切换的相关知识，希望大家喜欢。