Oracle数据库RMAN异机恢复一例-伙伴云

Oracle数据库RMAN异机恢复一例

网友投稿 1098 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

List of Archived Logs in backup set 3593

Oracle数据库RMAN异机恢复一例

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

表格中升序或降序之后怎么取消排序？（表格中的升序降序怎么消除）

1098 2022-05-29

Oracle数据库RMAN异机 恢复 一例

表格中升序或降序之后怎么取消排序？（表格中的升序降序怎么消除）

这种情况如何恢复表格背景白色空格（表格怎么变成白色空白）

这种情况如何恢复表格背景白色空格（表格里面背景怎么设置成空白）

推荐文章

企业生产管理是什么，企业生产管理软件

进盘点进销存软件排行榜前十名

进销存系统哪个简单好用？进销存系统优点

工厂生产管理（工厂生产管理流程及制度）

生产管理软件，机械制造业生产管理，制造业生产过程管理软件

进销存软件和ERP有什么区别？进销存与erp软件理解

进销存如何进行库存管理

如何利用excel制作销售订单管理系统？

数据库订单管理系统有哪些功能？数据库订单管理系统怎么设计？

什么是数据库管理系统？

最近发表

热评文章

零代码开发是什么？2022低代码平台排行榜

智能进销存库存管理系统（智慧进销存）

在线文档哪家强？8款在线文档编辑软件推荐

WPS2016怎么绘制简单的价格表?

什么是在线文档？怎么发在线文档

用在线电子表格，居家办公更轻松

友情链接