無備份丟失部分資料檔案和控制檔案恢復 [轉]
前提:
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 電腦檔案丟失資料恢復資料恢復
- Sql Server資料庫檔案丟失的恢復方法SQLServer資料庫
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- dg丟失歸檔，使用rman增量備份恢復
- 12 使用RMAN備份和恢復檔案
- DATA GUARD主庫丟失資料檔案的恢復(3)
- DATA GUARD主庫丟失資料檔案的恢復(1)
- DATA GUARD主庫丟失資料檔案的恢復(2)
- 檔案的基本管理和XFS檔案系統備份恢復
- 【/proc/檔案淺析】另類辦法恢復資料檔案和控制檔案
- Oracle-無備份情況下，如何手動恢復控制檔案Oracle
- 【北亞資料恢復】MongoDB資料遷移檔案丟失的MongoDB資料恢復案例資料恢復MongoDB
- 【資料庫資料恢復】mdb_catalog.wt檔案丟失的MongoDB資料恢復案例資料庫資料恢復MongoDB
- 【伺服器資料恢復】xfs檔案系統資料丟失的資料恢復案例伺服器資料恢復
- macOS Big Sur系統如何恢復丟失的資料檔案？Mac
- 【資料庫資料恢復】Sql Server資料庫檔案丟失的資料恢復過程資料庫資料恢復SQLServer
- MongoDB資料庫報錯，資料庫檔案丟失資料恢復案例MongoDB資料庫資料恢復
- 丟失的隨身碟檔案如何恢復？
- 新建的表空間(或資料檔案)丟失以及控制檔案丟失，有新建表空間(或資料檔案)前的控制文
- oracle控制檔案的損壞或完全丟失的恢復辦法Oracle
- RMAN備份恢復典型案例——資料檔案存在壞快
- 從備份片中恢復某個指定得歸檔或者資料檔案
- 資料庫資料恢復—MongoDB資料庫檔案丟失，啟動報錯的資料恢復案例資料庫資料恢復MongoDB
- 檔案丟失不用怕：超實用的Mac資料恢復軟體！Mac資料恢復
- 實戰：xfs檔案系統的備份和恢復
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- XFS檔案系統的備份、恢復、修復
- 【伺服器資料恢復】XFS檔案系統分割槽分割槽丟失無法訪問的資料恢復案例伺服器資料恢復
- 【虛擬機器資料恢復】Hyper-V虛擬化檔案丟失的資料恢復案例虛擬機資料恢復
- 怎樣恢復Mac檔案及資料夾資料？BackupLoupe for mac(資料恢復備份助手)3.5.4Mac資料恢復
- 雲備份和同步檔案資料
- 世界備份日——如果您丟失了所有檔案
- Sqlserver系統資料庫和使用者資料庫日誌檔案全部丟失的恢復SQLServer資料庫
- 如何從Active Backup for Business備份中恢復檔案
- 如何備份及恢復Linux檔案許可權Linux
- 【伺服器資料恢復】SAN LUN對映出錯導致檔案系統資料丟失的資料恢復案例伺服器資料恢復
- Linux中XFS檔案系統的備份，恢復，修復Linux
- 【RMAN】Oracle中如何備份控制檔案？備份控制檔案的方式有哪幾種？Oracle
- 伺服器資料恢復—重灌系統導致XFS檔案系統分割槽丟失的資料恢復案例伺服器資料恢復