課程目標：

對關鍵或者非關鍵資料檔案丟失進行恢復
使用增量更新備份進行恢復
切換到映象複製進行快速恢復
還原資料庫到異機
使用備份的控制檔案進行恢復

1、使用RMAN還原和恢復命令

恢復資料庫一般包括2個階段：

還原（RESTORE）：從備份獲取資料檔案。語法：RESTORE {DATABASE | TABLESPACE name [,name]... | DATAFILE name [,name] }...
恢復（RECOVER）：應用增量備份和重做日誌中的改變。語法：RECOVER {DATABASE | TABLESPACE name [,name]... | DATAFILE name [,name] }...

也可以使用EM中的恢復嚮導進行恢復。

2、執行完全恢復：歸檔模式下丟失非關鍵資料檔案

在歸檔模式下，丟失的資料檔案如果不屬於SYSTEM或者UNDO表空間，則只會影響丟失檔案中的物件。歸檔模式下可以恢復都最後一次提交的時間，資料不會丟失，不需要重新錄入資料。

例子：使用RMAN進行歸檔模式下丟失非關鍵資料檔案的恢復

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !rm /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c9kcl36t_.dbf

SQL> 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 4 - see DBWR trace file

ORA-01110: data file 4:

'/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c9kcl36t_.dbf'

[ ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 15 17:50:53 2016

connected to target database: STONE (DBID=3001485737, not open)

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

3282 HIGH OPEN 15-JAN-16 One or more non-system datafiles are missing

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

3282 HIGH OPEN 15-JAN-16 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c9kcl36t_.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

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

1 Restore and recover datafile 4

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2813147558.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2813147558.hm

contents of repair script:

# restore and recover datafile

restore datafile 4;

recover datafile 4;

sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 15-JAN-16

using channel ORA_DISK_1

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 00004 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c9kcl36t_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T161747_c9kbsg13_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T161747_c9kbsg13_.bkp tag=TAG20160115T161747

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 15-JAN-16

Starting recover at 15-JAN-16

using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=2

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T162003_c9kbxmkf_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T162003_c9kbxmkf_.bkp tag=TAG20160115T162003

channel ORA_DISK_1: restored backup piece 1

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

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_15/o1_mf_1_2_c9kj97ol_.arc thread=1 sequence=2

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_15/o1_mf_1_2_c9kj97ol_.arc RECID=170 STAMP=901216295

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

Finished recover at 15-JAN-16

sql statement: alter database datafile 4 online

repair failure complete

Do you want to open the database (enter YES or NO)? yes

database opened

3、執行完全恢復：歸檔模式下丟失關鍵資料檔案

SYSTEM和UNDO表空間的資料檔案為關鍵檔案，如果使用EM進行恢復，那麼SYSAUX的資料檔案也是關鍵檔案。關鍵資料檔案需要在MOUNT狀態下進行恢復。

例子：使用RMAN進行歸檔模式下丟失關鍵資料檔案的恢復

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !rm /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kcl35t_.dbf

SQL> 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/STONE/datafile/o1_mf_system_c9kcl35t_.dbf'

[ ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 15 18:00:48 2016

connected to target database: STONE (DBID=3001485737, not open)

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

4328 CRITICAL OPEN 15-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kcl35t_.dbf' is missing

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

4328 CRITICAL OPEN 15-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kcl35t_.dbf' is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kcl35t_.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

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

1 Restore and recover datafile 1

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3894925211.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3894925211.hm

contents of repair script:

# restore and recover datafile

restore datafile 1;

recover datafile 1;

sql 'alter database datafile 1 online';

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 15-JAN-16

using channel ORA_DISK_1

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/STONE/datafile/o1_mf_system_c9kcl35t_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T161747_c9kbsg13_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T161747_c9kbsg13_.bkp tag=TAG20160115T161747

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:55

Finished restore at 15-JAN-16

Starting recover at 15-JAN-16

using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=2

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T162003_c9kbxmkf_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T162003_c9kbxmkf_.bkp tag=TAG20160115T162003

channel ORA_DISK_1: restored backup piece 1

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

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_15/o1_mf_1_2_c9kjx4gm_.arc thread=1 sequence=2

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_15/o1_mf_1_2_c9kjx4gm_.arc RECID=171 STAMP=901216932

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

Finished recover at 15-JAN-16

sql statement: alter database datafile 1 online

repair failure complete

Do you want to open the database (enter YES or NO)? yes

database opened

4、恢復映象複製

可以使用RMAN對資料檔案映象複製應用增量備份，將映象複製前滾到指定的時間點或者增量備份的SCN，這樣就不需要每天對資料庫進行完整映象複製。對資料檔案映象複製應用增量備份還有以下好處：

減少介質恢復的時間，只需要應用最後增量備份之後的歸檔日誌。
不需要在增量還原之後進行完整映象複製。

5、恢復映象複製：示例

透過執行上圖中的命令，可以獲得所有資料檔案連續更新的映象複製。

每天執行的情況如下：

第一天：RECOVER命令什麼也不做，沒有映象複製需要恢復，BACKUP命令建立映象複製。

第二天：RECOVER命令什麼也不做，這是因為此時還沒有增量備份。BACKUP命令建立增量備份。

第三天：RECOVER命令從增量備份應用改變到映象複製。BACKUP命令建立另外的增量備份，用於第四天RECOVER映象複製。

在執行這種備份策略時使用tag是非常重要的，用於連線增量備份和映象複製。如果不使用tag，有可能會將不正確的增量備份應用到映象複製。

例子：恢復映象複製

RMAN> recover copy of database with tag '20160118';

Starting recover at 18-JAN-16

using channel ORA_DISK_1

no copy of datafile 1 found to recover

no copy of datafile 2 found to recover

no copy of datafile 3 found to recover

no copy of datafile 4 found to recover

no copy of datafile 5 found to recover

no copy of datafile 6 found to recover

no copy of datafile 7 found to recover

Finished recover at 18-JAN-16

RMAN> backup incremental level 1 for recover of copy with tag '20160118' database;

Starting backup at 18-JAN-16

using channel ORA_DISK_1

no parent backup or copy of datafile 1 found

no parent backup or copy of datafile 2 found

no parent backup or copy of datafile 5 found

no parent backup or copy of datafile 3 found

no parent backup or copy of datafile 7 found

no parent backup or copy of datafile 6 found

no parent backup or copy of datafile 4 found

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

output file name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_system_c9snq72l_.dbf tag=20160118 RECID=47 STAMP=901483036

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

output file name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_sysaux_c9snryc5_.dbf tag=20160118 RECID=48 STAMP=901483079

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

output file name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_example_c9sntcfx_.dbf tag=20160118 RECID=49 STAMP=901483095

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

output file name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_undotbs1_c9snttol_.dbf tag=20160118 RECID=50 STAMP=901483101

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

output file name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_rcat_ts_c9sntxsh_.dbf tag=20160118 RECID=51 STAMP=901483102

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

output file name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_test_ind_c9sntyx9_.dbf tag=20160118 RECID=52 STAMP=901483103

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c9kj96dg_.dbf

output file name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_users_c9snv01x_.dbf tag=20160118 RECID=53 STAMP=901483104

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 18-JAN-16

Starting Control File and SPFILE Autobackup at 18-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_18/o1_mf_s_901483105_c9snv1jt_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 18-JAN-16

RMAN> recover copy of database with tag '20160118';

Starting recover at 18-JAN-16

using channel ORA_DISK_1

no copy of datafile 1 found to recover

no copy of datafile 2 found to recover

no copy of datafile 3 found to recover

no copy of datafile 4 found to recover

no copy of datafile 5 found to recover

no copy of datafile 6 found to recover

no copy of datafile 7 found to recover

Finished recover at 18-JAN-16

RMAN> backup incremental level 1 for recover of copy with tag '20160118' database;

Starting backup at 18-JAN-16

using channel ORA_DISK_1

no parent backup or copy of datafile 5 found

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c9kj96dg_.dbf

channel ORA_DISK_1: starting piece 1 at 18-JAN-16

channel ORA_DISK_1: finished piece 1 at 18-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnnd1_20160118_c9so3rom_.bkp tag=20160118 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

output file name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_example_c9so3vtm_.dbf tag=20160118 RECID=54 STAMP=901483399

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 18-JAN-16

Starting Control File and SPFILE Autobackup at 18-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_18/o1_mf_s_901483402_c9so4c76_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 18-JAN-16

RMAN> recover copy of database with tag '20160118';

Starting recover at 18-JAN-16

using channel ORA_DISK_1

no copy of datafile 5 found to recover

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile copies to recover

recovering datafile copy file number=00001 name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_system_c9snq72l_.dbf

recovering datafile copy file number=00002 name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_sysaux_c9snryc5_.dbf

recovering datafile copy file number=00003 name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_undotbs1_c9snttol_.dbf

recovering datafile copy file number=00004 name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_users_c9snv01x_.dbf

recovering datafile copy file number=00006 name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_test_ind_c9sntyx9_.dbf

recovering datafile copy file number=00007 name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_rcat_ts_c9sntxsh_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnnd1_20160118_c9so3rom_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnnd1_20160118_c9so3rom_.bkp tag=20160118

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished recover at 18-JAN-16

Starting Control File and SPFILE Autobackup at 18-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_18/o1_mf_s_901483426_c9so52by_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 18-JAN-16

RMAN> backup incremental level 1 for recover of copy with tag '20160118' database;

Starting backup at 18-JAN-16

using channel ORA_DISK_1

no parent backup or copy of datafile 5 found

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c9kj96dg_.dbf

channel ORA_DISK_1: starting piece 1 at 18-JAN-16

channel ORA_DISK_1: finished piece 1 at 18-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnnd1_20160118_c9so6fmj_.bkp tag=20160118 comment=NONE

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

output file name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_example_c9so6gnj_.dbf tag=20160118 RECID=61 STAMP=901483485

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 18-JAN-16

Starting Control File and SPFILE Autobackup at 18-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_18/o1_mf_s_901483485_c9so6xxt_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 18-JAN-16

6、快速切換到映象複製

可以使用映象複製進行資料檔案的快速恢復，步驟如下：

（1）將資料檔案offline。

（2）使用SWITCH TO ... COPY命令指向資料檔案的映象複製。

（3）recover資料檔案。

（4）將資料檔案online

如果還想將資料檔案放回原來的位置，執行以下步驟：

（5）使用BACKUP AS COPY命令在原來的位置建立該資料檔案的映象複製。

（6）將資料檔案offline。

（7）使用SWITCH TO COPY命令切換到步驟5建立的映象複製。

（8）recover資料檔案。

（9）將資料檔案online。

可以使用這個命令來恢復資料檔案，表空間，臨時檔案或者整個資料庫。

例子：使用映象複製進行資料檔案的快速恢復

使用前面的備份

RMAN> list copy;

specification does not match any control file copy in the repository

specification does not match any archived log in the repository

List of Datafile Copies

=======================

Key File S Completion Time Ckp SCN Ckp Time

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

58 1 A 18-JAN-16 3901980 18-JAN-16

Name: /u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_system_c9snq72l_.dbf

Tag: 20160118

60 2 A 18-JAN-16 3901980 18-JAN-16

Name: /u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_sysaux_c9snryc5_.dbf

Tag: 20160118

59 3 A 18-JAN-16 3901980 18-JAN-16

Name: /u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_undotbs1_c9snttol_.dbf

Tag: 20160118

57 4 A 18-JAN-16 3901980 18-JAN-16

Name: /u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_users_c9snv01x_.dbf

Tag: 20160118

61 5 A 18-JAN-16 3618687 12-JAN-16

Name: /u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_example_c9so6gnj_.dbf

Tag: 20160118

54 5 A 18-JAN-16 3618687 12-JAN-16

Name: /u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_example_c9so3vtm_.dbf

Tag: 20160118

49 5 A 18-JAN-16 3618687 12-JAN-16

Name: /u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_example_c9sntcfx_.dbf

Tag: 20160118

56 6 A 18-JAN-16 3901980 18-JAN-16

Name: /u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_test_ind_c9sntyx9_.dbf

Tag: 20160118

55 7 A 18-JAN-16 3901980 18-JAN-16

Name: /u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_rcat_ts_c9sntxsh_.dbf

Tag: 20160118

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

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

USERS /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c9kj96dg_.dbf

UNDOTBS1 /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

SYSAUX /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

SYSTEM /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

TEST_INDEX /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

EXAMPLE /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

RCAT_TS /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

7 rows selected.

刪除資料檔案

SQL> !rm /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c9kj96dg_.dbf

離線資料檔案

SQL> alter database datafile 4 offline;

Database altered.

RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy "/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_users_c9snv01x_.dbf"

RMAN> recover datafile 4;

Starting recover at 18-JAN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=41 device type=DISK

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00004: /u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_users_c9snv01x_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnnd1_20160118_c9so6fmj_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnnd1_20160118_c9so6fmj_.bkp tag=20160118

channel ORA_DISK_1: restored backup piece 1

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

starting media recovery

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

Finished recover at 18-JAN-16

SQL> alter database datafile 4 online;

Database altered.

RMAN> backup as copy datafile 4 format '/u01/app/oracle/oradata/STONE/datafile/%U.dbf';

Starting backup at 18-JAN-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/fast_recovery_area/STONE/datafile/o1_mf_users_c9snv01x_.dbf

output file name=/u01/app/oracle/oradata/STONE/datafile/data_D-STONE_I-3001485737_TS-USERS_FNO-4_6hqrnagg.dbf tag=TAG20160118T221336 RECID=63 STAMP=901491216

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 18-JAN-16

Starting Control File and SPFILE Autobackup at 18-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_18/o1_mf_s_901491217_c9swrkdw_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 18-JAN-16

RMAN> sql 'alter database datafile 4 offline';

sql statement: alter database datafile 4 offline

RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy "/u01/app/oracle/oradata/STONE/datafile/data_D-STONE_I-3001485737_TS-USERS_FNO-4_6hqrnagg.dbf"

RMAN> recover datafile 4;

Starting recover at 18-JAN-16

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 18-JAN-16

RMAN> sql 'alter database datafile 4 online';

sql statement: alter database datafile 4 online

7、為切換的檔案使用SET NEWNAME

SET NEWNAME命令只能用於RUN塊中，對後續的操作進行名稱對映。上面的例子中，SET NEWNAME命令定義了對資料檔案進行還原操作的位置。當執行RESTORE命令，users01.dbf資料檔案還原到/disk2/users01.dbf。但是控制檔案還沒有指向該位置，故需要使用SWITCH命令使控制檔案更新指向。

更有效率的方式是使用SET NEWNAME為所有的資料檔案指定預設的命名格式，命令如下：

（1）SET NEWNAME FOR DATAFILE和SET NEWNAME FOR TEMPFILE

（2）SET NEWNAME FOR TABLESPACE

（3）SET NEWNAME FOR DATABASE

例子：使用複製的資料檔案進行恢復

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

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

USERS /home/oracle/users01.dbf

UNDOTBS1 /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

SYSAUX /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

SYSTEM /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

TEST_INDEX /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

EXAMPLE /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

RCAT_TS /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

7 rows selected.

SQL> !cp /home/oracle/users01.dbf /u01/app/oracle/oradata/STONE/datafile/user01.dbf

RMAN> run{

2> allocate channel dev1 device type disk;

3> sql 'alter tablespace users offline immediate';

4> set newname for datafile '/home/oracle/users01.dbf' to '/u01/app/oracle/oradata/STONE/datafile/user01.dbf';

5> restore tablespace users;

6> switch datafile all;

7> recover tablespace users;

8> sql 'alter tablespace users online';}

released channel: ORA_DISK_1

allocated channel: dev1

channel dev1: SID=18 device type=DISK

sql statement: alter tablespace users offline immediate

executing command: SET NEWNAME

Starting restore at 18-JAN-16

skipping datafile 4; already restored to file /u01/app/oracle/oradata/STONE/datafile/user01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 18-JAN-16

datafile 4 switched to datafile copy

input datafile copy RECID=76 STAMP=901492898 file name=/u01/app/oracle/oradata/STONE/datafile/user01.dbf

Starting recover at 18-JAN-16

starting media recovery

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

Finished recover at 18-JAN-16

sql statement: alter tablespace users online

released channel: dev1

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

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

USERS /u01/app/oracle/oradata/STONE/datafile/user01.dbf

UNDOTBS1 /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

SYSAUX /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

SYSTEM /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

TEST_INDEX /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

EXAMPLE /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

RCAT_TS /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

7 rows selected.

8、SET NEWNAME的替換變數

在SET NEWNAME命令中使用替換變數可以避免還原到其他地方發生名稱衝突，還可以為表空間的所有資料檔名指定替換變數減少指令碼輸入。至少指定%b，%f和%U中的一個，%I和%N為可選變數。

9、非歸檔模式下執行還原和恢復

非歸檔模式下資料檔案的丟失需要進行資料庫的完全還原，包括控制檔案和資料檔案。如果有增量備份，需要進行還原和恢復。如果丟失的資料檔案屬於只讀表空間，則只需要還原該資料檔案即可。

非歸檔模式下，只能恢復到最後一次備份的時刻，之後的資料需要重新錄入。

執行步驟如下：

（1）關閉資料庫。

（2）從備份還原整個資料庫。

（3）開啟資料庫。

例子：非歸檔模式下的備份和恢復

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 9

Current log sequence 11

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

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.

[ ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 18 22:53:46 2016

connected to target database: STONE (DBID=3001485737, not open)

RMAN> backup database;

Starting backup at 18-JAN-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 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/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: starting piece 1 at 18-JAN-16

channel ORA_DISK_1: finished piece 1 at 18-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T225352_c9sz40ps_.bkp tag=TAG20160118T225352 comment=NONE

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

Finished backup at 18-JAN-16

Starting Control File and SPFILE Autobackup at 18-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_18/o1_mf_s_901493394_c9sz7b6s_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 18-JAN-16

[ ~]$ rm /u01/app/oracle/oradata/STONE/datafile/user01.dbf

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> 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 4 - see DBWR trace file

ORA-01110: data file 4: '/u01/app/oracle/oradata/STONE/datafile/user01.dbf'

[ ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 18 23:06:15 2016

connected to target database: STONE (DBID=3001485737, not open)

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

3282 HIGH OPEN 18-JAN-16 One or more non-system datafiles are missing

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

3282 HIGH OPEN 18-JAN-16 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=17 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /u01/app/oracle/oradata/STONE/datafile/user01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

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

1 NOARCHIVELOG mode restore datafile 4

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_595042389.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_595042389.hm

contents of repair script:

# NOARCHIVELOG mode restore datafile

restore datafile 4;

recover datafile 4;

sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 18-JAN-16

using channel ORA_DISK_1

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 00004 to /u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T225352_c9sz40ps_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T225352_c9sz40ps_.bkp tag=TAG20160118T225352

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 18-JAN-16

Starting recover at 18-JAN-16

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 18-JAN-16

sql statement: alter database datafile 4 online

repair failure complete

Do you want to open the database (enter YES or NO)? yes

database opened

SQL> create table emp tablespace users as select * from hr.employees;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

SQL> shutdown immediate;

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u01/app/oracle/oradata/STONE/datafile/user01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL> 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 4 - see DBWR trace file

ORA-01110: data file 4: '/u01/app/oracle/oradata/STONE/datafile/user01.dbf'

[ ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 18 23:14:12 2016

connected to target database: STONE (DBID=3001485737, not open)

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

3282 HIGH OPEN 18-JAN-16 One or more non-system datafiles are missing

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

3282 HIGH OPEN 18-JAN-16 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /u01/app/oracle/oradata/STONE/datafile/user01.dbf was unintentionally renamed or moved, restore it

2. If you have an export of tablespace USERS, offline its data files, open the database read/write, then drop and re-create the tablespace and import the data.

Automated Repair Options

========================

Option Repair Description

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

1 Restore database and recover with UNTIL CANCEL option

Strategy: The repair includes recovery in NOARCHIVELOG mode with some data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2663793134.hm

RMAN> repair failure;

Strategy: The repair includes recovery in NOARCHIVELOG mode with some data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2663793134.hm

contents of repair script:

# database restore and recover until cancel

restore database;

recover database;

alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 18-JAN-16

using channel ORA_DISK_1

skipping datafile 5; already restored to file /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

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/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T225352_c9sz40ps_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T225352_c9sz40ps_.bkp tag=TAG20160118T225352

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 18-JAN-16

Starting recover at 18-JAN-16

using channel ORA_DISK_1

datafile 5 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c9kcnv60_.log

archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9kcnx8j_.log

archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c9kcnz1n_.log

RMAN-08187: WARNING: media recovery until SCN 3919717 complete

Finished recover at 18-JAN-16

database opened

repair failure complete

10、使用還原點

可以為某個時間點或者SCN指定一個名字，用於point-in-time恢復或者閃回操作。

第一個例子為當前時間建立了一個還原點。

第二個例子為SCN等於100建立了一個還原點。

還原點最短保留時間由引數CONTROL_FILE_RECORD_KEEP_TIME控制，可以使用PRESERVE選項建立永久保留的還原點。

可以透過V$RESTORE_POINT檢視檢視還原點的名字，SCN，timestamp等資訊。

例子：建立還原點

SQL> create restore point before_mods;

Restore point created.

SQL> create restore point end_q1 as of scn 3902076;

Restore point created.

SQL> select scn,time,name from v$restore_point;

SCN TIME NAME

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

3656190 12-JAN-16 10.06.47.000000000 PM %RESTORE_POINT

3902336 18-JAN-16 08.09.52.000000000 PM BEFORE_MODS

3902076 18-JAN-16 08.12.00.000000000 PM END_Q1

SQL> drop restore point end_q1;

Restore point dropped.

SQL> select scn,time,name from v$restore_point;

SCN TIME NAME

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

3656190 12-JAN-16 10.06.47.000000000 PM %RESTORE_POINT

3902336 18-JAN-16 08.09.52.000000000 PM BEFORE_MODS

11、執行Point-in-Time恢復

執行point-in-time恢復必須在歸檔模式下，具體步驟如下：

（1）指定還原目標，可以是時間、SCN、還原點或者日誌序列號，如果在下午3點提交了錯誤的事務，則可以指定2:59作為還原點時間。

（2）設定NLS作業系統環境變數，以便對提供的時間進行正確格式化，示例如下：

$export NLS_LANG=american_america.us7ascii

$export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"

（3）掛載資料庫，如果資料庫是開啟的，需要先關閉：

RMAN>shutdown immediate

RMAN>startup mount

（4）建立和執行RUN塊，RECOVER和RESTORE命令需要位於同一個RUN塊內，以便UNTIL設定應用到這兩個命令。示例如下：

RUN{

SET UNTIL TIME '2007-08-14 21:59:00';

RESTORE DATABASE;

RECOVER DATABASE;

}

（5）以只讀模式開啟資料庫，檢查資料確認是否恢復到指定的時間點：

RMAN>SQL 'ALTER DATABASE OPEN READ ONLY';

（6）如果滿足恢復要求，使用RESETLOGS選項開啟資料庫：

RMAN>ALTER DATABASE OPEN RESETLOGS;

例子：執行Point-in-Time恢復

先備份

RMAN> backup database plus archivelog delete input;

Starting backup at 19-JAN-16

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=201 STAMP=901527711

input archived log thread=1 sequence=2 RECID=202 STAMP=901527712

input archived log thread=1 sequence=3 RECID=203 STAMP=901527714

input archived log thread=1 sequence=4 RECID=204 STAMP=901527715

input archived log thread=1 sequence=5 RECID=205 STAMP=901527716

input archived log thread=1 sequence=6 RECID=206 STAMP=901527718

input archived log thread=1 sequence=7 RECID=207 STAMP=901527744

channel ORA_DISK_1: starting piece 1 at 19-JAN-16

channel ORA_DISK_1: finished piece 1 at 19-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_19/o1_mf_annnn_TAG20160119T082224_c9v0g130_.bkp tag=TAG20160119T082224 comment=NONE

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

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_1_c9v0dz9s_.arc RECID=201 STAMP=901527711

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_2_c9v0f0b8_.arc RECID=202 STAMP=901527712

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_3_c9v0f2jz_.arc RECID=203 STAMP=901527714

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_4_c9v0f3fv_.arc RECID=204 STAMP=901527715

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_5_c9v0f4s0_.arc RECID=205 STAMP=901527716

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_6_c9v0f64w_.arc RECID=206 STAMP=901527718

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_7_c9v0g0po_.arc RECID=207 STAMP=901527744

Finished backup at 19-JAN-16

Starting backup at 19-JAN-16

using channel ORA_DISK_1

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/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: starting piece 1 at 19-JAN-16

channel ORA_DISK_1: finished piece 1 at 19-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_19/o1_mf_nnndf_TAG20160119T082226_c9v0g30w_.bkp tag=TAG20160119T082226 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:06

Finished backup at 19-JAN-16

Starting backup at 19-JAN-16

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=8 RECID=208 STAMP=901527872

channel ORA_DISK_1: starting piece 1 at 19-JAN-16

channel ORA_DISK_1: finished piece 1 at 19-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_19/o1_mf_annnn_TAG20160119T082432_c9v0l0mp_.bkp tag=TAG20160119T082432 comment=NONE

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

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_8_c9v0l075_.arc RECID=208 STAMP=901527872

Finished backup at 19-JAN-16

Starting Control File and SPFILE Autobackup at 19-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_19/o1_mf_s_901527873_c9v0l1z3_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 19-JAN-16

建立還原點

SQL> create restore point rp20160119082559;

Restore point created.

SQL> select scn,time,name from v$restore_point;

SCN TIME NAME

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

3656190 12-JAN-16 10.06.47.000000000 PM %RESTORE_POINT

3902336 18-JAN-16 08.09.52.000000000 PM BEFORE_MODS

3922886 19-JAN-16 08.26.12.000000000 AM RP20160119082559

SQL> select count(*) from emp;

COUNT(*)

----------

107

SQL> delete from emp;

107 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)

----------

0

RMAN> shutdown immediate

database closed

database dismounted

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 507513936 bytes

Database Buffers 322961408 bytes

Redo Buffers 2371584 bytes

RMAN> run{

2> set until time "to_date('2016-01-19 08:26:12','yyyy-mm-dd hh24:mi:ss')";

3> restore database;

4> recover database;}

executing command: SET until clause

Starting restore at 19-JAN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

skipping datafile 5; already restored to file /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

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/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_19/o1_mf_nnndf_TAG20160119T082226_c9v0g30w_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_19/o1_mf_nnndf_TAG20160119T082226_c9v0g30w_.bkp tag=TAG20160119T082226

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 19-JAN-16

Starting recover at 19-JAN-16

using channel ORA_DISK_1

datafile 5 not processed because file is read-only

starting media recovery

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

Finished recover at 19-JAN-16

RMAN> alter database open resetlogs;

database opened

SQL> select count(*) from emp;

COUNT(*)

----------

107

12、使用備份的控制檔案執行恢復

如果丟失了所有的控制檔案，可以使用備份的控制檔案進行恢復，然後執行RECOVER命令，使用RESETLOGS選項開啟資料庫。

例子：丟失單個控制檔案的恢復

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl

/u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl

/home/oracle/o1_mf_c06xh9fs_.ctl

SQL> !rm /home/oracle/o1_mf_c06xh9fs_.ctl

SQL> shutdown immediate

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/home/oracle/o1_mf_c06xh9fs_.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount

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

ORA-00205: error in identifying control file, check alert log for more info

[ ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 22:26:39 2016

connected to target database: STONE (not mounted)

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

5930 CRITICAL OPEN 19-JAN-16 Control file /home/oracle/o1_mf_c06xh9fs_.ctl is missing

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

5930 CRITICAL OPEN 19-JAN-16 Control file /home/oracle/o1_mf_c06xh9fs_.ctl is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

no manual actions available

Automated Repair Options

========================

Option Repair Description

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

1 Use a multiplexed copy to restore control file /home/oracle/o1_mf_c06xh9fs_.ctl

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2365519798.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2365519798.hm

contents of repair script:

# restore control file using multiplexed copy

restore controlfile from '/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl';

sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 19-JAN-16

using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy

output file name=/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl

output file name=/u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl

output file name=/home/oracle/o1_mf_c06xh9fs_.ctl

Finished restore at 19-JAN-16

sql statement: alter database mount

released channel: ORA_DISK_1

repair failure complete

Do you want to open the database (enter YES or NO)? yes

database opened

例子：丟失所有控制檔案的恢復

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl

/u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl

/home/oracle/o1_mf_c06xh9fs_.ctl

SQL> !rm /u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl

SQL> !rm /u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl

SQL> !rm /home/oracle/o1_mf_c06xh9fs_.ctl

SQL> shutdown immediate

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount

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

ORA-00205: error in identifying control file, check alert log for more info

[ ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 22:30:08 2016

connected to target database: STONE (not mounted)

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

6079 CRITICAL OPEN 19-JAN-16 Control file /home/oracle/o1_mf_c06xh9fs_.ctl is missing

6076 CRITICAL OPEN 19-JAN-16 Control file /u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl is missing

6073 CRITICAL OPEN 19-JAN-16 Control file /u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl is missing

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

6079 CRITICAL OPEN 19-JAN-16 Control file /home/oracle/o1_mf_c06xh9fs_.ctl is missing

6076 CRITICAL OPEN 19-JAN-16 Control file /u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl is missing

6073 CRITICAL OPEN 19-JAN-16 Control file /u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl is missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=17 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /home/oracle/o1_mf_c06xh9fs_.ctl was unintentionally renamed or moved, restore it

2. If file /u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl was unintentionally renamed or moved, restore it

3. If file /u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl was unintentionally renamed or moved, restore it

4. If this is a primary database and a standby database is available, then perform a Data Guard failover initiated from the standby

Automated Repair Options

========================

Option Repair Description

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

1 Restore a backup control file

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3604429448.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3604429448.hm

contents of repair script:

# restore control file

restore controlfile from autobackup;

sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 19-JAN-16

using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/fast_recovery_area

database name (or database unique name) used for search: STONE

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_19/o1_mf_s_901577753_c9wk8sbv_.bkp found in the recovery area

AUTOBACKUP search with format "%F" not attempted because DBID was not set

channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_19/o1_mf_s_901577753_c9wk8sbv_.bkp

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl

output file name=/u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl

output file name=/home/oracle/o1_mf_c06xh9fs_.ctl

Finished restore at 19-JAN-16

sql statement: alter database mount

released channel: ORA_DISK_1

repair failure complete

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 01/19/2016 22:36:43

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 01/19/2016 22:36:54

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf'

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

6147 CRITICAL OPEN 19-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf' needs media recovery

6144 CRITICAL OPEN 19-JAN-16 Control file needs media recovery

3913 HIGH OPEN 19-JAN-16 One or more non-system datafiles need media recovery

RMAN> advise failure;

Starting implicit crosscheck backup at 19-JAN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=17 device type=DISK

Crosschecked 4 objects

Finished implicit crosscheck backup at 19-JAN-16

Starting implicit crosscheck copy at 19-JAN-16

using channel ORA_DISK_1

Finished implicit crosscheck copy at 19-JAN-16

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_1_c9wky82o_.arc

File Name: /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_15/o1_mf_s_901208867_c9k913mg_.bkp

File Name: /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_15/o1_mf_s_901210804_c9kbxnqb_.bkp

File Name: /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_19/o1_mf_s_901577753_c9wk8sbv_.bkp

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

6147 CRITICAL OPEN 19-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf' needs media recovery

6144 CRITICAL OPEN 19-JAN-16 Control file needs media recovery

3913 HIGH OPEN 19-JAN-16 One or more non-system datafiles need media recovery

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If you have the correct version of the control file, then shutdown the database and replace the old control file

2. If you restored the wrong version of data file /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf, then replace it with the correct one

3. If you restored the wrong version of data file /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf, then replace it with the correct one

4. If you restored the wrong version of data file /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf, then replace it with the correct one

5. If you restored the wrong version of data file /u01/app/oracle/oradata/STONE/datafile/user01.dbf, then replace it with the correct one

6. If you restored the wrong version of data file /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf, then replace it with the correct one

7. If you restored the wrong version of data file /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf, then replace it with the correct one

Automated Repair Options

========================

Option Repair Description

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

1 Recover datafile 1; Recover datafile 2; Recover datafile 3; ...

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3830732253.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3830732253.hm

contents of repair script:

# recover datafile

recover database;

alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting recover at 19-JAN-16

using channel ORA_DISK_1

datafile 5 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_1_c9wky82o_.arc

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9wk8m56_.log

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_1_c9wky82o_.arc thread=1 sequence=1

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9wk8m56_.log thread=1 sequence=2

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

Finished recover at 19-JAN-16

database opened

repair failure complete

13、伺服器引數檔案丟失的恢復

恢復引數檔案最簡單的方式是使用FROM MEMORY語句對當前系統引數設定建立PFILE或者SPFILE。

在例項啟動過程中，所有引數設定都會被寫入到alert.log檔案，可以複製出來作為引數檔案。

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

File created.

SQL> create pfile='/home/oracle/pfile1.ora' from memory;

File created.

SQL> create spfile='/home/oracle/spfile.ora' from pfile='/home/oracle/pfile.ora';

File created.

SQL> create spfile='/home/oracle/spfile1.ora' from memory;

File created.

SQL> !cat /home/oracle/pfile.ora

SQL> !cat /home/oracle/pfile1.ora

14、從控制檔案自動備份中還原伺服器引數檔案

如果丟失了伺服器引數檔案且無法使用FROM MEMORY語句，與還原控制檔案類似，可以從自動備份中還原。如果備份不在快速恢復區，需要設定DBID。使用命令RESTORE SPFILE FROM AUTOBACKUP。

如果要還原SPFILE到非預設位置，使用如下命令：

RESTORE SPFILE TO FROM AUTOBACKUP

如果從快速恢復區還原伺服器引數檔案，使用如下命令：

RMAN> run {

2> restore spfile from autobackup

3> recovery area = ''

4> db_name = '';

5> }

例子：還原伺服器引數檔案

[ ~]$ mv /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestone.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestone1.ora

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstone.ora'

[ ~]$ vim /u01/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora

#

# NOTE: The values that are used in this file are only intended to be used

# as a starting point. You may want to adjust/tune those values to your

# specific hardware and needs. You may also consider using Database

# Configuration Assistant tool (DBCA) to create INIT file and to size your

# initial set of tablespaces based on the user input.

###############################################################################

# Change '' to point to the oracle base (the one you specify at

# install time)

db_name='STONE'

#memory_target=1G

processes = 150

audit_file_dest='/u01/app/oracle/admin/stone/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='/u01/app/oracle'

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

# You may want to ensure that control files are created on separate physical

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora'

ORACLE instance started.

Total System Global Area 221331456 bytes

Fixed Size 2251856 bytes

Variable Size 163578800 bytes

Database Buffers 50331648 bytes

Redo Buffers 5169152 bytes

[ ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 20 08:22:40 2016

connected to target database: STONE (not mounted)

RMAN> restore spfile from autobackup;

Starting restore at 20-JAN-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area

database name (or database unique name) used for search: STONE

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_19/o1_mf_s_901579118_c9wlmh11_.bkp found in the recovery area

AUTOBACKUP search with format "%F" not attempted because DBID was not set

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_19/o1_mf_s_901579118_c9wlmh11_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 20-JAN-16

[ ~]$ ls /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestone.ora

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestone.ora

SQL> startup force

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.

Database opened.

15、從自動備份中還原控制檔案

如果沒有使用恢復目錄，則應該配置控制檔案自動備份。如果使用了快速恢復區，RMAN會隱式對控制檔案列出的備份和映象複製進行交叉檢查，將沒有記錄到控制檔案中的快速恢復區檔案記錄到控制檔案。如果使用的是磁帶，則不會進行交叉檢查，需要還原控制檔案並mount後，手動進行。

使用上圖中的命令進行恢復。首先啟動資料庫到nomount狀態，從備份中還原控制檔案，然後mount資料庫，由於還原的控制檔案是以前的一個版本，故需要recover資料庫。同時由於新的控制檔案表示一個不同的資料庫例項，故需要使用RESETLOGS選項開啟資料庫。

從自動備份中還原控制檔案，資料庫必須處於nomount狀態，如果自動備份不位於快速恢復區，則必須在執行RESTORE命令前先設定DBID，示例如下：

RMAN> SHUTDOWN ABORT;

RMAN> STARTUP NOMOUNT;

RMAN> SET DBID 1090770270;

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

RMAN從自動備份中搜尋控制檔案，如果找到，則還原到引數檔案中CONTROL_FILES指定的所有位置。

如果使用了恢復目錄，則不需要設定DBID或者使用控制檔案自動備份還原控制檔案，直接使用RESTORE CONTROLFILE命令即可：

RMAN> RESTORE CONTROLFILE;

使用RESTORE CONTROLFILE ... TO 命令將控制檔案還原到非預設位置。

16、非歸檔模式下使用增量備份恢復資料庫

在非歸檔模式下可以使用連續的增量備份進行恢復。如果聯機重做日誌檔案丟失或者不能應用於增量備份，則RECOVER DATABASE命令需要使用NOREDO選項。如果當前的聯機重做日誌檔案包含了增量備份之後的所有改變，則可以不使用NOREDO選項。

例子：非歸檔模式下使用增量備份恢復資料庫

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Current log sequence 2

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

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.

進行0級備份

RMAN> backup incremental level 0 database;

Starting backup at 20-JAN-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: starting piece 1 at 20-JAN-16

channel ORA_DISK_1: finished piece 1 at 20-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_20/o1_mf_nnnd0_TAG20160120T192701_c9yvr5r3_.bkp tag=TAG20160120T192701 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05

Finished backup at 20-JAN-16

Starting Control File and SPFILE Autobackup at 20-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_20/o1_mf_s_901654001_c9yvw36r_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 20-JAN-16

SQL> alter database open;

Database altered.

SQL> select count(*) from emp;

COUNT(*)

----------

107

SQL> insert into emp select * from emp;

107 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

COUNT(*)

----------

214

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

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.

進行1級備份

RMAN> backup incremental level 1 database;

Starting backup at 20-JAN-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

skipping datafile 00005 because it has not changed

input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: starting piece 1 at 20-JAN-16

channel ORA_DISK_1: finished piece 1 at 20-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_20/o1_mf_nnnd1_TAG20160120T193707_c9ywc3n5_.bkp tag=TAG20160120T193707 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 20-JAN-16

Starting Control File and SPFILE Autobackup at 20-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_20/o1_mf_s_901654580_c9ywc6xx_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 20-JAN-16

刪除所有的資料檔案和控制檔案

[ ~]$ ls /u01/app/oracle/oradata/STONE/datafile/

o1_mf_example_c9kcl362_.dbf o1_mf_sysaux_c9kcl360_.dbf o1_mf_temp_c9kco30z_.tmp o1_mf_undotbs1_c9kcl368_.dbf

o1_mf_rcat_ts_c9kcl36f_.dbf o1_mf_system_c9kjvdr4_.dbf o1_mf_test_ind_c9kcl36o_.dbf user01.dbf

[ ~]$ rm /u01/app/oracle/oradata/STONE/datafile/*

[ ~]$ rm /u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl

[ ~]$ rm /u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl

[ ~]$ rm /home/oracle/o1_mf_c06xh9fs_.ctl

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> 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

ORA-00205: error in identifying control file, check alert log for more info

恢復控制檔案

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

6487 CRITICAL OPEN 20-JAN-16 Control file /home/oracle/o1_mf_c06xh9fs_.ctl is missing

6484 CRITICAL OPEN 20-JAN-16 Control file /u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl is missing

6481 CRITICAL OPEN 20-JAN-16 Control file /u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl is missing

6441 CRITICAL OPEN 20-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf' is missing

3282 HIGH OPEN 20-JAN-16 One or more non-system datafiles are missing

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

6487 CRITICAL OPEN 20-JAN-16 Control file /home/oracle/o1_mf_c06xh9fs_.ctl is missing

6484 CRITICAL OPEN 20-JAN-16 Control file /u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl is missing

6481 CRITICAL OPEN 20-JAN-16 Control file /u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl is missing

6441 CRITICAL OPEN 20-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf' is missing

3282 HIGH OPEN 20-JAN-16 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

analyzing automatic repair options complete

Not all specified failures can currently be repaired.

The following failures must be repaired before advise for others can be given.

Failure ID Priority Status Time Detected Summary

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

6487 CRITICAL OPEN 20-JAN-16 Control file /home/oracle/o1_mf_c06xh9fs_.ctl is missing

6484 CRITICAL OPEN 20-JAN-16 Control file /u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl is missing

6481 CRITICAL OPEN 20-JAN-16 Control file /u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl is missing

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /home/oracle/o1_mf_c06xh9fs_.ctl was unintentionally renamed or moved, restore it

2. If file /u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl was unintentionally renamed or moved, restore it

3. If file /u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl was unintentionally renamed or moved, restore it

4. If this is a primary database and a standby database is available, then perform a Data Guard failover initiated from the standby

Automated Repair Options

========================

Option Repair Description

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

1 Restore a backup control file

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3607448101.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3607448101.hm

contents of repair script:

# restore control file

restore controlfile from autobackup;

sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 20-JAN-16

using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/fast_recovery_area

database name (or database unique name) used for search: STONE

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_20/o1_mf_s_901654580_c9ywc6xx_.bkp found in the recovery area

AUTOBACKUP search with format "%F" not attempted because DBID was not set

channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_20/o1_mf_s_901654580_c9ywc6xx_.bkp

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl

output file name=/u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl

output file name=/home/oracle/o1_mf_c06xh9fs_.ctl

Finished restore at 20-JAN-16

sql statement: alter database mount

released channel: ORA_DISK_1

repair failure complete

還原和恢復資料檔案

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

6552 CRITICAL OPEN 20-JAN-16 Control file needs media recovery

6441 CRITICAL OPEN 20-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf' is missing

3282 HIGH OPEN 20-JAN-16 One or more non-system datafiles are missing

RMAN> advise failure;

Starting implicit crosscheck backup at 20-JAN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

Crosschecked 3 objects

Finished implicit crosscheck backup at 20-JAN-16

Starting implicit crosscheck copy at 20-JAN-16

using channel ORA_DISK_1

Finished implicit crosscheck copy at 20-JAN-16

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_20/o1_mf_s_901654580_c9ywc6xx_.bkp

File Name: /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_15/o1_mf_s_901208867_c9k913mg_.bkp

File Name: /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_15/o1_mf_s_901210804_c9kbxnqb_.bkp

File Name: /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_19/o1_mf_s_901577753_c9wk8sbv_.bkp

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

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

6552 CRITICAL OPEN 20-JAN-16 Control file needs media recovery

6441 CRITICAL OPEN 20-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf' is missing

3282 HIGH OPEN 20-JAN-16 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If you have the correct version of the control file, then shutdown the database and replace the old control file

2. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf was unintentionally renamed or moved, restore it

3. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf was unintentionally renamed or moved, restore it

4. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf was unintentionally renamed or moved, restore it

5. If file /u01/app/oracle/oradata/STONE/datafile/user01.dbf was unintentionally renamed or moved, restore it

6. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf was unintentionally renamed or moved, restore it

7. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf was unintentionally renamed or moved, restore it

8. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

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

1 Restore database and recover with UNTIL CANCEL option

Strategy: The repair includes recovery in NOARCHIVELOG mode with some data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_1738708433.hm

RMAN> repair failure;

Strategy: The repair includes recovery in NOARCHIVELOG mode with some data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_1738708433.hm

contents of repair script:

# database restore and recover until cancel

restore database;

recover database;

alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 20-JAN-16

using channel ORA_DISK_1

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/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_20/o1_mf_nnnd0_TAG20160120T192701_c9yvr5r3_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_20/o1_mf_nnnd0_TAG20160120T192701_c9yvr5r3_.bkp tag=TAG20160120T192701

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 20-JAN-16

Starting recover at 20-JAN-16

using channel ORA_DISK_1

datafile 5 not processed because file is read-only

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9ywwthp_.dbf

destination for restore of datafile 00002: /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9ywwthy_.dbf

destination for restore of datafile 00003: /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9ywwtjb_.dbf

destination for restore of datafile 00004: /u01/app/oracle/oradata/STONE/datafile/user01.dbf

destination for restore of datafile 00006: /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9ywwtjx_.dbf

destination for restore of datafile 00007: /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9ywwtjk_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_20/o1_mf_nnnd1_TAG20160120T193707_c9ywc3n5_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_20/o1_mf_nnnd1_TAG20160120T193707_c9ywc3n5_.bkp tag=TAG20160120T193707

channel ORA_DISK_1: restored backup piece 1

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

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9wk8m56_.log

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9wk8m56_.log thread=1 sequence=2

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

Finished recover at 20-JAN-16

database opened

repair failure complete

SQL> select count(*) from emp;

COUNT(*)

----------

214

17、還原和恢復資料庫到異機

還原和恢復資料庫到異機需要保持DBID一致，故不要把這兩個資料庫到註冊到同一恢復目錄。可以使用RMAN的DUPLICATE命令建立一個複製的資料庫，帶有不同的DBID，可以註冊到同一個恢復目錄。

18、還原資料庫到異機：準備

還原資料庫到異機執行以下準備步驟：

記錄源資料庫的DBID。
複製源資料庫的初始化引數到異機。
確保異機可以訪問源資料庫的備份，包括控制檔案的自動備份

19、還原資料庫到異機

還原資料庫到異機的步驟：

（1）設定ORACLE_SID環境變數：

$ setenv ORACLE_SID orcl

（2）啟動RMAN，不要連線到恢復目錄：

$ rman TARGET /

（3）設定DBID：

RMAN> SET DBID 1090770270;

（4）啟動到NOMOUNT：

RMAN> STARTUP NOMOUNT

如果伺服器引數檔案不存在，則會出現如下報錯：

startup failed: ORA-01078: failure in processing system parameters

RMAN使用模版引數檔案啟動例項。

（5）從備份集中還原伺服器引數檔案：

RESTORE SPFILE TO PFILE '?/oradata/test/initorcl.ora' FROM AUTOBACKUP;

（6）關閉例項：

SHUTDOWN IMMEDIATE;

（7）根據異機的實際目錄結構編輯PFILE。

（8）使用PFILE啟動例項到NOMOUNT：

RMAN> STARTUP NOMOUNT

> PFILE='?/oradata/test/initorcl.ora';

20、還原資料庫到異機

（9）建立RUN塊，從自動備份中還原控制檔案並掛載資料庫：

RUN

{

RESTORE CONTROLFILE FROM AUTOBACKUP;

ALTER DATABASE MOUNT;

}

（10）查詢V$DATAFILE獲取控制檔案中記錄的資料檔名稱，建立RMAN恢復指令碼還原和恢復資料庫，包括：

a、如果異機與源主機的目錄結構不一致，需要使用SET NEWNAME為資料檔案指定路徑。

b、使用SQL語句ALTER DATABASE RENMAE FILE命令指定聯機重做日誌檔案路徑。

c、使用SET UNTIL命令指定恢復到歸檔日誌結束。

d、使用SWITCH命令讓控制檔案識別到資料檔案新的路徑及檔名。

例子：

RUN

{

SET NEWNAME FOR DATAFILE 1 TO '?/oradata/test/system01.dbf';

SET NEWNAME FOR DATAFILE 2 TO '?/oradata/test/undotbs01.dbf';

SET NEWNAME FOR DATAFILE 3 TO '?/oradata/test/sysaux.dbf';

SET NEWNAME FOR DATAFILE 4 TO '?/oradata/test/users01.dbf';

SET NEWNAME FOR DATAFILE 5 TO '?/oradata/test/example01.dbf';

SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo01.log''

TO ''?/oradata/test/redo01.log'' ";

SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo02.log''

TO ''?/oradata/test/redo02.log'' ";

SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/orcl/redo03.log''

TO ''?/oradata/test/redo03.log'' ";

SET UNTIL SCN 4545727;

RESTORE DATABASE;

SWITCH DATAFILE ALL;

RECOVER DATABASE;

}

（11）執行指令碼

（12）使用RESETLOGS選項開啟資料庫。

RMAN> ALTER DATABASE OPEN RESETLOGS;

例子：還原資料庫到異機

源資料庫進行備份：

RMAN> backup incremental level 1 database plus archivelog delete input;

Starting backup at 21-JAN-16

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=28 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=226 STAMP=901723670

channel ORA_DISK_1: starting piece 1 at 21-JAN-16

channel ORA_DISK_1: finished piece 1 at 21-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_annnn_TAG20160121T144750_cb0zrpom_.bkp tag=TAG20160121T144750 comment=NONE

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

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_21/o1_mf_1_1_cb0zrohv_.arc RECID=226 STAMP=901723670

Finished backup at 21-JAN-16

Starting backup at 21-JAN-16

using channel ORA_DISK_1

no parent backup or copy of datafile 1 found

no parent backup or copy of datafile 2 found

no parent backup or copy of datafile 5 found

no parent backup or copy of datafile 3 found

no parent backup or copy of datafile 4 found

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9ywwthp_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9ywwthy_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9ywwtj3_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9ywwtjb_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: starting piece 1 at 21-JAN-16

channel ORA_DISK_1: finished piece 1 at 21-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_nnnd0_TAG20160121T144751_cb0zrrmr_.bkp tag=TAG20160121T144751 comment=NONE

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

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9ywwtjk_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9ywwtjx_.dbf

channel ORA_DISK_1: starting piece 1 at 21-JAN-16

channel ORA_DISK_1: finished piece 1 at 21-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_nnnd1_TAG20160121T144751_cb0zvqlf_.bkp tag=TAG20160121T144751 comment=NONE

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

Finished backup at 21-JAN-16

Starting backup at 21-JAN-16

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=2 RECID=227 STAMP=901723768

channel ORA_DISK_1: starting piece 1 at 21-JAN-16

channel ORA_DISK_1: finished piece 1 at 21-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_annnn_TAG20160121T144928_cb0zvrsv_.bkp tag=TAG20160121T144928 comment=NONE

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

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_21/o1_mf_1_2_cb0zvrod_.arc RECID=227 STAMP=901723768

Finished backup at 21-JAN-16

Starting Control File and SPFILE Autobackup at 21-JAN-16

piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/o1_mf_s_901723769_cb0zvt13_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 21-JAN-16

RMAN> list backup;

List of Backup Sets

===================

BS Key Size Device Type Elapsed Time Completion Time

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

134 20.16M DISK 00:00:01 21-JAN-16

BP Key: 145 Status: AVAILABLE Compressed: NO Tag: TAG20160121T144750

Piece Name: /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_annnn_TAG20160121T144750_cb0zrpom_.bkp

List of Archived Logs in backup set 134

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 1 3979524 20-JAN-16 3999597 21-JAN-16

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

135 Incr 0 1.44G DISK 00:01:34 21-JAN-16

BP Key: 146 Status: AVAILABLE Compressed: NO Tag: TAG20160121T144751

Piece Name: /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_nnnd0_TAG20160121T144751_cb0zrrmr_.bkp

List of Datafiles in backup set 135

File LV Type Ckp SCN Ckp Time Name

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

1 0 Incr 3999608 21-JAN-16 /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9ywwthp_.dbf

2 0 Incr 3999608 21-JAN-16 /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9ywwthy_.dbf

3 0 Incr 3999608 21-JAN-16 /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9ywwtjb_.dbf

4 0 Incr 3999608 21-JAN-16 /u01/app/oracle/oradata/STONE/datafile/user01.dbf

5 0 Incr 3618687 12-JAN-16 /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9ywwtj3_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

136 Incr 1 21.01M DISK 00:00:01 21-JAN-16

BP Key: 147 Status: AVAILABLE Compressed: NO Tag: TAG20160121T144751

Piece Name: /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_nnnd1_TAG20160121T144751_cb0zvqlf_.bkp

List of Datafiles in backup set 136

File LV Type Ckp SCN Ckp Time Name

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

6 1 Incr 3999680 21-JAN-16 /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9ywwtjx_.dbf

7 1 Incr 3999680 21-JAN-16 /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9ywwtjk_.dbf

BS Key Size Device Type Elapsed Time Completion Time

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

137 27.00K DISK 00:00:00 21-JAN-16

BP Key: 148 Status: AVAILABLE Compressed: NO Tag: TAG20160121T144928

Piece Name: /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_annnn_TAG20160121T144928_cb0zvrsv_.bkp

List of Archived Logs in backup set 137

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 2 3999597 21-JAN-16 3999686 21-JAN-16

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

138 Full 9.58M DISK 00:00:01 21-JAN-16

BP Key: 149 Status: AVAILABLE Compressed: NO Tag: TAG20160121T144929

Piece Name: /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/o1_mf_s_901723769_cb0zvt13_.bkp

SPFILE Included: Modification time: 21-JAN-16

SPFILE db_unique_name: STONE

Control File Included: Ckp SCN: 3999697 Ckp time: 21-JAN-16

在異機上面安裝相同版本的資料庫，目錄結構保持與源主機一致，但不要建立資料庫。同時根據源主機備份目錄，在異機上面建立對應的目錄，這樣就不需要進行目錄轉換了。

[ ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/

[ ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/

複製備份檔案和引數檔案到異機

[ ~]$ scp oracle@192.168.230.138:/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/* /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/

The authenticity of host '192.168.230.138 (192.168.230.138)' can't be established.

RSA key fingerprint is 4d:57:54:66:9c:ee:ec:65:15:ae:ee:a4:93:68:f1:b4.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.230.138' (RSA) to the list of known hosts.

reverse mapping checking getaddrinfo for bogon [192.168.230.138] failed - POSSIBLE BREAK-IN ATTEMPT!

oracle@192.168.230.138's password:

Permission denied, please try again.

oracle@192.168.230.138's password:

o1_mf_annnn_TAG20160121T144750_cb0zrpom_.bkp 100% 20MB 20.2MB/s 00:00

o1_mf_annnn_TAG20160121T144928_cb0zvrsv_.bkp 100% 28KB 27.5KB/s 00:00

o1_mf_nnnd0_TAG20160121T144751_cb0zrrmr_.bkp 100% 1478MB 26.9MB/s 00:55

o1_mf_nnnd1_TAG20160121T144751_cb0zvqlf_.bkp 100% 21MB 21.0MB/s 00:00

[ ~]$ scp oracle@192.168.230.138:/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/* /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/

reverse mapping checking getaddrinfo for bogon [192.168.230.138] failed - POSSIBLE BREAK-IN ATTEMPT!

oracle@192.168.230.138's password:

o1_mf_s_901723769_cb0zvt13_.bkp 100% 9824KB 9.6MB/s 00:00

[ ~]$ scp oracle@192.168.230.138:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestone.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

reverse mapping checking getaddrinfo for bogon [192.168.230.138] failed - POSSIBLE BREAK-IN ATTEMPT!

oracle@192.168.230.138's password:

spfilestone.ora 100% 3584 3.5KB/s 00:00

根據源主機引數檔案相關引數建立目錄：

[ database]$ mkdir -p /u01/app/oracle/admin/stone/adump

[ database]$ mkdir -p /u01/app/oracle/oradata/STONE/controlfile/

[ database]$ mkdir -p /u01/app/oracle/fast_recovery_area/STONE/controlfile/

[ ~]$ echo $ORACLE_SID

stone

[ ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 21 16:13:37 2016

connected to target database (not started)

RMAN> set DBID=3001485737

executing command: SET DBID

RMAN> startup nomount

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

RMAN> run{

2> restore controlfile from autobackup;

3> alter database mount;}

Starting restore at 21-JAN-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area

database name (or database unique name) used for search: STONE

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/o1_mf_s_901723769_cb0zvt13_.bkp found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160121

channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/o1_mf_s_901723769_cb0zvt13_.bkp

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl

output file name=/u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl

output file name=/home/oracle/o1_mf_c06xh9fs_.ctl

Finished restore at 21-JAN-16

database mounted

released channel: ORA_DISK_1

RMAN> run{

2> restore database;

3> recover database;}

Starting restore at 21-JAN-16

Starting implicit crosscheck backup at 21-JAN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

Crosschecked 4 objects

Finished implicit crosscheck backup at 21-JAN-16

Starting implicit crosscheck copy at 21-JAN-16

using channel ORA_DISK_1

Finished implicit crosscheck copy at 21-JAN-16

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/o1_mf_s_901723769_cb0zvt13_.bkp

using channel ORA_DISK_1

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/STONE/datafile/o1_mf_system_c9ywwthp_.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9ywwthy_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9ywwtjb_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9ywwtj3_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_nnnd0_TAG20160121T144751_cb0zrrmr_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_nnnd0_TAG20160121T144751_cb0zrrmr_.bkp tag=TAG20160121T144751

channel ORA_DISK_1: restored backup piece 1

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

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 00006 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9ywwtjx_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9ywwtjk_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_nnnd1_TAG20160121T144751_cb0zvqlf_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_nnnd1_TAG20160121T144751_cb0zvqlf_.bkp tag=TAG20160121T144751

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 21-JAN-16

Starting recover at 21-JAN-16

using channel ORA_DISK_1

datafile 5 not processed because file is read-only

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=2

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_annnn_TAG20160121T144928_cb0zvrsv_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_21/o1_mf_annnn_TAG20160121T144928_cb0zvrsv_.bkp tag=TAG20160121T144928

channel ORA_DISK_1: restored backup piece 1

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

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_21/o1_mf_1_2_cb15khhp_.arc thread=1 sequence=2

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_21/o1_mf_1_2_cb15khhp_.arc RECID=228 STAMP=901729583

unable to find archived log

archived log thread=1 sequence=3

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/21/2016 16:26:24

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 3 and starting SCN of 3999686

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 01/21/2016 16:31:49

ORA-19751: could not create the change tracking file

ORA-19750: change tracking file: '/u01/app/oracle/oradata/STONE/changetracking/o1_mf_c98v5ocs_.chg'

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

[ database]$ mkdir -p /u01/app/oracle/oradata/STONE/changetracking/

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 01/21/2016 16:32:53

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

RMAN> alter database open;

database opened

21、進行災難恢復

災難恢復是指丟失了整個資料庫，包括恢復目錄，所有控制檔案，所有聯機重做日誌檔案以及所有引數檔案，然後對其進行恢復。

進行災難恢復，至少要求如下備份：

資料檔案備份
備份之後產生的歸檔日誌檔案
至少一個控制檔案自動備份

22、進行災難恢復

步驟如下：

從自動備份還原伺服器引數檔案
啟動資料庫例項
從自動備份還原控制檔案
mount資料庫
restore資料檔案
recover資料檔案
使用RESETLOGS選項開啟資料庫

例子：災難恢復

刪除控制檔案，聯機重做日誌檔案，引數檔案以及資料檔案

[ ~]$ rm /u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl

[ ~]$ rm /u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl

[ ~]$ rm /home/oracle/o1_mf_c06xh9fs_.ctl

[ ~]$ rm /u01/app/oracle/oradata/STONE/onlinelog/*

[ ~]$ rm /u01/app/oracle/fast_recovery_area/STONE/onlinelog/*

[ ~]$ rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestone.ora

[ ~]$ rm /u01/app/oracle/oradata/STONE/datafile/*

SQL> shutdown immediate

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstone.ora'

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstone.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes

Variable Size 281019272 bytes

Database Buffers 780140544 bytes

Redo Buffers 5517312 bytes

RMAN> set DBID=3001485737

executing command: SET DBID

RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/o1_mf_s_901723769_cb0zvt13_.bkp';

Starting restore at 21-JAN-16

using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/o1_mf_s_901723769_cb0zvt13_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 21-JAN-16

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)

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

RMAN> restore controlfile from autobackup;

Starting restore at 21-JAN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area

database name (or database unique name) used for search: STONE

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/o1_mf_s_901723769_cb0zvt13_.bkp found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160121

channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_21/o1_mf_s_901723769_cb0zvt13_.bkp

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl

output file name=/u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl

output file name=/home/oracle/o1_mf_c06xh9fs_.ctl

Finished restore at 21-JAN-16

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> restore database;