原 【DB宝29】使用Docker搭建Oracle 11g的DG环境
Tags： Oracle原创Linux高可用DockerOGGDG环境搭建
👉 本文共约2094个字，系统预计阅读时间或需8分钟。
目录
一、初始化环境
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 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 | -- 创建DG的网络 docker network create --subnet=192.168.68.0/16 mhalhr docker network inspect mhalhr -- 创建2台主机 docker run -itd --name LHR11G -h LHR11G \ -p 1528:1521 -p 1128:1158 -p 228:22 -p 3398:3389 \ --network mhalhr --ip 192.168.68.68 \ --privileged=true \ lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init docker run -itd --name LHR11GDG -h LHR11GDG \ -p 1529:1521 -p 1129:1158 -p 229:22 -p 3399:3389 \ --network mhalhr --ip 192.168.68.69 \ --privileged=true \ lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init -- 添加网卡 docker network connect bridge LHR11G docker network connect bridge LHR11GDG -- 进入容器 docker exec -it LHR11G bash docker exec -it LHR11GDG bash -- 备库删除原有的数据库 dbca -silent -deleteDatabase -sourceDB LHR11G |
二、主库操作
2.1 修改forcelogging、开闪回
1 2 3 | alter database force logging; alter database flashback on; select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_status from v$database; |
2.2 添加standby log
1 2 3 4 5 6 7 | select * from v$standby_log; select group#,bytes/1024/1024 ||'M' from v$log ; SELECT * FROM V$LOGFILE; alter database add standby logfile group 4 ('/u01/app/oracle/oradata/LHR11G/standby_redo04.log') size 50m; alter database add standby logfile group 5 ('/u01/app/oracle/oradata/LHR11G/standby_redo05.log') size 50m; alter database add standby logfile group 6 ('/u01/app/oracle/oradata/LHR11G/standby_redo06.log') size 50m; |
2.3 修改参数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | alter system set db_unique_name='LHR11G' scope=spfile; alter system set log_archive_config='DG_CONFIG=(LHR11G,LHR11GDG)'; alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=LHR11G valid_for=(ALL_LOGFILES,ALL_ROLES)'; alter system set log_archive_dest_2='SERVICE=LHR11GDG LGWR ASYNC db_unique_name=LHR11GDG valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'; alter system set log_archive_dest_state_1=ENABLE; alter system set log_archive_dest_state_2=ENABLE; alter system set log_archive_max_processes=4; alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile; alter system set db_file_name_convert='LHR11GDG','LHR11G' scope=spfile; alter system set log_file_name_convert='LHR11GDG','LHR11G' scope=spfile; alter system set standby_file_management='AUTO'; alter system set fal_server='LHR11GDG'; alter system set fal_client='LHR11G'; startup force |
2.4 修改监听
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | vi /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = LHR11G) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1) (SID_NAME= LHR11G) ) (SID_DESC = (GLOBAL_DBNAME = LHR11G_dgmgrl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1) (SID_NAME= LHR11G) ) ) |
2.5 修改tnsnames.ora
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | vi /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora LHR11G = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.68)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = LHR11G) ) ) LHR11GDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.69)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = LHR11GDG) ) ) |
三、主库密码文件、tnsnames.ora拷贝到备库
1 2 3 4 5 6 7 8 | docker cp LHR11G:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11G . docker cp orapwLHR11G LHR11GDG:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11GDG chown oracle.oinstall /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11GDG docker cp LHR11G:/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora . docker cp tnsnames.ora LHR11GDG:/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora chown oracle.oinstall /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora |
四、备库操作
4.1 修改监听
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | vi /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = LHR11GDG) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1) (SID_NAME= LHR11GDG) ) (SID_DESC = (GLOBAL_DBNAME = LHR11GDG_dgmgrl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1) (SID_NAME= LHR11GDG) ) ) |
4.2 备库修改到NOMOUNT状态
1 2 3 4 5 6 7 8 9 | mkdir -p /u01/app/oracle/oradata/LHR11GDG/ mkdir -p /u01/app/oracle/admin/LHR11GDG/adump echo "db_name=LHR11G" > $ORACLE_HOME/dbs/initLHR11GDG.ora export ORACLE_SID=LHR11GDG sas startup nomount |
4.3 duplicate复制搭建DG
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | rman target sys/lhr@LHR11G auxiliary sys/lhr@LHR11GDG duplicate target database for standby nofilenamecheck from active database DORECOVER spfile set db_unique_name='LHR11GDG' set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHR11GDG' set standby_file_management='AUTO' set fal_server='LHR11G' set fal_client='LHR11GDG' set control_files='/u01/app/oracle/oradata/LHR11GDG/control01.ctl' set db_file_name_convert='LHR11G','LHR11GDG' set log_file_name_convert='LHR11G','LHR11GDG' set audit_file_dest='/u01/app/oracle/admin/LHR11GDG/adump' set sga_max_size='346030080' ; |
执行过程：
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 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 | [oracle@lhr11gdg dbs]$ rman target sys/lhr@LHR11G auxiliary sys/lhr@LHR11GDG Recovery Manager: Release 11.2.0.4.0 - Production on Tue Oct 27 14:07:44 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: LHR11G (DBID=2007947551) connected to auxiliary database: LHR11G (not mounted) RMAN> RMAN> duplicate target database 2> for standby nofilenamecheck 3> from active database 4> DORECOVER 5> spfile 6> set db_unique_name='LHR11GDG' 7> set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=LHR11GDG' 8> set standby_file_management='AUTO' 9> set fal_server='LHR11G' 10> set fal_client='LHR11GDG' 11> set control_files='/u01/app/oracle/oradata/LHR11GDG/control01.ctl' 12> set db_file_name_convert='LHR11G','LHR11GDG' 13> set log_file_name_convert='LHR11G','LHR11GDG' 14> set audit_file_dest='/u01/app/oracle/admin/LHR11GDG/adump' 15> set sga_max_size='346030080' 16> ; Starting Duplicate Db at 2020-10-27 14:07:51 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=396 device type=DISK contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11G' auxiliary format '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwLHR11GDG' targetfile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11G.ora' auxiliary format '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11GDG.ora' ; sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11GDG.ora''"; } executing Memory Script Starting backup at 2020-10-27 14:07:53 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=161 device type=DISK Finished backup at 2020-10-27 14:07:55 sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileLHR11GDG.ora'' contents of Memory Script: { sql clone "alter system set db_unique_name = ''LHR11GDG'' comment= '''' scope=spfile"; sql clone "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"; sql clone "alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile"; sql clone "alter system set fal_server = ''LHR11G'' comment= '''' scope=spfile"; sql clone "alter system set fal_client = ''LHR11GDG'' comment= '''' scope=spfile"; sql clone "alter system set control_files = ''/u01/app/oracle/oradata/LHR11GDG/control01.ctl'' comment= '''' scope=spfile"; sql clone "alter system set db_file_name_convert = ''LHR11G'', ''LHR11GDG'' comment= '''' scope=spfile"; sql clone "alter system set log_file_name_convert = ''LHR11G'', ''LHR11GDG'' comment= '''' scope=spfile"; sql clone "alter system set audit_file_dest = ''/u01/app/oracle/admin/LHR11GDG/adump'' comment= '''' scope=spfile"; sql clone "alter system set sga_max_size = 346030080 comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set db_unique_name = ''LHR11GDG'' comment= '''' scope=spfile sql 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 sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile sql statement: alter system set fal_server = ''LHR11G'' comment= '''' scope=spfile sql statement: alter system set fal_client = ''LHR11GDG'' comment= '''' scope=spfile sql statement: alter system set control_files = ''/u01/app/oracle/oradata/LHR11GDG/control01.ctl'' comment= '''' scope=spfile sql statement: alter system set db_file_name_convert = ''LHR11G'', ''LHR11GDG'' comment= '''' scope=spfile sql statement: alter system set log_file_name_convert = ''LHR11G'', ''LHR11GDG'' comment= '''' scope=spfile sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/LHR11GDG/adump'' comment= '''' scope=spfile sql statement: alter system set sga_max_size = 346030080 comment= '''' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 346562560 bytes Fixed Size 2253144 bytes Variable Size 209718952 bytes Database Buffers 130023424 bytes Redo Buffers 4567040 bytes contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/LHR11GDG/control01.ctl'; } executing Memory Script Starting backup at 2020-10-27 14:08:04 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/snapcf_LHR11G.f tag=TAG20201027T140804 RECID=3 STAMP=1054908485 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2020-10-27 14:08:06 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/oradata/LHR11GDG/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/LHR11GDG/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/LHR11GDG/users01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/LHR11GDG/example01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/LHR11GDG/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/LHR11GDG/users01.dbf" datafile 5 auxiliary format "/u01/app/oracle/oradata/LHR11GDG/example01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/LHR11GDG/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 2020-10-27 14:08:12 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/LHR11G/system01.dbf output file name=/u01/app/oracle/oradata/LHR11GDG/system01.dbf tag=TAG20201027T140812 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/LHR11G/sysaux01.dbf output file name=/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf tag=TAG20201027T140812 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/LHR11G/undotbs01.dbf output file name=/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf tag=TAG20201027T140812 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/LHR11G/example01.dbf output file name=/u01/app/oracle/oradata/LHR11GDG/example01.dbf tag=TAG20201027T140812 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/LHR11G/users01.dbf output file name=/u01/app/oracle/oradata/LHR11GDG/users01.dbf tag=TAG20201027T140812 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2020-10-27 14:08:50 sql statement: alter system archive log current contents of Memory Script: { backup as copy reuse archivelog like "/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_26_hshgbgyr_.arc" auxiliary format "/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_%u_.arc" archivelog like "/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_27/o1_mf_1_27_hshgcl82_.arc" auxiliary format "/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_%u_.arc" ; catalog clone recovery area; switch clone datafile all; } executing Memory Script Starting backup at 2020-10-27 14:08:50 using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=26 RECID=7 STAMP=1054908501 output file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=27 RECID=8 STAMP=1054908530 output file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01 Finished backup at 2020-10-27 14:08:52 searching for all files in the recovery area List of Files Unknown to the Database ===================================== File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc File Name: /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc datafile 1 switched to datafile copy input datafile copy RECID=3 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=4 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=7 STAMP=1054908532 file name=/u01/app/oracle/oradata/LHR11GDG/example01.dbf contents of Memory Script: { set until scn 1138080; recover standby clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 2020-10-27 14:08:52 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=33 device type=DISK starting media recovery archived 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 archived 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 archived log file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_26_0eve183i_.arc thread=1 sequence=26 archived log file name=/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_27/o1_mf_1_27_0fve183j_.arc thread=1 sequence=27 media recovery complete, elapsed time: 00:00:00 Finished recover at 2020-10-27 14:08:54 Finished Duplicate Db at 2020-10-27 14:08:59 RMAN> exit Recovery Manager complete. |
4.4 实时应用
1 2 3 4 5 6 7 8 9 10 11 12 | select open_mode from v$database; alter database open; alter database flashback on; select * from v$log; set line 9999 select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database; alter database recover managed standby database cancel; alter database recover managed standby database using current logfile disconnect; |
执行过程：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
