- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

Oracle数据库RMAN异机恢复一例

寒六先生发表于 2021/02/03 17:22:31 2021/02/03

4k+ 0 0

【摘要】 0. 前言：# 前端业务人员误操作，把我们ERP里面的部分数据删除了，幸好之前RMAN备份比较完备，直接另起炉灶，进行异机恢复吧；# 在华为云ECS上搭建的Oracle环境，RMAN备份放在SFS存储内；# 恢复时间目标： 2020-11-30 01:00:001. 确认恢复点# 恢复之前，需要查看源数据库的RMAN备份日志，确认需要恢复的时间点# 查看源库，以确认需要恢复的spfile的位...

0. 前言：

# 前端业务人员误操作，把我们ERP里面的部分数据删除了，幸好之前RMAN备份比较完备，直接另起炉灶，进行异机恢复吧；
# 在华为云ECS上搭建的Oracle环境，RMAN备份放在SFS存储内；
# 恢复时间目标： 2020-11-30 01:00:00

1. 确认恢复点

# 恢复之前，需要查看源数据库的RMAN备份日志，确认需要恢复的时间点
# 查看源库，以确认需要恢复的spfile的位置：
RMAN> list backup;
......
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3585    Incr 0  3.43G      DISK        00:05:27     29-NOV-20      
        BP Key: 3585   Status: AVAILABLE  Compressed: YES  Tag: OADB_INCR_L0
        Piece Name: /dbbackup/ltdb/oadb/full_OADB_20201129_3585
  List of Datafiles in backup set 3585
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 978758364  29-NOV-20 /home/oracle/oadb/system01.dbf
  2    0  Incr 978758364  29-NOV-20 /home/oracle/oadb/sysaux01.dbf
  3    0  Incr 978758364  29-NOV-20 /home/oracle/oadb/undotbs01.dbf
  4    0  Incr 978758364  29-NOV-20 /home/oracle/oadb/users01.dbf
  5    0  Incr 978758364  29-NOV-20 /home/oracle/oadb/TABLE_SPACE

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3586    Incr 0  1.09M      DISK        00:00:02     29-NOV-20      
        BP Key: 3586   Status: AVAILABLE  Compressed: YES  Tag: OADB_INCR_L0
        Piece Name: /dbbackup/ltdb/oadb/full_OADB_20201129_3586
  SPFILE Included: Modification time: 26-NOV-20
  SPFILE db_unique_name: OADB
  Control File Included: Ckp SCN: 978759124    Ckp time: 29-NOV-20

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3587    77.96M     DISK        00:00:06     29-NOV-20      
        BP Key: 3587   Status: AVAILABLE  Compressed: YES  Tag: TAG20201129T030542
        Piece Name: /dbbackup/ltdb/oadb/arclog_0_3587_1_1057719942_OADB

  List of Archived Logs in backup set 3587
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    7028    978623087  28-NOV-20 978669945  28-NOV-20
  1    7029    978669945  28-NOV-20 978732444  28-NOV-20
  1    7030    978732444  28-NOV-20 978759135  29-NOV-20

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3588    Incr 1  43.51M     DISK        00:02:05     30-NOV-20      
        BP Key: 3588   Status: AVAILABLE  Compressed: YES  Tag: OADB_INCR_L1
        Piece Name: /dbbackup/ltdb/oadb/incr_OADB_20201130_3588
  List of Datafiles in backup set 3588
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 978892860  30-NOV-20 /home/oracle/oadb/system01.dbf
  2    1  Incr 978892860  30-NOV-20 /home/oracle/oadb/sysaux01.dbf
  3    1  Incr 978892860  30-NOV-20 /home/oracle/oadb/undotbs01.dbf
  4    1  Incr 978892860  30-NOV-20 /home/oracle/oadb/users01.dbf
  5    1  Incr 978892860  30-NOV-20 /home/oracle/oadb/TABLE_SPACE

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3589    Incr 1  1.09M      DISK        00:00:01     30-NOV-20      
        BP Key: 3589   Status: AVAILABLE  Compressed: YES  Tag: OADB_INCR_L1
        Piece Name: /dbbackup/ltdb/oadb/incr_OADB_20201130_3589
  SPFILE Included: Modification time: 26-NOV-20
  SPFILE db_unique_name: OADB
  Control File Included: Ckp SCN: 978893333    Ckp time: 30-NOV-20

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3590    78.09M     DISK        00:00:06     30-NOV-20      
        BP Key: 3590   Status: AVAILABLE  Compressed: YES  Tag: TAG20201130T030211
        Piece Name: /dbbackup/ltdb/oadb/arclog_1_3590_1_1057806131_OADB

  List of Archived Logs in backup set 3590
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    7031    978759135  29-NOV-20 978802271  29-NOV-20
  1    7032    978802271  29-NOV-20 978865784  29-NOV-20
  1    7033    978865784  29-NOV-20 978893344  30-NOV-20

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3591    Incr 1  102.13M    DISK        00:01:59     01-DEC-20      
        BP Key: 3591   Status: AVAILABLE  Compressed: YES  Tag: OADB_INCR_L1
        Piece Name: /dbbackup/ltdb/oadb/incr_OADB_20201201_3591
  List of Datafiles in backup set 3591
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 979213617  01-DEC-20 /home/oracle/oadb/system01.dbf
  2    1  Incr 979213617  01-DEC-20 /home/oracle/oadb/sysaux01.dbf
  3    1  Incr 979213617  01-DEC-20 /home/oracle/oadb/undotbs01.dbf
  4    1  Incr 979213617  01-DEC-20 /home/oracle/oadb/users01.dbf
  5    1  Incr 979213617  01-DEC-20 /home/oracle/oadb/TABLE_SPACE

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3592    Incr 1  1.09M      DISK        00:00:01     01-DEC-20      
        BP Key: 3592   Status: AVAILABLE  Compressed: YES  Tag: OADB_INCR_L1
        Piece Name: /dbbackup/ltdb/oadb/incr_OADB_20201201_3592
  SPFILE Included: Modification time: 26-NOV-20
  SPFILE db_unique_name: OADB
  Control File Included: Ckp SCN: 979214188    Ckp time: 01-DEC-20

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3593    118.16M    DISK        00:00:10     01-DEC-20      
        BP Key: 3593   Status: AVAILABLE  Compressed: YES  Tag: TAG20201201T030211
        Piece Name: /dbbackup/ltdb/oadb/arclog_1_3593_1_1057978931_OADB

  List of Archived Logs in backup set 3593
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    7034    978893344  30-NOV-20 979002365  30-NOV-20
  1    7035    979002365  30-NOV-20 979123966  30-NOV-20
  1    7036    979123966  30-NOV-20 979172358  30-NOV-20
  1    7037    979172358  30-NOV-20 979186182  30-NOV-20
  1    7038    979186182  30-NOV-20 979214198  01-DEC-20
