签到成功

知道了

CNDBA社区CNDBA社区

Oracle 18c DG 前滚恢复GAP

2018-11-26 08:47 2755 3 原创 Oracle 18c
作者： leo
本文链接：http://www.cndba.cn/leo1990/article/3173

前滚standby数据库

从Oracle Database 18c开始，可以使用RMAN命令RECOVER STANDBY DATABASE通过网络刷新备用数据库。
RECOVER STANDBY DATABASE命令重新启动备用实例，从主数据库刷新控制文件，并自动重命名数据文件，临时文件和联机日志。它还原已添加到主数据库的新数据文件，并将备用数据库恢复到当前时间。
使用RECOVER STANDBY DATABASE命令刷新备用数据库时，可以指定FROM SERVICE子句或NOREDO子句。 FROM SERVICE子句指定主服务的名称。 NOREDO子句指用于刷新备份，在恢复期间不能应用归档的重做日志文件，这允许备用数据库前滚到特定时间或SCN。
在尝试与主数据库同步之前，必须在备用数据库上手动停止MRP。以下是使用RECOVER STANDBY DATABASE命令的示例。它显示了PFILE子句的可选用法，以指定备用数据库的参数文件（在spfile不可用时使用）。
RECOVER STANDBY DATABASE FROM SERVICE service_name PFILE=pfile_location;

使用RECOVER命令前滚物理备用数据库步骤

RMAN通过创建增量备份来转发物理备用数据库，该增量备份包含对主数据库的更改，通过网络将增量备份传输到物理备用数据库，然后将增量备份应用于物理备用数据库。主数据库上的数据文件的所有更改（从备用数据文件头中的SCN开始）都包含在增量备份中。
可以使用RECOVER ... FROM SERVICE命令将物理备用数据库上的数据文件与主数据库上的数据文件同步。此命令刷新备用数据文件并将它们前滚到与主数据库相同的时间点。但是，备用控制文件仍包含旧SCN值，这些值低于备用数据文件中的SCN值。因此，要完成物理备用数据库的同步，必须刷新备用控制文件，然后更新刷新的备用控制文件中的数据文件名，联机重做日志文件名和备用重做日志文件名。
1）确保满足以下先决条件：
在物理备用数据库和主数据库之间建立Oracle Net连接。
可以通过在物理备用数据库的tnsnames.ora文件中添加与主数据库相对应的条目来完成此操作。
主数据库和物理备用数据库上的密码文件是相同的。
主数据库和物理备用数据库的初始化参数文件中的COMPATIBLE参数设置为12.0。
2）启动RMAN并将其作为目标连接到物理备用数据库。建议连接到恢复目录。
以下命令作为TARGET连接到物理备用数据库，并作为CATALOG连接到恢复目录。使用已被授予SYSBACKUP权限的hr用户建立与物理备用数据库的连接。物理备用数据库的网络服务名称是pdbcndba_s，恢复目录的网络服务名称是catpdbcndba_s。
CONNECT TARGET "hr@pdbcndba_s AS SYSBACKUP";
CONNECT CATALOG rman@catpdbcndba_s;
3）使用带有FROM SERVICE子句的RECOVER STANDBY DATABASE命令前滚物理备用数据库。
FROM SERVICE子句指定必须使用物理备用数据库前滚的主数据库的服务名称。前滚操作后重新启动备用数据库。
以下示例使用服务名称为pdbcndba_p的主数据库前滚物理备用数据库。
RECOVER STANDBY DATABASE FROM SERVICE pdbcndba_p;
4）（仅适用于Active Data Guard）执行以下步骤以恢复重做数据并以只读模式打开物理备用数据库：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE UNTIL CONSISTENT;
ALTER DATABASE OPEN READ ONLY;
5）在物理备库启动MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

RECOVER命令前滚物理备库示例

1.场景

备库网络断开，主库操作并切换归档，不小心把最新的几个归档删除，备库网络修复之后发现数据不同步分别查看主备库日志
在备库网络断开之后主库操作

http://www.cndba.cn/leo1990/article/3173
http://www.cndba.cn/leo1990/article/3173
http://www.cndba.cn/leo1990/article/3173http://www.cndba.cn/leo1990/article/3173http://www.cndba.cn/leo1990/article/3173http://www.cndba.cn/leo1990/article/3173http://www.cndba.cn/leo1990/article/3173
http://www.cndba.cn/leo1990/article/3173
http://www.cndba.cn/leo1990/article/3173http://www.cndba.cn/leo1990/article/3173

SQL> conn hr/oracle@pdbcndba
Connected.
SQL> select * from test2;

 I
----------
 1

SQL> insert into test2 values(2);

1 row created.

SQL> insert into test2 values(3);

SQL> insert into test2 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test2;

 I
----------
 2
 3
 1
