无备份丢失部分数据文件和控制文件恢复 [转]

前提:

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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/503782/viewspace-983135/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/503782/viewspace-983135/