恢復archivelog模式下丟失的系統資料檔案

在歸檔模式下丟失system表空間的資料檔案，該如何恢復？以下是恢復的簡單的演示：

----歸檔模式下丟失關鍵資料檔案：

---檢視資料庫的歸檔模式：

sys@PROD>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

 

---備份系統表空間：

[oracle@enmo ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 11:38:26 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> quit

Recovery Manager complete.

[oracle@enmo ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 22 11:40:27 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=338469376)

RMAN>

RMAN> backup tablespace system;

 

Starting backup at 22-NOV-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=26 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/PROD/system01.dbf

channel ORA_DISK_1: starting piece 1 at 22-NOV-16

channel ORA_DISK_1: finished piece 1 at 22-NOV-16

piece handle=/u01/app/backup/db_0nrli388_1_1.rmn tag=TAG20161122T114200 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:47

Finished backup at 22-NOV-16

 

Starting Control File and SPFILE Autobackup at 22-NOV-16

piece handle=/u01/app/FRA/PROD/autobackup/2016_11_22/o1_mf_s_928583027_d37hqom2_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 22-NOV-16

RMAN>

 

---刪除系統資料檔案：

sys@PROD>!rm /u01/app/oracle/oradata/PROD/system01.dbf

sys@PROD>!ls /u01/app/oracle/oradata/PROD/system01.dbf

ls: /u01/app/oracle/oradata/PROD/system01.dbf: No such file or directory

#已經刪除。

 

---關庫並嘗試重啟資料庫：

sys@PROD>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@PROD>

sys@PROD>startup

ORACLE instance started.

 

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             507513936 bytes

Database Buffers          322961408 bytes

Redo Buffers                2371584 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/system01.dbf'

#丟失了系統資料檔案。

---透過rman嘗試過載並恢復並開啟資料庫：
--過載系統關鍵資料檔案：

RMAN> restore datafile 1;  

 

Starting restore at 22-NOV-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=17 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 /u01/app/oracle/oradata/PROD/system01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/backup/db_0nrli388_1_1.rmn

channel ORA_DISK_1: piece handle=/u01/app/backup/db_0nrli388_1_1.rmn tag=TAG20161122T114200

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:25

Finished restore at 22-NOV-16

 
---恢復資料庫：

RMAN> recover database;

Starting recover at 22-NOV-16

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 22-NOV-16

 

--alert日誌的記錄：

Full restore complete of datafile 1 /u01/app/oracle/oradata/PROD/system01.dbf.  Elapsed time: 0:01:11

  checkpoint is 2137364

  last deallocation scn is 1896097

  Undo Optimization current scn is 2121545

Tue Nov 22 11:57:49 2016

alter database recover datafile list clear

Completed: alter database recover datafile list clear

alter database recover datafile list

 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8

Completed: alter database recover datafile list

 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8

alter database recover if needed

 start

Media Recovery Start

Serial Media Recovery started

Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/PROD/redo01.log

  Mem# 1: /u01/app/oracle/oradata/PROD/redo01b.log

Media Recovery Complete (PROD)

Completed: alter database recover if needed

 Start

 

---嘗試開啟資料庫：

RMAN> alter database open;

database opened
#已開啟。

sys@PROD>select status from v$instance;

STATUS

------------

OPEN


--歸檔模式下恢復資料庫，只需有有效的備份，再加上
重做日誌檔案的記錄，就可以完全恢復資料庫。