與控制檔案有關的恢復1

sxzhanghl發表於2009-05-06

由此可以引出,建立控制檔案時丟失資料檔案的解決方法(條件:丟失的資料檔案要有備份和備份以來的所有歸檔日誌)

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章