1.將數(shù)據(jù)庫abort掉,然后破壞對應的數(shù)據(jù)文件
2.startup數(shù)據(jù)庫,因為此時被破壞了,所以數(shù)據(jù)庫只能到mount狀態(tài)
3.當前情況是沒有數(shù)據(jù)文件的備份,但是有完好的歸檔
4.∴可以利用create datafile來進行恢復
具體參看代碼:
SQL> shutdown abort
ORACLE 例程已經(jīng)關閉。
==============在這兒破壞數(shù)據(jù)文件==================
SQL> startup
ORACLE 例程已經(jīng)啟動。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 180358020 bytes
Database Buffers 423624704 bytes
Redo Buffers 7135232 bytes
數(shù)據(jù)庫裝載完畢。
ORA-01157: 無法標識/鎖定數(shù)據(jù)文件 6 - 請參閱 DBWR 跟蹤文件
ORA-01110: 數(shù)據(jù)文件 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';
數(shù)據(jù)庫已更改。
SQL> recover datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS01.DBF';
完成介質(zhì)恢復。
SQL> select * from dd.t1;
select * from dd.t1
*
第 1 行出現(xiàn)錯誤:
ORA-01219: 數(shù)據(jù)庫未打開: 僅允許在固定表/視圖中查詢
SQL> alter tablespace ddtest online;
alter tablespace ddtest online
*
第 1 行出現(xiàn)錯誤:
ORA-01109: 數(shù)據(jù)庫未打開
SQL> alter database open;
數(shù)據(jù)庫已更改。
SQL> select * from dd.t1;
ID NAME
---------- ----------
1 test1
2 test2
SQL>
如果不是這樣子的話,則會出現(xiàn)下面的情況:
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 行出現(xiàn)錯誤:
ORA-01178: 文件 6 在最后一個 CREATE CONTROLFILE 之前創(chuàng)建, 無法重新創(chuàng)建
ORA-01110: 數(shù)據(jù)文件 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SUREDD\DDTESTTBS02.DBF'
查詢聯(lián)機文檔:
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.
那只有用備份的數(shù)據(jù)文件進行恢復了.
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 行出現(xiàn)錯誤:
ORA-01178: 文件 6 在最后一個 CREATE CONTROLFILE 之前創(chuàng)建, 無法重新創(chuàng)建
ORA-01110: 數(shù)據(jù)文件 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';
完成介質(zhì)恢復。
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-備份恢復