课程目标：
- 主要的恢复操作
- 备份和恢复控制文件
- 恢复重做日志组
1、还原和恢复
![clipboard[75]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_14628539769tyn.png "clipboard[75]")
Oracle中的备份和恢复中的恢复包括2方面：
还原（Restore）：拷贝备份文件到数据库可以使用的位置。
恢复（Recover）：应用重做日志到指定时间点。
2、文件丢失的原因
![clipboard[76]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853978mjEQ.png "clipboard[76]")
丢失或者损坏文件的原因有：
- 用户错误：误删除或覆盖文件
- 应用错误：应用逻辑错误导致文件丢失或者损坏
- 介质故障：磁盘或者控制器故障导致文件丢失或者损坏
3、关键与非关键
![clipboard[77]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853980Bvcm.png "clipboard[77]")
对于数据库来讲，非关键文件是指即使该文件不存在，数据库也可以继续运行。例如，丢失了日志组中的某一个日志文件，仍然可以使用剩余的日志文件，保持数据库继续运行。
即使非关键文件丢失不会导致数据库崩溃，但是还是会影响数据库稳定高效运行，例如：
- 丢失索引表空间会导致应用和查询变慢，甚至不可用
- 非当前联机日志组丢失，在生成新的日志文件之前数据库会挂起
- 临时表空间丢失会导致用户不能运行某些查询或者创建索引
4、自动临时文件恢复
![clipboard[78]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853981eesg.png "clipboard[78]")
临时表空间的临时文件丢失或者损坏，会导致需要使用临时表空间进行排序的SQL语句执行失败。
如果实例启动时候没有临时文件，那么数据库会自动创建，并在告警日志中增加如下信息：
Re-creating tempfile /u01/app/oracle/oradata/orcl/temp01.dbf
也可以使用如下命令手工重建临时文件：
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 20M;
SQL> ALTER TABLESPACE temp DROP TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf';
例子：数据库重启自动创建临时文件
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
-------------------- ---------------------------------------------------------------------- ---------------
TEMP /u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_c06xhsm8_.tmp 29
SQL> !rm /u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_c06xhsm8_.tmp
SQL> insert into emp select * from emp;
876544 rows created.
SQL> select * from emp order by 1,2,3,4,5,6,7,8,9;
select * from emp order by 1,2,3,4,5,6,7,8,9
*
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_c06xhsm8_.tmp'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown immediate;
Database closed.
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.
Database opened.
数据库重启后自动重建一个新的临时文件并删除无效的记录。
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
-------------------- ---------------------------------------------------------------------- ---------------
TEMP /u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_c9dnkhxd_.tmp 20
例子：手工重建临时文件
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
-------------------- ---------------------------------------------------------------------- ---------------
TEMP /u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_c9dnkhxd_.tmp 20
SQL> !rm /u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_c9dnkhxd_.tmp
SQL> alter tablespace temp add tempfile size 20M;
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
-------------------- ---------------------------------------------------------------------- ---------------
TEMP /u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_c9dnkhxd_.tmp 20
TEMP /u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_c9dnt4fs_.tmp 20
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_c9dnkhxd_.tmp';
Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024
-------------------- ---------------------------------------------------------------------- ---------------
TEMP /u01/app/oracle/oradata/STONE/datafile/o1_mf_temp_c9dnt4fs_.tmp 20
5、日志组状态：回顾
![clipboard[79]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853983rTLt.png "clipboard[79]")
日志组有以下三种循环状态：
- CURRENT：LGWR进程正在写入的日志组
- ACTIVE：不再写入，实例恢复需要该组日志，等待执行检查点
- INACTIVE：不再写入，实例恢复也不需要该组日志，已经执行了检查点
6、丢失联机日志组的恢复
![clipboard[80]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853984HweK.png "clipboard[80]")
（1）丢失的日志组是INACTIVE状态，如果介质修复了，那么就可以继续使用，如果介质不能修复，可以清除日志文件进行重建。
（2）丢失的日志组是ACTIVE状态，如果可以执行检查点，则表示不再需要它进行实例恢复，会到INACTIVE状态，后续操作与INACTIVE一样。
（3）丢失的日志组是CURRENT状态，实例会崩溃，只能还原备份，执行cacel-based point-in-time恢复，使用RESETLOGS选项打开数据库。
例子：丢失INACTIVE日志组
SQL> select l.group#,l.status,archived,member from v$log l join v$logfile lf on l.group#=lf.group# order by 1;
GROUP# STATUS ARC MEMBER
---------- ---------------- --- --------------------------------------------------------------------------------
1 INACTIVE YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_1_c06xh9t5_.log
1 INACTIVE YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c06xh9xt_.log
2 INACTIVE YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_2_c06xhdlj_.log
2 INACTIVE YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c06xhdnc_.log
3 CURRENT NO /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_3_c06xhj1g_.log
3 CURRENT NO /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c06xhj37_.log
6 rows selected.
SQL> !rm /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_1_c06xh9t5_.log
SQL> !rm /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c06xh9xt_.log
SQL> alter database clear logfile group 1;
Database altered.
SQL> select l.group#,l.status,archived,member from v$log l join v$logfile lf on l.group#=lf.group# order by 1;
GROUP# STATUS ARC MEMBER
---------- ---------------- --- --------------------------------------------------------------------------------
1 UNUSED YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_1_c9ds6g3g_.log
1 UNUSED YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c9ds6g7o_.log
2 INACTIVE YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_2_c06xhdlj_.log
2 INACTIVE YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c06xhdnc_.log
3 CURRENT NO /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_3_c06xhj1g_.log
3 CURRENT NO /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c06xhj37_.log
6 rows selected.
例子：丢失ACTIVE日志组
SQL> select l.group#,l.status,archived,member from v$log l join v$logfile lf on l.group#=lf.group# order by 1;
GROUP# STATUS ARC MEMBER
---------- ---------------- --- --------------------------------------------------------------------------------
1 INACTIVE YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_1_c9ds6g3g_.log
1 INACTIVE YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c9ds6g7o_.log
2 ACTIVE YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_2_c06xhdlj_.log
2 ACTIVE YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c06xhdnc_.log
3 CURRENT NO /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_3_c9dt1dc8_.log
3 CURRENT NO /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c9dt1dfx_.log
6 rows selected.
SQL> !rm /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_2_c06xhdlj_.log
SQL> !rm /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c06xhdnc_.log
SQL> alter system checkpoint;
System altered.
SQL> alter database clear logfile group 2;
Database altered.
SQL> select l.group#,l.status,archived,member from v$log l join v$logfile lf on l.group#=lf.group# order by 1;
GROUP# STATUS ARC MEMBER
---------- ---------------- --- --------------------------------------------------------------------------------
1 INACTIVE YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_1_c9ds6g3g_.log
1 INACTIVE YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c9ds6g7o_.log
2 UNUSED YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_2_c9dtbvbt_.log
2 UNUSED YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9dtbvhf_.log
3 CURRENT NO /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_3_c9dt1dc8_.log
3 CURRENT NO /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c9dt1dfx_.log
6 rows selected.
例子：丢失CURRENT日志组
SQL> select l.group#,l.status,archived,member from v$log l join v$logfile lf on l.group#=lf.group# order by 1;
GROUP# STATUS ARC MEMBER
---------- ---------------- --- --------------------------------------------------------------------------------
1 INACTIVE YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_1_c9ds6g3g_.log
1 INACTIVE YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c9ds6g7o_.log
2 UNUSED YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_2_c9dtbvbt_.log
2 UNUSED YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9dtbvhf_.log
3 CURRENT NO /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_3_c9dt1dc8_.log
3 CURRENT NO /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c9dt1dfx_.log
6 rows selected.
SQL> !rm /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_3_c9dt1dc8_.log
SQL> !rm /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c9dt1dfx_.log
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance stone (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/STONE/onlinelog/o1_mf_3_c9dt1dc8_.log'
ORA-00312: online log 3 thread 1: '/u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c9dt1dfx_.log'
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> select l.group#,l.status,archived,member from v$log l join v$logfile lf on l.group#=lf.group# order by 1;
GROUP# STATUS ARC MEMBER
---------- ---------------- --- --------------------------------------------------------------------------------
1 INACTIVE YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_1_c9ds6g3g_.log
1 INACTIVE YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c9ds6g7o_.log
2 CURRENT NO /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_2_c9dtbvbt_.log
2 CURRENT NO /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9dtbvhf_.log
3 UNUSED YES /u01/app/oracle/oradata/STONE/onlinelog/o1_mf_3_c9dthbft_.log
3 UNUSED YES /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c9dthblx_.log
6 rows selected.
7、清除日志文件
![clipboard[81]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853985W447.png "clipboard[81]")
清除日志文件使用如下命令：
ALTER DATABASE CLEAR [UNARCHIVED] LOGFILE GROUP
[UNRECOVERABLE DATAFILE]
如果日志文件已经归档，则使用最简单的格式：
ALTER DATABASE CLEAR LOGFILE GROUP
使用如下语句查询日志组是否归档：
SQL> SELECT GROUP#, STATUS, ARCHIVED FROM V$LOG;
例如使用如下语句清除已经归档的重做日志组3并重建：
SQL> ALTER DATABASE CLEAR LOFGILE GROUP 3;
如果日志文件没有归档，则必须使用UNARCHIVED关键字，确认放弃这一部分重做日志的恢复，然后需要对数据库进行备份，这样后续就不会需要这一部分重做日志了。
恢复一个当前offline的数据文件是有可能需要重做日志的，使用UNRECOVERABLE DATAFILE进行恢复。
8、丢失索引表空间的恢复
![clipboard[82]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853986P8Vd.png "clipboard[82]")
索引表空间的数据文件丢失，执行以下步骤恢复：
（1）Drop数据文件。
（2）Drop表空间。
（3）重建索引表空间。
（4）重建索引。
9、重建索引
![clipboard[83]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853987rWyy.png "clipboard[83]")
使用以下关键字创建索引，缩短创建时间：
- PARALLEL：默认是不并行，使用并行可以让多个处理器同时用于创建索引，速度更快。
- NOLOGGING：只会产生非常少量的重做日志记录，也可以通过ALTER INDEX NOLOGGING/LOGGING命令进行修改。
如果使用了Data Guard或者在数据库或表级使用FORCE LOGGING，则会覆盖NOLOGGING设置。
如果索引丢失了，相比恢复，创建索引有时会更快和更简单。可以使用Data Pump Export命令以及CONTENT=METADATA_ONLY参数创建只包含重建索引的SQL语句的dump文件，再使用Data Pump Import命令及SQLFILE=参数导入dump文件。
例子：丢失索引表空间
SQL> create tablespace test_index datafile size 10M;
Tablespace created.
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------------------------------------
USERS /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf
UNDOTBS1 /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf
SYSAUX /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf
SYSTEM /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c06xczt5_.dbf
TEST_INDEX /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g2k2h2_.dbf
EXAMPLE /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c06xj0bk_.dbf
RCAT_TS /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c8vd4mmy_.dbf
7 rows selected.
SQL> create user ind identified by ind default tablespace test_index;
User created.
SQL> grant dba to ind;
Grant succeeded.
SQL> conn ind/ind
Connected.
SQL> create table emp as select * from hr.employees where 1=0;
Table created.
SQL> create index index_1 on emp(last_name) tablespace test_index nologging;
Index created.
[oracle@oracletest ~]$ expdp ind/ind directory=data_pump_dir dumpfile=test.dmp
Export: Release 11.2.0.4.0 - Production on Thu Jan 14 11:28:39 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "IND"."SYS_EXPORT_SCHEMA_01": ind/******** directory=data_pump_dir dumpfile=test.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "IND"."EMP" 0 KB 0 rows
^[[AMaster table "IND"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for IND.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/stone/dpdump/test.dmp
Job "IND"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jan 14 11:29:04 2016 elapsed 0 00:00:25
[oracle@oracletest ~]$ impdp ind/ind directory=data_pump_dir dumpfile=test.dmp sqlfile=createindex.sql
Import: Release 11.2.0.4.0 - Production on Thu Jan 14 11:29:12 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "IND"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "IND"."SYS_SQL_FILE_FULL_01": ind/******** directory=data_pump_dir dumpfile=test.dmp sqlfile=createindex.sql
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "IND"."SYS_SQL_FILE_FULL_01" successfully completed at Thu Jan 14 11:29:15 2016 elapsed 0 00:00:02
[oracle@oracletest ~]$ cat /u01/app/oracle/admin/stone/dpdump/createindex.sql
-- CONNECT IND
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "IND" IDENTIFIED BY VALUES 'S:BDCE451E685962D89DC91F33FFFA101C70087BA591000F074BA73C684E64;83FE70BA53E35C65'
DEFAULT TABLESPACE "TEST_INDEX"
TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "IND";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
GRANT "DBA" TO "IND";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "IND" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT IND
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'STONE', inst_scn=>'3758719');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "IND"."EMP"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20 BYTE),
"LAST_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE,
"EMAIL" VARCHAR2(25 BYTE) NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20 BYTE),
"HIRE_DATE" DATE NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "TEST_INDEX" ;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "IND"."INDEX_1" ON "IND"."EMP" ("LAST_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
TABLESPACE "TEST_INDEX" PARALLEL 1 ;
ALTER INDEX "IND"."INDEX_1" NOPARALLEL;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60);
I_O VARCHAR2(60);
NV VARCHAR2(1);
c DBMS_METADATA.T_VAR_COLL;
df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';
stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
DELETE FROM "SYS"."IMPDP_STATS";
i_n := 'INDEX_1';
i_o := 'IND';
EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,0,0,0,0,0,0,0,0,NV,NV,TO_DATE('2016-01-14 11:28:28',df),NV;
DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !rm /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g2k2h2_.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 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g2k2h2_.dbf'
SQL> alter database datafile 6 offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> drop tablespace test_index including contents;
Tablespace dropped.
SQL> create tablespace test_index datafile size 10M;
Tablespace created.
SQL> create index index_1 on hr.employees(last_name) tablespace test_index nologging;
Index created.
10、对数据库管理员的认证方式
![clipboard[84]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853988Ssgg.png "clipboard[84]")
数据库管理员有可能是在数据库服务器上面本地管理数据库，也有可能是通过远程管理数据库，可以选择通过操作系统或者密码文件进行认证。
- 如果数据库有一个密码文件，且已经授予用户SYSDBA和SYSOPER权限，则用户可以使用密码文件认证。
- 如果数据库没有密码文件，或者没有授予用户SYSDBA和SYSOPER权限，则用户可以使用操作系统认证。用户在OSDBA组会被授予SYSDBA权限，用户在OSOPER组会被授予SYSOPEN权限。
系统会优先使用操作系统认证，如果用户属于OSDBA或者OSOPER组，使用as SYSDBA或者SYSOPER连接，则不管指定了什么用户名和密码，都会以管理权限进行连接。
11、重建密码文件
![clipboard[85]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_14628539888F1B.png "clipboard[85]")
Oracle提供了一个名为orapwd的密码工具用于创建密码文件。当使用SYSDBA连接，则会连接到SYS模式，当使用SYSOPER连接，则会连接到PUBLIC模式。通过GRANT命令授权SYSDBA或者SYSOPER的用户才能使用密码文件访问数据库。
因为可以轻松重建密码文件，所以一般不会备份密码文件。
如果设置了REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE或SHARED，则不要删除密码文件，否则无法通过密码文件远程连接到数据库。
密码大小写敏感，但是如果原有密码文件设置了IGNORECASE=Y选项，重建也必须使用该选项。
重建密码文件的步骤：
（1）通过orapwd创建密码文件
orapwd file=filename password=password entries=max_users
其中：
filename：密码文件名称
password：SYS的密码
entries：允许使用SYSDBA或者SYSOPER连接的最大用户数量，如果要超过这个数量，必须创建一个新的密码文件。等号之间没有空格。
例子：
orapwd file=$ORACLE_HOME/dbs/orapwU15 password=admin entries=5
（2）使用创建的密码文件连接到数据库，授予用户权限
SQL> CONNECT sys/admin AS SYSDBA
SQL> grant sysdba to admin2;
密码文件位置：
SQL> CONNECT sys/admin AS SYSDBA
SQL> grant sysdba to admin2;
例子：查询密码文件内容，删除密码文件并重建
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
SQL> !rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone
SQL> select * from v$pwfile_users;
no rows selected
SQL> !orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstone password=123456 entries=10
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
12、对比完全和不完全恢复
![clipboard[86]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853990N9a9.png "clipboard[86]")
有2种恢复类型：
- 完全恢复：将数据库恢复到当前时间点，包括所有提交的数据。
- 不完全恢复：将数据库恢复到过去的某一个时间点，也称之为“Database Point in Time Recovery”，会丢失这个时间点之后的所有事务。用于取消某个时间点后对数据所做的所有改变。
13、完全恢复过程
![clipboard[87]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_14628539916q5T.png "clipboard[87]")
完全恢复过程：
（1）从备份还原文件。
（2）从增量备份，归档重做日志文件，联机重做日志文件应用改变到最近的事务。称之为cache recovery。
（3）还原的数据文件可能包括已经提交的修改和没有提交的修改。
（4）使用undo blocks回滚没有提交的修改。称之为transaction recovery。
（5）恢复完成，文件一致。
14、基于时间点恢复过程
![clipboard[88]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853993ZH02.png "clipboard[88]")
不完全恢复或者基于时间点的恢复，就是使用备份产生一个非当前版本的数据库，不需要应用所有的重做记录。
执行Point-in-Time recovery，需要：
- 在恢复点之前的所有数据文件的有效备份，包括脱机备份和联机备份。
- 从备份时间点到恢复时间点的所有归档日志。
恢复步骤如下：
（1）从备份还原数据文件：可以使用操作系统的复制命令或者rman的restore命令。
（2）使用recover命令：应用归档重做日志文件到还原点，包括了undo数据。
（3）数据文件包括提交和未提交的数据。
（4）使用ALTER DATABASE OPEN命令。
（5）应用undo数据，撤销未提交的事务。
（6）处理完成。
例子：基于时间点的不完全恢复
先做一个整备
RMAN> backup database plus archivelog delete input;
Starting backup at 15-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=142 RECID=144 STAMP=901202541
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T140221_c9k2vg5w_.bkp tag=TAG20160115T140221 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_15/o1_mf_1_142_c9k2vf0r_.arc RECID=144 STAMP=901202541
Finished backup at 15-JAN-16
Starting backup at 15-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_c06xczt5_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c06xj0bk_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c8vd4mmy_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g4h22q_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T140223_c9k2vmjm_.bkp tag=TAG20160115T140223 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:17
Finished backup at 15-JAN-16
Starting backup at 15-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=143 RECID=145 STAMP=901202680
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T140440_c9k2zs4b_.bkp tag=TAG20160115T140440 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_15/o1_mf_1_143_c9k2zrkw_.arc RECID=145 STAMP=901202680
Finished backup at 15-JAN-16
Starting Control File and SPFILE Autobackup at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_15/o1_mf_s_901202682_c9k2ztgc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-JAN-16
重启到mount
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 507513936 bytes
Database Buffers 322961408 bytes
Redo Buffers 2371584 bytes
Database mounted.
执行不完全恢复
RMAN> restore database until time "to_date('2016-01-17 14:07:17','yyyy-mm-dd hh24:mi:ss')";
Starting restore at 15-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
skipping datafile 5; already restored to file /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c06xj0bk_.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_c06xczt5_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g4h22q_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c8vd4mmy_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T140223_c9k2vmjm_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T140223_c9k2vmjm_.bkp tag=TAG20160115T140223
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
Finished restore at 15-JAN-16
RMAN> recover database until time "to_date('2016-01-17 14:07:17','yyyy-mm-dd hh24:mi:ss')";
Starting recover at 15-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:01
Finished recover at 15-JAN-16
RMAN> alter database open resetlogs;
database opened
15、恢复只读表空间
![clipboard[89]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853994m1F4.png "clipboard[89]")
因为只读表空间不能写入，故备份的时候不需要将表空间置于备份模式或者离线，简单拷贝就可以了。
当还原只读表空间时，将表空间离线，还原，再联机。
当只读表空间修改为可读写，考虑执行以下操作：
（1）备份只读表空间
（2）将表空间置为读写
（3）恢复表空间
即使备份之后表空间置为读写，甚至被写入了，只读时候做的备份仍然可用于恢复。
例子：只读表空间的恢复
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE READ ONLY
RCAT_TS ONLINE
TEST_INDEX ONLINE
8 rows selected.
RMAN> backup database plus archivelog delete input;
Starting backup at 15-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=154 STAMP=901205555
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T145235_c9k5smqc_.bkp tag=TAG20160115T145235 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_15/o1_mf_1_1_c9k5sm90_.arc RECID=154 STAMP=901205555
Finished backup at 15-JAN-16
Starting backup at 15-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_c06xczt5_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c06xj0bk_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c8vd4mmy_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g4h22q_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T145236_c9k5soy4_.bkp tag=TAG20160115T145236 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15
Finished backup at 15-JAN-16
Starting backup at 15-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=155 STAMP=901205692
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T145452_c9k5xx10_.bkp tag=TAG20160115T145452 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_15/o1_mf_1_2_c9k5xwlc_.arc RECID=155 STAMP=901205692
Finished backup at 15-JAN-16
Starting Control File and SPFILE Autobackup at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_15/o1_mf_s_901205694_c9k5xycp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-JAN-16
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !rm /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c06xj0bk_.dbf
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 Fri Jan 15 15:06:30 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_example_c06xj0bk_.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 5
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3443618444.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_3443618444.hm
contents of repair script:
# restore and recover datafile
restore datafile 5;
recover datafile 5;
sql 'alter database datafile 5 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 00005 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c06xj0bk_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T145236_c9k5soy4_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T145236_c9k5soy4_.bkp tag=TAG20160115T145236
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 15-JAN-16
Starting recover at 15-JAN-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-JAN-16
sql statement: alter database datafile 5 online
repair failure complete
Do you want to open the database (enter YES or NO)? yes
database opened
16、恢复非归档数据库对象
![clipboard[90]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853996tOpm.png "clipboard[90]")
创建表和索引的时候加上NOLOGGING属性，可以提高插入速度。
在上图中，创建sales_copy表使用了nologging属性。当insert语句使用APPEND hint，不会产生redo，故不能恢复这个事务。
进行介质恢复的时候，如果有NOLOGGING对象，在恢复过程中，这些对象会被标记为逻辑损坏，需要删除NOLOGGING对象并重建。
使用REPORT UNRECOVERABLE命令从最近的表空间备份中列出包含执行过NOLOGGING操作的对象的表空间。
SQL> alter table emp nologging;
Table altered.
SQL> select table_name,logging from user_tables where table_name='EMP';
TABLE_NAME LOG
------------------------------ ---
EMP NO
SQL> insert /*+ APPEND */ into emp select * from emp;
107 rows created.
SQL> commit;
Commit complete.
RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
4 full or incremental /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf
17、丢失所有控制文件的恢复：概览
![clipboard[91]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853997ns12.png "clipboard[91]")
确保不要丢失所有的控制文件，如果丢失了所有的控制文件，但是还有备份，则恢复的方法取决于联机日志文件和数据文件的状态。
（1）联机重做日志文件可用
如果联机重做日志文件可用并包含恢复所需的重做记录，不论数据文件当前是否存在，则可以还原控制文件，执行完全恢复，使用RESETLOGS打开数据库。在恢复过程中需要指定联机重做日志文件名。
（2）联机重做日志文件不可用
如果联机重做日志文件不可用但数据文件存在，则需要重建控制文件，使用RESETLOGS打开数据库。如果数据文件不存在，则还原备份的控制文件，执行point-in-time恢复，使用RESETLOGS打开数据库。
例子：丢失所有控制文件，数据文件以及联机重做日志文件的恢复
RMAN> backup database plus archivelog delete input;
Starting backup at 15-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=167 STAMP=901210666
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T161746_c9kbsboc_.bkp tag=TAG20160115T161746 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_15/o1_mf_1_1_c9kbsb9p_.arc RECID=167 STAMP=901210666
Finished backup at 15-JAN-16
Starting backup at 15-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_c06xczt5_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9k6o4tp_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c8vd4mmy_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g4h22q_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T161747_c9kbsg13_.bkp tag=TAG20160115T161747 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15
Finished backup at 15-JAN-16
Starting backup at 15-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=168 STAMP=901210803
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T162003_c9kbxmkf_.bkp tag=TAG20160115T162003 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_15/o1_mf_1_2_c9kbxm8q_.arc RECID=168 STAMP=901210803
Finished backup at 15-JAN-16
Starting Control File and SPFILE Autobackup at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_15/o1_mf_s_901210804_c9kbxnqb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-JAN-16
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf
/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf
/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf
/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c06xczt5_.dbf
/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g4h22q_.dbf
/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9k6o4tp_.dbf
/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c8vd4mmy_.dbf
7 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/STONE/onlinelog/o1_mf_3_c9k5fb7q_.log
/u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_3_c9k5fc9t_.log
/u01/app/oracle/oradata/STONE/onlinelog/o1_mf_2_c9k5f7l7_.log
/u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9k5f8t5_.log
/u01/app/oracle/oradata/STONE/onlinelog/o1_mf_1_c9k5f63d_.log
/u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_1_c9k5f6or_.log
6 rows selected.
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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !rm /u01/app/oracle/oradata/STONE/datafile/*
SQL> !rm /u01/app/oracle/oradata/STONE/onlinelog/*
SQL> !rm /u01/app/oracle/fast_recovery_area/STONE/onlinelog/*
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> 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
[oracle@oracletest ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 15 16:27:45 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: STONE (not mounted)
RMAN> restore controlfile from autobackup;
Starting restore at 15-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 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_15/o1_mf_s_901210804_c9kbxnqb_.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_15/o1_mf_s_901210804_c9kbxnqb_.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 15-JAN-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> list failure;
no failures found that match specification
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 01/15/2016 16:29:41
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/15/2016 16:29:53
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_c06xczt5_.dbf'
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4107 CRITICAL OPEN 15-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c06xczt5_.dbf' is missing
4104 CRITICAL OPEN 15-JAN-16 Control file needs media recovery
3282 HIGH OPEN 15-JAN-16 One or more non-system datafiles are missing
RMAN> advise failure;
Starting implicit crosscheck backup at 15-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 15-JAN-16
Starting implicit crosscheck copy at 15-JAN-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-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_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
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
4107 CRITICAL OPEN 15-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c06xczt5_.dbf' is missing
4104 CRITICAL OPEN 15-JAN-16 Control file needs media recovery
3282 HIGH OPEN 15-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_c06xczt5_.dbf was unintentionally renamed or moved, restore it
3. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf was unintentionally renamed or moved, restore it
4. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf was unintentionally renamed or moved, restore it
5. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf was unintentionally renamed or moved, restore it
6. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9k6o4tp_.dbf was unintentionally renamed or moved, restore it
7. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g4h22q_.dbf was unintentionally renamed or moved, restore it
8. If file /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c8vd4mmy_.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Perform incomplete database recovery
Strategy: The repair includes point-in-time recovery with some data loss
Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2303641599.hm
RMAN> repair failure;
Strategy: The repair includes point-in-time recovery with some data loss
Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2303641599.hm
contents of repair script:
# database point-in-time recovery until a missing log
restore database until scn 3830448;
recover database until scn 3830448;
alter database open resetlogs;
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_c06xczt5_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9k6o4tp_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g4h22q_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c8vd4mmy_.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:01:25
Finished restore at 15-JAN-16
Starting recover at 15-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_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_c9kcnrx0_.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_c9kcnrx0_.arc RECID=169 STAMP=901211544
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-JAN-16
database opened
repair failure complete
18、恢复控制文件到默认位置
![clipboard[92]](http://img.blog.itpub.net/blog/attachment/201605/10/28536251_1462853998zwYz.png "clipboard[92]")
如果需要恢复控制文件且默认位置可用，按照上图中的步骤进行恢复：
需要先关闭数据库，修复硬件故障，确保默认位置有效，还原控制文件到默认位置。使用如下命令拷贝备份控制文件到默认位置：
% cp /backup/control01.dbf /disk1/oradata/trgt/control01.dbf
% cp /backup/control02.dbf /disk2/oradata/trgt/control02.dbf
mount数据库，指定使用备份的控制文件进行恢复：
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
在恢复过程中，如果提示缺少重做日志，有可能是缺少联机重做日志文件，指定一个联机重做日志文件名字。恢复完成后，使用RESETLOGS选项打开数据库。
例子：所有数据文件丢失的恢复
RMAN> backup database plus archivelog delete input;
Starting backup at 15-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=3 RECID=156 STAMP=901208738
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T154538_c9k8x34j_.bkp tag=TAG20160115T154538 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_15/o1_mf_1_3_c9k8x2pk_.arc RECID=156 STAMP=901208738
Finished backup at 15-JAN-16
Starting backup at 15-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_c06xczt5_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_c06xczw8_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_c9k6o4tp_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_c06xczwc_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_c8vd4mmy_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_c9g4h22q_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.dbf
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_nnndf_TAG20160115T154540_c9k8x4lx_.bkp tag=TAG20160115T154540 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
Finished backup at 15-JAN-16
Starting backup at 15-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=4 RECID=157 STAMP=901208865
channel ORA_DISK_1: starting piece 1 at 15-JAN-16
channel ORA_DISK_1: finished piece 1 at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_15/o1_mf_annnn_TAG20160115T154746_c9k9126o_.bkp tag=TAG20160115T154746 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_15/o1_mf_1_4_c9k911wp_.arc RECID=157 STAMP=901208865
Finished backup at 15-JAN-16
Starting Control File and SPFILE Autobackup at 15-JAN-16
piece handle=/u01/app/oracle/fast_recovery_area/STONE/autobackup/2016_01_15/o1_mf_s_901208867_c9k913mg_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-JAN-16
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/STONE/
controlfile/o1_mf_c06xh9fs_.ct
l, /u01/app/oracle/fast_recove
ry_area/STONE/controlfile/o1_m
f_c06xh9jx_.ctl, /home/oracle/
o1_mf_c06xh9fs_.ctl
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
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> 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> restore controlfile from autobackup;
Starting restore at 15-JAN-16
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_15/o1_mf_s_901208867_c9k913mg_.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_15/o1_mf_s_901208867_c9k913mg_.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 15-JAN-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3910 CRITICAL OPEN 15-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c06xczt5_.dbf' needs media recovery
3907 CRITICAL OPEN 15-JAN-16 Control file needs media recovery
3913 HIGH OPEN 15-JAN-16 One or more non-system datafiles need media recovery
RMAN> advise failure;
Starting implicit crosscheck backup at 15-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 15-JAN-16
Starting implicit crosscheck copy at 15-JAN-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-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_15/o1_mf_s_901208867_c9k913mg_.bkp
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
3910 CRITICAL OPEN 15-JAN-16 System datafile 1: '/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_c06xczt5_.dbf' needs media recovery
3907 CRITICAL OPEN 15-JAN-16 Control file needs media recovery
3913 HIGH OPEN 15-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_c06xczt5_.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_c06xczw8_.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_c06xczwc_.dbf, then replace it with the correct one
5. If you restored the wrong version of data file /u01/app/oracle/oradata/STONE/datafile/o1_mf_users_c06xczx2_.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_c9g4h22q_.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_c8vd4mmy_.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_117371079.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_117371079.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 15-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 5 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9k5f8t5_.log
archived log file name=/u01/app/oracle/fast_recovery_area/STONE/onlinelog/o1_mf_2_c9k5f8t5_.log thread=1 sequence=5
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-JAN-16
database opened
repair failure complete
19、相关习题（1）Your database is in ARCHIVELOG mode. You have two online redo log groups, each of which contains one redo member. When you attempt to start the database, you receive the following errors:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'D:\REDO01.LOG'
You discover that the online redo log file of the current redo group is corrupted.
Which statement should you use to resolve this issue?
A.ALTER DATABASE DROP LOGFILE GROUP 1;
B.ALTER DATABASE CLEAR LOGFILE GROUP 1;
C.ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
D.ALTER DATABASE DROP LOGFILE MEMBER 'D:\REDO01.LOG';
答案：C
（2）You are using recovery Manager (RMAN) with a recovery catalog to backup up your production database. The backups and the archived redo log files are copied to a tape drive on a daily basis. The database was open and transactions were recorded in the redo logs. Because of fire in the building you lost your servers having the production database and the recovery catalog database. The archive log files generated after the last backup are intact on one of the remote locations. While performing a disaster recovery of the production database what is the next step that you must perform after restoring the data files and applying archived redo logs?
A.Open the database in NORMAL mode
B.Open the database in read-only mode
C.Open the database in RESTRICTED mode
D.Open the database with the RESETLOGS option
答案：D
（3）The database is configured in ARCHIVELOG mode and regular complete database backups are taken. The loss of which two types of files may require a recovery with the RESETLOGS option? (Choose two)
A.Control files
B.Password files
C.Inactive online redo log file
D.Archived log files required to perform recovery
E.Newly created tablespace which is not backed up
答案：AD
（4）Identify two situations in which you can use Data Recovery Advisor for recovery. (Choose two.)
A. The user has dropped an important table that needs to be recovered.
B. The database files are corrupted when the database is open.
C. You are not able to start up the database instance because the required database files are missing.
D. The archived log files are missing for which backup is not available.
答案：BC
（5）You have control-file autobackups enabled. When starting your database from SQL*Plus, you receive the following error message:
SQL> startup
ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file ‘C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\INITORCL.ORA’
Using RMAN, how would you respond to this error?
A. Issue the startup nomount command and then issue the restore parameter file command from the RMAN prompt.
B. Issue the startup nomount command and then issue the restore spfile command from the RMAN prompt.
C. Issue the startup nomount command and then issue the restore spfile from autobackup command from the RMAN prompt.
D. Issue the startup nomount command and then issue the restore spfile from backup command from the RMAN prompt.
E. Issue the restore spfile from autobackup command from the RMAN prompt.
答案：C
（6）While working on a data problem, Curt, Bill, Ben, Mike, and Matt introduced a vast amount of corrupted data into the database. Pablo has discovered this problem and he needs you to recover the database to the point in time prior to the introduction of the corruption. The logical corruption was introduced at 6:30 p.m. on September 6, 2008. Which of the following would be the correct commands to use to restore the database to a point in time before the corruption?
A. restore database until time ('06-SEP-2008 06:30:00');
recover database until time ('06-SEP-2008 06:30:00');
alter database open;
B. restore database until time ('06-SEP-2008 06:30:00');
recover database until time ('06-SEP-2008 06:30:00');
alter database open resetlogs;
C. restore database until time ('06-SEP-2008 18:29:55');
recover database until time ('06-SEP-2008 18:29:55');
alter database open resetlogs;
D. restore database until time ('06-SEP-2008 18:29:55');
alter database open resetlogs;
E. restore database until time ('06-SEP-2008 18:29:55');
recover database;
alter database open resetlogs;
答案：C
（7）What is the purpose of the recover command? (Choose all that apply.)
A. Recover database datafiles from physical disk backup sets.
B. Recover required incremental backups from physical disk backup sets.
C. Recover required archived redo logs from physical disk backup sets.
D. Apply incremental backups to recover the database.
E. Apply archived redo logs to recover the database.
答案：BCDE
（8）Which command will restore all datafiles to the date 9/30/2008 at 18:00 hours?
A. restore datafiles until time '09/28/2008:21:03:11';
B. restore database files until time '09/28/2008:18:00:00';
C. restore database until time '09/28/2008:18:00:00';
D. recover database until time '09/28/2008:18:00:00';
E. recover database until timestamp '09/28/2008:18:00:00';
答案：C
（9）Which of the following does the recover command not do?
A. Restore archived redo logs.
B. Apply archived redo logs.
C. Restore incremental backups.
D. Apply incremental backups.
E. Restore datafile images.
答案：A
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28536251/viewspace-2096989/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-2096989/
655
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