......

#注意一定要选择要恢复的时间点前的第一个备份，否则会报错：
Starting recover at 05-NOV-20
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/05/2020 21:04:41
RMAN-06555: datafile 1 must be restored from backup created before 31-OCT-20

2. 基础环境准备

# 准备rman备份集，将备份集复制到目标数据库环境；
# 为懒省事儿，在目标主机创建位置相同的路径
mkdir /dbbackup
mount -t nfs -o vers=3,timeo=600,nolock sfs-nas3.cn-east-2.myhuaweicloud.com:/share-****** /nfs
cp -r /nfs/ly /dbbackup/
chown -R oracle.dba /dbbackup/

# 在新环境里，准备数据库所需要的目录
[root@ecs-ae88 ~]# mkdir -p /home/oracle/oadb
[root@ecs-ae88 ~]# chown -R oracle.dba /home/oracle/oadb/
[root@ecs-ae88 ~]# mkdir -p /home/oracle11g/app/archive/oadb
[root@ecs-ae88 ~]# mkdir -p /home/oracle11g/app/fast_recovery_area/oadb/
[root@ecs-ae88 ~]# mkdir -p /home/oracle11g/app/admin/oadb/adump/
[root@ecs-ae88 ~]# chown -R oracle.dba /home/oracle11g

