当前位置 博文首页 > LuciferLiu_DBA:实战篇:Oracle DataGuard出现GAP如何修复?看
- 作者:LuciferLiu,中国DBA联盟(ACDU)成员。
- 目前主要从事Oracle DBA工作,曾从事 Oracle 数据库开发工作,主要服务于生产制造,汽车金融等行业。
- 现拥有Oracle OCP,OceanBase OBCA认证,擅长Oracle数据库运维开发,备份恢复,安装迁移,Linux自动化运维脚本编写等。
那么,如果遇到GAP如何修复呢?且听我细细道来~
- 主库归档日志存在,可以通过配置 Fetch Archive Log(FAL) 参数,自动解决归档GAP。
- 主库归档日志丢失,需要 人工干预 来修复:
- 11G 的处理步骤:
a.在主库上创建一个备库的控制文件
b.以备库的当前SCN号为起点,在主库上做一个增量备份
c.将增量备份拷贝到备库上
d.使用新的控制文件将备库启动到mount状态
e.将增量备份注册到RMAN的catalog,取消备库的恢复应用,恢复增量备份
f.开启备库的恢复进程- 12C 的新特性(RECOVER … FROM SERVICE)
- 18C 的新特性(RECOVER STANDBY DATABASE FROM SERVICE)
Oracle随着版本的升级,逐渐将步骤缩减,进行封装,18C之后可谓是达到了所谓的一键刷新,恢复DG同步。
下面我们通过实验来进行演示如何修复:
- 11G常规修复
- 12C新特性(RECOVER … FROM SERVICE)修复
- 18C新特性(RECOVER STANDBY DATABASE FROM SERVICE)修复
测试环境数据库安装:
- 11G:./OracleShellInstall.sh -i 10.211.55.100
- 12C:./OracleShellInstall.sh -i 10.211.55.101
- 18C:./OracleShellInstall.sh -i 10.211.55.102
更多更详细的脚本使用方式可以订阅专栏:Oracle一键安装脚本。
脚本获取方式:
- GitHub 持续保持更新中🔥
- Gitee 持续保持更新中🔥
ADG搭建可参考:
- 手把手教你DBCA搭建Oracle ADG
- ADG搭建系列之 11G RAC to Single DATABASE
- ADG单实例系列搭建之(RMAN备份恢复)
- ADG单实例搭建系列之 (DBCA)
- ADG单实例搭建系列之(Active Database Duplicate Using Image Copies)
以上实验环境已搭建完毕。
首先,模拟备库断电,主库切几个最新的归档,然后手工删掉,重新开启DG同步。
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
sqlplus / as sysdba
alter system switch logfile;
rm 1_34_1070147137.arc
rm 1_33_1070147137.arc
startup
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 32 34
SELECT max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
31
注意:当前DG数据库已存在GAP,GAP日志为:32—34 。
alter database create standby controlfile as '/tmp/standby.ctl';
sqlplus / as sysdba
select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
1086639
sqlplus / as sysdba
select file# from v$datafile where creation_change# > =1086639;
rman target /
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup INCREMENTAL from scn 1086639 database format '/tmp/incre_%U';
release channel c1;
release channel c2;
}
注意:如果存在新增数据文件,备库恢复时需要先restore新添加的数据文件。
scp incre_0* oracle@orcl_stby:/home/oracle
scp standby.ctl oracle@orcl_stby:/home/oracle
注意:确认备库的磁盘空间是否足够存放。
sqlplus / as sysdba
shutdown immediate
startup nomount
rman target /
restore controlfile from '/home/oracle/standby.ctl';
alter database mount;
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
rman target /
catalog start with '/home/oracle/';
YES
recover database noredo;
sqlplus / as sysdba
alter database open read only;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=defer;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;
sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
SELECT max(sequence#) from v$archived_log where applied='YES';
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
至此,DG GAP已被修复,以上方式为常规修复方式,各个版本都通用。
首先,模拟备库断电,主库切几个最新的归档,然后手工删掉,重新开启DG同步。
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
sqlplus / as sysdba
alter system switch logfile;
rm 1_30_1070147137.arc
rm 1_31_1070147137.arc
sqlplus / as sysdba
startup
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 30 31
SELECT max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
31
##主库添加数据文件
alter tablespace TEST add datafile '/oradata/ORCL/test02.dbf' size 100M autoextend off;
注意:当前DG数据库已存在GAP,GAP日志为:30—31 。
sqlplus / as sysdba
SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
2600487
recover standby using service
,通过RMAN连接到target备库,然后用主库的service执行恢复备库命令。语法:
RECOVER DATABASE FROM SERVICE < PRIMARY DB SERVICE NAME > NOREDO USING COMPRESSED BACKUPSET;
注意:确认主库的TNS已配置,这里的< PRIMARY DB SERVICE NAME >即 TNSNAME。
sqlplus / as sysdba
shutdown immediate
startup nomount
rman target /
restore standby controlfile from service orcl;
sqlplus / as sysdba
alter database mount;
sqlplus / as sysdba
select file# from v$datafile where creation_change# > =2600487;
FILE#
----------
13
rman target /
run
{
SET NEWNAME FOR DATABASE TO '/oradata/ORCL_STBY/%f_%U';
RESTORE DATAFILE 13 FROM SERVICE orcl;
}
rman target /
catalog start with '/oradata/ORCL_STBY';
YES
SWITCH DATABASE TO COPY;
sqlplus / as sysdba
alter system set standby_file_management=MANUAL;
sqlplus / as sysdba
##logfile
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database rename file '/oradata/ORCL/redo03.log' to '/oradata/ORCL_STBY/redo03.log';
alter database rename file '/oradata/ORCL/redo02.log' to '/oradata/ORCL_STBY/redo02.log';
alter database rename file '/oradata/ORCL/redo01.log' to '/oradata/ORCL_STBY/redo01.log';
alter database rename file '/oradata/ORCL/standby_redo04.log' to '/oradata/ORCL_STBY/standby_redo04.log';
alter database rename file '/oradata/ORCL/standby_redo05.log' to '/oradata/ORCL_STBY/standby_redo05.log';
alter database rename file '/oradata/ORCL/standby_redo06.log' to '/oradata/ORCL_STBY/standby_redo06.log';
alter database rename file '/oradata/ORCL/standby_redo07.log' to '/oradata/ORCL_STBY/standby_redo07.log';
##tempfile
alter database rename file '/oradata/ORCL/temp01.dbf' to '/oradata/ORCL_STBY/temp01.dbf';
alter database rename file '/oradata/ORCL/pdbseed/temp012021-04-11_06-13-50-844-AM.dbf' to '/oradata/ORCL_STBY/pdbseed/temp012021-04-11_06-13-50-844-AM.dbf';
alter database rename file '/oradata/ORCL/BFA6BEE45A1E3605E053AC01A8C0DD20/datafile/o1_mf_temp_j749f5fy_.dbf' to '/oradata/ORCL_STBY/BFA6BEE45A1E3605E053AC01A8C0DD20/datafile/o1_mf_temp_j749f5fy_.dbf';
sqlplus / as sysdba
alter system set standby_file_management=AUTO;
recover database from service orcl noredo using compressed backupset;
Notes:如果主备库文件目录不一致,则需要catalog切换控制文件中路径,否则报错:
sqlplus / as sysdba
col HXFNM for a100
set line222
select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;
sqlplus / as sysdba
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM SWITCH LOGFILE;
sqlplus / as sysdba
alter database open;
alter pluggable database all open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
sqlplus / as sysdba
set line222
col member for a60
select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#;
sqlplus test/test@pdb01
insert into test values (999);
commit;
alter session set container=pdb01;
select * from test.test;
ID
----------
1
2
999
至此,GAP已修复完成,可以发现,12C这个新特性,将一些步骤进行了省略和封装,进一步减少了我们的操作步骤,但是内部的原理仍然是一致的。
语法:
RECOVER STANDBY DATABASE FROM SERVICE primary_db;
首先,模拟备库断电,主库切几个最新的归档,然后手工删掉,重新开启DG同步。
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
sqlplus / as sysdba
alter system switch logfile;
rm 1_69_1070147137.arc
rm 1_70_1070147137.arc
startup
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
sqlplus / as sysdba
SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 70 70
SELECT max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
69
##主库添加数据文件
alter tablespace TEST add datafile '/oradata/ORCL/test02.dbf' size 100M autoextend off;
注意:当前DG数据库已存在GAP,GAP日志为:69—70 。
下面演示一下,如何使用一行命令在线修复DG GAP:
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
rman target /
RMAN> RECOVER STANDBY DATABASE FROM SERVICE orcl;
Starting recover at 19-APR-21
using target database control file instead of recovery catalog
Oracle instance started
Total System Global Area3355441944 bytes
Fixed Size 9141016 bytes
Variable Size671088640 bytes
Database Buffers2667577344 bytes
Redo Buffers 7634944 bytes
contents of Memory Script:
{
restore standby controlfile from service 'orcl';
alter database mount standby database;
}
executing Memory Script
Starting restore at 19-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=502 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcl
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/oradata/ORCL_STBY/control01.ctl
output file name=/oradata/ORCL_STBY/control02.ctl
Finished restore at 19-APR-21
released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
contents of Memory Script:
{
set newname for tempfile 1 to
"/oradata/ORCL_STBY/temp01.dbf";
set newname for tempfile 2