原 【DB宝31】Oracle DG环境中主库使用rman做不完全恢复后，备库如何修复继续同步
Tags： Oracle原创LinuxDocker小麦苗racDGMHA
👉 本文共约2287个字，系统预计阅读时间或需9分钟。
目录
本文介绍一下，在DG环境中，主库使用rman做不完全恢复后，备库如何通过flashback操作，继续和主库保持同步，而不用重新搭建DG。
主备库均已开启闪回数据库特性。
一、下载镜像
小麦苗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环境初始化
| 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 |
查询目前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 | -- 查询目前DG部署 DGMGRL> show configuration Configuration - LHR11G Protection Mode: MaxPerformance Databases: LHR11G - Primary database LHR11GDG - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS -- 主库 SYS@LHR11G> select flashback_on,db_unique_name,database_role from v$database; FLASHBACK_ON DB_UNIQUE_NAME DATABASE_ROLE ------------------------------------ ------------------------------------------------------------ -------------------------------- YES LHR11G PRIMARY -- 备库 SYS@LHR11GDG> select flashback_on,db_unique_name,database_role from v$database; FLASHBACK_ON DB_UNIQUE_NAME DATABASE_ROLE ------------------------------------ ------------------------------------------------------------ -------------------------------- YES LHR11GDG PHYSICAL STANDBY |
这里需要说明的是，我这套环境做过多次的主备切换操作，所以，建议做实验之前，把归档日志号切换增长到50以上，多次执行“alter system switch logfile;”即可：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- 主库 SYS@LHR11G> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 52 Next log sequence to archive 54 Current log sequence 54 -- 备库 SYS@LHR11GDG> @dg_status THREAD# NAME SEQUENCE# APPLIED FIRST_TIME ---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ ------------------- 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_49_hssgortv_.arc 49 YES 2020-10-31 09:15:35 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_50_hssgosm5_.arc 50 YES 2020-10-31 09:15:36 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_51_hssgp5hh_.arc 51 YES 2020-10-31 09:15:37 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_52_hssgp66k_.arc 52 YES 2020-10-31 09:15:49 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_53_hssgp6wc_.arc 53 IN-MEMORY 2020-10-31 09:15:50 |
另外，把闪回恢复区设置大一点，例如：alter system set db_recovery_file_dest_size=8g;
三、主库做备份操作
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 | [oracle@lhr11g ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 31 09:20:41 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: LHR11G (DBID=2007947551) RMAN> run 2> { 3> backup as compressed backupset database; 4> backup archivelog all ; sql 'alter system archive log current'; 5> backup archivelog all ; 6> backup current controlfile; 7> } Starting backup at 2020-10-31 09:20:44 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=191 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/LHR11G/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/LHR11G/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/LHR11G/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/LHR11G/example01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/LHR11G/users01.dbf channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:20:46 channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:01 piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T092045_hssgzg25_.bkp tag=TAG20201031T092045 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:02 channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:03 piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_ncsnf_TAG20201031T092045_hssh1t5y_.bkp tag=TAG20201031T092045 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2020-10-31 09:22:03 RMAN-08591: WARNING: invalid archived log deletion policy sql statement: alter system archive log current Starting backup at 2020-10-31 09:22:04 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=14 RECID=1 STAMP=1054919461 input archived log thread=1 sequence=15 RECID=3 STAMP=1054919751 input archived log thread=1 sequence=16 RECID=2 STAMP=1054919751 input archived log thread=1 sequence=17 RECID=4 STAMP=1054919820 input archived log thread=1 sequence=18 RECID=5 STAMP=1054919827 input archived log thread=1 sequence=19 RECID=6 STAMP=1054919829 input archived log thread=1 sequence=20 RECID=7 STAMP=1054919831 input archived log thread=1 sequence=21 RECID=11 STAMP=1054919840 input archived log thread=1 sequence=22 RECID=13 STAMP=1054919858 input archived log thread=1 sequence=23 RECID=15 STAMP=1054919903 input archived log thread=1 sequence=24 RECID=17 STAMP=1054919906 input archived log thread=1 sequence=25 RECID=19 STAMP=1054919909 input archived log thread=1 sequence=26 RECID=21 STAMP=1054920097 channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:04 channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:05 piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_annnn_TAG20201031T092204_hssh1wl7_.bkp tag=TAG20201031T092204 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=22 STAMP=1054920097 input archived log thread=1 sequence=2 RECID=23 STAMP=1054920097 input archived log thread=1 sequence=3 RECID=24 STAMP=1054920097 input archived log thread=1 sequence=4 RECID=25 STAMP=1054920100 input archived log thread=1 sequence=5 RECID=26 STAMP=1054920112 input archived log thread=1 sequence=6 RECID=28 STAMP=1054920284 channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:05 channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:06 piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_annnn_TAG20201031T092204_hssh1xp8_.bkp tag=TAG20201031T092204 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=27 STAMP=1054920279 input archived log thread=1 sequence=2 RECID=29 STAMP=1054920284 input archived log thread=1 sequence=3 RECID=30 STAMP=1054920397 input archived log thread=1 sequence=4 RECID=35 STAMP=1054920436 input archived log thread=1 sequence=5 RECID=37 STAMP=1054920446 input archived log thread=1 sequence=6 RECID=39 STAMP=1055236276 input archived log thread=1 sequence=7 RECID=41 STAMP=1055236281 input archived log thread=1 sequence=8 RECID=44 STAMP=1055236334 channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:06 channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:07 piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_annnn_TAG20201031T092204_hssh1yt7_.bkp tag=TAG20201031T092204 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=9 RECID=46 STAMP=1055236406 input archived log thread=1 sequence=10 RECID=47 STAMP=1055236409 input archived log thread=1 sequence=11 RECID=49 STAMP=1055236417 input archived log thread=1 sequence=12 RECID=51 STAMP=1055236418 input archived log thread=1 sequence=13 RECID=53 STAMP=1055236419 input archived log thread=1 sequence=14 RECID=55 STAMP=1055236420 input archived log thread=1 sequence=15 RECID=57 STAMP=1055236421 input archived log thread=1 sequence=16 RECID=59 STAMP=1055236422 input archived log thread=1 sequence=17 RECID=61 STAMP=1055236422 input archived log thread=1 sequence=18 RECID=63 STAMP=1055236423 input archived log thread=1 sequence=19 RECID=65 STAMP=1055236424 input archived log thread=1 sequence=20 RECID=67 STAMP=1055236425 input archived log thread=1 sequence=21 RECID=69 STAMP=1055236425 input archived log thread=1 sequence=22 RECID=71 STAMP=1055236426 input archived log thread=1 sequence=23 RECID=73 STAMP=1055236427 input archived log thread=1 sequence=24 RECID=75 STAMP=1055236427 input archived log thread=1 sequence=25 RECID=77 STAMP=1055236428 input archived log thread=1 sequence=26 RECID=79 STAMP=1055236428 input archived log thread=1 sequence=27 RECID=81 STAMP=1055236429 input archived log thread=1 sequence=28 RECID=83 STAMP=1055236430 input archived log thread=1 sequence=29 RECID=85 STAMP=1055236430 input archived log thread=1 sequence=30 RECID=87 STAMP=1055236431 input archived log thread=1 sequence=31 RECID=89 STAMP=1055236432 input archived log thread=1 sequence=32 RECID=91 STAMP=1055236432 input archived log thread=1 sequence=33 RECID=93 STAMP=1055236433 input archived log thread=1 sequence=34 RECID=95 STAMP=1055236434 input archived log thread=1 sequence=35 RECID=97 STAMP=1055236434 input archived log thread=1 sequence=36 RECID=100 STAMP=1055236435 input archived log thread=1 sequence=37 RECID=101 STAMP=1055236436 input archived log thread=1 sequence=38 RECID=103 STAMP=1055236436 input archived log thread=1 sequence=39 RECID=105 STAMP=1055236437 input archived log thread=1 sequence=40 RECID=107 STAMP=1055236437 input archived log thread=1 sequence=41 RECID=109 STAMP=1055236439 input archived log thread=1 sequence=42 RECID=111 STAMP=1055236511 input archived log thread=1 sequence=43 RECID=113 STAMP=1055236514 input archived log thread=1 sequence=44 RECID=115 STAMP=1055236517 input archived log thread=1 sequence=45 RECID=117 STAMP=1055236519 input archived log thread=1 sequence=46 RECID=119 STAMP=1055236531 input archived log thread=1 sequence=47 RECID=121 STAMP=1055236532 input archived log thread=1 sequence=48 RECID=123 STAMP=1055236535 input archived log thread=1 sequence=49 RECID=125 STAMP=1055236536 input archived log thread=1 sequence=50 RECID=128 STAMP=1055236537 input archived log thread=1 sequence=51 RECID=129 STAMP=1055236549 input archived log thread=1 sequence=52 RECID=131 STAMP=1055236550 input archived log thread=1 sequence=53 RECID=133 STAMP=1055236550 input archived log thread=1 sequence=54 RECID=135 STAMP=1055236924 input archived log thread=1 sequence=55 RECID=136 STAMP=1055236924 channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:08 channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:09 piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_annnn_TAG20201031T092204_hssh202w_.bkp tag=TAG20201031T092204 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2020-10-31 09:22:09 RMAN-08591: WARNING: invalid archived log deletion policy Starting backup at 2020-10-31 09:22:10 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:11 channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:12 piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_ncnnf_TAG20201031T092210_hssh237k_.bkp tag=TAG20201031T092210 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2020-10-31 09:22:12 RMAN-08591: WARNING: invalid archived log deletion policy RMAN> |
四、主库查询SCN，后续恢复到该时间点
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 | SYS@LHR11G> alter system switch logfile; System altered. SYS@LHR11G> SYS@LHR11G> select current_scn from v$database; CURRENT_SCN ----------- 1373192 SYS@LHR11G> alter system switch logfile; System altered. SYS@LHR11G> create table lhr.emp1 as select * from scott.emp; Table created. SYS@LHR11G> alter system switch logfile; System altered. -- 备库查询 SYS@LHR11GDG> @dg_status THREAD# NAME SEQUENCE# APPLIED FIRST_TIME ---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ ------------------- 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_54_hssh1w8f_.arc 54 YES 2020-10-31 09:15:50 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_55_hssh205f_.arc 55 YES 2020-10-31 09:22:04 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_56_hssh6g23_.arc 56 YES 2020-10-31 09:22:04 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_57_hssh7dd2_.arc 57 YES 2020-10-31 09:24:29 1 /u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_58_hssh817g_.arc 58 IN-MEMORY 2020-10-31 09:25:00 SYS@LHR11GDG> select count(*) from lhr.emp1; COUNT(*) ---------- 14 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
