無備份丟失部分資料檔案和控制檔案恢復 [轉]
前提:
1. 需要有除丟失檔案以外其他檔案的備份.
2. 丟失的檔案需要是在其他檔案備份後建立的.
3. 所有其他檔案備份後的歸檔都在存在
恢復步驟:
1,複製任一未丟失檔案的備份回來
2.建立新控制檔案,但控制檔案不包括丟失的資料檔案
3.進行恢復
4.碰到
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 3: 'D:ORACLEORADATAxxxxxxxx.DBF'
5.select name from v＄datafile找出uname file
6. alter database create datafile 'D:ORACLEORA92DATABASEUNNAMEDxxxxx' as 'D:ORACLEORADATA xxxxxxxx.DBF ';
7.繼續恢復
8.恢復完成,開啟.
可能會遇到的問題:
1.如果沒有複製備份回來直接重建不包含丟失檔案的控制檔案時,可以開啟資料庫,但丟失的檔案會顯示成missingxxxx的file name,這時候就會不能恢復.
試驗過程:
Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.
C:Documents and Settingsqigong>oradim -startup -sid test
C:Documents and Settingsqigong>sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on 星期三 8月 25 15:22:39 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Producti
With the Partitioning, OLAP and Data Mining options
C:Documents and Settingsqigong>sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on 星期三 8月 25 15:25:37 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 163577856 bytes
Fixed Size 787948 bytes
Variable Size 74447380 bytes
Database Buffers 88080384 bytes
Redo Buffers 262144 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying controlfile, check alert log for more info
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 163577856 bytes
Fixed Size 787948 bytes
Variable Size 74447380 bytes
Database Buffers 88080384 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> create tablespace test2
2 datafile 'c:test2.dbf' size 10m
3 extent management local
4 segment space management auto
5 uniform size 100k;
Tablespace created.
SQL> insert into testlost values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> create table testlost2(a number) tablespace test2;
Table created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 163577856 bytes
Fixed Size 787948 bytes
Variable Size 74447380 bytes
Database Buffers 88080384 bytes
Redo Buffers 262144 bytes
C:Documents and Settingsqigong>rman
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target
connected to target database: test (not mounted)
RMAN> restore controlfile from autobackup;
Starting restore at 25-8月 -04
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
recovery area destination: C:oracleproduct10.1.0flash_recovery_area
database name (or lock name space) used for search: TEST
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:ORACLEPRODUCT10.1.0 FLASH_RECOVERY_AREATESTAUTOBACKUP2004_08_24O1_MF_S_535041963_0LOS1FCX_.BKP
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:ORACLEPRODUCT10.1.0ORADATATESTCONTROL01.CTL
output filename=C:ORACLEPRODUCT10.1.0ORADATATESTCONTROL02.CTL
output filename=C:ORACLEPRODUCT10.1.0ORADATATESTCONTROL03.CTL
Finished restore at 25-8月 -04
RMAN> exit
RMAN-06900: WARNING: unable to generate V＄RMAN_STATUS or V＄RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V＄RMAN_STATUS and V＄RMAN_OUTPUT rows
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01991: invalid password file
'C:oracleproduct10.1.0Db_4DATABASEPWDtest.ORA'
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>14,toname=>'c:test1.DBF');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'C:2FUAT0K_1_1_2.
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 163577856 bytes
Fixed Size 787948 bytes
Variable Size 74447380 bytes
Database Buffers 88080384 bytes
Redo Buffers 262144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 454
7 LOGFILE
8 GROUP 1 'C:ORACLEPRODUCT10.1.0ORADATATESTREDO01.LOG' SIZE 10M,
9 GROUP 2 'C:ORACLEPRODUCT10.1.0ORADATATESTREDO02.LOG' SIZE 10M,
10 GROUP 3 'C:ORACLEPRODUCT10.1.0ORADATATESTREDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'C:ORACLEPRODUCT10.1.0ORADATATESTSYSTEM01.DBF',
14 'C:ORACLEPRODUCT10.1.0ORADATATESTUNDOTBS01.DBF',
15 'C:ORACLEPRODUCT10.1.0ORADATATESTSYSAUX01.DBF',
16 'C:ORACLEPRODUCT10.1.0ORADATATESTUSERS01.DBF',
17 'C:ORACLEPRODUCT10.1.0ORADATATESTEXAMPLE01.DBF',
18 'C:TBS1.DBF',
19 'C:TBS2.DBF',
20 'C:TBS3.DBF',
21 'C:TBS4.DBF',
22 'C:TBS5.DBF',
23 'C:TBS6.DBF',
24 'C:TBS7.DBF',
25 'C:TBS8.DBF',
26 'C:TEST1.DBF'
27 CHARACTER SET ZHS16GBK
28 ;
Control file created.
SQL> recover database;
ORA-00279: change 1196994 generated at 08/25/2004 15:32:36 needed for thread 1
ORA-00289: suggestion : C:ORACLEARCHIVEARC00010_0535038675.001
ORA-00280: change 1196994 for thread 1 is in sequence #10
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 15: 'C:TEST2.DBF'
ORA-01112: media recovery not started
SQL> select name from v＄datafile;
NAME
--------------------------------------------------------------------------------
C:ORACLEPRODUCT10.1.0ORADATATESTSYSTEM01.DBF
C:ORACLEPRODUCT10.1.0ORADATATESTUNDOTBS01.DBF
C:ORACLEPRODUCT10.1.0ORADATATESTSYSAUX01.DBF
C:ORACLEPRODUCT10.1.0ORADATATESTUSERS01.DBF
C:ORACLEPRODUCT10.1.0ORADATATESTEXAMPLE01.DBF
C:TBS1.DBF
C:TBS2.DBF
C:TBS3.DBF
C:TBS4.DBF
C:TBS5.DBF
C:TBS6.DBF
NAME
--------------------------------------------------------------------------------
C:TBS7.DBF
C:TBS8.DBF
C:TEST1.DBF
C:WINDOWSSYSTEM32UNNAMED00015
15 rows selected.
SQL> alter database create datafile 'C:WINDOWSSYSTEM32UNNAMED00015' as 'c:test2.db
Database altered.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from testlost2;
no rows selected
SQL> select * from testlost;
A
----------
2
1
在上面這個試驗裡面我們可能還會有一個疑問，既然控制檔案裡面記載的是unamedxxxxxxx的檔名，資料字典的裡面的filename也是 unamedxxxxxxx(實際上資料字典裡面不儲存檔名，所有的檔名都只包含在控制檔案當中)，日誌裡也是沒有記載檔名，那麼當我們 recover database的時候為什麼會出現
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 15: 'C:TEST2.DBF'
ORA-01112: media recovery not started
Oracle他是從哪裡得到這個unamedxxxxxxx檔案的真實檔案是'C:TEST2.DBF'呢？
先來看看日誌裡記載了什麼東西
用logminer查一下
EXECUTE DBMS_LOGMNR_D.BUILD(dictionary_filename => 'l_dictionary.ora',dictionary_location => '/disk1/oradata/');
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LogFileName => '/disk1/oradata/redo04.log',Options => dbms_logmnr.NEW);
EXECUTE DBMS_LOGMNR.START_LOGMNR(DictFileName =>'/disk1/oradata/l_dictionary.ora');
SELECT sql_redo FROM V＄LOGMNR_CONTENTS where upper(sql_redo) like '%TEST%';
SQL_REDO
--------------------------------------------------------------------------------
create tablespace test3
datafile 'c:test3.dbf' size 1m reuse
extent management local
segment space management auto
uniform size 40k;
只發現了這句ddl.並沒有往資料字典裡插入檔名。
再來看create tablespace的trace檔案
……
insert into ts＄ (ts#,name,online＄,contents＄,undofile#,undoblock#,blocksize,
dflmaxext,dflinit,dflincr,dflextpct,dflminext,dflminlen,inc#,owner#,scnwrp,
scnbas,pitrscnwrp,pitrscnbas,dflogging, affstrength,bitmapped,plugged,
directallowed,flags,spare1,spare2)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,1,:14,:15,:16,:17,:18,:19,0,:20,:21,1,:22,:23,:24)
insert into file＄ (file#,blocks,ts#,status＄,relfile#,maxextend,inc,crscnwrp,crscnbas,spare1)
values
(:1,:2,DECODE(:3,-1,NULL,:3),:4, DECODE(:5,0,NULL,:5),:6,:7,:8,:9,DECODE(:10,0,NULL,:10))
……
這兩條語句說明了oracle在ts＄,file＄分別插入了新建立表空間和檔案的資訊，但並不包括檔名。
看起來好像這個檔名是無出處的，即不直接取至日誌，也不存在於資料字典，控制檔案中則存在著錯誤的檔名，那他究竟從哪裡來呢？
看下控制檔案中包含這個檔案的dump 資訊
DATA FILE #15:
(name #4) C:WINDOWSSYSTEM32UNNAMED00015
creation size=0 block size=8192 status=0x2 head=4 tail=4 dup=1
tablespace 16, index=15 krfil=15 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:135 scn: 0x0000.001d4e00 12/02/2004 19:32:57
Stop scn: 0x0000.001d4e00 12/02/2004 19:36:28
Creation Checkpointed at scn: 0x0000.0012445c 08/25/2004 15:37:56
從這裡我們知道test2.dbf是16號檔案，這樣的話oracle可以從file＄表中找到這個檔案對應的ts#,再從ts＄中找到表空間的名字，然後回到日誌，對應日誌中的ddl即可以得到錯誤的檔案是c:test2.dbf,答案出來了！oracle根據這麼一套流程找到了正確的檔名並提示給使用者，這樣的話我們只需要很方便的create datafile reuse一下就可以繼續恢復程式了。 [@more@]
1. 需要有除丟失檔案以外其他檔案的備份.
2. 丟失的檔案需要是在其他檔案備份後建立的.
3. 所有其他檔案備份後的歸檔都在存在
恢復步驟:
1,複製任一未丟失檔案的備份回來
2.建立新控制檔案,但控制檔案不包括丟失的資料檔案
3.進行恢復
4.碰到
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 3: 'D:ORACLEORADATAxxxxxxxx.DBF'
5.select name from v＄datafile找出uname file
6. alter database create datafile 'D:ORACLEORA92DATABASEUNNAMEDxxxxx' as 'D:ORACLEORADATA xxxxxxxx.DBF ';
7.繼續恢復
8.恢復完成,開啟.
可能會遇到的問題:
1.如果沒有複製備份回來直接重建不包含丟失檔案的控制檔案時,可以開啟資料庫,但丟失的檔案會顯示成missingxxxx的file name,這時候就會不能恢復.
試驗過程:
Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.
C:Documents and Settingsqigong>oradim -startup -sid test
C:Documents and Settingsqigong>sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on 星期三 8月 25 15:22:39 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Producti
With the Partitioning, OLAP and Data Mining options
C:Documents and Settingsqigong>sqlplus / as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on 星期三 8月 25 15:25:37 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 163577856 bytes
Fixed Size 787948 bytes
Variable Size 74447380 bytes
Database Buffers 88080384 bytes
Redo Buffers 262144 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying controlfile, check alert log for more info
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 163577856 bytes
Fixed Size 787948 bytes
Variable Size 74447380 bytes
Database Buffers 88080384 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> create tablespace test2
2 datafile 'c:test2.dbf' size 10m
3 extent management local
4 segment space management auto
5 uniform size 100k;
Tablespace created.
SQL> insert into testlost values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> create table testlost2(a number) tablespace test2;
Table created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 163577856 bytes
Fixed Size 787948 bytes
Variable Size 74447380 bytes
Database Buffers 88080384 bytes
Redo Buffers 262144 bytes
C:Documents and Settingsqigong>rman
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect target
connected to target database: test (not mounted)
RMAN> restore controlfile from autobackup;
Starting restore at 25-8月 -04
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
recovery area destination: C:oracleproduct10.1.0flash_recovery_area
database name (or lock name space) used for search: TEST
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:ORACLEPRODUCT10.1.0 FLASH_RECOVERY_AREATESTAUTOBACKUP2004_08_24O1_MF_S_535041963_0LOS1FCX_.BKP
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:ORACLEPRODUCT10.1.0ORADATATESTCONTROL01.CTL
output filename=C:ORACLEPRODUCT10.1.0ORADATATESTCONTROL02.CTL
output filename=C:ORACLEPRODUCT10.1.0ORADATATESTCONTROL03.CTL
Finished restore at 25-8月 -04
RMAN> exit
RMAN-06900: WARNING: unable to generate V＄RMAN_STATUS or V＄RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V＄RMAN_STATUS and V＄RMAN_OUTPUT rows
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01991: invalid password file
'C:oracleproduct10.1.0Db_4DATABASEPWDtest.ORA'
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>14,toname=>'c:test1.DBF');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'C:2FUAT0K_1_1_2.
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 163577856 bytes
Fixed Size 787948 bytes
Variable Size 74447380 bytes
Database Buffers 88080384 bytes
Redo Buffers 262144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 454
7 LOGFILE
8 GROUP 1 'C:ORACLEPRODUCT10.1.0ORADATATESTREDO01.LOG' SIZE 10M,
9 GROUP 2 'C:ORACLEPRODUCT10.1.0ORADATATESTREDO02.LOG' SIZE 10M,
10 GROUP 3 'C:ORACLEPRODUCT10.1.0ORADATATESTREDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'C:ORACLEPRODUCT10.1.0ORADATATESTSYSTEM01.DBF',
14 'C:ORACLEPRODUCT10.1.0ORADATATESTUNDOTBS01.DBF',
15 'C:ORACLEPRODUCT10.1.0ORADATATESTSYSAUX01.DBF',
16 'C:ORACLEPRODUCT10.1.0ORADATATESTUSERS01.DBF',
17 'C:ORACLEPRODUCT10.1.0ORADATATESTEXAMPLE01.DBF',
18 'C:TBS1.DBF',
19 'C:TBS2.DBF',
20 'C:TBS3.DBF',
21 'C:TBS4.DBF',
22 'C:TBS5.DBF',
23 'C:TBS6.DBF',
24 'C:TBS7.DBF',
25 'C:TBS8.DBF',
26 'C:TEST1.DBF'
27 CHARACTER SET ZHS16GBK
28 ;
Control file created.
SQL> recover database;
ORA-00279: change 1196994 generated at 08/25/2004 15:32:36 needed for thread 1
ORA-00289: suggestion : C:ORACLEARCHIVEARC00010_0535038675.001
ORA-00280: change 1196994 for thread 1 is in sequence #10
Specify log: {
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 15: 'C:TEST2.DBF'
ORA-01112: media recovery not started
SQL> select name from v＄datafile;
NAME
--------------------------------------------------------------------------------
C:ORACLEPRODUCT10.1.0ORADATATESTSYSTEM01.DBF
C:ORACLEPRODUCT10.1.0ORADATATESTUNDOTBS01.DBF
C:ORACLEPRODUCT10.1.0ORADATATESTSYSAUX01.DBF
C:ORACLEPRODUCT10.1.0ORADATATESTUSERS01.DBF
C:ORACLEPRODUCT10.1.0ORADATATESTEXAMPLE01.DBF
C:TBS1.DBF
C:TBS2.DBF
C:TBS3.DBF
C:TBS4.DBF
C:TBS5.DBF
C:TBS6.DBF
NAME
--------------------------------------------------------------------------------
C:TBS7.DBF
C:TBS8.DBF
C:TEST1.DBF
C:WINDOWSSYSTEM32UNNAMED00015
15 rows selected.
SQL> alter database create datafile 'C:WINDOWSSYSTEM32UNNAMED00015' as 'c:test2.db
Database altered.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from testlost2;
no rows selected
SQL> select * from testlost;
A
----------
2
1
在上面這個試驗裡面我們可能還會有一個疑問，既然控制檔案裡面記載的是unamedxxxxxxx的檔名，資料字典的裡面的filename也是 unamedxxxxxxx(實際上資料字典裡面不儲存檔名，所有的檔名都只包含在控制檔案當中)，日誌裡也是沒有記載檔名，那麼當我們 recover database的時候為什麼會出現
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 15: 'C:TEST2.DBF'
ORA-01112: media recovery not started
Oracle他是從哪裡得到這個unamedxxxxxxx檔案的真實檔案是'C:TEST2.DBF'呢？
先來看看日誌裡記載了什麼東西
用logminer查一下
EXECUTE DBMS_LOGMNR_D.BUILD(dictionary_filename => 'l_dictionary.ora',dictionary_location => '/disk1/oradata/');
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LogFileName => '/disk1/oradata/redo04.log',Options => dbms_logmnr.NEW);
EXECUTE DBMS_LOGMNR.START_LOGMNR(DictFileName =>'/disk1/oradata/l_dictionary.ora');
SELECT sql_redo FROM V＄LOGMNR_CONTENTS where upper(sql_redo) like '%TEST%';
SQL_REDO
--------------------------------------------------------------------------------
create tablespace test3
datafile 'c:test3.dbf' size 1m reuse
extent management local
segment space management auto
uniform size 40k;
只發現了這句ddl.並沒有往資料字典裡插入檔名。
再來看create tablespace的trace檔案
……
insert into ts＄ (ts#,name,online＄,contents＄,undofile#,undoblock#,blocksize,
dflmaxext,dflinit,dflincr,dflextpct,dflminext,dflminlen,inc#,owner#,scnwrp,
scnbas,pitrscnwrp,pitrscnbas,dflogging, affstrength,bitmapped,plugged,
directallowed,flags,spare1,spare2)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,1,:14,:15,:16,:17,:18,:19,0,:20,:21,1,:22,:23,:24)
insert into file＄ (file#,blocks,ts#,status＄,relfile#,maxextend,inc,crscnwrp,crscnbas,spare1)
values
(:1,:2,DECODE(:3,-1,NULL,:3),:4, DECODE(:5,0,NULL,:5),:6,:7,:8,:9,DECODE(:10,0,NULL,:10))
……
這兩條語句說明了oracle在ts＄,file＄分別插入了新建立表空間和檔案的資訊，但並不包括檔名。
看起來好像這個檔名是無出處的，即不直接取至日誌，也不存在於資料字典，控制檔案中則存在著錯誤的檔名，那他究竟從哪裡來呢？
看下控制檔案中包含這個檔案的dump 資訊
DATA FILE #15:
(name #4) C:WINDOWSSYSTEM32UNNAMED00015
creation size=0 block size=8192 status=0x2 head=4 tail=4 dup=1
tablespace 16, index=15 krfil=15 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:135 scn: 0x0000.001d4e00 12/02/2004 19:32:57
Stop scn: 0x0000.001d4e00 12/02/2004 19:36:28
Creation Checkpointed at scn: 0x0000.0012445c 08/25/2004 15:37:56
從這裡我們知道test2.dbf是16號檔案，這樣的話oracle可以從file＄表中找到這個檔案對應的ts#,再從ts＄中找到表空間的名字，然後回到日誌，對應日誌中的ddl即可以得到錯誤的檔案是c:test2.dbf,答案出來了！oracle根據這麼一套流程找到了正確的檔名並提示給使用者，這樣的話我們只需要很方便的create datafile reuse一下就可以繼續恢復程式了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/503782/viewspace-983135/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 備份恢復實驗（1）丟失部分控制檔案
- 歸檔模式無備份丟失資料檔案後恢復模式
- 非歸檔無備份下控制檔案丟失的恢復
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式
- 備份恢復之資料檔案丟失
- 控制檔案部分丟失的恢復
- 恢復之丟失全部控制檔案以及備份中的控制檔案
- 引數檔案控制檔案和資料檔案丟失的恢復
- 【備份恢復】所有控制檔案丟失後 利用trace中的控制檔案備份執行恢復
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 歸檔模式有備份丟失控制檔案和資料檔案後恢復模式
- 【備份恢復】 丟失一個控制檔案 之恢復操作
- 資料檔案丟失 無備份 無重啟 通過控制程式碼恢復
- 資料檔案丟失，無備份，無重啟，通過控制程式碼恢復
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- 歸檔模式有備份丟失資料檔案後恢復模式
- 只有rman備份集，控制檔案丟失的恢復
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 控制檔案全部丟失，無備份，通過異機trace恢復
- rman備份丟失控制檔案恢復
- 開啟 控制檔案自動備份下，引數檔案、控制檔案全部丟失恢復
- 【備份恢復】非歸檔模式下丟失任意資料檔案 恢復操作模式
- 備份與恢復--資料檔案損壞或丟失
- rman備份-（1） 利用備份級恢復資料檔案和控制檔案
- 恢復案例：無歸檔，丟失全部控制檔案、日誌檔案恢復案例
- 控制檔案丟失恢復(二)
- 恢復丟失的控制檔案
- 控制檔案全部丟失恢復
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 沒備份，歸檔日誌存在，丟失資料檔案的恢復
- 恢復案例：無歸檔，掉電，控制檔案全部丟失恢復
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- 丟失全部控制檔案後從RMAN備份集中恢復示例
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- 資料檔案丟失的恢復