OCP課程47：管理II之還原和恢復任務
課程目標：
- 主要的恢復操作
- 備份和恢復控制檔案
- 恢復重做日誌組
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.
[ ~]$ 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
[ ~]$ 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
[ ~]$ 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.
[ ~]$ 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
[ ~]$ 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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OCP課程58：管理II之自動任務
- OCP課程48：管理II之使用RMAN執行恢復
- OCP課程42：管理II之核心概念和工具
- OCP課程54：管理II之管理記憶體記憶體
- OCP課程56：管理II之SQL調優SQL
- OCP課程45：管理II之備份設定
- OCP課程61：管理II之複製資料庫資料庫
- OCP課程50：管理II之診斷資料庫資料庫
- OCP課程51：管理II之使用閃回技術1
- OCP課程52：管理II之使用閃回技術2
- OCP課程53：管理II之使用閃回資料庫資料庫
- OCP課程27：管理Ⅰ之管理ASM例項ASM
- OCP課程39：管理Ⅰ之移動資料
- OCP課程15：SQL之管理模式物件SQL模式物件
- 恢復之還原資料檔案
- OCP課程60：管理Ⅰ之管理資料庫空間資料庫
- OCP課程26：管理Ⅰ之管理資料庫例項資料庫
- OCP課程11：SQL之使用DDL語句建立和管理表SQL
- OCP課程24：管理Ⅰ之資料庫安裝資料庫
- rman還原恢復操作
- 使用RMAN還原和恢復資料庫資料庫
- OCP課程25：管理Ⅰ之使用DBCA建立資料庫資料庫
- OCP課程4：SQL之限制和排序資料SQL排序
- oracle資料恢復還原Oracle資料恢復
- OCP課程23：管理Ⅰ之資料庫體系結構資料庫
- 【備份恢復】下：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- 【備份恢復】上：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- myloader還原恢復詳解
- OCP課程13：SQL之使用資料字典檢視管理物件SQL物件
- OCP課程18：SQL之管理不同時區下的資料SQL
- ORACLE事務和例項恢復過程梳理Oracle
- Laravel 服務之任務排程Laravel
- 【iCore4 雙核心板_uC/OS-II】例程三：任務的掛起與恢復
- 2 Day DBA-管理方案物件-執行備份和恢復-練習：為備份和恢復任務輸入身份證明物件
- 虛擬化還原快照導致資料丟失恢復過程
- OCP課程21：SQL之正規表示式SQL
- OCP課程12：SQL之建立其他模式物件SQL模式物件
- OCP課程9：SQL之使用集合運算子SQL