一、初始化环境二、主库操作 2.1 修改forcelogging、开闪回 2.2 添加standby log 2.3 修改参数 2.4 修改监听 2.5 修改tnsnames.ora三、主库密码文件拷贝到备库四、备库操作 4.1 修改监听 4.2 备库修改到NOMOUNT状态 4.3 duplicate复制搭建DG 4.4 实时应用五、配置dgmgrl 5.1 基础配置 5.2 配置FSFO 5.3 重建DG
一、初始化环境
DG环境规划
| 项目 | 主库 | 物理备库 |
|---|---|---|
| db 类型 | 单实例 | 单实例 |
| db version | 11.2.0.4.0 | 11.2.0.4.0 |
| db 存储 | FS | FS |
| OS版本 | RHEL7.6 64位 | CentOS7.6 64位 |
| OS hostname | LHR11G | LHR11GDG |
| IP地址 | 192.168.68.68 | 192.168.68.69 |
| ORACLE_SID | LHR11G | LHR11GDG |
| db_name/GLOBAL_DBNAME | LHR11G | LHR11G |
| db_unique_name | LHR11G | LHR11GDG |
| TNS_NAME | LHR11G | LHR11GDG |
| 监听端口 | 1521 | 1521 |
| 映射的主机端口 | 1528 | 1529 |
| ORACLE_HOME | /u01/app/oracle/product/11.2.0.4/dbhome_1 | /u01/app/oracle/product/11.2.0.4/dbhome_1 |
| dbid | 2007947551 | 2007947551 |
1-- 创建DG的网络 2docker network create --subnet=192.168.68.0/16 mhalhr 3docker network inspect mhalhr 4 5-- 创建2台主机 6docker run -itd --name LHR11G -h LHR11G \ 7 -p 1528:1521 -p 1128:1158 -p 228:22 -p 3398:3389 \ 8 --network mhalhr --ip 192.168.68.68 \ 9 --privileged=true \ 10 lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init 11 12 13docker run -itd --name LHR11GDG -h LHR11GDG \ 14 -p 1529:1521 -p 1129:1158 -p 229:22 -p 3399:3389 \ 15 --network mhalhr --ip 192.168.68.69 \ 16 --privileged=true \ 17 lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init 18 19 20 -- 添加网卡 21docker network connect bridge LHR11G 22docker network connect bridge LHR11GDG 23 24 25-- 进入容器 26docker exec -it LHR11G bash 27docker exec -it LHR11GDG bash 28 29--备库删除原有的数据库 30dbca -silent -deleteDatabase -sourceDB LHR11G
二、主库操作
2.1 修改forcelogging、开闪回
1alter database force logging; 2alter database flashback on; 3select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from v$database;
2.2 添加standby log
1select * from v$standby_log;
2select group#,bytes/1024/1024 ||'M' from v$log ;
3SELECT * FROM V$LOGFILE;
4
5alter database add standby logfile group 4 ('/u01/app/oracle/oradata/LHR11G/standby_redo04.log') size 50m;
6alter database add standby logfile group 5 ('/u01/app/oracle/oradata/LHR11G/standby_redo05.log') size 50m;
7alter database add standby logfile group 6 ('/u01/app/oracle/oradata/LHR11G/standby_redo06.log') size 50m;
2.3 修改参数
1alter system set db_unique_name='LHR11G' scope=spfile; 2alter system set log_archive_config='DG_CONFIG=(LHR11G,LHR11GDG)'; 3alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=LHR11G valid_for=(ALL_LOGFILES,ALL_ROLES)'; 4alter system set log_archive_dest_2='SERVICE=LHR11GDG LGWR ASYNC db_unique_name=LHR11GDG valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'; 5alter system set log_archive_dest_state_1=ENABLE; 6alter system set log_archive_dest_state_2=ENABLE; 7alter system set log_archive_max_processes=4; 8alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile; 9 10alter system set db_file_name_convert='LHR11GDG','LHR11G' scope=spfile; 11alter system set log_file_name_convert='LHR11GDG','LHR11G' scope=spfile; 12alter system set standby_file_management='AUTO'; 13alter system set fal_server='LHR11GDG'; 14alter system set fal_client='LHR11G';
2.4 修改监听
1SID_LIST_LISTENER = 2 (SID_LIST = 3 (SID_DESC = 4 (GLOBAL_DBNAME = LHR11G) 5 (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1) 6 (SID_NAME= LHR11G) 7 ) 8 (SID_DESC = 9 (GLOBAL_DBNAME = LHR11G_dgmgrl) 10 (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1) 11 (SID_NAME= LHR11G) 12 ) 13)
2.5 修改tnsnames.ora
1LHR11G = 2 (DESCRIPTION = 3 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.68)(PORT = 1521)) 4 (CONNECT_DATA = 5 (SERVER = DEDICATED) 6 (SERVICE_NAME = LHR11G) 7 ) 8 ) 9 10LHR11GDG = 11 (DESCRIPTION = 12 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.69)(PORT = 1521)) 13 (CONNECT_DATA = 14 (SERVER = DEDICATED) 15 (SERVICE_NAME = LHR11GDG) 16 ) 17 )
三、主库密码文件拷贝到备库
1docker cp LHR11G:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11G . 2docker cp orapwLHR11G LHR11GDG:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11GDG 3chown oracle.oinstall /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11GDG
四、备库操作
4.1 修改监听
1SID_LIST_LISTENER = 2 (SID_LIST = 3 (SID_DESC = 4 (GLOBAL_DBNAME = LHR11GDG) 5 (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1) 6 (SID_NAME= LHR11GDG) 7 ) 8 (SID_DESC = 9 (GLOBAL_DBNAME = LHR11GDG_dgmgrl) 10 (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1) 11 (SID_NAME= LHR11GDG) 12 ) 13)
4.2 备库修改到NOMOUNT状态
1mkdir -p /u01/app/oracle/oradata/LHR11GDG/ 2mkdir -p /u01/app/oracle/admin/LHR11GDG/adump 3 4echo "db_name=LHR11G" > $ORACLE_HOME/dbs/initLHR11GDG.ora 5 6ORACLE_SID=LHR11GDG 7startup nomount
4.3 duplicate复制搭建DG
1rman target sys/lhr@LHR11G auxiliary sys/lhr@LHR11GDG 2 3 4duplicate target database 5for standby nofilenamecheck 6from active database 7DORECOVER 8spfile 9set db_unique_name='LHR11GDG' 10set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHR11GDG' 11set standby_file_management='AUTO' 12set fal_server='LHR11G' 13set fal_client='LHR11GDG' 14set control_files='/u01/app/oracle/oradata/LHR11GDG/control01.ctl' 15set db_file_name_convert='LHR11G','LHR11GDG' 16set log_file_name_convert='LHR11G','LHR11GDG' 17set audit_file_dest='/u01/app/oracle/admin/LHR11GDG/adump' 18set sga_max_size='346030080' 19;
执行过程：
1[oracle@lhr11gdg dbs]$ rman target sys/lhr@LHR11G auxiliary sys/lhr@LHR11GDG
2
3Recovery Manager: Release 11.2.0.4.0 - Production on Tue Oct 27 14:07:44 2020
4
5Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
6
7connected to target database: LHR11G (DBID=2007947551)
8connected to auxiliary database: LHR11G (not mounted)
9
10RMAN>
11RMAN> duplicate target database
122> for standby nofilenamecheck
133> from active database
144> DORECOVER
155> spfile
166> set db_unique_name='LHR11GDG'
177> set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHR11GDG'
188> set standby_file_management='AUTO'
199> set fal_server='LHR11G'
2010> set fal_client='LHR11GDG'
2111> set control_files='/u01/app/oracle/oradata/LHR11GDG/control01.ctl'
2212> set db_file_name_convert='LHR11G','LHR11GDG'
2313> set log_file_name_convert='LHR11G','LHR11GDG'
2414> set audit_file_dest='/u01/app/oracle/admin/LHR11GDG/adump'
2515> set sga_max_size='346030080'
2616> ;
27
28Starting Duplicate Db at 2020-10-27 14:07:51
29using target database control file instead of recovery catalog
30allocated channel: ORA_AUX_DISK_1
31channel ORA_AUX_DISK_1: SID=396 device type=DISK
32
33contents of Memory Script:
34{
35 backup as copy reuse
36 targetfile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11G' auxiliary format
37 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11GDG' targetfile
38 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11G.ora' auxiliary format
39 '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11GDG.ora' ;
40 sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11GDG.ora''";
41}
42executing Memory Script
43
44Starting backup at 2020-10-27 14:07:53
45allocated channel: ORA_DISK_1
46channel ORA_DISK_1: SID=161 device type=DISK
47Finished backup at 2020-10-27 14:07:55
48
49sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11GDG.ora''
50
51contents of Memory Script:
52{
53 sql clone "alter system set db_unique_name =
54 ''LHR11GDG'' comment=
55 '''' scope=spfile";
56 sql clone "alter system set log_archive_dest_1 =
57 ''LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHR11GDG'' comment=
58 '''' scope=spfile";
59 sql clone "alter system set standby_file_management =
60 ''AUTO'' comment=
61 '''' scope=spfile";
62 sql clone "alter system set fal_server =
63 ''LHR11G'' comment=
64 '''' scope=spfile";
65 sql clone "alter system set fal_client =
66 ''LHR11GDG'' comment=
67 '''' scope=spfile";
68 sql clone "alter system set control_files =
69 ''/u01/app/oracle/oradata/LHR11GDG/control01.ctl'' comment=
70 '''' scope=spfile";
71 sql clone "alter system set db_file_name_convert =
72 ''LHR11G'', ''LHR11GDG'' comment=
73 '''' scope=spfile";
74 sql clone "alter system set log_file_name_convert =
75 ''LHR11G'', ''LHR11GDG'' comment=
76 '''' scope=spfile";
77 sql clone "alter system set audit_file_dest =
78 ''/u01/app/oracle/admin/LHR11GDG/adump'' comment=
79 '''' scope=spfile";
80 sql clone "alter system set sga_max_size =
81 346030080 comment=
82 '''' scope=spfile";
83 shutdown clone immediate;
84 startup clone nomount;
85}
86executing Memory Script
87
88sql statement: alter system set db_unique_name = ''LHR11GDG'' comment= '''' scope=spfile
89
90sql statement: alter system set log_archive_dest_1 = ''LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHR11GDG'' comment= '''' scope=spfile
91
92sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
93
94sql statement: alter system set fal_server = ''LHR11G'' comment= '''' scope=spfile
95
96sql statement: alter system set fal_client = ''LHR11GDG'' comment= '''' scope=spfile
97
98sql statement: alter system set control_files = ''/u01/app/oracle/oradata/LHR11GDG/control01.ctl'' comment= '''' scope=spfile
99
100sql statement: alter system set db_file_name_convert = ''LHR11G'', ''LHR11GDG'' comment= '''' scope=spfile
101
102sql statement: alter system set log_file_name_convert = ''LHR11G'', ''LHR11GDG'' comment= '''' scope=spfile
103
104sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/LHR11GDG/adump'' comment= '''' scope=spfile
105
106sql statement: alter system set sga_max_size = 346030080 comment= '''' scope=spfile
107
108Oracle instance shut down
109
110connected to auxiliary database (not started)
111Oracle instance started
112
113Total System Global Area 346562560 bytes
114
115Fixed Size 2253144 bytes
116Variable Size 209718952 bytes
117Database Buffers 130023424 bytes
118Redo Buffers 4567040 bytes
119
120contents of Memory Script:
121{
122 backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/LHR11GDG/control01.ctl';
123}
124executing Memory Script
125
126Starting backup at 2020-10-27 14:08:04
127using channel ORA_DISK_1
128channel ORA_DISK_1: starting datafile copy
129copying standby control file
130output file name=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_LHR11G.f tag=TAG20201027T140804 RECID=3 STAMP=1054908485
131channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
132Finished backup at 2020-10-27 14:08:06
133
134contents of Memory Script:
135{
136 sql clone 'alter database mount standby database';
137}
138executing Memory Script
139
140sql statement: alter database mount standby database
141
142contents of Memory Script:
143{
144 set newname for tempfile 1 to
145 "/u01/app/oracle/oradata/LHR11GDG/temp01.dbf";
146 switch clone tempfile all;
147 set newname for datafile 1 to
148 "/u01/app/oracle/oradata/LHR11GDG/system01.dbf";
149 set newname for datafile 2 to
150 "/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf";
151 set newname for datafile 3 to
152 "/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf";
153 set newname for datafile 4 to
154 "/u01/app/oracle/oradata/LHR11GDG/users01.dbf";
155 set newname for datafile 5 to
156 "/u01/app/oracle/oradata/LHR11GDG/example01.dbf";
157 backup as copy reuse
158 datafile 1 auxiliary format
159 "/u01/app/oracle/oradata/LHR11GDG/system01.dbf" datafile
160 2 auxiliary format
161 "/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf" datafile
162 3 auxiliary format
163 "/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf" datafile
164 4 auxiliary format
165 "/u01/app/oracle/oradata/LHR11GDG/users01.dbf" datafile
166 5 auxiliary format
167 "/u01/app/oracle/oradata/LHR11GDG/example01.dbf" ;
168 sql 'alter system archive log current';
169}
170executing Memory Script
171
172executing command: SET NEWNAME
173
174renamed tempfile 1 to /u01/app/oracle/oradata/LHR11GDG/temp01.dbf in control file
175
176executing command: SET NEWNAME
177
178executing command: SET NEWNAME
179
180executing command: SET NEWNAME
181
182executing command: SET NEWNAME
183
184executing command: SET NEWNAME
185
186Starting backup at 2020-10-27 14:08:12
187using channel ORA_DISK_1
188channel ORA_DISK_1: starting datafile copy
189input datafile file number=00001 name=/u01/app/oracle/oradata/LHR11G/system01.dbf
190output file name=/u01/app/oracle/oradata/LHR11GDG/system01.dbf tag=TAG20201027T140812
191channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
192channel ORA_DISK_1: starting datafile copy
193input datafile file number=00002 name=/u01/app/oracle/oradata/LHR11G/sysaux01.dbf
194output file name=/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf tag=TAG20201027T140812
195channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
196channel ORA_DISK_1: starting datafile copy
197input datafile file number=00003 name=/u01/app/oracle/oradata/LHR11G/undotbs01.dbf
198output file name=/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf tag=TAG20201027T140812
199channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
200channel ORA_DISK_1: starting datafile copy
201input datafile file number=00005 name=/u01/app/oracle/oradata/LHR11G/example01.dbf
202output file name=/u01/app/oracle/oradata/LHR11GDG/example01.dbf tag=TAG20201027T140812
203channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
204channel ORA_DISK_1: starting datafile copy
205input datafile file number=00004 name=/u01/app/oracle/oradata/LHR11G/users01.dbf
206output file name=/u01/app/oracle/oradata/LHR11GDG/users01.dbf tag=TAG20201027T140812
207channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
208Finished backup at 2020-10-27 14:08:50
209
210sql statement: alter system archive log current
211
212contents of Memory Script:
213{
214 backup as copy reuse
215 archivelog like "/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_26_hshgbgyr_.arc" auxiliary format
216 "/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_%u_.arc" archivelog like
217 "/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_27_hshgcl82_.arc" auxiliary format
218 "/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_%u_.arc" ;
219 catalog clone recovery area;
220 switch clone datafile all;
221}
222executing Memory Script
223
224Starting backup at 2020-10-27 14:08:50
225using channel ORA_DISK_1
226channel ORA_DISK_1: starting archived log copy
227input archived log thread=1 sequence=26 RECID=7 STAMP=1054908501
228output file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc RECID=0 STAMP=0
229channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
230channel ORA_DISK_1: starting archived log copy
231input archived log thread=1 sequence=27 RECID=8 STAMP=1054908530
232output file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc RECID=0 STAMP=0
233channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
234Finished backup at 2020-10-27 14:08:52
235
236searching for all files in the recovery area
237
238List of Files Unknown to the Database
239=====================================
240File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc
241File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc
242cataloging files...
243cataloging done
244
245List of Cataloged Files
246=======================
247File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc
248File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc
249
250datafile 1 switched to datafile copy
251input datafile copy RECID=3 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/system01.dbf
252datafile 2 switched to datafile copy
253input datafile copy RECID=4 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf
254datafile 3 switched to datafile copy
255input datafile copy RECID=5 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf
256datafile 4 switched to datafile copy
257input datafile copy RECID=6 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/users01.dbf
258datafile 5 switched to datafile copy
259input datafile copy RECID=7 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/example01.dbf
260
261contents of Memory Script:
262{
263 set until scn 1138080;
264 recover
265 standby
266 clone database
267 delete archivelog
268 ;
269}
270executing Memory Script
271
272executing command: SET until clause
273
274Starting recover at 2020-10-27 14:08:52
275allocated channel: ORA_AUX_DISK_1
276channel ORA_AUX_DISK_1: SID=33 device type=DISK
277
278starting media recovery
279
280archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc
281archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc
282archived log file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc thread=1 sequence=26
283archived log file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc thread=1 sequence=27
284media recovery complete, elapsed time: 00:00:00
285Finished recover at 2020-10-27 14:08:54
286Finished Duplicate Db at 2020-10-27 14:08:59
287
288RMAN> exit
289
290
291Recovery Manager complete.
292
4.4 实时应用
1select open_mode from v$database; 2alter database open; 3 4alter database flashback on; 5 6select * from v$log; 7set line 9999 8select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database; 9 10alter database recover managed standby database cancel; 11alter database recover managed standby database using current logfile disconnect;
主备库常用脚本：
1------------------------------ 主备库信息 2set line 9999 3col DEST_NAME format a20 4col DESTINATION format a15 5col GAP_STATUS format a10 6col DB_UNIQUE_NAME format a15 7col error format a10 8col APPLIED_SCN for 999999999999999 9SELECT al.thread#, 10 ads.dest_id, 11 ads.DEST_NAME, 12 (SELECT ads.TYPE || ' ' || ad.TARGET 13 FROM v$archive_dest AD 14 WHERE AD.DEST_ID = ADS.DEST_ID) TARGET, 15 ADS.DATABASE_MODE, 16 ads.STATUS, 17 ads.error, 18 ads.RECOVERY_MODE, 19 ads.DB_UNIQUE_NAME, 20 ads.DESTINATION, 21 ads.GAP_STATUS, 22 (SELECT CASE 23 WHEN NB.DATABASE_ROLE like '%STANDBY%' then 24 (SELECT MAX(sequence#) 25 FROM v$standby_log na 26 WHERE na.thread# = al.thread#) 27 ELSE 28 (SELECT MAX(sequence#) 29 FROM v$log na 30 WHERE na.thread# = al.thread#) 31 END 32 FROM V$DATABASE NB) Current_Seq#, 33 MAX(sequence#) Last_Archived, 34 MAX(CASE 35 WHEN al.APPLIED = 'YES' AND 36 aL.STANDBY_DEST = 37 (SELECT CASE 38 WHEN NB.DATABASE_ROLE like '%STANDBY%' then 39 'NO' 40 ELSE 41 'YES' 42 END 43 FROM V$DATABASE NB) THEN 44 al.sequence# 45 end) APPLIED_SEQ#, 46 (SELECT ad.applied_scn 47 FROM v$archive_dest AD 48 WHERE AD.DEST_ID = ADS.DEST_ID) applied_scn 49 FROM (SELECT * 50 FROM v$archived_log V 51 WHERE V.resetlogs_change# = 52 (SELECT d.RESETLOGS_CHANGE# FROM v$database d)) al, 53 v$archive_dest_status ads 54 WHERE al.dest_id(+) = ads.dest_id 55 AND ads.STATUS != 'INACTIVE' 56 AND DEST_NAME <> 'STANDBY_ARCHIVE_DEST' 57 GROUP BY al.thread#, 58 ads.dest_id, 59 ads.DEST_NAME, 60 ads.STATUS, 61 ads.error, 62 ads.TYPE, 63 ADS.DATABASE_MODE, 64 ads.RECOVERY_MODE, 65 ads.DB_UNIQUE_NAME, 66 ads.DESTINATION, 67 ads.GAP_STATUS 68 ORDER BY ads.dest_id, al.thread#; 69 70------------物理dg日志应用情况(主备库都可以) 71COL NAME FOR A80 72SET LINESIZE 9999 PAGESIZE 9999 73SELECT A.THREAD#, A.NAME, A.SEQUENCE#, A.APPLIED, A.FIRST_TIME 74 FROM V$ARCHIVED_LOG A, 75 (SELECT NB.THREAD#,NB.RESETLOGS_ID,NB.DEST_ID,(MAX(NB.SEQUENCE#) - 3) MAX_SEQUENCE# 76 FROM V$ARCHIVED_LOG NB 77 WHERE NB.APPLIED = 'YES' 78 and NB.DEST_ID in (SELECT NB.DEST_ID FROM V$ARCHIVE_DEST_STATUS NB where STATUS <>'INACTIVE' AND NB.RECOVERY_MODE like 'MANAGED%' ) 79 and RESETLOGS_ID=(select max(nbb.RESETLOGS_ID) from V$ARCHIVED_LOG nbb) 80 GROUP BY NB.THREAD#,NB.RESETLOGS_ID,NB.DEST_ID) B 81 WHERE A.THREAD# = B.THREAD# 82 AND A.RESETLOGS_ID = B.RESETLOGS_ID 83 AND A.DEST_ID=B.DEST_ID 84 AND A.SEQUENCE# >= MAX_SEQUENCE# 85 and A.RESETLOGS_ID=(select max(nb.RESETLOGS_ID) from V$ARCHIVED_LOG nb) 86 AND A.STANDBY_DEST = (SELECT CASE WHEN NB.DATABASE_ROLE LIKE '%STANDBY%' THEN 'NO' ELSE 'YES' END FROM V$DATABASE NB) 87 ORDER BY A.THREAD#, A.SEQUENCE#;
执行过程：
1[oracle@lhr11gdg dbs]$ sas 2 3SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 27 14:11:21 2020 4 5Copyright (c) 1982, 2013, Oracle. All rights reserved. 6 7 8Connected to: 9Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 10With the Partitioning, OLAP, Data Mining and Real Application Testing options 11 12SYS@LHR11GDG> select open_mode from v$database; 13 14OPEN_MODE 15---------------------------------------- 16MOUNTED 17 18SYS@LHR11GDG> alter database open; 19 20Database altered. 21 22SYS@LHR11GDG> set line 9999 23SYS@LHR11GDG> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database; 24 25 DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_ OPEN_MODE SWITCHOVER_STATUS 26---------- ------------------ ----------- ---------------------------------------- -------------------------------- ------ ---------------------------------------- ---------------------------------------- 272007947551 LHR11G 1138079 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED 28 29SYS@LHR11GDG> alter database recover managed standby database using current logfile disconnect; 30 31Database altered. 32 33SYS@LHR11GDG> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database; 34 35 DBID NAME CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_ OPEN_MODE SWITCHOVER_STATUS 36---------- ------------------ ----------- ---------------------------------------- -------------------------------- ------ ---------------------------------------- ---------------------------------------- 372007947551 LHR11G 1138079 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWEDc
查询DG状态：
1 SYS@LHR11G> @dg_info.sql 2 3 THREAD# DEST_ID DEST_NAME TARGET DATABASE_MODE STATUS ERROR RECOVERY_MODE DB_UNIQUE_NAME DESTINATION GAP_STATUS CURRENT_SEQ# LAST_ARCHIVED APPLIED_SEQ# APPLIED_SCN 4 ---------- ---------- -------------------- -------------------------------------------- ------------------------------ ------------------ ---------- ---------------------------------------------- --------------- --------------- ---------- ------------ ------------- ------------ ---------------- 5 1 1 LOG_ARCHIVE_DEST_1 LOCAL PRIMARY OPEN VALID IDLE LHR11G 34 33 0 6 1 2 LOG_ARCHIVE_DEST_2 PHYSICAL STANDBY OPEN_READ-ONLY VALID MANAGED REAL TIME APPLY LHR11GDG LHR11GDG NO GAP 34 33 32 1142459 7 8 SYS@LHR11G> @dg_status 9 10 THREAD# NAME SEQUENCE# APPLIED FIRST_TIME 11 ---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ ------------------- 12 1 30 YES 2020-10-27 15:36:40 13 1 31 YES 2020-10-27 15:36:41 14 1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_39_hshmk43l_.arc 32 YES 2020-10-27 15:36:47 15 1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_40_hshmow9l_.arc 33 IN-MEMORY 2020-10-27 15:37:07
五、配置dgmgrl
5.1 基础配置
1-- 主备库都修改 2alter system set dg_broker_start=true scope=both; 3 4-- 开始配置 5CREATE CONFIGURATION 'LHR11G' AS primary database is 'LHR11G' connect identifier is LHR11G; 6 7SHOW CONFIGURATION; 8 9add database 'LHR11GDG' as connect identifier is LHR11GDG maintained as physical; 10 11show database verbose 'LHR11G'; 12show database 'LHR11G'; 13 14enable configuration 15 16 17EDIT DATABASE 'LHR11G' SET PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.68.68)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=LHR11G_DGMGRL)(INSTANCE_NAME=LHR11G)(SERVER=DEDICATED)))'; 18EDIT DATABASE 'LHR11GDG' SET PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.68.69)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=LHR11GDG_DGMGRL)(INSTANCE_NAME=LHR11GDG)(SERVER=DEDICATED)))'; 19 20show configuration 21 22 23show database 'LHR11GDG' InconsistentProperties 24show database 'LHR11GDG' statusreport; 25 26alter system set archive_lag_target=0 scope=both sid='*'; 27alter system set log_archive_min_succeed_dest=1 scope=both sid='*'; 28alter system set Log_Archive_Trace=0 scope=both sid='*'; 29alter system set Log_Archive_Format='%t_%s_%r.dbf' scope=spfile sid='*';
配置完成后：
1[oracle@lhr11g ~]$ dgmgrl sys/lhr@lhr11g 2DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production 3 4Copyright (c) 2000, 2009, Oracle. All rights reserved. 5 6Welcome to DGMGRL, type "help" for information. 7Connected. 8DGMGRL> show configuration 9 10Configuration - LHR11G 11 12 Protection Mode: MaxPerformance 13 Databases: 14 LHR11G - Primary database 15 LHR11GDG - Physical standby database 16 17Fast-Start Failover: DISABLED 18 19Configuration Status: 20SUCCESS 21
5.2 配置FSFO
1---- Fast-Start Failover FSFO配置
2- 将Data Guard配置设置为MaxAvailability或MaxPerformance保护模式。
3- 如果配置保护模式设置为MaxAvailability，请确保将主数据库和快速启动故障转移目标备用数据库的LogXptMode属性设置为SYNC。
4- 如果配置保护模式设置为MaxPerformance，请确保将主数据库和快速启动故障转移目标备用数据库的LogXptMode属性设置为ASYNC。
5- 确保主数据库和快速启动故障转移目标备用数据库都启用了闪回。
6- 将主数据库FastStartFailoverTarget属性设置为所需目标备用数据库的DB_UNIQUE_NAME值，并将所需目标备用数据库FastStartFailoverTarget属性设置为主数据库的DB_UNIQUE_NAME值。
7
8show resource verbose 'LHR11G' logxptmode on site 'LHR11G';
9show resource verbose 'LHR11GDG' logxptmode on site 'LHR11GDG';
10alter resource 'LHR11G' set property logxptmode='SYNC';
11alter resource 'LHR11GDG' set property logxptmode='SYNC';
12-- edit database LHR11GDG set property logxptmode='SYNC';
13edit configuration set protection mode as maxavailability;
14
15
16
17edit database 'LHR11G' set property 'FastStartFailoverTarget'='LHR11GDG';
18edit database 'LHR11GDG' set property 'FastStartFailoverTarget'='LHR11G';
19
20
21show database 'LHR11G' FastStartFailoverTarget
22show database 'LHR11GDG' FastStartFailoverTarget
23show database 'LHR11G' logxptmode
24show database 'LHR11GDG' logxptmode
25
26-- 10秒后开始自动切换
27edit configuration set property FastStartFailoverThreshold=10;
28
29
30-- 配置客户端自动故障转移的service
31begin
32 DBMS_SERVICE.CREATE_SERVICE(service_name => 'dg_taf_lhr',
33 network_name => 'dg_taf_lhr',
34 aq_ha_notifications => TRUE,
35 failover_method => 'BASIC',
36 failover_type => 'SELECT',
37 failover_retries => 30,
38 failover_delay => 5);
39end;
40/
41
42create or replace procedure dg_taf_proc_lhr is
43 v_role VARCHAR(30);
44begin
45 select DATABASE_ROLE into v_role from V$DATABASE;
46 if v_role = 'PRIMARY' then
47 DBMS_SERVICE.START_SERVICE('dg_taf_lhr');
48 else
49 DBMS_SERVICE.STOP_SERVICE('dg_taf_lhr');
50 end if;
51end;
52/
53create or replace TRIGGER dg_taf_trg_startup_lhr
54 after startup or db_role_change on database
55begin
56 dg_taf_proc_lhr;
57end;
58/
59
60exec dg_taf_proc_lhr ;
61alter system switch logfile;
62
63dg_taf =
64 (DESCRIPTION =
65 (ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.68.68)(PORT = 1521))
66 (ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.68.69)(PORT = 1521))
67 (LOAD_BALANCE = yes)
68 (CONNECT_DATA =
69 (SERVER = DEDICATED)
70 (SERVICE_NAME = dg_taf_lhr)
71 (FAILOVER_MODE =
72 (TYPE = session)
73 (METHOD = basic)
74 (RETRIES = 180)
75 (DELAY = 5)
76 )
77 )
78 )
79
80
81
82ENABLE FAST_START FAILOVER;
83nohup dgmgrl -logfile '/tmp/observer_LHR11G.log' sys/lhr@LHR11GDG "start observer" &
84tailf /tmp/observer_LHR11G.log
85
86SHOW FAST_START FAILOVER;
87
配置结果：
1DGMGRL> SHOW FAST_START FAILOVER; 2 3Fast-Start Failover: ENABLED 4 5 Threshold: 10 seconds 6 Target: LHR11GDG 7 Observer: lhr11gdg 8 Lag Limit: 30 seconds 9 Shutdown Primary: TRUE 10 Auto-reinstate: TRUE 11 Observer Reconnect: (none) 12 Observer Override: FALSE 13 14Configurable Failover Conditions 15 Health Conditions: 16 Corrupted Controlfile YES 17 Corrupted Dictionary YES 18 Inaccessible Logfile NO 19 Stuck Archiver NO 20 Datafile Offline YES 21 22 Oracle Error Conditions: 23 (none)
5.3 重建DG
如果由于特殊原因导致备库不可用，必须进行重建，那么可以使用如下过程直接进行重建DG
1startup force nomount 2 3rman target sys/lhr@LHR11G auxiliary sys/lhr@LHR11GDG 4 5duplicate target database 6for standby nofilenamecheck 7from active database 8DORECOVER 9; 10 11 12alter database flashback on;