【DB宝32】Oracle DG环境中主库做闪回操作后，备库如何修复继续同步

AiDBA宝典

发布于 2020-12-18 14:41:53

8390

发布于 2020-12-18 14:41:53

一、下载镜像

小麦苗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@LHR11G> alter system switch logfile ;

System altered.

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

Table created.

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

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

SYS@LHR11G> select current_scn from v$database;

CURRENT_SCN
-----------
    1363331

-- 备库

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

FLASHBACK_ON                         DB_UNIQUE_NAME                                               DATABASE_ROLE
------------------------------------ ------------------------------------------------------------ --------------------------------
YES                                  LHR11GDG                                                     PHYSICAL STANDBY


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

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

SYS@LHR11GDG> select current_scn from v$database;

CURRENT_SCN
-----------
    1363340

三、主库做闪回数据库操作

SYS@LHR11G> select current_scn from v$database;

CURRENT_SCN
-----------
    1363438

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

Table created.

SYS@LHR11G> startup force mount
ORACLE instance started.

Total System Global Area  325685248 bytes
Fixed Size                  2252944 bytes
Variable Size             188747632 bytes
Database Buffers          130023424 bytes
Redo Buffers                4661248 bytes
Database mounted.
SYS@LHR11G> flashback database to scn 1363438;

Flashback complete.

SYS@LHR11G> alter database open resetlogs;

Database altered.

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

Table created.

四、备库做闪回操作

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


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

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

SYS@LHR11GDG>  select current_scn from v$database;

CURRENT_SCN
-----------
    1363462

SYS@LHR11GDG>  select current_scn from v$database;

CURRENT_SCN
-----------
    1363462

可见，主备关系已经断掉。接下来，对备库进行闪回。

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 1363438;
flashback database to scn 1363438
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 1363064 to SCN 1363438
ORA-38761: redo log sequence 9 in thread 1, incarnation 7 could not be accessed

SYS@LHR11GDG> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
             1232930 2020-10-27 16:21:36             1440      104857600                        0

SYS@LHR11GDG>  flashback database to scn 1361000;

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.emp3;

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

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


SYS@LHR11GDG> select current_scn from v$database;

CURRENT_SCN
-----------
    1365917

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

[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.emp4 as select * from scott.emp;

Table created.

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

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

DG环境恢复正常。