Oracle數據庫RMAN異機恢復一例

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. 開始恢復

# 恢復參數文件

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小時內刪除侵權內容。