AI 助理
备案控制台
开发者社区 数据库 文章 正文

oracle-dg-DataGuard使用RMAN增量备份修复GAP

2022-07-21 378
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
日志服务 SLS，月写入数据量 50GB 1个月
简介：

172.16.10.10 DataGuard使用RMAN增量备份修复GAP.txt

参考：

11g Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)
Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary (Doc ID 1531031.1)

standby,查询缺少归档日志号，可以看到seq# 222299~的归档日志丢失未应用

SQL> select first_time,first_change#,next_change#,sequence# from v$log_history;
SQL> select sequence#,applied from v$archived_log where applied='YES';
SQL> select sequence#,applied from v$archived_log;

standby,查询GAP信息，发现seq# 222299 开始就丢失

SQL> select * from v$archive_gap;
THREAD#  LOW_SEQUENCE#  HIGH_SEQUENCE#
———-     ————-           ————–
          1             222299             


SQL>

在Primary上查询seq#丢失的的归档是否存在，这里查询结果是不存在

SQL> select name from v$archived_log where thread#=1 and dest_id=1 and sequence# =222299;

在Primary找到丢失归档的SCN

SQL> select first_change# from v$archived_log where sequence#=222299;
FIRST_CHANGE#
————----------------
       6844050347488
Primary增量备份，将SCN大于 6844050347488 的block备份出来，并且传到Standby的一个空目录中

Primary，增量备份数据库 、控制文件

backup device type disk incremental from scn 6844050347488 database format '/oradata01/arch02/%U';


run{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
backup  incremental from scn 6844050347488 database format '/oradata01/arch02/%U';
Release channel ch1;
Release channel ch2;
Release channel ch3;
Release channel ch4;
}

backup current controlfile for standby format '/oradata01/arch02/contronfil.bak';


[oracle@dghpl1018 arch02]$ ls -lrh
total 666G
-rw-r----- 1 oracle oinstall  48M Jun 10 13:27 gu0vn3q2_1_1
-rw-r----- 1 oracle oinstall 109G Jun 10 13:42 gt0vn3q1_1_1
-rw-r----- 1 oracle oinstall 108G Jun 10 13:41 gs0vn3q0_1_1
-rw-r----- 1 oracle oinstall 115G Jun 10 13:27 gr0vn23n_1_1
-rw-r----- 1 oracle oinstall 114G Jun 10 13:27 gq0vn23m_1_1
-rw-r----- 1 oracle oinstall 110G Jun 10 13:27 gp0vn23m_1_1
-rw-r----- 1 oracle oinstall 113G Jun 10 13:27 go0vn23m_1_1
-rw-r----- 1 oracle oinstall  48M Jun 10 13:58 contronfil.bak
[oracle@dghpl1018 arch02]$ scp * @172.16.10.10:/oradata01/arch02/
The authenticity of host '172.16.10.10 (172.16.10.10)' can't be established.
ECDSA key fingerprint is SHA256:nQeixwcKl0q6bmdEpE6/xsfugqDb9CeRrROgD6j41M4.
ECDSA key fingerprint is MD5:35:76:4b:21:c7:08:3b:ca:6f:1d:1d:20:37:a2:0e:f9.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.10.10' (ECDSA) to the list of known hosts.
oracle@172.16.10.10's password: 
contronfil.bak                                                                                                                                              100%   48MB 210.9MB/s   00:00    
go0vn23m_1_1                                                                                                                                                100%  113GB 203.5MB/s   09:27    
gp0vn23m_1_1                                                                                                                                                100%  109GB 201.2MB/s   09:15    
gq0vn23m_1_1                                                                                                                                                100%  113GB 204.7MB/s   09:27    
gr0vn23n_1_1                                                                                                                                                100%  114GB 206.0MB/s   09:26    
gs0vn3q0_1_1                                                                                                                                                100%  107GB 211.3MB/s   08:40    
gt0vn3q1_1_1                                                                                                                                                100%  109GB 210.2MB/s   08:49    
gu0vn3q2_1_1                                                                                                                                                100%   48MB 214.7MB/s   00:00    
[oracle@dghpl1018 arch02]$

