【DB宝29】使用Docker搭建Oracle 11g的DG环境
2021/4/10 2:31:08
本文主要是介绍【DB宝29】使用Docker搭建Oracle 11g的DG环境,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
目录一、初始化环境二、主库操作 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;
这篇关于【DB宝29】使用Docker搭建Oracle 11g的DG环境的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-31Docker部署资料:新手入门指南
- 2024-10-31Dockerfile 增强新语法
- 2024-10-31Docker部署资料:新手入门教程
- 2024-10-31docker镜像下搭建的L2tp 只能连接一个小时是什么原因-icode9专业技术文章分享
- 2024-10-30Docker部署教程:新手必备的详细指南
- 2024-10-30五个常见的Docker部署坑(以及如何像高手一样轻松避开它们 ??)
- 2024-10-30Docker部署入门教程:轻松上手Docker部署
- 2024-10-30Docker部署入门教程:轻松搭建你的第一个Docker应用
- 2024-10-30Docker部署学习:新手入门教程
- 2024-10-30Docker部署入门教程:轻松掌握Docker部署技巧