Oracle DG环境中主库使用rman做不完全恢复后,备库如何修复继续同步
本文介绍一下,在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)。
- 点赞
- 收藏
- 关注作者
评论(0)