OCP课程48：管理II之使用RMAN执行恢复

最新推荐文章于 2021-04-12 14:01:34 发布

cuishan5183

最新推荐文章于 2021-04-12 14:01:34 发布

阅读量331

点赞数

课程目标：

对关键或者非关键数据文件丢失进行恢复
使用增量更新备份进行恢复
切换到镜像拷贝进行快速恢复
还原数据库到异机
使用备份的控制文件进行恢复

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'

[oracle@oracletest ~]$ 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'

[oracle@oracletest ~]$ 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.

[oracle@oracletest ~]$ 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

[oracle@oracletest ~]$ 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'

[oracle@oracletest ~]$ 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'

[oracle@oracletest ~]$ 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(*)

----------

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

[oracle@oracletest ~]$ 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

[oracle@oracletest ~]$ 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> }

例子：还原服务器参数文件

[oracle@oracletest ~]$ 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'

[oracle@oracletest ~]$ 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

[oracle@oracletest ~]$ 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

[oracle@oracletest ~]$ 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

删除所有的数据文件和控制文件

[oracle@oracletest ~]$ 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

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

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

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

[oracle@oracletest ~]$ 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

在异机上面安装相同版本的数据库，目录结构保持与源主机一致，但不要创建数据库。同时根据源主机备份目录，在异机上面创建对应的目录，这样就不需要进行目录转换了。

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

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

拷贝备份文件和参数文件到异机

[oracle@rmanrec ~]$ 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

[oracle@rmanrec ~]$ 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

[oracle@rmanrec ~]$ 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

根据源主机参数文件相关参数创建目录：

[oracle@rmanrec database]$ mkdir -p /u01/app/oracle/admin/stone/adump

[oracle@rmanrec database]$ mkdir -p /u01/app/oracle/oradata/STONE/controlfile/

[oracle@rmanrec database]$ mkdir -p /u01/app/oracle/fast_recovery_area/STONE/controlfile/

[oracle@rmanrec ~]$ echo $ORACLE_SID

stone

[oracle@rmanrec ~]$ 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

[oracle@rmanrec 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选项打开数据库

例子：灾难恢复

删除控制文件，联机重做日志文件，参数文件以及数据文件

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

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

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

[oracle@oracletest ~]$ rm /u01/app/oracle/oradata/STONE/onlinelog/*

[oracle@oracletest ~]$ rm /u01/app/oracle/fast_recovery_area/STONE/onlinelog/*

[oracle@oracletest ~]$ rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestone.ora

[oracle@oracletest ~]$ 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;