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 閱讀(683)
評論(0) 編輯 收藏 所屬分類:
oracle-備份恢復