【DB宝31】Oracle DG环境中主库使用rman做不完全恢复后,备库如何修复继续同步

2021/4/15 2:31:04

本文主要是介绍【DB宝31】Oracle DG环境中主库使用rman做不完全恢复后,备库如何修复继续同步,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!


 

目录

一、下载镜像二、DG环境初始化三、主库做备份操作四、主库查询SCN,后续恢复到该时间点五、主库还原,做不完全恢复六、备库做闪回操作七、查询主备库是否实时同步八、总结
 

本文介绍一下,在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

1nohup docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_phy_11.2.0.4:1.0 &
2nohup docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_pri_11.2.0.4:1.0 &
 

查看镜像:

1[root@docker36 ~]# docker images | grep dg
2registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_phy_11.2.0.4                1.0                 f2ea019fe540        15 hours ago        10.7GB
3registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_pri_11.2.0.4                1.0                 b7fae2029b40        15 hours ago        10.8GB
 

给镜像打tag:

1[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
2[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

4[root@docker36 ~]# docker images | grep dg
5lhrbest/dg_phy_11.2.0.4                                                  1.0                 f2ea019fe540        15 hours ago        10.7GB
6registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_phy_11.2.0.4                1.0                 f2ea019fe540        15 hours ago        10.7GB
7lhrbest/dg_pri_11.2.0.4                                                  1.0                 b7fae2029b40        15 hours ago        10.8GB
8registry.cn-hangzhou.aliyuncs.com/lhrbest/dg_pri_11.2.0.4                1.0                 b7fae2029b40        15 hours ago        10.8GB
   

二、DG环境初始化

DG环境情况见下表:

项目主库物理备库
db 类型单实例单实例
db version11.2.0.4.011.2.0.4.0
db 存储FSFS
OS版本RHEL7.6 64位CentOS7.6 64位
OS hostnameLHR11GLHR11GDG
IP地址192.168.68.68192.168.68.69
ORACLE_SIDLHR11GLHR11GDG
db_name/GLOBAL_DBNAMELHR11GLHR11G
db_unique_nameLHR11GLHR11GDG
TNS_NAMELHR11GLHR11GDG
监听端口15211521
映射的主机端口15281529
ORACLE_HOME/u01/app/oracle/product/11.2.0.4/dbhome_1/u01/app/oracle/product/11.2.0.4/dbhome_1
dbid20079475512007947551
 1-- 创建DG的网络
 2docker network create --subnet=192.168.68.0/16 mhalhr
 3docker network inspect mhalhr

 5-- 分别初始化主库和备库
 6docker 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

13docker 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

-- 添加网卡
21docker network connect bridge LHR11G
22docker network connect bridge LHR11GDG

25-- 进入容器
26docker exec -it LHR11G bash
27docker exec -it LHR11GDG bash

29-- 分别启动主库、备库和监听
30su - oracle
31lsnrctl start
32sas
33startup 
 

查询目前DG的同步情况:

 1-- 查询目前DG部署
 2DGMGRL> show configuration

 4Configuration - LHR11G
 Protection Mode: MaxPerformance
 Databases:
   LHR11G   - Primary database
   LHR11GDG - Physical standby database

11Fast-Start Failover: DISABLED

13Configuration Status:
14SUCCESS

16-- 主库
17SYS@LHR11G> select flashback_on,db_unique_name,database_role from v$database;

19FLASHBACK_ON                         DB_UNIQUE_NAME                                               DATABASE_ROLE
20------------------------------------ ------------------------------------------------------------ --------------------------------
21YES                                  LHR11G                                                       PRIMARY


25-- 备库

27SYS@LHR11GDG> select flashback_on,db_unique_name,database_role from v$database;

29FLASHBACK_ON                         DB_UNIQUE_NAME                                               DATABASE_ROLE
30------------------------------------ ------------------------------------------------------------ --------------------------------
31YES                                  LHR11GDG                                                     PHYSICAL STANDBY
 

这里需要说明的是,我这套环境做过多次的主备切换操作,所以,建议做实验之前,把归档日志号切换增长到50以上,多次执行“alter system switch logfile;”即可:

 1-- 主库
 2SYS@LHR11G> archive log list;
 3Database log mode              Archive Mode
 4Automatic archival             Enabled
 5Archive destination            USE_DB_RECOVERY_FILE_DEST
 6Oldest online log sequence     52
 7Next log sequence to archive   54
 8Current log sequence           54

10-- 备库
11SYS@LHR11GDG> @dg_status
  THREAD# NAME                                                                                                  SEQUENCE# APPLIED            FIRST_TIME
14---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ -------------------
/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
/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
/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
/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
/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[oracle@lhr11g ~]$ rman target /

  3Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 31 09:20:41 2020

  5Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

  7connected to target database: LHR11G (DBID=2007947551)

  9RMAN> run
 102> {
 113>  backup as compressed backupset database;
 124>  backup archivelog all ;
sql 'alter system archive log current';
 145>  backup archivelog all ;
 156>  backup current controlfile;
 167> }

 18Starting backup at 2020-10-31 09:20:44
 19using target database control file instead of recovery catalog
 20allocated channel: ORA_DISK_1
 21channel ORA_DISK_1: SID=191 device type=DISK
 22channel ORA_DISK_1: starting compressed full datafile backup set
 23channel ORA_DISK_1: specifying datafile(s) in backup set
 24input datafile file number=00001 name=/u01/app/oracle/oradata/LHR11G/system01.dbf
 25input datafile file number=00002 name=/u01/app/oracle/oradata/LHR11G/sysaux01.dbf
 26input datafile file number=00003 name=/u01/app/oracle/oradata/LHR11G/undotbs01.dbf
 27input datafile file number=00005 name=/u01/app/oracle/oradata/LHR11G/example01.dbf
 28input datafile file number=00004 name=/u01/app/oracle/oradata/LHR11G/users01.dbf
 29channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:20:46
 30channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:01
 31piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T092045_hssgzg25_.bkp tag=TAG20201031T092045 comment=NONE
 32channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
 33channel ORA_DISK_1: starting compressed full datafile backup set
 34channel ORA_DISK_1: specifying datafile(s) in backup set
 35including current control file in backup set
 36including current SPFILE in backup set
 37channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:02
 38channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:03
 39piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_ncsnf_TAG20201031T092045_hssh1t5y_.bkp tag=TAG20201031T092045 comment=NONE
 40channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
 41Finished backup at 2020-10-31 09:22:03
 42RMAN-08591: WARNING: invalid archived log deletion policy

 44sql statement: alter system archive log current

 46Starting backup at 2020-10-31 09:22:04
 47current log archived
 48using channel ORA_DISK_1
 49channel ORA_DISK_1: starting archived log backup set
 50channel ORA_DISK_1: specifying archived log(s) in backup set
 51input archived log thread=1 sequence=14 RECID=1 STAMP=1054919461
 52input archived log thread=1 sequence=15 RECID=3 STAMP=1054919751
 53input archived log thread=1 sequence=16 RECID=2 STAMP=1054919751
 54input archived log thread=1 sequence=17 RECID=4 STAMP=1054919820
 55input archived log thread=1 sequence=18 RECID=5 STAMP=1054919827
 56input archived log thread=1 sequence=19 RECID=6 STAMP=1054919829
 57input archived log thread=1 sequence=20 RECID=7 STAMP=1054919831
 58input archived log thread=1 sequence=21 RECID=11 STAMP=1054919840
 59input archived log thread=1 sequence=22 RECID=13 STAMP=1054919858
 60input archived log thread=1 sequence=23 RECID=15 STAMP=1054919903
 61input archived log thread=1 sequence=24 RECID=17 STAMP=1054919906
 62input archived log thread=1 sequence=25 RECID=19 STAMP=1054919909
 63input archived log thread=1 sequence=26 RECID=21 STAMP=1054920097
 64channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:04
 65channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:05
 66piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_annnn_TAG20201031T092204_hssh1wl7_.bkp tag=TAG20201031T092204 comment=NONE
 67channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
 68channel ORA_DISK_1: starting archived log backup set
 69channel ORA_DISK_1: specifying archived log(s) in backup set
 70input archived log thread=1 sequence=1 RECID=22 STAMP=1054920097
 71input archived log thread=1 sequence=2 RECID=23 STAMP=1054920097
 72input archived log thread=1 sequence=3 RECID=24 STAMP=1054920097
 73input archived log thread=1 sequence=4 RECID=25 STAMP=1054920100
 74input archived log thread=1 sequence=5 RECID=26 STAMP=1054920112
 75input archived log thread=1 sequence=6 RECID=28 STAMP=1054920284
 76channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:05
 77channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:06
 78piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_annnn_TAG20201031T092204_hssh1xp8_.bkp tag=TAG20201031T092204 comment=NONE
 79channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
 80channel ORA_DISK_1: starting archived log backup set
 81channel ORA_DISK_1: specifying archived log(s) in backup set
 82input archived log thread=1 sequence=1 RECID=27 STAMP=1054920279
 83input archived log thread=1 sequence=2 RECID=29 STAMP=1054920284
 84input archived log thread=1 sequence=3 RECID=30 STAMP=1054920397
 85input archived log thread=1 sequence=4 RECID=35 STAMP=1054920436
 86input archived log thread=1 sequence=5 RECID=37 STAMP=1054920446
 87input archived log thread=1 sequence=6 RECID=39 STAMP=1055236276
 88input archived log thread=1 sequence=7 RECID=41 STAMP=1055236281
 89input archived log thread=1 sequence=8 RECID=44 STAMP=1055236334
 90channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:06
 91channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:07
 92piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_annnn_TAG20201031T092204_hssh1yt7_.bkp tag=TAG20201031T092204 comment=NONE
 93channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
 94channel ORA_DISK_1: starting archived log backup set
 95channel ORA_DISK_1: specifying archived log(s) in backup set
 96input archived log thread=1 sequence=9 RECID=46 STAMP=1055236406
 97input archived log thread=1 sequence=10 RECID=47 STAMP=1055236409
 98input archived log thread=1 sequence=11 RECID=49 STAMP=1055236417
 99input archived log thread=1 sequence=12 RECID=51 STAMP=1055236418
100input archived log thread=1 sequence=13 RECID=53 STAMP=1055236419
101input archived log thread=1 sequence=14 RECID=55 STAMP=1055236420
102input archived log thread=1 sequence=15 RECID=57 STAMP=1055236421
103input archived log thread=1 sequence=16 RECID=59 STAMP=1055236422
104input archived log thread=1 sequence=17 RECID=61 STAMP=1055236422
105input archived log thread=1 sequence=18 RECID=63 STAMP=1055236423
106input archived log thread=1 sequence=19 RECID=65 STAMP=1055236424
107input archived log thread=1 sequence=20 RECID=67 STAMP=1055236425
108input archived log thread=1 sequence=21 RECID=69 STAMP=1055236425
109input archived log thread=1 sequence=22 RECID=71 STAMP=1055236426
110input archived log thread=1 sequence=23 RECID=73 STAMP=1055236427
111input archived log thread=1 sequence=24 RECID=75 STAMP=1055236427
112input archived log thread=1 sequence=25 RECID=77 STAMP=1055236428
113input archived log thread=1 sequence=26 RECID=79 STAMP=1055236428
114input archived log thread=1 sequence=27 RECID=81 STAMP=1055236429
115input archived log thread=1 sequence=28 RECID=83 STAMP=1055236430
116input archived log thread=1 sequence=29 RECID=85 STAMP=1055236430
117input archived log thread=1 sequence=30 RECID=87 STAMP=1055236431
118input archived log thread=1 sequence=31 RECID=89 STAMP=1055236432
119input archived log thread=1 sequence=32 RECID=91 STAMP=1055236432
120input archived log thread=1 sequence=33 RECID=93 STAMP=1055236433
121input archived log thread=1 sequence=34 RECID=95 STAMP=1055236434
122input archived log thread=1 sequence=35 RECID=97 STAMP=1055236434
123input archived log thread=1 sequence=36 RECID=100 STAMP=1055236435
124input archived log thread=1 sequence=37 RECID=101 STAMP=1055236436
125input archived log thread=1 sequence=38 RECID=103 STAMP=1055236436
126input archived log thread=1 sequence=39 RECID=105 STAMP=1055236437
127input archived log thread=1 sequence=40 RECID=107 STAMP=1055236437
128input archived log thread=1 sequence=41 RECID=109 STAMP=1055236439
129input archived log thread=1 sequence=42 RECID=111 STAMP=1055236511
130input archived log thread=1 sequence=43 RECID=113 STAMP=1055236514
131input archived log thread=1 sequence=44 RECID=115 STAMP=1055236517
132input archived log thread=1 sequence=45 RECID=117 STAMP=1055236519
133input archived log thread=1 sequence=46 RECID=119 STAMP=1055236531
134input archived log thread=1 sequence=47 RECID=121 STAMP=1055236532
135input archived log thread=1 sequence=48 RECID=123 STAMP=1055236535
136input archived log thread=1 sequence=49 RECID=125 STAMP=1055236536
137input archived log thread=1 sequence=50 RECID=128 STAMP=1055236537
138input archived log thread=1 sequence=51 RECID=129 STAMP=1055236549
139input archived log thread=1 sequence=52 RECID=131 STAMP=1055236550
140input archived log thread=1 sequence=53 RECID=133 STAMP=1055236550
141input archived log thread=1 sequence=54 RECID=135 STAMP=1055236924
142input archived log thread=1 sequence=55 RECID=136 STAMP=1055236924
143channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:08
144channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:09
145piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_annnn_TAG20201031T092204_hssh202w_.bkp tag=TAG20201031T092204 comment=NONE
146channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
147Finished backup at 2020-10-31 09:22:09
148RMAN-08591: WARNING: invalid archived log deletion policy

150Starting backup at 2020-10-31 09:22:10
151using channel ORA_DISK_1
152channel ORA_DISK_1: starting full datafile backup set
153channel ORA_DISK_1: specifying datafile(s) in backup set
154including current control file in backup set
155channel ORA_DISK_1: starting piece 1 at 2020-10-31 09:22:11
156channel ORA_DISK_1: finished piece 1 at 2020-10-31 09:22:12
157piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_ncnnf_TAG20201031T092210_hssh237k_.bkp tag=TAG20201031T092210 comment=NONE
158channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
159Finished backup at 2020-10-31 09:22:12
160RMAN-08591: WARNING: invalid archived log deletion policy

162RMAN> 

   

四、主库查询SCN,后续恢复到该时间点

 1SYS@LHR11G> alter system switch logfile;

 3System altered.

 5SYS@LHR11G> 
 6SYS@LHR11G> select current_scn from v$database;

 8CURRENT_SCN
 9-----------


12SYS@LHR11G> alter system switch logfile;

14System altered.

16SYS@LHR11G> create table lhr.emp1 as select * from scott.emp;

18Table created.

20SYS@LHR11G> alter system switch logfile;

22System altered.

24-- 备库查询
25SYS@LHR11GDG> @dg_status
  THREAD# NAME                                                                                                  SEQUENCE# APPLIED            FIRST_TIME
28---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ -------------------
/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
/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
/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
/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
/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

35SYS@LHR11GDG> select count(*) from lhr.emp1;
 COUNT(*)
38----------

 

说明主备是同步的,接下来我们对主库做不完全恢复。

 

五、主库还原,做不完全恢复

假设主库出现很严重的错误,必须使用rman做不完全恢复,现在要恢复主库到SCN为 1373192

 1[oracle@lhr11g ~]$ rman target /

 3Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 31 09:27:38 2020

 5Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 7connected to target database: LHR11G (DBID=2007947551)

 9RMAN> shutdown abort

11using target database control file instead of recovery catalog
12Oracle instance shut down

14RMAN> startup mount

16connected to target database (not started)
17Oracle instance started
18database mounted

20Total System Global Area     325685248 bytes

22Fixed Size                     2252944 bytes
23Variable Size                192941936 bytes
24Database Buffers             125829120 bytes
25Redo Buffers                   4661248 bytes

27RMAN> restore database;

29Starting restore at 2020-10-31 09:28:01
30allocated channel: ORA_DISK_1
31channel ORA_DISK_1: SID=3 device type=DISK

33channel ORA_DISK_1: starting datafile backup set restore
34channel ORA_DISK_1: specifying datafile(s) to restore from backup set
35channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/LHR11G/system01.dbf
36channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/LHR11G/sysaux01.dbf
37channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/LHR11G/undotbs01.dbf
38channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/LHR11G/users01.dbf
39channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/LHR11G/example01.dbf
40channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T092045_hssgzg25_.bkp
41channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/LHR11G/backupset/2020_10_31/o1_mf_nnndf_TAG20201031T092045_hssgzg25_.bkp tag=TAG20201031T092045
42channel ORA_DISK_1: restored backup piece 1
43channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
44Finished restore at 2020-10-31 09:29:27

46RMAN> recover database until scn 1373192;

48Starting recover at 2020-10-31 09:29:32
49using channel ORA_DISK_1

51starting media recovery

53archived log for thread 1 with sequence 54 is already on disk as file /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_31/o1_mf_1_54_hssh1w5j_.arc
54archived log for thread 1 with sequence 55 is already on disk as file /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_31/o1_mf_1_55_hssh1w7d_.arc
55archived log for thread 1 with sequence 56 is already on disk as file /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_31/o1_mf_1_56_hssh6fz3_.arc
56archived log for thread 1 with sequence 57 is already on disk as file /u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_31/o1_mf_1_57_hssh7d8w_.arc
57archived log file name=/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_31/o1_mf_1_54_hssh1w5j_.arc thread=1 sequence=54
58archived log file name=/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_31/o1_mf_1_55_hssh1w7d_.arc thread=1 sequence=55
59archived log file name=/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_31/o1_mf_1_56_hssh6fz3_.arc thread=1 sequence=56
60media recovery complete, elapsed time: 00:00:00
61Finished recover at 2020-10-31 09:29:33

63RMAN> alter database open resetlogs;

65database opened

67RMAN> exit

70Recovery Manager complete.
 

主库查询:

 1[oracle@lhr11g ~]$ sas

 3SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 31 09:30:20 2020

 5Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 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

12SYS@LHR11G> select count(*) from lhr.emp1;
13select count(*) from lhr.emp1
                        *
15ERROR at line 1:
16ORA-00942: table or view does not exist

19SYS@LHR11G> select resetlogs_change# from v$database;

21RESETLOGS_CHANGE#
22-----------------


25SYS@LHR11G> create table lhr.emp2 as select * from scott.emp;

27Table created.
 

备库查询:

 1SYS@LHR11GDG> select count(*) from lhr.emp1;
 COUNT(*)
 4----------


 7SYS@LHR11GDG> select * from  lhr.emp2;
 8select * from  lhr.emp2
                  *
10ERROR at line 1:
11ORA-00942: table or view does not exist
 

可见,主备关系已经断掉了,不能再自动同步了。

备库的告警日志:

 1A new recovery destination branch has been registered
 2RFS[5]: Standby in the future of new recovery destinationBranch(resetlogs_id) 1055237385
 3Incomplete Recovery SCN: 1373219
 4Resetlogs SCN: 1373193
 5Standby Became Primary SCN: 1361559
 6Flashback database to SCN 1361559 to follow new branch
 7Flashback database to SCN 1361559 to follow new branch
 8RFS[5]: New Archival REDO Branch(resetlogs_id): 1055237385  Prior: 1054920278
 9RFS[5]: Archival Activation ID: 0x78326358 Current: 0x782dc04f
10RFS[5]: Effect of primary database OPEN RESETLOGS
11RFS[5]: Managed Standby Recovery process is active
12RFS[5]: Incarnation entry added for Branch(resetlogs_id): 1055237385 (LHR11GDG)
13Sat Oct 31 09:29:48 2020
14Setting recovery target incarnation to 8
15Sat Oct 31 09:29:48 2020
16MRP0: Incarnation has changed! Retry recovery...
17Errors in file /u01/app/oracle/diag/rdbms/lhr11gdg/LHR11GDG/trace/LHR11GDG_pr00_1076.trc:
18ORA-19906: recovery target incarnation changed during recovery
19Managed Standby Recovery not using Real Time Apply
20Managed Standby Recovery not using Real Time Apply
21Sat Oct 31 09:29:48 2020
22Archived Log entry 180 added for thread 1 sequence 1 ID 0x78326358 dest 1:
23Recovery interrupted!
24Recovered data files to a consistent state at change 1374084
25Sat Oct 31 09:29:49 2020
started logmerger process
27Sat Oct 31 09:29:49 2020
28Managed Standby Recovery starting Real Time Apply
29Warning: Recovery target destination is in a sibling branch
30of the controlfile checkpoint. Recovery will only recover
31changes to datafiles.
32Datafile 1 (ckpscn 1374084) is orphaned on incarnation#=7
33MRP0: Detected orphaned datafiles! 
34Recovery will possibly be retried after flashback...
35Errors in file /u01/app/oracle/diag/rdbms/lhr11gdg/LHR11GDG/trace/LHR11GDG_pr00_1151.trc:
36ORA-19909: datafile 1 belongs to an orphan incarnation
37ORA-01110: data file 1: '/u01/app/oracle/oradata/LHR11GDG/system01.dbf'
38Managed Standby Recovery not using Real Time Apply
39Recovery Slave PR00 previously exited with exception 19909
40Sat Oct 31 09:29:52 2020
41RFS[6]: Assigned to RFS process 1157
42RFS[6]: Selected log 6 for thread 1 sequence 2 dbid 2007947551 branch 1055237385
43Sat Oct 31 09:29:52 2020
44Archived Log entry 181 added for thread 1 sequence 2 ID 0x78326358 dest 1:
45Sat Oct 31 09:29:52 2020
46Primary database is in MAXIMUM PERFORMANCE mode
47RFS[7]: Assigned to RFS process 1159
48RFS[7]: Selected log 6 for thread 1 sequence 3 dbid 2007947551 branch 1055237385
49Sat Oct 31 09:30:10 2020
50MRP0: Background Media Recovery process shutdown (LHR11GDG)
51Sat Oct 31 09:30:48 2020
52RFS[8]: Assigned to RFS process 1167
53RFS[8]: Opened log for thread 1 sequence 57 dbid 2007947551 branch 1054920278
54Archived Log entry 182 added for thread 1 sequence 57 rlc 1054920278 ID 0x782dc04f dest 2:
 

可以看到dg备库已经知道主库做了resetlogs,也提示dg需要flashback才能继续同步。这里提示dg需要flashback到1361559(Flashback database to SCN 1361559 to follow new branch),主库做不完全恢复的点为1373192。

下边进行修复操作。

 

六、备库做闪回操作

这里需要注意的是,scn号必须备库的告警日志中获取,这里获取到的是1361559,闪回操作如下:

 1[oracle@lhr11gdg ~]$ sas

 3SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 31 09:40:46 2020

 5Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 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

12SYS@LHR11GDG> startup force mount
13ORACLE instance started.

15Total System Global Area  346562560 bytes
16Fixed Size                  2253144 bytes
17Variable Size             209718952 bytes
18Database Buffers          130023424 bytes
19Redo Buffers                4567040 bytes
20Database mounted.

22SYS@LHR11GDG> flashback database to scn 1361559;

24Flashback complete.

26SYS@LHR11GDG> alter database open;

28Database altered.

30SYS@LHR11GDG> alter database recover managed standby database using current logfile disconnect from session;

32Database altered.

34SYS@LHR11GDG> select count(*) from lhr.emp1;
35select count(*) from lhr.emp1
                        *
37ERROR at line 1:
38ORA-00942: table or view does not exist

41SYS@LHR11GDG> select count(*) from lhr.emp2;
 COUNT(*)
44----------


47SYS@LHR11GDG> @dg_status
  THREAD# NAME                                                                                                  SEQUENCE# APPLIED            FIRST_TIME
50---------- ---------------------------------------------------------------------------------------------------- ---------- ------------------ -------------------
/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_4_hssjos1z_.arc                    4 YES                2020-10-31 09:41:51
/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_5_hssjot1o_.arc                    5 YES                2020-10-31 09:49:44
/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_6_hssjox45_.arc                    6 YES                2020-10-31 09:49:45
/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_7_hssjqs32_.arc                    7 YES                2020-10-31 09:49:49
/u01/app/oracle/flash_recovery_area/LHR11GDG/archivelog/2020_10_31/o1_mf_1_8_hssjr25j_.arc                    8 IN-MEMORY          2020-10-31 09:50:48
   

七、查询主备库是否实时同步

 1[oracle@lhr11g ~]$ dgmgrl /
 2DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

 4Copyright (c) 2000, 2009, Oracle. All rights reserved.

 6Welcome to DGMGRL, type "help" for information.
 7Connected.
 8DGMGRL> show configuration

10Configuration - LHR11G
 Protection Mode: MaxPerformance
 Databases:
   LHR11G   - Primary database
   LHR11GDG - Physical standby database

17Fast-Start Failover: DISABLED

19Configuration Status:
20SUCCESS

23-- 主库建表
24SYS@LHR11G> create table lhr.emp3 as select * from scott.emp;

26Table created.

28-- 备库查询
29SYS@LHR11GDG> select count(*) from lhr.emp3;
 COUNT(*)
32----------


 

DG环境恢复正常。

 

八、总结

1、在DG环境中,建议对主库和备库都开启闪回数据库的特性,并且设置比较大的闪回恢复区(db_recovery_file_dest_size)。

2、在DG环境中,若主库做了不完全恢复,那么备库必须做相关的闪回操作才能恢复DG的同步关系。DG备库闪回的SCN号可以从备库的告警日志中获取到(Flashback database to SCN 1361559 to follow new branch)。

本文结束。


watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

• 微信公众号:DB宝,作者:小麦苗
• 作者博客地址:http://blog.itpub.net/26736162/
• 作者微信:db_bao

• 作者QQ:646634621,QQ群:230161599、618766405
• 提供Oracle OCP、OCM、高可用(rac+dg+ogg)和MySQL DBA培训
• 版权所有,欢迎分享本文,转载请保留出处

• 若有侵权请联系小麦苗删除

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

★DB宝分享的IT资料:https://mp.weixin.qq.com/s/Iwsy-zkzwgs8nYkcMz29ag★DB宝笔试面试详解:https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w
 

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

长按下图识别二维码,关注小麦苗的微信公众号:DB宝,学习最实用的数据库技术。

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=


本文分享自微信公众号 - DB宝(lhrdba)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。



这篇关于【DB宝31】Oracle DG环境中主库使用rman做不完全恢复后,备库如何修复继续同步的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程