恢复一则 alter database create datafile '' as ''

之前有控制文件的备份，数据文件全部丢失，online redo file和archived redo是连续的，恢复如下。
 我们要用noresetlogs因为日志文件全都是完好的。
SQL> CREATE CONTROLFILE REUSE DATABASE "ICMNLSDB" NORESETLOGS  ARCHIVELOG
  2  --  SET STANDBY TO MAXIMIZE PERFORMANCE
  3      MAXLOGFILES 5
  4      MAXLOGMEMBERS 3
  5      MAXDATAFILES 100
  6      MAXINSTANCES 1
  7      MAXLOGHISTORY 226
  8  LOGFILE
  9    GROUP 1 'D:\ORACLE\ORADATA\ICMNLSDB\REDO01.LOG'  SIZE 100M,
 10    GROUP 2 'D:\ORACLE\ORADATA\ICMNLSDB\REDO02.LOG'  SIZE 100M,
 11    GROUP 3 'D:\ORACLE\ORADATA\ICMNLSDB\REDO03.LOG'  SIZE 100M
 12  -- STANDBY LOGFILE
 13  DATAFILE
 14    'D:\ORACLE\ORADATA\ICMNLSDB\SYSTEM01.DBF',
 15    'D:\ORACLE\ORADATA\ICMNLSDB\UNDOTBS01.DBF',
 16    'D:\ORACLE\ORADATA\ICMNLSDB\INDX01.DBF',
 17    'D:\ORACLE\ORADATA\ICMNLSDB\TOOLS01.DBF',
 18    'D:\ORACLE\ORADATA\ICMNLSDB\USERS01.DBF'
 19  CHARACTER SET ZHS16GBK
 20  ;

控制文件已创建

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter session set events 'immediate trace name controlf level 10';

会话已更改。

SQL> recover database;
ORA-00279: ?? 92128 (? 12/25/2007 14:26:11 ??) ???? 1 ????
ORA-00289: ??: D:\ORACLE\ARCHIVED_DEST\ARC00005.001
ORA-00280: ?? 92128 ???? 1 ???? # 5 ???

指定日志: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: ??????????
ORA-01244: ????????????????????
ORA-01110: ???? 6: 'D:\ORACLE\ORADATA\ICMNLSDB\ALAN01.DBF'

ORA-01112: ???????

SQL> recover database;
ORA-00283: ??????????
ORA-01111: ???? 6 ???? - ?????????
ORA-01110: ???? 6: 'D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006'
ORA-01157: ????/?????? 6 - ??? DBWR ????
ORA-01111: ???? 6 ???? - ?????????
ORA-01110: ???? 6: 'D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006'

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\ICMNLSDB\SYSTEM01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\UNDOTBS01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\INDX01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\TOOLS01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\USERS01.DBF
D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006

已选择6行。

SQL> alter database create datafile 'D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006' as
  2  ' D:\ORACLE\ORADATA\ICMNLSDB\alan01.dbf' reuse;
alter database create datafile 'D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006' as
*
ERROR 位于第 1 行:
ORA-01119: ??????? ' D:\ORACLE\ORADATA\ICMNLSDB\alan01.dbf' ???
ORA-27040: skgfrcre: ???????????
OSD-04002: ????????????
O/S-Error: (OS 123) ????????????????????????????????

SQL> alter database create datafile 'D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006' as
  2  ' D:\ORACLE\ORADATA\ICMNLSDB\alan01.dbf';
alter database create datafile 'D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006' as
*
ERROR 位于第 1 行:
ORA-01119: ??????? ' D:\ORACLE\ORADATA\ICMNLSDB\alan01.dbf' ???
ORA-27040: skgfrcre: ???????????
OSD-04002: ????????????
O/S-Error: (OS 123) ????????????????????????????????

SQL> alter database create datafile 'D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006' as
  2  ' D:\ORACLE\ORADATA\ICMNLSDB\alan01.dbf' reuse;
alter database create datafile 'D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006' as
*
ERROR 位于第 1 行:
ORA-01119: ??????? ' D:\ORACLE\ORADATA\ICMNLSDB\alan01.dbf' ???
ORA-27040: skgfrcre: ???????????
OSD-04002: ????????????
O/S-Error: (OS 123) ????????????????????????????????

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\ICMNLSDB\SYSTEM01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\UNDOTBS01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\INDX01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\TOOLS01.DBF
D:\ORACLE\ORADATA\ICMNLSDB\USERS01.DBF
D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006

已选择6行。

SQL> alter database create datafile 'D:\ORACLE\PRODUCT\ORA92\DATABASE\UNNAMED00006'
  2  as 'D:\ORACLE\ORADATA\ICMNLSDB\ALAN01.DBF';

数据库已更改。

SQL> recover database;
完成介质恢复。
SQL> quit
从Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production中断开

C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 12月 25 15:05:57 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba;
已连接。
SQL> alter session set events 'immediate trace name controlf level 10';

会话已更改。

SQL> alter database open;

数据库已更改。

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

转载于:http://blog.itpub.net/12361284/viewspace-1311/