[oracle@ly-eims-db-01 app]$ cd /home/oracle11g/app/
[oracle@ly-eims-db-01 app]$ scp -r admin 192.168.50.218:$PWD/

3. 开始恢复

3.1. 恢复参数文件

# 恢复参数文件
rman target /
RMAN> startup nomount;
RMAN> restore spfile from '/dbbackup/ltdb/oadb/incr_OADB_20201201_3592';
RMAN> shutdown immediate;

3.2. 恢复控制文件

# 恢复控制文件
##准备恢复
RMAN> startup nomount;
RMAN> restore controlfile from '/dbbackup/ltdb/oadb/incr_OADB_20201201_3592';
RMAN> alter database mount;

3.3. 恢复数据文件

# 恢复数据文件，约需要10分钟
RMAN> restore database;

Starting restore at 05-NOV-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3771 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 /home/oracle/oadb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oadb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/oadb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oadb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oadb/TABLE_SPACE
channel ORA_DISK_1: reading from backup piece /dbbackup/ly/oadb/full_OADB_20201101_3177
channel ORA_DISK_1: piece handle=/dbbackup/ly/oadb/full_OADB_20201101_3177 tag=OADB_INCR_L0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:55
Finished restore at 05-NOV-20

3.4. 数据库recover

# 数据库recover
run{
allocate channel t1 device type disk;
set until time "to_date('2020-11-30 01:00:00','yyyy-mm-dd hh24:mi:ss')";
recover database;
}

#报错：
starting media recovery
unable to find archived log
archived log thread=1 sequence=6163
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/05/2020 21:16:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6163 and starting SCN of 896957574

#因为恢复的控制文件时间较早，所以部分需要的归档，不在list backup内：
#查看源库，发现上面提示的6163号归档日志，其对应的备份文件是在磁盘上存在的；
#所以进行手动编目：
catalog start with '/dbbackup/ly/oadb/';
catalog start with '/dbbackup/ltdb/oadb/';

# 再次执行recover
......
Finished recover at 05-NOV-20
released channel: t1

4. 启动数据库

# 启动数据库，完成恢复：
alter database open resetlogs;

至此，数据库恢复完成。

【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com

点赞
收藏
关注作者

作者其他文章

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

开发者空间

了解空间

工作台

开发工具

实战案例

空间活动

空间论坛

开发平台

软件开发生产线 CodeArts

AI平台ModelArts

数据治理中心 DataArts Studio

数字内容生产线 MetaStudio

精选服务

云数据库 GaussDB

云数据库 RDS for MySQL

MapReduce服务 MRS

数据仓库服务 DWS

分布式缓存服务Redis版

分布式消息服务 DMS

华为云实时音视频 SparkRTC

媒体处理 MPC

主机迁移服务 SMS

对象存储迁移服务 OMS

云消息服务 KooMessage

云手机服务 KooPhone

企业搜索服务 KooSearch

云地图服务 KooMap

更多开放能力

开发工具

API生态

CodeArts API

API Explorer

Terraform Explorer

KooCLI

API 中心

SDK 中心

开发服务

Codelabs

DevStar

低代码平台Astro

CodeArts IDE

Huawei Cloud Toolkit

Classroom

开发资源

开源镜像站

开源资源

开发语言

开发实践

入门精选

分发资源

企业应用中心

企业通用专区

教育专区

华为应用专区

政务云专区

硬件云服务商城

医疗健康专区

跳蚤市场

华为云开发者日

直播专区

开发者精品活动

DTSE Tech Talk

加入HCDE

加入HCDG

加入HCSD

加入HCWD

鲁班会

沃土云创计划·企业

沃土云创计划·高校

沃土云创计划·个人

沃土云创计划·开源共创

博客

论坛

专题

开发者榜单

学习路径

在线课程

动手实验

考试认证

培训服务