前提:
需要有除丢失文件以外其他文件的备份
丢失的文件需要是在其他文件备份后创建的
所有其他文件备份后的归档都在存在
恢复步骤:
拷贝任一未丢失文件的备份回来
创建新控制文件但控制文件不包括丢失的数据文件
进行恢复
碰到
ORA: recovery session canceled due to errors
ORA: unnamed datafile(s) added to controlfile by media recovery
ORA: data file : D:\ORACLE\ORADATA\xxxx\xxxxDBF
select name from v$datafile找出uname file
alter database create datafile D:\ORACLE\ORA\DATABASE\UNNAMEDxxxxx as D:\ORACLE\ORADATA\ xxxx\xxxxDBF ;
继续恢复
恢复完成打开
可能会遇到的问题:
如果没有拷贝备份回来直接重建不包含丢失文件的控制文件时可以打开数据库但丢失的文件会显示成missingxxxx的file name这时候就会不能恢复
试验过程:
Microsoft Windows XP [版本 ]
(C) 版权所有 Microsoft Corp
C:\Documents and Settings\qigong>oradim startup sid test
C:\Documents and Settings\qigong>sqlplus / as sysdba
SQL*Plus: Release Production on 星期三 月 ::
Copyright (c) Oracle All rights reserved
Connected to:
Oracle Database g Enterprise Edition Release 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 g Enterprise Edition Release Producti
With the Partitioning OLAP and Data Mining options
C:\Documents and Settings\qigong>sqlplus / as sysdba
SQL*Plus: Release Production on 星期三 月 ::
Copyright (c) Oracle All rights reserved
Connected to an idle instance
SQL> startup nomount;
ORACLE instance started
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
SQL> alter database mount;
alter database mount
*
ERROR at line :
ORA: error in identifying controlfile check alert log for more info
SQL> shutdown
ORA: database not mounted
ORACLE instance shut down
SQL> startup
ORACLE instance started
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted
Database opened
SQL> alter system switch logfile;
System altered
SQL> /
System altered
SQL> /
System altered
SQL> create tablespace test
datafile c:\testdbf size m
extent management local
segment space management auto
uniform size k;
Tablespace created
SQL> insert into testlost values();
row created
SQL> commit;
Commit complete
SQL> create table testlost(a number) tablespace test;
Table created
SQL> shutdown immediate;
Database closed
Database dismounted
ORACLE instance shut down
SQL> startup nomount
ORACLE instance started
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
C:\Documents and Settings\qigong>rman
Recovery Manager: Release Production
Copyright (c) Oracle All rights reserved
RMAN> connect target
connected to target database: test (not mounted)
RMAN> restore controlfile from autobackup;
Starting restore at 月
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_
channel ORA_DISK_: sid= devtype=DISK
recovery area destination: C:\oracle\product\\flash_recovery_area
database name (or lock name space) used for search: TEST
channel ORA_DISK_: autobackup found in the recovery area
channel ORA_DISK_: autobackup found: C:\ORACLE\PRODUCT\ \FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\__\O_MF_S__LOSFCX_BKP
channel ORA_DISK_: controlfile restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\\ORADATA\TEST\CONTROLCTL
output filename=C:\ORACLE\PRODUCT\\ORADATA\TEST\CONTROLCTL
output filename=C:\ORACLE\PRODUCT\\ORADATA\TEST\CONTROLCTL
Finished restore at 月
RMAN> exit
RMAN: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
SQL> alter database mount;
alter database mount
*
ERROR at line :
ORA: invalid password file
C:\oracle\product\\Db_\DATABASE\PWDtestORA
SQL> DECLARE
devtype varchar();
done boolean;
BEGIN
devtype:=sysdbms_backup_restoredeviceAllocate (type=>ident=>);
sysdbms_backup_restorerestoreSetDatafile;
sysdbms_backup_restorerestoreDatafileTo(dfnumber=>toname=>c:\testDBF);
sysdbms_backup_restorerestoreBackupPiece(done=>donehandle=>C:\FUATK___
sysdbms_backup_restoredeviceDeallocate;
END;
/
PL/SQL procedure successfully completed
SQL> shutdown immediate;
ORA: database not open
Database dismounted
ORACLE instance shut down
SQL> startup nomount
ORACLE instance started
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
SQL> CREATE CONTROLFILE REUSE DATABASE TEST NORESETLOGS ARCHIVELOG
MAXLOGFILES
MAXLOGMEMBERS
MAXDATAFILES
MAXINSTANCES
MAXLOGHISTORY
LOGFILE
GROUP C:\ORACLE\PRODUCT\\ORADATA\TEST\REDOLOG SIZE M
GROUP C:\ORACLE\PRODUCT\\ORADATA\TEST\REDOLOG SIZE M
GROUP C:\ORACLE\PRODUCT\\ORADATA\TEST\REDOLOG SIZE M
STANDBY LOGFILE
DATAFILE
C:\ORACLE\PRODUCT\\ORADATA\TEST\SYSTEMDBF
C:\ORACLE\PRODUCT\\ORADATA\TEST\UNDOTBSDBF
C:\ORACLE\PRODUCT\\ORADATA\TEST\SYSAUXDBF
C:\ORACLE\PRODUCT\\ORADATA\TEST\USERSDBF
C:\ORACLE\PRODUCT\\ORADATA\TEST\EXAMPLEDBF
C:\TBSDBF
C:\TBSDBF
C:\TBSDBF
C:\TBSDBF
C:\TBSDBF
C:\TBSDBF
C:\TBSDBF
C:\TBSDBF
C:\TESTDBF
CHARACTER SET ZHSGBK
;
Control file created
SQL> recover database;
ORA: change generated at // :: needed for thread
ORA: suggestion : C:\ORACLE\ARCHIVE\ARC_
ORA: change for thread is in sequence #
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA: recovery session canceled due to errors
ORA: unnamed datafile(s) added to controlfile by media recovery
ORA: data file : C:\TESTDBF
ORA: media recovery not started
SQL> select name from v$datafile;
NAME
C:\ORACLE\PRODUCT\\ORADATA\TEST\SYSTEMDBF
C:\ORACLE\PRODUCT\\ORADATA\TEST\UNDOTBSDBF
C:\ORACLE\PRODUCT\\ORADATA\TEST\SYSAUXDBF
C:\ORACLE\PRODUCT\\ORADATA\TEST\USERSDBF
C:\ORACLE\PRODUCT\\ORADATA\TEST\EXAMPLEDBF
C:\TBSDBF
C:\TBSDBF
C:\TBSDBF
C:\TBSDBF
C:\TBSDBF
C:\TBSDBF
NAME
C:\TBSDBF
C:\TBSDBF
C:\TESTDBF
C:\WINDOWS\SYSTEM\UNNAMED
rows selected
SQL> alter database create datafile C:\WINDOWS\SYSTEM\UNNAMED as c:\testdb
Database altered
SQL> recover database;
Media recovery complete
SQL> alter database open;
Database altered
SQL> select * from testlost;
no rows selected
SQL> select * from testlost;
A
在上面这个试验里面我们可能还会有一个疑问既然控制文件里面记载的是unamedxxxxxxx的文件名数据字典的里面的filename也是 unamedxxxxxxx(实际上数据字典里面不保存文件名所有的文件名都只包含在控制文件当中)日志里也是没有记载文件名那么当我们 recover database的时候为什么会出现
ORA: recovery session canceled due to errors
ORA: unnamed datafile(s) added to controlfile by media recovery
ORA: data file : C:\TESTDBF
ORA: media recovery not started
Oracle他是从哪里得到这个unamedxxxxxxx文件的真实文件是C:\TESTDBF呢？
先来看看日志里记载了什么东西
用logminer查一下
EXECUTE DBMS_LOGMNR_DBUILD(dictionary_filename => l_dictionaryoradictionary_location => /disk/oradata/);
EXECUTE DBMS_LOGMNRADD_LOGFILE( LogFileName => /disk/oradata/redologOptions => dbms_logmnrNEW);
EXECUTE DBMS_LOGMNRSTART_LOGMNR(DictFileName =>/disk/oradata/l_dictionaryora);
SELECT sql_redo FROM V$LOGMNR_CONTENTS where upper(sql_redo) like %TEST%;
SQL_REDO
create tablespace test
datafile c:\testdbf size m reuse
extent management local
segment space management auto
uniform size k;
只发现了这句ddl并没有往数据字典里插入文件名
再来看create tablespace的trace文件
……
insert into ts$ (ts#nameonline$contents$undofile#undoblock#blocksize
dflmaxextdflinitdflincrdflextpctdflminextdflminleninc#owner#scnwrp
scnbaspitrscnwrppitrscnbasdflogging affstrengthbitmappedplugged
directallowedflagssparespare)
values
(::::::::::::::::::::::::)
insert into file$ (file#blocksts#status$relfile#maxextendinccrscnwrpcrscnbasspare)
values
(::DECODE(:NULL:): DECODE(:NULL:)::::DECODE(:NULL:))
……
这两条语句说明了oracle在ts$file$分别插入了新创建表空间和文件的信息但并不包括文件名
看起来好像这个文件名是无出处的即不直接取至日志也不存在于数据字典控制文件中则存在着错误的文件名那他究竟从哪里来呢？
看下控制文件中包含这个文件的dump 信息
DATA FILE #:
(name #) C:\WINDOWS\SYSTEM\UNNAMED
creation size= block size= status=x head= tail= dup=
tablespace index= krfil= prev_file=
unrecoverable scn: x // ::
Checkpoint cnt: scn: xde // ::
Stop scn: xde // ::
Creation Checkpointed at scn: xc // ::
从这里我们知道testdbf是号文件这样的话oracle可以从file$表中找到这个文件对应的ts#再从ts$中找到表空间的名字然后回到日志对应日志中的ddl即可以得到错误的文件是c:\testdbf答案出来了！oracle根据这么一套流程找到了正确的文件名并提示给用户这样的话我们只需要很方便的create datafile reuse一下就可以继续恢复进程了
