如何恢复删除的数据文件?

tomer

SQL> alter tablespace test begin backup
  2  ;

Tablespace altered.


SQL> host copy f:\ngndata\tablespace\file1.dbf f:\
已复制         1 个文件。

SQL> alter tablespace test end backup;

Tablespace altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

现在把file1.dbf删除
SQL> startup
ORACLE instance started.

Total System Global Area  163577856 bytes
Fixed Size                   787948 bytes
Variable Size             145750548 bytes
Database Buffers           16777216 bytes
Redo Buffers                 262144 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'F:\NGNDATA\TABLESPACE\FILE1.DBF'


SQL> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup force mount
ORACLE instance started.

Total System Global Area  163577856 bytes
Fixed Size                   787948 bytes
Variable Size             145750548 bytes
Database Buffers           16777216 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: 'F:\NGNDATA\TABLESPACE\FILE1.DBF'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'F:\NGNDATA\TABLESPACE\FILE1.DBF'


SQL>

请问除了把备份的文件直间copy回来,还有其他的命令吗?比如recover

numenhuang

如果是archivelog模式，可以通过这样的步骤：
startup mount;
alter database datafile 'xxx' offline;
alter database create datafile 'xxxxx';
recover datafile 'xxxx';
alter database datafile 'xxxx' online;
alter database open;

tomer

SQL> alter database datafile 'f:\ngndata\TABLESPACE\FILE1.DBF' offline
  2  ;

Database altered.

SQL> alter database create datafile 'f:\ngndata\TABLESPACE\FILE1.DBF';

Database altered.

SQL> recover datafile 'f:\ngndata\TABLESPACE\FILE1.DBF';
ORA-00279: change 327310 generated at 07/24/2007 10:42:40 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00005_0628769665.001
ORA-00280: change 327310 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00005_0628769665.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            d:\oracle\product\10.1.0\Db_3\RDBMS
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16
SQL>

numenhuang

archived log不全，手工删除过？

tomer

没有删过

numenhuang

把所有的archived  log列出来

joelau

看不懂lz的问题
是想问recover和restore命令的区别？
这样的备份方法只可以用在archive模式下，非归档模式需要备份所有datafile.

tomer

原帖由 numenhuang 于 2007-7-27 09:23 发表
把所有的archived  log列出来

如图:

numenhuang

ARC00001_0628769665.001-ARC00005_0628769665.001 文件呢？

select name,completion_time,first_time,block from v$archived_log

tomer

SQL> select name,completion_time,first_time,blocks from v$ar

NAME
------------------------------------------------------------

COMPLETION_TIM FIRST_TIME         BLOCKS
-------------- -------------- ----------
D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00006_0628769665.001
24-7月 -07     24-7月 -07           6732

D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00007_0628769665.001
24-7月 -07     24-7月 -07          19566

D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00008_0628769665.001
24-7月 -07     24-7月 -07          19826


NAME
------------------------------------------------------------

COMPLETION_TIM FIRST_TIME         BLOCKS
-------------- -------------- ----------
D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00009_0628769665.001
25-7月 -07     24-7月 -07          10206

D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00010_0628769665.001
25-7月 -07     25-7月 -07          19566

D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00011_0628769665.001
25-7月 -07     25-7月 -07          19566


NAME
------------------------------------------------------------

COMPLETION_TIM FIRST_TIME         BLOCKS
-------------- -------------- ----------
D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00012_0628769665.001
25-7月 -07     25-7月 -07          19554

D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00013_0628769665.001
26-7月 -07     25-7月 -07          10236

D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00014_0628769665.001
26-7月 -07     26-7月 -07          19565


NAME
------------------------------------------------------------

COMPLETION_TIM FIRST_TIME         BLOCKS
-------------- -------------- ----------
D:\ORACLE\PRODUCT\10.1.0\DB_3\RDBMS\ARC00015_0628769665.001
26-7月 -07     26-7月 -07          19566


10 rows selected.

SQL>