Oracle数据库RMAN异机恢复一例

举报
寒六先生 发表于 2021/02/03 17:22:31 2021/02/03
【摘要】 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
Oracle 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

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

全部回复

上滑加载中

设置昵称

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

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

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

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入