與控制檔案有關的恢復1
由此可以引出，建立控制檔案時丟失資料檔案的解決方法（條件：丟失的資料檔案要有備份和備份以來的所有歸檔日誌）
SQL> CREATE CONTROLFILE REUSE DATABASE "TS" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 4674
7 LOGFILE
8 GROUP 1 'D:ORACLEPRODUCT10.2.0ORADATATSREDO01.LOG' SIZE 50M,
9 GROUP 2 'D:ORACLEPRODUCT10.2.0ORADATATSREDO02.LOG' SIZE 50M,
10 GROUP 3 'D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF',
14 'D:ORACLEPRODUCT10.2.0ORADATATST.DBF',
15 'D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF',
16 'D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF',
17 'D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF',
18 'D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF',
19 'D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF',
20 'D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF',
21 'D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF',
22 'D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF',
23 'D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF',
24 'D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF',
25 'D:ORACLEPRODUCT10.2.0ORADATATST1.DBF',
26 'D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF',
27 'D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF',
28 'D:ORACLEPRODUCT10.2.0ORADATATSA.DBF'
29 CHARACTER SET ZHS16GBK
30 /
Control file created.//控制檔案建立時丟失表空間b下的資料檔案b.dbf
SQL> alter database open;
Database altered.
SQL> select * from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
SYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
USERS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT NOLOGGING NO LOCAL SYSTEM YES AUTO DISABLED NOT APPLY NO
BLOCK 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEST 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
BBED 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEMP2 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM. NO MANUAL DISABLED NOT APPLY NO
FLM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
ASSM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
UNDOTBS 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
T 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
DEMO 8192 65536 65536 1 2147483645 0 65536 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM. NO AUTO DISABLED NOT APPLY NO
DUMMY 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
A 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
B 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
16 rows selected.
SQL> select * from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSA.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF
D:ORACLEPRODUCT10.2.0ORADATATST1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF
D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATATST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00010
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00019
19 rows selected.
SQL> select * from t13;
select * from t13
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00019' as 'D:ORACLEPRODUCT10.2.0DB_1DATABASEb.dbf' reuse;
alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00019' as 'D:ORACLEPRODUCT10.2.0DB_1DATABASEb.dbf' reuse
*
ERROR at line 1:
ORA-01178: file 19 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 19 is unknown - rename to correct file
ORA-01110: data file 19: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00019'
SQL> alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00019' to 'D:ORACLEPRODUCT10.2.0DB_1DATABASEb.dbf';
Database altered.
SQL> select * from t13;
select * from t13
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> recover datafile 19;
Media recovery complete.
SQL> alter database datafile 19 online;
Database altered.
SQL> select * from t13;
select * from t13
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from dba_segments
2 where tablespace_name='T13';
no rows selected
SQL> select * from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
SYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
USERS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT NOLOGGING NO LOCAL SYSTEM YES AUTO DISABLED NOT APPLY NO
BLOCK 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEST 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
BBED 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEMP2 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM. NO MANUAL DISABLED NOT APPLY NO
FLM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
ASSM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
UNDOTBS 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
T 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
DEMO 8192 65536 65536 1 2147483645 0 65536 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM. NO AUTO DISABLED NOT APPLY NO
DUMMY 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
A 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
B 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
16 rows selected.
SQL> select * from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSA.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF
D:ORACLEPRODUCT10.2.0ORADATATST1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF
D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATATST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00010
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011
D:ORACLEPRODUCT10.2.0DB_1DATABASEB.DBF
19 rows selected.
SQL>
SQL> alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' as 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' as 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf'
*
ERROR at line 1:
ORA-01178: file 11 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011'
SQL> alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 11 - new file 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf' not found
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系統找不到指定的檔案。
SQL> alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
Database altered.
SQL> recover datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
ORA-00283: recovery session canceled due to errors
ORA-04031: unable to allocate 32332 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","previous log names for rcv so")
SQL> SQL> alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' as 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' as 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf'
*
ERROR at line 1:
ORA-01178: file 11 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011'
SQL> alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 11 - new file 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf' not found
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系統找不到指定的檔案。
SQL> alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
Database altered.
SQL> recover datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
ORA-00283: recovery session canceled due to errors
ORA-04031: unable to allocate 32332 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","previous log names for rcv so")
SQL> recover datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/730796/viewspace-594356/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 與控制檔案有關的恢復
- 與控制檔案有關的恢復(二)
- 與控制檔案有關的恢復2
- oralce備份與恢復1 控制檔案(轉)
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- oracle實驗記錄 （恢復-關於控制檔案(1)）Oracle
- 備份與恢復--利用備份的控制檔案恢復
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- 備份與恢復系列 十一 控制檔案的備份與恢復
- 控制檔案恢復—從trace檔案中恢復
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- 備份與恢復--重建控制檔案
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 【備份與恢復】恢復受損的複用控制檔案
- RMAN 恢復之控制檔案篇(1/5)
- rman恢復--丟失控制檔案的恢復
- RMAN恢復控制檔案
- 手工恢復控制檔案
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 恢復測試：擁有當時的全部歸檔，控制檔案，恢復丟失的資料檔案。
- 控制檔案恢復—從快照中恢復
- 備份與恢復--重新控制檔案資料字典和控制檔案不一致的恢復
- 同時丟失控制檔案與資料檔案的恢復
- 恢復丟失的控制檔案
- 控制檔案的恢復方法（一）
- 控制檔案的恢復方法（二）
- 控制檔案的恢復方法（三）
- 控制檔案的恢復方法（四）
- 【rman 備份與恢復】恢復丟失所有的控制檔案
- 循序漸進oracle第7章：備份與恢復之利用控制檔案快照恢復控制檔案Oracle
- 使用舊的控制檔案備份來恢復控制檔案
- cp方式恢復控制檔案
- 控制檔案恢復測試
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 使用rman恢復控制檔案
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）