standby,catalog刚传过来的备份文件

RMAN> catalog start with '/oradata01/arch02/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /oradata01/arch02/

List of Files Unknown to the Database
=====================================
File Name: /oradata01/arch02/gr0vn23n_1_1
File Name: /oradata01/arch02/contronfil.bak
File Name: /oradata01/arch02/gp0vn23m_1_1
File Name: /oradata01/arch02/gs0vn3q0_1_1
File Name: /oradata01/arch02/gq0vn23m_1_1
File Name: /oradata01/arch02/go0vn23m_1_1
File Name: /oradata01/arch02/gu0vn3q2_1_1
File Name: /oradata01/arch02/gt0vn3q1_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata01/arch02/gr0vn23n_1_1
File Name: /oradata01/arch02/contronfil.bak
File Name: /oradata01/arch02/gp0vn23m_1_1
File Name: /oradata01/arch02/gs0vn3q0_1_1
File Name: /oradata01/arch02/gq0vn23m_1_1
File Name: /oradata01/arch02/go0vn23m_1_1
File Name: /oradata01/arch02/gu0vn3q2_1_1
File Name: /oradata01/arch02/gt0vn3q1_1_1

RMAN>

standby,还原控制文件

sql> shutdown immediate
sql>startup nomount

RMAN> restore standby controlfile from '/oradata01/arch02/contronfil.bak';

RMAN> alter database mount;

standby,恢复数据库

RMAN>
run{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
recover database noredo;
Release channel ch1;
Release channel ch2;
Release channel ch3;
Release channel ch4;
}




SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process, status, thread#, sequence# from v$managed_standby where process='MRP0';

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
MRP0      WAIT_FOR_GAP          1     222741

SQL> /

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
MRP0      WAIT_FOR_GAP          1     222741

Standby应用日志

SQL> alter database recover managed standby database disconnect from session;


#standby,alterlog ，发现有GAP - thread 1 sequence 222741-222759

PR00 (PID:2032): FAL: Failed to request gap sequence
PR00 (PID:2032):  GAP - thread 1 sequence 222741-222759
PR00 (PID:2032):  DBID 3701582317 branch 1025620463
PR00 (PID:2032): FAL: All defined FAL servers have been attempted
PR00 (PID:2032): -------------------------------------------------------------------------
PR00 (PID:2032): Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
PR00 (PID:2032): parameter is defined to a value that's sufficiently large
PR00 (PID:2032): enough to maintain adequate log switch information to resolve
PR00 (PID:2032): archived redo log gaps.
PR00 (PID:2032): -------------------------------------------------------------------------
2022-06-10T17:05:09.570243+08:00

standby,查看归档日志是存在的，说明是没注册到控制文件而已

[oracle@dghpl1021 ~]$ 
[oracle@dghpl1021 ~]$ cd /arch01/orcl/
[oracle@dghpl1021 orcl]$ ls -ltr *22274*
-rw-r----- 1 oracle oinstall  3564085760 Jun 10 12:52 1_222740_1025620463.arc
-rw-r----- 1 oracle oinstall  9646633472 Jun 10 13:05 1_222741_1025620463.arc
-rw-r----- 1 oracle oinstall  9502136320 Jun 10 13:08 1_222742_1025620463.arc
-rw-r----- 1 oracle oinstall 10046875648 Jun 10 13:11 1_222743_1025620463.arc
-rw-r----- 1 oracle oinstall  9445153792 Jun 10 13:15 1_222744_1025620463.arc
-rw-r----- 1 oracle oinstall  9451726336 Jun 10 13:20 1_222745_1025620463.arc
-rw-r----- 1 oracle oinstall  9330305536 Jun 10 13:27 1_222746_1025620463.arc
-rw-r----- 1 oracle oinstall  9653885440 Jun 10 13:31 1_222747_1025620463.arc
-rw-r----- 1 oracle oinstall  9538931200 Jun 10 17:06 1_222748_1025620463.arc
-rw-r----- 1 oracle oinstall 10015410688 Jun 10 17:06 1_222749_1025620463.arc
[oracle@dghpl1021 orcl]$

