前提: 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:/Oracle/ORADATA/xxxx/xxxx.DBF' 5.select name from v$datafile找出uname file 6. alter database create datafile 'D:/ORACLE/ORA92/DATABASE/UNNAMEDxxxxx' as 'D:/ORACLE/ORADATA/ xxxx/xxxx.DBF '; 7.繼續恢復 8.恢復完成,打開. 可能會碰到的問題: 1.假如沒有拷貝備份回來直接重建不包含丟失文件的控制文件時,可以打開數據庫,但丟失的文件會顯示成missingxxxx的file name,這時候就會不能恢復. 試驗過程: Microsoft Windows xp [版本 5.1.2600] (C) 版權所有 1985-2001 Microsoft Corp. C:/Documents and Settings/qigong>oradim -startup -sid test C:/Documents and Settings/qigong>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 Settings/qigong>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 Settings/qigong>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:/oracle/product/10.1.0/Flash_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:/ORACLE/PRODUCT/10.1.0 /FLASH_RECOVERY_AREA/TEST/AUTOBACKUP/2004_08_24/O1_MF_S_535041963_0LOS1FCX_.BKP
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/CONTROL01.CTL
output filename=C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/CONTROL02.CTL
output filename=C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/CONTROL03.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:/oracle/product/10.1.0/Db_4/DATABASE/PWDtest.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:/02FUAT0K_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:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/REDO01.LOG' SIZE 10M,
9 GROUP 2 'C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/REDO02.LOG' SIZE 10M,
10 GROUP 3 'C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/REDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/SYSTEM01.DBF',
14 'C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/UNDOTBS01.DBF',
15 'C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/SYSAUX01.DBF',
16 'C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/USERS01.DBF',
17 'C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/EXAMPLE01.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:/ORACLE/ARCHIVE/ARC00010_0535038675.001
ORA-00280: change 1196994 for thread 1 is in sequence #10 Specify log: {<RET>=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:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/SYSTEM01.DBF
C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/UNDOTBS01.DBF
C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/SYSAUX01.DBF
C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/USERS01.DBF
C:/ORACLE/PRODUCT/10.1.0/ORADATA/TEST/EXAMPLE01.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:/WINDOWS/SYSTEM32/UNNAMED00015 15 rows selected. SQL> alter database create datafile 'C:/WINDOWS/SYSTEM32/UNNAMED00015' 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:/WINDOWS/SYSTEM32/UNNAMED00015
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一下就可以繼續恢復進程了。
