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

举报
小麦苗DB宝 发表于 2022/04/01 16:23:35 2022/04/01
【摘要】 一、下载镜像 二、DG环境初始化 三、主库做备份操作 四、主库查询SCN,后续恢复到该时间点 五、主库还原,做不完全恢复 六、备库做闪回操作 七、查询主备库是否实时同步 八、总结本文介绍一下,在DG环境中,主库使用rman做不完全恢复后,备库如何通过flashback操作,继续和主库保持同步,而不用重新搭建DG。主备库均已开启闪回数据库特性。 一、下载镜像小麦苗DG环境的hub地址:htt...

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

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 &

查看镜像:

[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:

[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
-- 创建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的同步情况:

-- 查询目前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;”即可:

-- 主库
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;

三、主库做备份操作

[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,后续恢复到该时间点

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

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

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

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

[oracle@lhr11g ~]$ rman target /

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

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

connected to target database: LHR11G (DBID=2007947551)

RMAN> shutdown abort

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

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     325685248 bytes

Fixed Size                     2252944 bytes
Variable Size                192941936 bytes
Database Buffers             125829120 bytes
Redo Buffers                   4661248 bytes

RMAN> restore database;

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

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

RMAN> recover database until scn 1373192;

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

starting media recovery

archived 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
archived 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
archived 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
archived 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
archived log file name=/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_31/o1_mf_1_54_hssh1w5j_.arc thread=1 sequence=54
archived log file name=/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_31/o1_mf_1_55_hssh1w7d_.arc thread=1 sequence=55
archived log file name=/u01/app/oracle/flash_recovery_area/LHR11G/archivelog/2020_10_31/o1_mf_1_56_hssh6fz3_.arc thread=1 sequence=56
media recovery complete, elapsed time: 00:00:00
Finished recover at 2020-10-31 09:29:33

RMAN> alter database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.


主库查询:

[oracle@lhr11g ~]$ sas

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

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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


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

RESETLOGS_CHANGE#
-----------------
          1373193

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

Table created.

备库查询:

SYS@LHR11GDG> select count(*) from lhr.emp1;

  COUNT(*)
----------
        14

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

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

备库的告警日志:

A new recovery destination branch has been registered
RFS[5]: Standby in the future of new recovery destinationBranch(resetlogs_id) 1055237385
Incomplete Recovery SCN: 1373219
Resetlogs SCN: 1373193
Standby Became Primary SCN: 1361559
Flashback database to SCN 1361559 to follow new branch
Flashback database to SCN 1361559 to follow new branch
RFS[5]: New Archival REDO Branch(resetlogs_id): 1055237385  Prior: 1054920278
RFS[5]: Archival Activation ID: 0x78326358 Current: 0x782dc04f
RFS[5]: Effect of primary database OPEN RESETLOGS
RFS[5]: Managed Standby Recovery process is active
RFS[5]: Incarnation entry added for Branch(resetlogs_id): 1055237385 (LHR11GDG)
Sat Oct 31 09:29:48 2020
Setting recovery target incarnation to 8
Sat Oct 31 09:29:48 2020
MRP0: Incarnation has changed! Retry recovery...
Errors in file /u01/app/oracle/diag/rdbms/lhr11gdg/LHR11GDG/trace/LHR11GDG_pr00_1076.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Managed Standby Recovery not using Real Time Apply
Sat Oct 31 09:29:48 2020
Archived Log entry 180 added for thread 1 sequence 1 ID 0x78326358 dest 1:
Recovery interrupted!
Recovered data files to a consistent state at change 1374084
Sat Oct 31 09:29:49 2020
 started logmerger process
Sat Oct 31 09:29:49 2020
Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 1374084) is orphaned on incarnation#=7
MRP0: Detected orphaned datafiles! 
Recovery will possibly be retried after flashback...
Errors in file /u01/app/oracle/diag/rdbms/lhr11gdg/LHR11GDG/trace/LHR11GDG_pr00_1151.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/app/oracle/oradata/LHR11GDG/system01.dbf'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 19909
Sat Oct 31 09:29:52 2020
RFS[6]: Assigned to RFS process 1157
RFS[6]: Selected log 6 for thread 1 sequence 2 dbid 2007947551 branch 1055237385
Sat Oct 31 09:29:52 2020
Archived Log entry 181 added for thread 1 sequence 2 ID 0x78326358 dest 1:
Sat Oct 31 09:29:52 2020
Primary database is in MAXIMUM PERFORMANCE mode
RFS[7]: Assigned to RFS process 1159
RFS[7]: Selected log 6 for thread 1 sequence 3 dbid 2007947551 branch 1055237385
Sat Oct 31 09:30:10 2020
MRP0: Background Media Recovery process shutdown (LHR11GDG)
Sat Oct 31 09:30:48 2020
RFS[8]: Assigned to RFS process 1167
RFS[8]: Opened log for thread 1 sequence 57 dbid 2007947551 branch 1054920278
Archived 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,闪回操作如下:

[oracle@lhr11gdg ~]$ sas

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

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@LHR11GDG> startup force mount
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.

SYS@LHR11GDG> flashback database to scn 1361559;

Flashback complete.

SYS@LHR11GDG> alter database open;

Database altered.

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

Database altered.

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


SYS@LHR11GDG> select count(*) from lhr.emp2;

  COUNT(*)
----------
        14

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_4_hssjos1z_.arc                    4 YES                2020-10-31 09:41:51
         1 /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
         1 /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
         1 /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
         1 /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

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

[oracle@lhr11g ~]$ dgmgrl /
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


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

Table created.

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

  COUNT(*)
----------
        14

DG环境恢复正常。

八、总结

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

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

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。