原 【DB宝30】使用Docker测试Oracle 11g高可用DG功能
Tags： Oracle原创Linux高可用Docker小麦苗DG
👉 本文共约2499个字，系统预计阅读时间或需10分钟。
一、下载镜像
小麦苗DG环境的hub地址：
https://hub.docker.com/r/lhrbest/dg_pri_11.2.0.4/tags
https://hub.docker.com/r/lhrbest/dg_phy_11.2.0.4/tags
1 2 | nohup docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_phy_11.2.0.4:1.0 & nohup docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_pri_11.2.0.4:1.0 & |
查看镜像：
1 2 3 | [root@docker36 ~]# docker images | grep dg registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_phy_11.2.0.4 1.0 f2ea019fe540 15 hours ago 10.7GB registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_pri_11.2.0.4 1.0 b7fae2029b40 15 hours ago 10.8GB |
给镜像打tag：
1 2 3 4 5 6 7 8 | [root@docker36 ~]# docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_phy_11.2.0.4:1.0 lhrbest/dg_phy_11.2.0.4:1.0 [root@docker36 ~]# docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_pri_11.2.0.4:1.0 lhrbest/dg_pri_11.2.0.4:1.0 [root@docker36 ~]# docker images | grep dg lhrbest/dg_phy_11.2.0.4 1.0 f2ea019fe540 15 hours ago 10.7GB registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_phy_11.2.0.4 1.0 f2ea019fe540 15 hours ago 10.7GB lhrbest/dg_pri_11.2.0.4 1.0 b7fae2029b40 15 hours ago 10.8GB registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_pri_11.2.0.4 1.0 b7fae2029b40 15 hours ago 10.8GB |
二、初始化环境
| 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 28 29 30 31 | -- 创建DG的网络 docker network create --subnet=192.168.68.0/16 mhalhr docker network inspect mhalhr -- 分别初始化主库和备库 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/dg_pri_11.2.0.4:1.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/dg_phy_11.2.0.4:1.0 init -- 添加网卡 docker network connect bridge LHR11G docker network connect bridge LHR11GDG -- 进入容器 docker exec -it LHR11G bash docker exec -it LHR11GDG bash -- 分别启动主库、备库和监听 su - oracle lsnrctl start sas startup |
启动主库过程：
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 | [root@docker36 ~]# docker exec -it LHR11G bash [root@lhr11g /]# su - oracle [oracle@lhr11g ~]$ sas SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 28 08:43:43 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SYS@LHR11G> startup ORACLE instance started. Total System Global Area 325685248 bytes Fixed Size 2252944 bytes Variable Size 188747632 bytes Database Buffers 130023424 bytes Redo Buffers 4661248 bytes Database mounted. Database opened. SYS@LHR11G> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@lhr11g ~]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-OCT-2020 08:48:58 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/lhr11g/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LHR11G)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 28-OCT-2020 08:48:59 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/lhr11g/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LHR11G)(PORT=1521))) Services Summary... Service "LHR11G" has 1 instance(s). Instance "LHR11G", status UNKNOWN, has 1 handler(s) for this service... Service "LHR11G_dgmgrl" has 1 instance(s). Instance "LHR11G", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@lhr11g ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-OCT-2020 08:49:46 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 28-OCT-2020 08:48:59 Uptime 0 days 0 hr. 0 min. 47 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/lhr11g/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LHR11G)(PORT=1521))) Services Summary... Service "LHR11G" has 2 instance(s). Instance "LHR11G", status UNKNOWN, has 1 handler(s) for this service... Instance "LHR11G", status READY, has 1 handler(s) for this service... Service "LHR11GXDB" has 1 instance(s). Instance "LHR11G", status READY, has 1 handler(s) for this service... Service "LHR11G_DGB" has 1 instance(s). Instance "LHR11G", status READY, has 1 handler(s) for this service... Service "LHR11G_dgmgrl" has 1 instance(s). Instance "LHR11G", status UNKNOWN, has 1 handler(s) for this service... Service "dg_taf_lhr" has 1 instance(s). Instance "LHR11G", status READY, has 1 handler(s) for this service... The command completed successfully |
启动备库过程：
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 | [root@docker36 ~]# docker exec -it LHR11GDG bash [root@lhr11gdg /]# su - oracle [oracle@lhr11gdg ~]$ sas SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 28 08:43:51 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SYS@LHR11GDG> startup 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 Database mounted. Database opened. SYS@LHR11GDG> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@lhr11gdg ~]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-OCT-2020 08:49:05 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/lhr11gdg/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LHR11GDG)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 28-OCT-2020 08:49:05 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/lhr11gdg/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LHR11GDG)(PORT=1521))) Services Summary... Service "LHR11GDG" has 1 instance(s). Instance "LHR11GDG", status UNKNOWN, has 1 handler(s) for this service... Service "LHR11GDG_dgmgrl" has 1 instance(s). Instance "LHR11GDG", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@lhr11gdg ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-OCT-2020 08:50:31 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 28-OCT-2020 08:49:05 Uptime 0 days 0 hr. 1 min. 26 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/lhr11gdg/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LHR11GDG)(PORT=1521))) Services Summary... Service "LHR11GDG" has 2 instance(s). Instance "LHR11GDG", status UNKNOWN, has 1 handler(s) for this service... Instance "LHR11GDG", status READY, has 1 handler(s) for this service... Service "LHR11GDG_DGB" has 1 instance(s). Instance "LHR11GDG", status READY, has 1 handler(s) for this service... Service "LHR11GDG_dgmgrl" has 1 instance(s). Instance "LHR11GDG", status UNKNOWN, has 1 handler(s) for this service... Service "LHR11GXDB" has 1 instance(s). Instance "LHR11GDG", status READY, has 1 handler(s) for this service... The command completed successfully |
三、测试DG的高可用功能
下面会分别测试DG环境的以下几个功能：
1、验证同步
2、switchover
3、failover
4、fsfo
5、故障切换
3.1 主备同步
主库查询DG情况：
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 | SYS@LHR11G> alter system switch logfile; System altered. SYS@LHR11G> alter system switch logfile; System altered. SYS@LHR11G> alter system switch logfile; System altered. SYS@LHR11G> @dg_info 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 ---------- ---------- -------------------- -------------------------------------------- ------------------------------ ------------------ ---------- ---------------------------------------------- --------------- --------------- ---------- ------------ ------------- ------------ ---------------- 1 1 LOG_ARCHIVE_DEST_1 LOCAL PRIMARY OPEN VALID IDLE LHR11G 12 11 0 1 2 LOG_ARCHIVE_DEST_2 PHYSICAL STANDBY OPEN_READ-ONLY VALID MANAGED REAL TIME APPLY LHR11GDG lhr11gdg NO GAP 12 11 10 1363798 SYS@LHR11G> @dg_status THREAD# NAME SEQUENCE# APPLIED FIRST_TIME ---------- --------------- ---------- ------------------ ------------------- 1 lhr11gdg 7 YES 2020-10-28 09:45:48 1 lhr11gdg 8 YES 2020-10-28 09:45:51 1 lhr11gdg 9 YES 2020-10-28 09:46:48 1 lhr11gdg 10 YES 2020-10-28 09:55:02 1 lhr11gdg 11 NO 2020-10-28 09:55:06 SYS@LHR11G> create table lhr.testdg as select * from scott.emp; Table created. SYS@LHR11G> select count(*) from lhr.testdg; COUNT(*) ---------- 14 |
备库查询日志应用情况：
1 2 3 4 5 6 7 8 9 10 11 12 13 | SYS@LHR11GDG> @dg_status THREAD# NAME SEQUENCE# APPLIED FIRST_TIME ---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ ------------------- 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_28/o1_mf_1_8_hskmd9nq_.arc 8 YES 2020-10-28 09:45:51 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_28/o1_mf_1_9_hskmvpld_.arc 9 YES 2020-10-28 09:46:48 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_28/o1_mf_1_10_hskmvtc1_.arc 10 YES 2020-10-28 09:55:02 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_28/o1_mf_1_11_hskmvvrb_.arc 11 IN-MEMORY 2020-10-28 09:55:06 SYS@LHR11GDG> select count(*) from lhr.testdg; COUNT(*) ---------- 14 |
可以看到，主备是实时同步的。
3.2 switchover
接下来使用dgmgrl来验证switchover功能。
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 | [oracle@lhr11g ~]$ dgmgrl sys/lhr@lhr11g DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> show configuration Configuration - LHR11G Protection Mode: MaxPerformance Databases: LHR11G - Primary database LHR11GDG - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> switchover to 'LHR11GDG' Performing switchover NOW, please wait... Operation requires a connection to instance "LHR11GDG" on database "LHR11GDG" Connecting to instance "LHR11GDG"... Connected. New primary database "LHR11GDG" is opening... Operation requires startup of instance "LHR11G" on database "LHR11G" Starting instance "LHR11G"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "LHR11GDG" DGMGRL> show configuration Configuration - LHR11G Protection Mode: MaxPerformance Databases: LHR11GDG - Primary database LHR11G - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
可以看到，主备角色已成功切换，接下来验证同步功能。
主库操作，注意此时主库为LHR11GDG：
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 | SYS@LHR11GDG> @dg_info 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 ---------- ---------- -------------------- -------------------------------------------- ------------------------------ ------------------ ---------- ---------------------------------------------- --------------- --------------- ---------- ------------ ------------- ------------ ---------------- 1 1 LOG_ARCHIVE_DEST_1 LOCAL PRIMARY OPEN VALID IDLE LHR11GDG 18 17 0 1 2 LOG_ARCHIVE_DEST_2 PHYSICAL STANDBY OPEN_READ-ONLY VALID MANAGED REAL TIME APPLY LHR11G lhr11g NO GAP 18 17 16 1384751 SYS@LHR11GDG> @dg_status THREAD# NAME SEQUENCE# APPLIED FIRST_TIME ---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ ------------------- 1 lhr11g 13 YES 2020-10-28 10:00:22 1 lhr11g 14 YES 2020-10-28 10:00:27 1 lhr11g 15 YES 2020-10-28 10:00:29 1 lhr11g 16 YES 2020-10-28 10:00:32 1 lhr11g 17 NO 2020-10-28 10:00:41 SYS@LHR11GDG> insert into lhr.testdg select * from lhr.testdg; 14 rows created. SYS@LHR11GDG> commit; Commit complete. SYS@LHR11GDG> select count(*) from lhr.testdg; COUNT(*) ---------- 28 |
备库操作，注意此时备库为LHR11G：
1 2 3 4 5 6 7 8 9 10 11 12 | SYS@LHR11G> @dg_status THREAD# NAME SEQUENCE# APPLIED FIRST_TIME ---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ ------------------- 1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_28/o1_mf_1_16_hskn6bfj_.arc 16 YES 2020-10-28 10:00:32 1 /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_28/o1_mf_1_17_hskn6vql_.arc 17 IN-MEMORY 2020-10-28 10:00:41 SYS@LHR11G> select count(*) from lhr.testdg; COUNT(*) ---------- 28 |
可以看到，同步功能正常。
3.3 failover
接下来使用dgmgrl来验证failover功能。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
