資料檔案offline 時oracle 幹了那些事？，offlineoracle

資料檔案offline 時oracle 幹了那些事？，offlineoracle

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL>  oradebug event 10046 trace name context forever ,level 12;
Statement processed.
SQL> alter database datafile '/oracle/oradata/lixora/LIXORA/datafile/o1_mf_zjhcsoft_b24623wy_.dbf' offline;

Database altered.

SQL> oradebug event 10046 trace name context  off;
Statement processed.
SQL> oradebug tracefile_name;
/oracle/admin/lixora/udump/lixora_ora_29515.trc

*** 2014-09-30 10:50:56.261
*** SERVICE NAME:(SYS$USERS) 2014-09-30 10:50:56.261
*** SESSION ID:(159.3) 2014-09-30 10:50:56.261
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1378950640880510
WAIT #0: nam='SQL*Net message from client' ela= 9226316 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1378950650107126
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #9 len=101 dep=0 uid=0 oct=35 lid=0 tim=1378950650107611 hv=707773262 ad='7e6cd4e8'
alter database datafile '/oracle/oradata/lixora/LIXORA/datafile/o1_mf_zjhcsoft_b24623wy_.dbf' offline
END OF STMT
PARSE #9:c=0,e=394,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1378950650107606
BINDS #9:
WAIT #9: nam='control file sequential read' ela= 24 file#=0 block#=1 blocks=1 obj#=0 tim=1378950650107957
WAIT #9: nam='control file sequential read' ela= 21 file#=1 block#=1 blocks=1 obj#=0 tim=1378950650108006
WAIT #9: nam='control file sequential read' ela= 9 file#=2 block#=1 blocks=1 obj#=0 tim=1378950650108035
WAIT #9: nam='control file sequential read' ela= 6 file#=0 block#=16 blocks=1 obj#=0 tim=1378950650108060
WAIT #9: nam='control file sequential read' ela= 7 file#=0 block#=18 blocks=1 obj#=0 tim=1378950650108083
WAIT #9: nam='control file sequential read' ela= 10 file#=0 block#=30 blocks=1 obj#=0 tim=1378950650108114
WAIT #9: nam='control file sequential read' ela= 8 file#=0 block#=1 blocks=1 obj#=0 tim=1378950650108250
WAIT #9: nam='control file sequential read' ela= 5 file#=1 block#=1 blocks=1 obj#=0 tim=1378950650108278
WAIT #9: nam='control file sequential read' ela= 4 file#=2 block#=1 blocks=1 obj#=0 tim=1378950650108299
WAIT #9: nam='control file sequential read' ela= 5 file#=0 block#=16 blocks=1 obj#=0 tim=1378950650108321
WAIT #9: nam='control file sequential read' ela= 6 file#=0 block#=18 blocks=1 obj#=0 tim=1378950650108342
WAIT #9: nam='control file sequential read' ela= 20 file#=0 block#=23 blocks=1 obj#=0 tim=1378950650108385
WAIT #9: nam='rdbms ipc reply' ela= 32296 from_process=5 timeout=910 p3=0 obj#=0 tim=1378950650141300
WAIT #9: nam='rdbms ipc reply' ela= 9556 from_process=5 timeout=910 p3=0 obj#=0 tim=1378950650150904
XCTEND rlbk=0, rd_only=1
EXEC #9:c=1000,e=43469,p=0,cr=0,cu=2,mis=0,r=0,dep=0,og=1,tim=1378950650151144
WAIT #9: nam='log file sync' ela= 14463 buffer#=1811 p2=0 p3=0 obj#=0 tim=1378950650165680
WAIT #9: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1378950650165756
*** 2014-09-30 10:51:13.382
WAIT #9: nam='SQL*Net message from client' ela= 7434358 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1378950657600174

 

 

結論：

對一個datafile無論是執行offline還是offline drop，都是只改寫了control檔案，不會更新file$和ts$，這就是為什麼可以在mount狀態下對某個datafile執行offline/offline drop的本質原因；

而在datafile on line 過程中 oracle會檢查資料檔案頭的scn 值，如果scn 值小於其他的資料檔案，那麼這個資料檔案就需要進行恢複，也就是說只要用bbed 把offline 的資料檔案的scn 該改成和其他資料檔案一樣，那個這個檔案就又能online ，而又不需要記錄檔來進行恢複。

oracle資料庫手動刪除資料檔案前沒有將資料表空間offline,沒有開啟歸檔

startup mount;後alter database datafile n offline drop;
alter database open;
 
oracle資料庫問題，如果資料庫系統不小心丟失了沒有備份的資料檔案,該怎恢複資料庫的正常狀態?

那資料是肯定丟失了的。你可以選擇將丟失的資料檔案offline掉，再試圖開啟資料庫。