Oracle数据库RMAN异机恢复一例

网友投稿 1069 2022-05-29

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

Oracle数据库RMAN异机恢复一例

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. 开始恢复

# 恢复参数文件

rman target /

RMAN> startup nomount;

RMAN> restore spfile from '/dbbackup/ltdb/oadb/incr_OADB_20201201_3592';

RMAN> shutdown immediate;

# 恢复控制文件

##准备恢复

RMAN> startup nomount;

RMAN> restore controlfile from '/dbbackup/ltdb/oadb/incr_OADB_20201201_3592';

RMAN> alter database mount;

# 恢复数据文件,约需要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

# 数据库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;

至此,数据库恢复完成。

Oracle 数据库

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:[论文阅读] (08) NDSS2020 UNICORN: Runtime Provenance-Based Detector
下一篇:使用capability加强container的安全
相关文章