手动切换归档日志
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.
备库日志：
2018-07-26T08:56:51.223801+08:00
MRP0 (PID:1150): FAL: Failed to request gap sequence
MRP0 (PID:1150):  GAP - thread 1 sequence 356-358
MRP0 (PID:1150):  DBID 243175950 branch 980035223
MRP0 (PID:1150): FAL: All defined FAL servers have been attempted
MRP0 (PID:1150): -------------------------------------------------------------------------
MRP0 (PID:1150): Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
MRP0 (PID:1150): parameter is defined to a value that's sufficiently large
MRP0 (PID:1150): enough to maintain adequate log switch information to resolve
MRP0 (PID:1150): archived redo log gaps.
MRP0 (PID:1150): -------------------------------------------------------------------------
2018-07-26T08:57:04.663234+08:00
Errors in file /u01/app/oracle/diag/rdbms/pdbcndba_s/cndba/trace/cndba_rfs_3528.trc:
ORA-00315: 日志 5 (用于线程 0) 标头中的线程 # 1 错误
ORA-00312: 联机日志 5 线程 0: '/u01/app/oracle/oradata/cndba/stdredo02.log'
2018-07-26T08:57:04.663903+08:00
Errors in file /u01/app/oracle/diag/rdbms/pdbcndba_s/cndba/trace/cndba_rfs_3528.trc:
ORA-00315: 日志 7 (用于线程 0) 标头中的线程 # 1 错误
ORA-00312: 联机日志 7 线程 0: '/u01/app/oracle/oradata/cndba/stdredo04.log'
主库日志：
Errors in file /u01/app/oracle/diag/rdbms/pdbcndba_p/cndba/trace/cndba_fal_32513.trc:
ORA-00308: 无法打开归档日志 '/u01/archive/1_358_980035223.dbf'
ORA-27037: 无法获得文件状态
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-07-26T08:55:46.772778+08:00
Errors in file /u01/app/oracle/diag/rdbms/pdbcndba_p/cndba/trace/cndba_fal_32513.trc:
ORA-00308: 无法打开归档日志 '/u01/archive/1_357_980035223.dbf'
ORA-27037: 无法获得文件状态
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2018-07-26T08:55:46.772841+08:00
Errors in file /u01/app/oracle/diag/rdbms/pdbcndba_p/cndba/trace/cndba_fal_32513.trc:
ORA-00308: 无法打开归档日志 '/u01/archive/1_356_980035223.dbf'
ORA-27037: 无法获得文件状态
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

2.查找主库中是否存在产生GAP的归档文件

备库查看gap
SQL>  SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
 1     356    358
主库查看丢失的归档
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 356 AND 358;

NAME
--------------------------------------------------------------------------------
/u01/archive/1_356_980035223.dbf
/u01/archive/1_357_980035223.dbf
/u01/archive/1_358_980035223.dbf
可以看到主库的归档被意外删除，无法直接恢复。

3.使用RECOVER STANDBY DATABASE增量刷新备库

关闭备库MRP
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
将备库启动到mount状态
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1048575224 bytes
Fixed Size    8903928 bytes
Variable Size  377487360 bytes
Database Buffers  658505728 bytes
Redo Buffers    3678208 bytes
Database mounted.
执行增量刷新备库命令
RMAN> recover database from service pdbcndba_p noredo;

Starting recover at 2018:07:2609:05:24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=56 device type=DISK
datafile 17 not processed because file is read-only
skipping datafile 2; already restored to SCN 2425590
skipping datafile 4; already restored to SCN 2425590
skipping datafile 7; already restored to SCN 2425590
skipping datafile 8; already restored to SCN 2425590
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdbcndba_p
destination for restore of datafile 00001: /u01/app/oracle/oradata/cndba/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdbcndba_p
destination for restore of datafile 00003: /u01/app/oracle/oradata/cndba/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdbcndba_p
destination for restore of datafile 00005: /u01/app/oracle/oradata/cndba/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdbcndba_p
destination for restore of datafile 00006: /u01/app/oracle/oradata/cndba/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdbcndba_p
destination for restore of datafile 00062: /u01/app/oracle/oradata/pdbcndba/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdbcndba_p
destination for restore of datafile 00063: /u01/app/oracle/oradata/pdbcndba/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdbcndba_p
destination for restore of datafile 00064: /u01/app/oracle/oradata/pdbcndba/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdbcndba_p
destination for restore of datafile 00065: /u01/app/oracle/oradata/pdbcndba/usertbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdbcndba_p
destination for restore of datafile 00066: /u01/app/oracle/oradata/pdbcndba/undo01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished recover at 2018:07:2609:06:20

4.RMAN通过网络恢复备库的控制文件

将数据库启动到nomount状态
RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area    1048575224 bytes

Fixed Size                     8903928 bytes
Variable Size                377487360 bytes
Database Buffers             658505728 bytes
Redo Buffers                   3678208 bytes
恢复控制文件
RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE pdbcndba_p;

Starting restore at 2018:07:2609:08:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service pdbcndba_p
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output file name=/u01/app/oracle/oradata/cndba/control01.ctl
output file name=/u01/app/oracle/oradata/cndba/control02.ctl
Finished restore at 2018:07:2609:08:54

5.验证

启动备库并启动MRP
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.
SQL> alter pluggable database pdbcndba open;

Pluggable database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

查看归档GAP
SQL>  SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected
查看日志应用
主库查看
SQL> select sequence,applied from (select sequence# sequence,applied from v$archived_log order by sequence# desc) where rownum <=10;

  SEQUENCE APPLIED
---------- ---------------------------
       364 NO
       364 NO
       363 NO
       363 YES
       362 NO
       362 YES
       361 NO
       360 NO
       360 YES
       359 NO
备库查看
SQL> select sequence,applied from (select sequence# sequence,applied from v$archived_log order by sequence# desc) where rownum <=10;

  SEQUENCE APPLIED
---------- ---------------------------
       364 NO
       363 YES
主库创建测试数据
SQL> conn hr/oracle@pdbcndba
Connected.
SQL> insert into test2 values(4);

1 row created.

SQL> commit;

Commit complete.
备库查看
SQL> conn hr/oracle@pdbcndba
Connected.
SQL> select * from test2;

 I
----------
 4
 2
 3
 1

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
leo

leo

关注

坚持你的坚持

  • 202
    原创
  • 0
    翻译
  • 39
    转载
  • 16
    评论
  • 访问：674835次
  • 积分：1270
  • 等级：核心会员
  • 排名：第8名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