standby,查看，果然是没有记录

[oracle@dghpl1021 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jun 10 17:05:29 2022
Version 19.3.0.0.0

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

connected to target database: orcl (DBID=3701582317, not open)

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name orclS
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - -------------------
37      1    222750  A 2022-06-10 13:51:04
        Name: /arch01/orcl/1_222750_1025620463.arc

3       1    222760  A 2022-06-10 13:57:31
        Name: /arch01/orcl/1_222760_1025620463.arc

2       1    222761  A 2022-06-10 13:58:28
        Name: /arch01/orcl/1_222761_1025620463.arc

1       1    222762  A 2022-06-10 13:59:52
        Name: /arch01/orcl/1_222762_1025620463.arc

standby,于是，手工catalog一下

catalog start with '/arch01/orcl/';

standby,再次查看，standby已在apply日志了


SQL> select process, status, thread#, sequence# from v$managed_standby where process='MRP0';

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
MRP0      WAIT_FOR_GAP          1     222741

SQL> /

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
MRP0      WAIT_FOR_GAP          1     222741

SQL> /

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
MRP0      APPLYING_LOG          1     222742

SQL> /

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
MRP0      APPLYING_LOG          1     222743

SQL> /

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
MRP0      APPLYING_LOG          1     222747

打开到只读实时应用模式

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL>  select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> select process, status, thread#, sequence# from v$managed_standby where process='MRP0';

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
MRP0      APPLYING_LOG          1     222772

SQL>
文章标签：
日志服务
数据库
关键词：
Oracle数据库一键上云修复
Oracle数据库一键上云rman
Oracle数据库一键上云备份
Oracle数据库一键上云rman备份
Oracle数据库一键上云增量
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
lemotree
目录
相关文章
以山向海
|
5月前
|
Oracle 安全 关系型数据库
关系型数据库Oracle执行RMAN脚本权限验证
【7月更文挑战第24天】
以山向海
107 10
以山向海
|
5月前
|
存储 Oracle 关系型数据库
关系型数据库Oracle执行RMAN脚本环境检查
【7月更文挑战第24天】
以山向海
96 4
以山向海
|
5月前
|
Oracle 关系型数据库 数据库
关系型数据库Oracle执行备份或恢复操作
【7月更文挑战第25天】
以山向海
64 3
以山向海
|
5月前
|
存储 监控 Oracle
关系型数据库Oracle备份策略建议
【7月更文挑战第21天】
以山向海
75 6
以山向海
|
5月前
|
存储 Oracle 关系型数据库
关系型数据库Oracle备份类型与频率
【7月更文挑战第21天】
以山向海
90 6
以山向海
|
5月前
|
存储 Oracle 关系型数据库
关系型数据库Oracle运行RMAN脚本
【7月更文挑战第23天】
以山向海
54 4
以山向海
|
5月前
|
SQL Oracle 关系型数据库
关系型数据库Oracle设置 RMAN 环境:
【7月更文挑战第25天】
以山向海
76 2
以山向海
|
5月前
|
监控 Oracle 算法
关系型数据库Oracle执行RMAN脚本注意事项
【7月更文挑战第23天】
以山向海
69 3
以山向海
|
5月前
|
SQL Oracle 关系型数据库
关系型数据库Oracle结束 RMAN 会话:
【7月更文挑战第25天】
以山向海
107 1
以山向海
|
5月前
|
Oracle 关系型数据库 数据库
关系型数据库Oracle编写RMAN脚本
【7月更文挑战第23天】
以山向海
44 2