oracle dg 三大模式切换
===================================
1 最大性能模式MAXIMUM PERFORMANCE ------默认模式
===================================
一 最大性能模式特点
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
|
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)
===================================
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
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跑一些报表)
|
1
2
3
4
|
$sqlplus “/as sysdba”
SQL>startup mount
SQL>alter database open read only;
[@more@]
|
将只读模式standby数据库切换至管理模式
|
1
2
|
$sqlplus “/as sysdba”
SQL>alter database recover managed standby database disconnect from session;
|
将管理模式的standby数据库切换至只读模式
|
1
2
3
|
$sqlplus “/as sysdba”
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
|
以上内容给大家介绍了Oracle dg 三种模式切换的相关知识，希望大家喜欢。