恢復一則 alter database create datafile '' as ''

oracle_ace發表於2007-12-25
Database

之前有控制檔案的備份，資料檔案全部丟失，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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章