课程目标:
- 对关键或者非关键数据文件丢失进行恢复
- 使用增量更新备份进行恢复
- 切换到镜像拷贝进行快速恢复
- 还原数据库到异机
- 使用备份的控制文件进行恢复
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: STONE (DBID=3001485737, not open)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3282 HIGH OPEN 18-JAN-16 One or more non-system datafiles are missing
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3282 HIGH OPEN 18-JAN-16 One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/STONE/datafile/user01.dbf was unintentionally renamed or moved, restore it
2. If you have an export of tablespace USERS, offline its data files, open the database read/write, then drop and re-create the tablespace and import the data.
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore database and recover with UNTIL CANCEL option
Strategy: The repair includes recovery in NOARCHIVELOG mode with some data loss
Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2663793134.hm
RMAN> repair failure;
Strategy: The repair includes recovery in NOARCHIVELOG mode with some data loss
Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2663793134.hm
contents of repair script:
# database restore and recover until cancel
restore database;
recover database;
alter database open resetlogs;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 18-JAN-16
using channel ORA_DISK_1
skipping datafile 5; already restored to file /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/STONE/datafile/user01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T225352_c9sz40ps_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_18/o1_mf_nnndf_TAG20160118T225352_c9sz40ps_.bkp tag=TAG20160118T225352
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 18-JAN-16
Starting recover at 18-JAN-16
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c9kcnv60_.log
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9kcnx8j_.log
archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c9kcnz1n_.log
RMAN-08187: WARNING: media recovery until SCN 3919717 complete
Finished recover at 18-JAN-16
database opened
repair failure complete
10、使用还原点
可以为某个时间点或者SCN指定一个名字,用于point-in-time恢复或者闪回操作。
第一个例子为当前时间创建了一个还原点。
第二个例子为SCN等于100创建了一个还原点。
还原点最短保留时间由参数CONTROL_FILE_RECORD_KEEP_TIME控制,可以使用PRESERVE选项创建永久保留的还原点。
可以通过V$RESTORE_POINT视图查看还原点的名字,SCN,timestamp等信息。
例子:创建还原点
SQL> create restore point before_mods;
Restore point created.
SQL> create restore point end_q1 as of scn 3902076;
Restore point created.
SQL> select scn,time,name from v$restore_point;
SCN TIME NAME
---------- ---------------------------------------- --------------------
3656190 12-JAN-16 10.06.47.000000000 PM %RESTORE_POINT
3902336 18-JAN-16 08.09.52.000000000 PM BEFORE_MODS
3902076 18-JAN-16 08.12.00.000000000 PM END_Q1
SQL> drop restore point end_q1;
Restore point dropped.
SQL> select scn,time,name from v$restore_point;
SCN TIME NAME
---------- ---------------------------------------- --------------------
3656190 12-JAN-16 10.06.47.000000000 PM %RESTORE_POINT
3902336 18-JAN-16 08.09.52.000000000 PM BEFORE_MODS
11、执行Point-in-Time恢复
执行point-in-time恢复必须在归档模式下,具体步骤如下:
(1)指定还原目标,可以是时间、SCN、还原点或者日志序列号,如果在下午3点提交了错误的事务,则可以指定2:59作为还原点时间。
(2)设置NLS操作系统环境变量,以便对提供的时间进行正确格式化,示例如下:
$export NLS_LANG=american_america.us7ascii
$export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
(3)挂载数据库,如果数据库是打开的,需要先关闭:
RMAN>shutdown immediate
RMAN>startup mount
(4)创建和运行RUN块,RECOVER和RESTORE命令需要位于同一个RUN块内,以便UNTIL设置应用到这两个命令。示例如下:
RUN{
SET UNTIL TIME '2007-08-14 21:59:00';
RESTORE DATABASE;
RECOVER DATABASE;
}
(5)以只读模式打开数据库,检查数据确认是否恢复到指定的时间点:
RMAN>SQL 'ALTER DATABASE OPEN READ ONLY';
(6)如果满足恢复要求,使用RESETLOGS选项打开数据库:
RMAN>ALTER DATABASE OPEN RESETLOGS;
例子:执行Point-in-Time恢复
先备份
RMAN> backup database plus archivelog delete input;
Starting backup at 19-JAN-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=201 STAMP=901527711
input archived log thread=1 sequence=2 RECID=202 STAMP=901527712
input archived log thread=1 sequence=3 RECID=203 STAMP=901527714
input archived log thread=1 sequence=4 RECID=204 STAMP=901527715
input archived log thread=1 sequence=5 RECID=205 STAMP=901527716
input archived log thread=1 sequence=6 RECID=206 STAMP=901527718
input archived log thread=1 sequence=7 RECID=207 STAMP=901527744
channel ORA_DISK_1: starting piece 1 at 19-JAN-16
channel ORA_DISK_1: finished piece 1 at 19-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_19/o1_mf_annnn_TAG20160119T082224_c9v0g130_.bkp tag=TAG20160119T082224 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_1_c9v0dz9s_.arc RECID=201 STAMP=901527711
archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_2_c9v0f0b8_.arc RECID=202 STAMP=901527712
archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_3_c9v0f2jz_.arc RECID=203 STAMP=901527714
archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_4_c9v0f3fv_.arc RECID=204 STAMP=901527715
archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_5_c9v0f4s0_.arc RECID=205 STAMP=901527716
archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_6_c9v0f64w_.arc RECID=206 STAMP=901527718
archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_7_c9v0g0po_.arc RECID=207 STAMP=901527744
Finished backup at 19-JAN-16
Starting backup at 19-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/user01.dbf
channel ORA_DISK_1: starting piece 1 at 19-JAN-16
channel ORA_DISK_1: finished piece 1 at 19-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_19/o1_mf_nnndf_TAG20160119T082226_c9v0g30w_.bkp tag=TAG20160119T082226 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:06
Finished backup at 19-JAN-16
Starting backup at 19-JAN-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=208 STAMP=901527872
channel ORA_DISK_1: starting piece 1 at 19-JAN-16
channel ORA_DISK_1: finished piece 1 at 19-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_19/o1_mf_annnn_TAG20160119T082432_c9v0l0mp_.bkp tag=TAG20160119T082432 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_19/o1_mf_1_8_c9v0l075_.arc RECID=208 STAMP=901527872
Finished backup at 19-JAN-16
Starting Control File and SPFILE Autobackup at 19-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_19/o1_mf_s_901527873_c9v0l1z3_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JAN-16
创建还原点
SQL> create restore point rp20160119082559;
Restore point created.
SQL> select scn,time,name from v$restore_point;
SCN TIME NAME
---------- ---------------------------------------- --------------------
3656190 12-JAN-16 10.06.47.000000000 PM %RESTORE_POINT
3902336 18-JAN-16 08.09.52.000000000 PM BEFORE_MODS
3922886 19-JAN-16 08.26.12.000000000 AM RP20160119082559
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> delete from emp;
107 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
0
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
RMAN> run{
2> set until time "to_date('2016-01-19 08:26:12','yyyy-mm-dd hh24:mi:ss')";
3> restore database;
4> recover database;}
executing command: SET until clause
Starting restore at 19-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
skipping datafile 5; already restored to file /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9kcl362_.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c9kjvdr4_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c9kcl360_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c9kcl368_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/STONE/datafile/user01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9kcl36o_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c9kcl36f_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_19/o1_mf_nnndf_TAG20160119T082226_c9v0g30w_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_19/o1_mf_nnndf_TAG20160119T082226_c9v0g30w_.bkp tag=TAG20160119T082226
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 19-JAN-16
Starting recover at 19-JAN-16
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-JAN-16
RMAN> alter database open resetlogs;
database opened
SQL> select count(*) from emp;
COUNT(*)
----------
107
12、使用备份的控制文件执行恢复
如果丢失了所有的控制文件,可以使用备份的控制文件进行恢复,然后执行RECOVER命令,使用RESETLOGS选项打开数据库。
例子:丢失单个控制文件的恢复
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/STONE/controlfile/o1_mf_c06xh9fs_.ctl
/u01/app/oracle/fast_recovery_area/STONE/controlfile/o1_mf_c06xh9jx_.ctl
/home/oracle/o1_mf_c06xh9fs_.ctl
SQL> !rm /home/oracle/o1_mf_c06xh9fs_.ctl
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/home/oracle/o1_mf_c06xh9fs_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@oracletest ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 22:26:39 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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;
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_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_21/o1_mf_s_901723769_cb0zvt13_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_19/o1_mf_s_901577753_c9wk8sbv_.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:16
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
RMAN> recover database;
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_cb1797w5_.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_cb1797w5_.arc RECID=228 STAMP=901731367
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:56:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 3 and starting SCN of 3999686
RMAN> alter database open resetlogs;
database opened
23、相关习题:
(1)In your production database, you:
-Are using Recovery Manager (RMAN) with a recovery catalog to perform the backup operation at regular intervals Set the control file autobackup to "on"
-Are maintaining image copies of the database files
You have lost the server parameter tile (SPFILE) and the control file. Which option must you consider before restoring the SPFILIE and the control file by using the control file autobackup?
A.setting DBID for the database
B.using the RMAN SWITCH command
C.using the RMAN SWITCH command
D.starting up the database Instance In the NOMOUNT state
答案:D
(2)Which statement about recovering from the loss of a redo log group is true?
A.If the lost redo log group is ACTIVE, you should first attempt to clear the log file.
B.If the lost redo log group is CURRENT, you must clear the log file.
C.If the lost redo log group is ACTIVE, you must restore, perform cancel-based incomplete recovery,
and open the database using the RESETLOGS option.
D.If the lost redo log group is CURRENT, you must restore, perform cancel-based incomplete recovery,
and open the database using the RESETLOGS option.
答案:D
(3)You performed an incomplete recovery and opened the database with the RESETLOGS option. The LOG_ARCHIVE_FORMAT parameter is set to 'ora_%t_%s_%r.log'. Which statement regarding the archived redo log files, created in an earlier incarnation of the database, is true?
A.The archived redo log files will be overwritten.
B.The archived redo log files are deleted automatically.
C.The archived redo log files should be moved to some other location.
D.The archived redo log files are still maintained because the file names are unique.
答案:D
(4)You executed the following command:
RMAN> RECOVER COPY OF DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf';
Which statement regarding the above command is correct?
A.The '/u01/app/oracle/oradata/orcl/users01.dbf' data file is recovered from the image copy.
B.The '/u01/app/oracle/oradata/orcl/users01.dbf' data file is recovered from the last incremental backup.
C.Image copies of the '/u01/app/oracle/oradata/orcl/users01.dbf' data file are updated with all changes up to incremental backup SCN.
D.Image copies of the '/u01/app/oracle/oradata/orcl/users01.dbf' data file are recovered using the above command if data file recovery fails.
答案:C
(5)Your production database is running in archivelog mode and you are using recovery manager (RMAN) with recovery catalog to perform the database backup at regular intervals. When you attempt to restart the database instance after a regular maintenance task on Sunday, the database fails to open displaying the message that the data file belonging to the users tablespace are corrupted.
The steps to recover the damaged data files are follows:
1. Mount the database
2. Open the database
3. Recover the data file
4. Restore the data file
5. Make the data file offline
6. Make the data file online
Which option identifies the correct sequence that you must use to recover the data files?
A.2, 4, 3
B.1, 4, 3, 2
C.2, 5, 4, 3, 6
D.5, 2, 4, 3, 6
E.1, 5, 4, 3, 6, 2
答案:E
(6)You are performing incomplete recovery using RMAN. You execute the following RUN block:
RUN { SET UNTIL SCN 1107600; RESTORE DATABASE; RECOVER DATABASE; }
Which statement is true about the result?
A. RMAN restores all datafiles from the most recent backup available since the failure and applies the redo logs necessary to recover the database to SCN 1107600
B. RMAN restores all datafiles needed to restore the database through SCN 1107599 and applies the redo logs necessary to recover the database through SCN 1107599.
C. RMAN restores all datafiles and control files from the most recent backup
D. The RUN block fails because you did not specify an UNTIL clause in your RECOVER DATABASE command
答案:B
(7)A database is running in ARCHIVELOG mode and regular backups are performed. A user receives the following error message:
Which is the recommended sequence of operations you need to perform for the query successfully?
A. Drop the affected tablespace, re-create the tablespace, restore the datafiles, and the tablespace.
B. Take the affected datafile offline (if not already offline), restore the damaged image of the datafile, and then bring it online.
C. Restart the database in MOUNT mode, restore the damaged datafile, recover the datafile and then open the database with resetlogs.
D. Put the database in RESTRICTED mode, restore all the datafiles in the affected datafile and recover the tablespace, and then put the database in normal operational mode.
答案:C
(8)If a log file becomes corrupted, it may cause the database to stall. How would you correct such a situation?
A. Recover the online redo log from backup.
B. Delete and re-create the log file.
C. Use the alter database clear logfile command to clear the log file.
D. Shut down the database and restart it.
E. Shut down the database and then mount it. Clear the log file with the alter database clear logfile command and then restart the database with alter database open resetlogs.
答案:C
(9)You have lost datafiles 1 and 3 from your database, and the database has crashed. In what order should you perform the following steps to recover your database?
1. Take the datafiles that were lost offline.
2. startup mount the database
3. Issue the alter database open command.
4. Restore the datafiles that were lost
5. Recover the datafiles with the recover datafile command.
6. Bring the datafiles back online.
7. Recover the database with the recover database command.
A. 2, 1, 3, 4, 5, 6
B. 2, 4, 5, 3
C. 4, 7, 3
D. 2, 4, 7, 3
E. 2, 7, 3
答案:B
(10)Which command is used to open the database after an incomplete recovery?
A. alter database open
B. alter database open repairlog
C. alter database open resetlogs
D. alter database open resetlog
E. alter database resetlogs open
答案:C
(11)Your database has a backup that was taken yesterday (Tuesday) between 13:00 and 15:00 hours. This is the only backup you have. You have lost all the archived redo logs generated since the previous Monday, but you have archived redo logs available from the previous Sunday and earlier. You now need to restore your backup due to database loss. To which point can you restore your database?
A. 13:00 on Tuesday.
B. 15:00 on Tuesday.
C. Up until the last available archived redo log on Sunday.
D. To any point; all the redo should still be available in the online redo logs.
E. The database is not recoverable.
答案:E
(12)
What is the purpose of the until change option of the restore command?
A. It allows you to select the SCN that you want to restore to.
B. It allows you to select the log sequence number you want to restore to.
C. It allows you to select the timestamp you want to restore to.
D. It allows you to manually stop the restore at any time as online redo logs are applied.
E. None of the above.
答案:A
(13)You want the ability to recover to any time within last seven days and therefore you configured the recovery window retention policy using the conmand:
RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
After configuring the recovery window,you performed the database backup as follows:
- Backup RB1 at log sequence number 12871 on 5th Jan
- Backup RB2 at log sequence number 15622 on 5th Jan
- Backup RB3 at log sequence number 16721 on 5th Jan
On 20th Jan when the log sequence number was 18112 you realize that there is a need to recover to a point in time at the beginning of the recovery window. You have all archived redo log files to date.
Which components are needed for successful and most efficient recovery.
A. The backup RB3 and the current online redo log files
B. the backup RB2 and the archived redo log files after the log sequence number 15622
C. Backup R81 and the archived redo log hies after the log sequence number 12871
D. The backup RB3 and the archived redo log files after the log sequence number 16721
答案:D
(14)You are using Recovery Manager (RMAN) for backup and recovery operations with a recovery catalog. You have been taken database backups every evening. On November 15, 2007, at 11:30 AM, you were informed that the USER_DATA tablespace was accidentally dropped. On investigation, you found that the tablespace existed until 11:00 AM, and important transactions were done after that. So you decided to perform incomplete recovery until 11:00 AM. All the archive logs needed to perform recovery are intact. In NOMOUNT state you restored the control file that has information about the USER_DATA tablespace from the latest backup. Then you mounted the database. Identify the next set of commands that are required to accomplish the task?
A. Option A
B. Option B
C. Option C
D. Option D
答案:A
(15)Given the following RMAN commands, choose the option that reflects the order required to restore your currently operational ARCHIVELOG-mode database.
a. restore database;
b. recover database;
c. shutdown immediate
d. startup
e. restore archivelog all;
f. alter database open;
A. a, b, c, d, e, f
B. c, b, a, d, e, f
C. c, b, a, d, f
D. c, a, b, d
E. c, a, e, b, d, f
答案:E
(16)Which commands are used for RMAN database recovery? (Choose all that apply.)
A. restore
B. repair
C. copy
D. recover
E. replace
答案:AD
(17)Given a complete loss of your database, in what order would you need to perform the following RMAN operations to restore it?
a. restore controlfile
b. restore database
c. restore spfile
d. recover database
e. alter database open
f. alter database open resetlogs
A. b, a, c, d, e
B. a, c, b, d, f
C. c, a, b, d, e
D. c, a, b, d, f
E. e, a, b, d, c
答案:D
(18)If you lost your entire database, including the database spfile, control files, online redo logs, and database datafiles, what kind of recovery would be required with RMAN?
A. Complete database recovery.
B. Incomplete database recovery.
C. Approximate database recovery.
D. Archived database recovery.
E. The database could not be recovered with RMAN.
答案:B
(19)You need to restore your database back to 9/30/2008 at 18:00. In what order would you run the following commands to compete this task?
a. restore controlfile until time
`09/30/2008:18:00:00';
b. restore database until time
`09/30/2008:18:00:00';
c. restore spfile until time
`09/30/2008:18:00:00';;
d. recover database until time
`09/30/2008:18:00:00';
e. alter database open resetlogs;
f. alter database open;
A. b, d, e
B. b, d, f
C. c, a, b, d, e
D. c, a, b, d, f
E. a, b, d, e
答案:A
(20)What is the correct order of the following commands if you wanted to restore datafile 4, which was accidentally removed from the file system?
a. sql 'alter database datafile 4 online';
b. restore datafile 4;
c. recover datafile 4;
d. sql 'alter database datafile 4 offline';
e. startup
f. shutdown
A. a, c, b, d
B. d, b, c, a
C. f, d, b, c, a, e
D. c, a, b, d, f
E. a, b, d, e
答案:B
(21)Your database is up and running and one of your three control files is accidentally erased. You start RMAN and run the following command:
RESTORE CONTROLFILE FROM AUTOBACKUP;
Which of the following statements is true? (Choose all that apply.)
A. The command restores only the missing control file.
B. The command restores all the control files.
C. The command fails because the database is running.
D. This is the correct way to address this problem.
E. This is not the correct way to address this problem.
答案:CE
(22)Which of the following are valid until command options when attempting point-in-time recovery in RMAN? (Choose all that apply.)
A. until time
B. until change
C. until sequence
D. until SCN
E. until commit
答案:ACD
(23)You have backed up your database using image copies. You have lost the SYSTEM tablespace and need to restart your database as quickly as possible. What is the correct solution?
A. Restore the SYSTEM tablespace from the last backup set and then recover the database.
B. Restore the SYSTEM tablespace image copy using the restore command and then restore the database.
C. Use the switch datafile command to instantly switch to the datafile copy, recover the tablespace, and open the database.
D. The database is not recoverable in this situation with image copies.
E. Manually copy the datafile image copy to the correct location and then manually restore the database from SQL*Plus.
答案:C
(24)If you find errors in the view V$DATABASE_BLOCK_CORRUPTION with a status of MEDIA_CORRUPT, what RMAN command would you run to correct the problem?
A. recover lost blocks;
B. recover corrupt blocks;
C. recover media corrupt blocks from list;
D. recover corrupt blocks from list;
E. recover corruption list;
答案:E
(25)What will be the end result of this set of RMAN commands?
shutdown abort
startup mount
restore datafile 4 until time ‘09/30/2008:15:00:00’;
recover datafile 4 until time ‘09/29/2008:15:00:00’;
alter database open resetlogs;
A. Datafile 4 will be recovered until 9/30/2008 at 15:00 and the database will open.
B. The restore command will fail.
C. The recover command will fail.
D. The alter database open resetlogs command will fail.
E. All these commands will fail because they must be in the confines of a run block.
答案:D
(26)Which of the following represents the correct way to perform an online recovery of datafile 4, which is assigned to a tablespace called USERS?
A. shutdown restore datafile 4; recover datafile 4; alter database open;
B. Sql alter database datafile 4 offline; restore datafile 4; recover datafile 4;
alter database open;
C. Sql alter database datafile 4 offline; restore datafile 4; Sql alter database datafile 4 online;
D. Sql alter database datafile 4 offline; restore database datafile 4; recover database datafile 4;
Sql alter database datafile 4 online;
E. Sql alter database datafile 4 offline; restore datafile 4; recover datafile 4;
Sql alter database datafile 4 online;
答案:E
(27)David managed to accidentally delete the datafiles for database called DSL. He called Heber and Heber tried to help but he managed to delete the control files of the database. Heber called Bill and Bill saved the day. They are using a recovery catalog for this database. What steps did Bill perform to recover the database and in what order?
a. Restored the control file with the RMAN restore controlfile command.
b. Mounted the DSL instance with the alter database mount command.
c. Restored the datafiles for the DSL database with the RMAN restore command.
d. Opened the DSL database with the alter database open resetlogs command.
e. Recovered the datafiles for the DSL database with the RMAN recover command.
f. Started the DSL instance.
g. Connected to the recovery catalog with RMAN.
A. a, b, c, d, e, f, g
B. b, c, d, g, f, e, a
C. g, f, a, b, c, e, d
D. c, a, d, b, f, e, g
E. g, f, a, b, e, c, d
答案:C
(28)When performing a full database disaster recovery with RMAN, in what order would you execute these steps?
a. Restore the control file from autobackups.
b. Run the RMAN restore and recover command.
c. Restore the database spfile from autobackups.
d. Make the RMAN backup set pieces available.
e. Open the database with the alter database open resetlogs command.
f. Open the database with the alter database open command.
A. a, b, c, d, e, f
B. c, d, a, b, f
C. d, c, a, b, f
D. d, b, d, c, e
E. d, c, a, b, e
答案:E
(29)A database is running In ARCHIVELOG mode. It has two online redo log groups and each group has one member.
A LGWR Input/output (I/O) fells due to permanent media failure that has resulted in the loss of redo log file and the LWGR terminates causing the instance to crash. The steps to recover from the loss of a current redo log group member in the random order are as follow.
1) Restore the corrupted redo log group.
2) Restore from a whole database backup.
3) Perform incomplete recovery.
4) Relocate by renaming the member of the damaged online redo log group to a new location.
5) Open the database with the RESETLOGS option.
6) Restart the database instance.
7) Issue a checkpoint and clear the log.
Identify the option with the correct sequential steps to accomplish the task efficiently.
A. 1, 3, 4, and 5
B. 7, 3, 4. and 5
C. 2, 3, 4, and 5
D. 7, 4, 3. and 5
E. Only 6 is required
答案:B
(30)Your database is running In ARCIIIVELOG mode. One of the data files, USBRDATAOI. DBF, in the users tablespace is damaged and you need to recover the file until the point of failure. The backup for the data file is available.
Which three files would be used in the user-managed recovery process performed by the database administrator (DBA)? (Choose three.)
A. redo logs
B. control file
C. temporary files of temporary tablespace
D. the latest backup of only the damaged data file
E. the latest backup of all the data files In the USERS tablespace
答案:ABD
(31)Which of the following are true concerning block media recovery? (Choose all that apply.)
A. Any gap in archive logs ends the recovery.
B. If a gap in archive logs is encountered, RMAN will search forward for newer versions of the blocks that are not corrupt.
C. Uncorrupted blocks from the flashback logs may be used to speed recovery.
D. The database can be in NOARCHIVELOG mode.
E. None of the above.
答案:BC
(32)Your database has experienced a loss of datafile users_01.dbf, which is associated with tablespace called USERS.The database is still running. Which answer properly describes the order of the steps that you would use to recover from this error?
a. Shut down the database.
b. Take the users_01.dbf datafile offline with the alter database command.
c. Restore the users_01.dbf datafile from backup media with the required archived redo logs.
d. Restore all users tablespace-related datafiles from backup media.
e. Issue the recover tablespace users command.
f. Issue the recover datafile users_01.dbf command.
g. Start up the database.
h. Bring the users_01.dbf datafile online with the alter database command.
A. a, c, f, g
B. b, c, f, h
C. a, b, c, f, g
D. a, b, c, f, g, h
E. b, c, f, e, g
答案:B
(33)As soon as you discover that you have lost an online redo log, if the database is still functioning, what should be your first action?
A. Shut down the database
B. Clear the online redo log
C. Back up the database
D. Checkpoint the database
E. Call Oracle support
答案:D
(34)You have lost all your SYSTEM tablespace datafiles (system_01.dbf and system_02.dbf) and the database has crashed. What would be the appropriate order of operations to correct the situation?
a. Mount the database with the startup mount command.
b. Take the SYSTEM datafile offline with the alter database command.
c. Restore the SYSTEM_01.dbf datafile from backup media with the required archived redo logs.
d. Restore all SYSTEM tablespace-related datafiles from backup media.
e. Issue the recover tablespace SYSTEM command.
f. Issue the recover datafile SYSTEM_01.dbf command.
g. Open the database with the alter database open command.
h. Open the database with the alter database open resetlogs command.
A. a, c, f, g
B. b, d, e, h
C. a, b, c, f, g
D. d, a, e, g
E. b, c, f, e, g
答案:D
(35)You have discovered that one of three control files has been lost. What steps would you follow to recover that control file?
a. Shut down the database.
b. Restore a control-file copy from backup media.
c. Use the create control file command to create a new control file.
d. Copy the backup control file into place.
e. Create a new copy of the control file from one of the surviving control files.
f. Recover the database using the recover database using backup control file command.
g. Start up the database.
A. a, b, f, g
B. c, f, g
C. a, d, f, g
D. a, f, g
E. a, e, g
答案:E
(36)Which files will you need to perform a full recovery of a database backed up in NOARCHIVELOG mode? (Choose all that apply.)
A. Database datafiles
B. Control files
C. Archived redo logs
D. Online redo logs
E. Flashback logs
答案:ABD
(37)Which are the correct steps, in order, to deal with the loss of an online redo log if the database has not yet crashed?
a. Issue a checkpoint.
b. Shut down the database.
c. Issue an alter database open command to open the database.
d. Startup mount the database.
e. Issue an alter database clear logfile command.
f. Recover all database datafiles.
A. a, b, c, d
B. b, d, e, c
C. a, b, d, e, c
D. b, f, d, f, c
E. b, d, a, c
答案:C
(38)What methods of point-in-time recovery are available? (Choose all that apply.)
A. Change-based
B. Cancel-based
C. Time-based
D. Sequence number-based
E. Transaction number-based
答案:ABCD
(39)Which files are required for a full recovery of the database in ARCHIVELOG mode? (Choose three.)
A. Database datafiles
B. Online redo logs
C. Archived redo logs
D. Backup control file
E. Control file from a backup
答案:ACD
(40)What is the proper procedure to recover a lost tempfile?
A. Restore the backup copy of the tempfile from the backup media.
B. Re-create the tempfile with the create tempfile command.
C. Copy an existing tempfile from another database.
D. Re-create the tempfile with the create tablespace command.
E. Re-create the tempfile with the alter tablespace command.
答案:E
(41)Upon starting your database, you receive the following error:
SQL> startup
ORACLE instance started.
Total System Global Area 171581440 bytes
Fixed Size 1298640 bytes
Variable Size 146804528 bytes
Database Buffers 20971520 bytes
Redo Buffers 2506752 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle01/oradata/orcl/redo01.log'
ORA-00312: online log 1 thread 1: '/oracle01/oradata/orcl/redo01a.log'
You can choose from the following steps:
a. Restore the database datafiles.
b. Issue the alter database clear unarchived logfile group 1 command.
c. Issue the alter database open command.
d. Issue the alter database open resetlogs command.
e. Recover the database using point-in-time recovery.
f. Issue the Startup Mount command to mount the database.
g. Back up the database.
Which is the correct order of these steps in this case?
A. a, f, e, d, g
B. f, e, d
C. f, b, c, g
D. a, f, c
E. The database cannot be recovered.
(42)A user sends you an email with the following error message: create table idtable(id number) * ERROR at line 1: ORA- 01110: data file 4: ‘/oracle01/oradata/orcl/users01.dbf’ ORA-27041:01116: error in opening database file 4 ORA-unable to open file Linux Error: 2: No such file or directory Additional information:
You can choose from the following steps:
a. Restore the missing database datafiles.
b. Take the missing datafile offline.
c. Shut down the database.
d. Issue the recover tablespace USERS command.
e. Issue the Startup Mount command to mount the database.
f. Bring the USERS tablespace online.
g. Issue the alter database open command.
Which is the correct order of these steps in this case?
A. b, a, d, f
B. c, a, e, b, d, f, g
C. c, e, d, g
D. b, d, f
E. e, d, g
答案:A
(43)You have lost all your database control files. To recover them, you are going to use the results of the alter database backup controlfile to trace command. Your datafiles and your online redo logs are all intact. Which of the following is true regarding your recovery?
A. You will need to open the database with the resetlogs command.
B. All you need to do is execute the trace file from SQL*Plus and it will perform the recovery for you.
C. You will use the resetlogs version of the create controlfile command.
D. You will use the noresetlogs version of the create controlfile command.
E. You will use the trace file to create a backup control file, and then you will recover the database with the recover database using backup controlfile command
答案:D
(44)Your developers have asked you to restore the development database, which is in NOARCHIVELOG mode, back to last Tuesday the 20th. Your last backup is from Monday the 19th.
What do you do?
A. Restore the 19ths backup, restore all archived redo logs, recover the database to the 20th, and open the database.
B. Tell them that their request cannot be met with the current backup strategy.
C. Restore the 19ths backup, apply the online redo logs, and open the database.
D. Switch the database into ARCHIVELOG mode, restore the 19th's backup, restore all archived redo logs, and recover the database to the 20th.
E. Use the recover database command to roll back the database from today to the 19th of the month.
答案:B
(45)What methods are available to recover lost control files? (Choose all that apply.)
A. Backup control file.
B. Emergency control file.
C. The create controlfile command.
D. The restore controlfile RMAN command.
E. No backup is required. The database will re-create the control file when it is discovered to be lost.
答案:CD
(46)Your ARCHIVELOG-mode database has lost three datafiles and shut down. One is assigned to the SYSTEM tablespace and two are assigned to the USERS tablespace. You can choose from the following steps to recover your database:
a. Restore the three database datafiles that were lost.
b. Issue the Startup Mount command to mount the database.
c. Issue the alter database open command.
d. Issue the alter database open resetlogs command.
e. Recover the database using the recover database command.
f. Recover the datafiles with the recover datafile command.
g. Take the datafiles offline.
Which is the correct order of these steps in this case?
A. a, b, e, c
B. b, e, d
C. a, b, d, c
D. b, g, c, f
E. a, b, d, f
答案:A
(47)You have lost all your online redo logs. As a result, your database has crashed. You have tried to restart the database and clear the online redo log files, but when you try to open the database you get the following error.
SQL> startup
ORACLE instance started.
Total System Global Area 167395328 bytes
Fixed Size 1298612 bytes
Variable Size 142610252 bytes
Database Buffers 20971520 bytes
Redo Buffers 2514944 bytes Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02a.log’
ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02.log’
ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database clear logfile group 2;
alter database clear logfile group 2 * ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02.log’
ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02a.log’
What steps must you take to resolve the error?
a. Issue the recover database redo logs command.
b. Issue the Startup Mount command to mount the database.
c. Restore the last full database backup.
d. Perform a point-in-time recovery, applying all archived redo logs that are available.
e. Restore all archived redo logs generated during and after the last full database backup.
f. Open the database using the alter database open resetlogs command.
g. Issue the alter database open command.
A. b, a, f
B. e, b, a, f
C. e, b, a, g
D. b, a, g
E. c, e, b, d, f
答案:E
(48)What does the SCN represent?
A. The system change number, which is a point in time relative to transactions within a given database.
B. A number that represents time. Thus, at 1300 hours, the SCN is the same on all databases.
C. The security change number, which represents the security code that is needed to access any database structure.
D. A conversion factor that converts internal database time to external clock time.
E. UTC time in the database, providing a standardized way of tracking time in Oracle.
答案:A
(49)You have lost datafile 4 from your database. Which is typically the fastest way to restore your database?
A. Restore and recover the datafile.
B. Restore and recover the tablespace.
C. Restore and recover the database.
D. Restore and recover the control file
E. Restore and recover the parameter file.
答案:A
(50)You are trying to recover your database. During the recovery process, you receive the following error:
ORA-00279: change 5033391 generated at 08/17/2008 06:37:40 needed for thread 1
ORA-00289: Suggestion: /oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17/o1_mf_1_11_%u_.arc
ORA-00280: change 5033391 for thread 1 is in sequence #11
ORA-00278: logfile ‘/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17/o1_mf_1_10_4bj6wnqm_.arc no longer needed for this recovery Specify log :{< RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log ‘/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17/o1_mf_1_11_%u_.arc’
ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3
How do you respond to this error? (Choose two.)
A. Restore the archived redo log that is missing and attempt recovery again.
B. Recovery is complete and you can open the database.
C. Recovery needs redo that is not available in any archived redo log.
Attempt to apply an online redo log if available.
D. Recover the entire database and apply all archived redo logs again.
E. Recovery is not possible because an archived redo log has been lost.
答案:AC
(51)During recovery, you need to know if log sequence 11 is in the online redo logs, and if so, you need to know the names of the online redo logs so you can apply them during recovery. Which view or views would you use to determine this information? (Choose all that apply.)
A. V$LOGFILE
B. V$RECOVER_LOG
C. V$RECOVER_DATABASE
D. V$LOG_RECOVER
E. V$LOG
答案:AE
(51)You need to perform a block media recovery on the tools01.dbf data file in the SALES database by using Recovery Manager (RMAN).
Which two are the prerequisites to perform this operation? (Choose two)
A. You must configure block change tracking file
B. You must have first level 1 backups for RMAN to restore blocks
C. You must ensure that the SALES database is mounted or open
D. You must have full or level 0 backups for RMAN to restore blocks
E. You must take the tools01.dbf data file offline before you start a block media recovery
答案:CD
(52)You realize that the control file is damaged in your production database. After restoring the control file from autobackup, what is the next step that you must do to proceed with the database recovery?
A. Mount the database
B. Open the database in NORMAL mode
C. Open the database in RESTRICTED mode
D. Open the database with the RESETLOGS option
答案:A
(53)Examine the following scenario:
-Database is running in ARCHIVELOG mode.
-Complete consistent backup is taken every Sunday.
-On Tuesday the instance terminates abnormally because the disk on which control files are located gets corrupted
The disk having active online redo log files is also corrupted.
The hardware is repaired and the paths for online redo log files and control files are still valid.
Which option would you use to perform the recovery of database till the point of failure?
A. Restore the latest whole backup, perform complete recovery, and open the database normally
B. Restore the latest whole backup, perform incomplete recovery, and open the database with the RESETLOGS option.
C. Restore the latest backups control file, perform complete recovery, and open the database with the RESETLOGS option.
D. Restore the latest backup control file, perform incomplete recovery using backup control file, and open the database with the RESETLOGS option.
答案:D
(54)In Recovery Manager (RMAN), you are taking image copies of the data files of your production database and rolling them forward at regular intervals. You attempt to restart your database. After a regular maintenance task, you realize that one of the data files that belongs to the USERS tablespace is damaged and you need to recover the data file by using the image copy. Because a media failure caused the data file to be damaged, you want to place the data file in a different location while restoring the file.
Which option must you consider for this task?
A. using only the RMAN SWITCH command to set the new location for the data file
B. placing the database in the MOUNT state for the restore and recovery operations.
C. using an RMAN RUN block with the SET NEWNAME and then the SWITCH command.
D. configuring two channels: one for the restore operation and the other for the recovery operation
答案:C
(55)The database is running in the ARCHIVELOG mode. It has three redo log groups with one member each. One of the redo log groups has become corrupted. You have issued the following command during the recovery of a damaged redo log file:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Which action should you perform immediately after using this command?
A. You should perform a log switch
B. You should make a backup of the database
C. You should switch the database to the NONARCHIVELOG mode
D. You should shut down the database instance and perform a complete database recovery
答案:B
(56)You are using Recovery Manager (RMAN) with a recovery catalog to back up your production database. The backups and the archived redo log files are copied to a tape drive on a daily basis. Because of media failure, you lost your production database completely along with the recovery catalog database. You want to recover the target database and make it functional. You consider performing the following steps to accomplish the task:
1) Restore an autobackup of the server parameter file.
2) Restore the control file
3) Start the target database instance
4) Mount the database
5) Restore the data files
6) Open the database with RESETLOGS option
7) Recover the data files
8) Set DBID for the target database
Which option illustrates the correct sequence that you must use?
A. 8, 1, 3, 2, 4, 5, 7, 6
B. 1, 8, 3, 4, 2, 5, 7, 6
C. 1, 3, 4, 2, 8, 5, 6, 7
D. 1, 3, 2, 4, 6, 5, 7, 8
答案:A
(57)The database is currently open and the temp03.dbf tempfile belonging to the default temporary tablespace TEMP has been corrupted. What steps should you take to recover from this tempfile loss in an efficient manner?
A. Allow the database to continue running, drop the TEMP tablespace, and then re-create it with new tempfiles
B. Shut down the database, restore and recover the tempfile from backup, and then open the database with RESETLOGS
C. Allow the database to continue running, take the TEMP tablespace offline, drop the missing tempfile, and then create a new tempfile
D. Allow the database to continue running, add a new tempfile to TEMP tablespace with a new name, and drop the tempfile that has been corrupted.
答案:D
(58)After you have restored and recovered a database to a new host by using a previously performed Recovery Manager (RMAN) backup, which is the best option you would consider for the new database?
A. Opening the database in RESTRICTED mode
B. Opening the database with the RESETLOGS option
C. Setting a new DBID for the newly restored database
D. Restoring the server parameter file (SPFILE) to the new host
答案:B
(59)In Recovery Manager (RMAN), you are taking image copies of the datafiles of your production database and rolling them forward as regular intervals. You attempt to restart your database instance after a regular maintenance task, you realize that one of the data files that belongs to the USERS tablespace is damaged and you need to recover the datafile by using the image copy.
You could perform the following steps to accomplish this:
1) Mount the database
2) Take the data file offline
3) Bring the data file online
4) Use the RMAN SWITCH TO command to switch the image copy
5) Apply the archived redo logs
6) Open the database
7) Use the RMAN RESTORE TO command to switch to the image copy
Which two options illustrate the correct sequence of steps that you could follow? (Choose two.)
A. 2, 6, 4, 5, 3
B. 1, 2, 4, 5, 3, 6
C. 1, 2, 4, 6, 3
D. 1, 2, 7, 5, 3, 6
答案:BD
(60) Your production database it functional on the SHOST1 host. You are backing up the production database by using Recovery Manager (RMAN) with the recovery catalog. You want to replicate the production database to anther host , SHOST2, for testing new applications.
After you ensured that the backups of the target database are accessible on the new host, what must you do to restore and recover the backup for the test environment?
A.Restoring the control file from the backup by using the NOCATALOG option to restore, and recovering the data files
B.Restoring the data files by using the NOCATALOG option and using the SET NEWNAME command to change the location
C.Restoring the server parameter file from the backup by using the recovery catalog to restore, and recovering the data files
D.Restoring the data files from the backup by using the recovery catalog to recover the files, and using the SWITCH command to change the location.
答案:A
(61)Your database is running in ARCHIVELOG mode. You have been taking backups of all the data files and control files regularly.
You are informed that some important tables in the BILLING tablespace have been dropped on February 28, 2007 at 10.30 AM and must be recovered.
You decide to perform an incomplete recovery using the following command: SQL> RECOVER DATABASE UNTIL TIME ‘2007-02-28:10:15:00’;
Identify the files that must be restored to recover the missing tables successfully.
A.Restore the backup of all the data files.
B.Restore the backup of all the data files and the control file.
C.Restore the backup of only the data files that contain the dropped tables.
D.Restore the backup of all the data files belonging to the tablespace containing the dropped tables.
答案:A
(62)In your test database:
-You are using Recovery Manager (RMAN) to perform incremental backups of your test database
-The test database is running in NOARCHIVELOG mode
-One of the data files is corrupted
-All online redo log files are lost because of a media failure
Which option must you consider in this scenario?
A.Configuring the database in ARCHVIELOG mode and then using incremental backup to recover the database
B.Using incremental backup to recover the damaged data file and then manually creating the online redo log files
C.Creating a new test database because the database is not recoverable due to the fact that the database is configured in NOARCHIVELOG mode
D.Using incremental backups to recover the database by using the RECOVER DATABASE NOREDO command and then using the RESETLOGS option to open the database.
答案:D
(63)In your production database, you:
-Are using Recovery Manager (RMAN) with a recovery catalog to perform the backup operation at regular intervals
-Set the control file autobackup to “on”
-Are maintaining image copies of the database files
You have lost the server parameter file (SPFILE) and the control file. Which option must you consider before restoring the SPFILE and the control file by using the control file autobackup?
A.Setting DBID for the database
B.Using the RMAN SWITCH command
C.Using the RMAN SRT NEWNAME command
D.Starting up the database instance in the NOMOUNT state
答案:A
(64)A database has three online redo log groups with one member each. A redo log member with the status ACTICE is damages while the database is running.
What is the first step you should take to solve this problem?
A.Attempt to Issue a checkpoint.
B.Restart the database using the RESETLOGS option.
C.Drop the redo log number and create it in a different location.
D.Perform and incomplete recovery up to the most recent available redo log.
答案:A
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-2097733/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-2097733/