1.将数据库abort掉,然后破坏对应的数据文件
2.startup数据库,因为此时被破坏了,所以数据库只能到mount状态
3.当前情况是没有数据文件的备份,但是有完好的归档
4.∴可以利用create datafile来进行恢复
具体参看代码:
SQL> shutdown abort
ORACLE 例程已经关闭。
==============在这儿破坏数据文件==================
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 180358020 bytes
Database Buffers 423624704 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS01.DBF'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------------------------------
6 ONLINE ONLINE FILE NOT FOUND 0
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- --------------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 FILE NOT FOUND 0
已选择6行。
SQL> edit
已写入 file afiedt.buf
1 SELECT D.NAME, T.NAME AS TABLESPACE_NAME
2 FROM V$DATAFILE D, V$TABLESPACE T
3 WHERE T.TS# = D.TS#
4* AND D.FILE# = 6
SQL> /
NAME
----------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS01.DBF
DDTEST
SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS02.DBF'
2 as 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS01.DBF';
数据库已更改。
SQL> recover datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS01.DBF';
完成介质恢复。
SQL> select * from dd.t1;
select * from dd.t1
*
第 1 行出现错误:
ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询
SQL> alter tablespace ddtest online;
alter tablespace ddtest online
*
第 1 行出现错误:
ORA-01109: 数据库未打开
SQL> alter database open;
数据库已更改。
SQL> select * from dd.t1;
ID NAME
---------- ----------
1 test1
2 test2
SQL>
如果不是这样子的话,则会出现下面的情况:
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> SELECT * FROM V$RECOVER_FILE;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- -------
6 OFFLINE OFFLINE FILE NOT FOUND 0
SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS02.DBF'
2 as 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS01.DBF';
alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS02.DBF'
*
第 1 行出现错误:
ORA-01178: 文件 6 在最后一个 CREATE CONTROLFILE 之前创建, 无法重新创建
ORA-01110: 数据文件 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS02.DBF'
查询联机文档:
ORA-01178: file string created before last CREATE CONTROLFILE, cannot recreate
Cause: Attempted to use ALTER DATABASE CREATE DATAFILE to recreate a datafile that existed at the last CREATE CONTROLFILE command. The information needed to recreate the file was lost with the control file that existed when the file was added to the database.
Action: Find a backup of the file, and recover it. Do incomplete recovery to time before file was originally created.
那只有用备份的数据文件进行恢复了.
SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS02.DBF'
2 as 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS01.DBF';
alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS02.DBF'
*
第 1 行出现错误:
ORA-01178: 文件 6 在最后一个 CREATE CONTROLFILE 之前创建, 无法重新创建
ORA-01110: 数据文件 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS02.DBF'
SQL> host copy E:\oracletest\backup\suredd\DDTESTTBS02.DBF D:\oracle\product\10.2.0\oradata\suredd\
SQL> recover datafile 'D:\oracle\product\10.2.0\oradata\suredd\DDTESTTBS02.DBF';
完成介质恢复。
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> alter tablespace ddtest online;
表空间已更改。
SQL> select * from dd.t1;
ID NAME
---------- ----------
1 test1
2 test2
SQL>
由此可见,有备份是多么的重要啊!!!
posted on 2011-05-04 23:32
xrzp 阅读(677)
评论(0) 编辑 收藏 所属分类:
oracle-备份恢复