[20151023]linux下刪除資料檔案的恢復細節2
[20151023]linux下刪除資料檔案的恢復的一些細節問題(補充).txt
--以前曾經寫過一篇關於
--連結：http://blog.itpub.net/267265/viewspace-763969/
--裡面提到實際上這種方式對於生產系統不是很合適，而且生產系統情況非常複雜，不可能出現刪除資料檔案時沒有事務產生。
--這種方式僅僅適合no archivelog的模式(沒有辦法的選擇)，我當時還提到這種方式一定要快，因為我的測試執行 alter system
--checkpoint;,資料庫直接crash。
--正好別人問我一些檢查點的問題，讓我重新思考以前的解決思路。我喜歡透過例子詳細說明：
1.建立環境：
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 510 SYSTEM *** /mnt/ramdisk/test/system01.dbf
2 350 UNDOTBS1 *** /mnt/ramdisk/test/undotbs01.dbf
3 370 SYSAUX *** /mnt/ramdisk/test/sysaux01.dbf
4 100 USERS *** /mnt/ramdisk/test/users01.dbf
5 100 EXAMPLE *** /mnt/ramdisk/test/example01.dbf
6 15 MSSM *** /mnt/ramdisk/test/mssm01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 18 TEMP 32767 /mnt/ramdisk/test/test01.dbf
SYS@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
--保險期間我在關閉資料庫的情況下做了一個冷備份，當然僅僅備份沒mssm01.dbf檔案。
--注：我前面的測試是11g，這次是10g。
2.開始測試：
--session 1:
SCOTT@test> create table t tablespace mssm as select * from dba_objects ;
Table created.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50650
SCOTT@test> alter system checkpoint;
System altered.
SCOTT@test> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1));
SPID
------
10113
$ ls -l /proc/10113/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 10:50:40 18 -> /mnt/ramdisk/test/mssm01.dbf
lrwx------ 1 oracle oinstall 64 2015-10-23 10:50:40 19 -> /mnt/ramdisk/test/mssm01.dbf
--可以發現18,19指向/mnt/ramdisk/test/mssm01.dbf.
2.刪除資料檔案.
$ cd /mnt/ramdisk/test
# rm mssm01.dbf
/bin/rm: remove regular file `mssm01.dbf'? y
# ls -l /proc/10113/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 10:50:40 18 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2015-10-23 10:50:40 19 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
--session 1:
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50650
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SQL> commit ;
Commit complete.
--刪除了檔案,file habdle以及資料塊的記憶體並沒有釋放,依舊可以操作.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50651
--session 2,這個是刪除檔案後進入的：
SCOTT@test> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1));
SPID
------
10401
# ls -l /proc/10401/fd | grep mssm
--無輸出
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50651
SCOTT@test> alter system flush buffer_cache;
alter system flush buffer_cache
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
--在10g下直接crash.看看alert*.log檔案：
Fri Oct 23 10:58:07 2015
ALTER SYSTEM: Flushing buffer cache
Fri Oct 23 10:58:07 2015
Errors in file /u01/app/oracle/admin/test/bdump/test_dbw1_10067.trc:
ORA-01116: error in opening database file 6
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Fri Oct 23 10:58:07 2015
DBW1: terminating instance due to error 1116
Instance terminated by DBW1, pid = 10067
--注意看錯誤提示：dbw1程式。不是版本的問題。重新啟動恢復資料檔案。
SYS@test> show parameter cpu_count
NAME TYPE VALUE
------------------------------------ -------- ------
cpu_count integer 24
--這次的測試環境是cpu_count=24,24/8=3個，也就是會開啟3個dbw程式。
$ ps -ef | grep ora_db[w]
oracle 11198 1 0 11:46 ? 00:00:00 ora_dbw0_test
oracle 11200 1 0 11:46 ? 00:00:00 ora_dbw1_test
oracle 11202 1 0 11:46 ? 00:00:00 ora_dbw2_test
$ ls -l /proc/11198/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 11:48:02 23 -> /mnt/ramdisk/test/mssm01.dbf
$ ls -l /proc/11200/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 11:48:07 15 -> /mnt/ramdisk/test/mssm01.dbf
$ ls -l /proc/11202/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 11:48:10 15 -> /mnt/ramdisk/test/mssm01.dbf
--我估計前面的測試其中一個dbw1沒有開啟/mnt/ramdisk/test/mssm01.dbf的控制程式碼。重新測試：
--session 1:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
154 5 11289 alter system kill session '154,5' immediate;
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
--刪除檔案：
$ rm mssm01.dbf
/bin/rm: remove regular file `mssm01.dbf'? y
--session 2:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
151 7 11341 alter system kill session '151,7' immediate;
SCOTT@test> select count(*) from t;
select count(*) from t
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--因為訪問的檔案一些塊沒有讀到記憶體，報錯。
SCOTT@test> select * from t where rownum<=1;
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S
------ -------------------- ---------- ------------ -------------- ------------------- ------------------- ------------------- ------------------- ------- - - -
SYS ICOL$ 20 2 TABLE 2008-03-12 00:39:48 2008-03-12 00:58:12 2008-03-12:00:39:48 VALID N N N
--因為訪問的這個塊已經在記憶體中，不會報錯。但是執行如下：
SCOTT@test> alter tablespace mssm read only ;
alter tablespace mssm read only
*
ERROR at line 1:
ORA-03135: connection lost contact
--報錯出現：10g下一旦出現寫檔案出錯，資料庫直接crash。alert*.log檔案：
Fri Oct 23 11:54:46 2015
Errors in file /u01/app/oracle/admin/test/bdump/test_dbw2_11202.trc:
ORA-01116: error in opening database file 6
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Fri Oct 23 11:54:46 2015
DBW2: terminating instance due to error 1116
Instance terminated by DBW2, pid = 11202
--很奇怪，對於dbw2程式,檔案 /mnt/ramdisk/test/mssm01.dbf是開啟的。這樣對於10g的資料庫，我個人認為難度更大。
--猜測在執行alter tablespace mssm read only ;，會話也許要先開啟檔案控制程式碼，在透過dbw寫髒塊。因為檔案沒有刪除，
--報錯以後直接crash。
3.在重新測試,恢復過程略。
--session 1:
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50653
$ rm /mnt/ramdisk/test/mssm01.dbf
/bin/rm: remove regular file `/mnt/ramdisk/test/mssm01.dbf'? y
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50653
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
--因為控制程式碼是開啟的，訪問沒有問題。
SCOTT@test> alter tablespace mssm read only ;
Tablespace altered.
--可以發現在開啟控制程式碼的情況下，執行以上語句ok的。再次驗證我前面的推測。
SCOTT@test> alter tablespace mssm read write ;
Tablespace altered.
SCOTT@test> alter system flush buffer_cache;
System altered.
SCOTT@test> alter system checkpoint;
System altered.
--可以發現在開啟控制程式碼的情況下，執行以上語句ok的。並沒有像我前面的測試直接crash，實際上如果你新開一個會話執行以上語句，至
--少在10g的資料庫，直接crash，因為dbw程式在寫盤錯誤的情況下，直接crash。
4.這就提出一個問題，如果想透過copy的方式，必須設定表空間只讀，而連上的會話並沒有開啟/mnt/ramdisk/test/mssm01.dbf控制程式碼。
這樣alter tablespace mssm read only ;這樣的操作要寫檔案檢查點(我認為把它稱為表空間檢查點更準確一點。)
參考連結：http://blog.itpub.net/267265/viewspace-1798792/
--只有想辦法先騙過oracle這個檔案是存在的才行，有了這個思路就簡單了，先使用ln命令做一個軟連結，騙過oracle先。
$ ls -l /proc/11613/fd/12
lrwx------ 1 oracle oinstall 64 2015-10-23 12:14:57 /proc/11613/fd/12 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
$ ln -s /proc/11613/fd/12 /mnt/ramdisk/test/mssm01.dbf
--session 2:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
137 10 11770 alter system kill session '137,10' immediate;
SCOTT@test> select count(*) from t;
select count(*) from t
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--???，檢視alert*.log檔案，發現：
Fri Oct 23 12:07:43 2015
Errors in file /u01/app/oracle/admin/test/bdump/test_ckpt_11543.trc:
ORA-01171: datafile 6 going offline due to error advancing checkpoint
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--當我發alter system checkpoint;時，實際上資料檔案6已經無法寫入直接offline.
SCOTT@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
6 0 0 0 OFFLINE 0
--再次說明這種恢復方式值得商榷。
5.恢復重新繼續測試：
-- session 1:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
157 5 13787 alter system kill session '157,5' immediate;
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50655
$ rm mssm01.dbf
/bin/rm: remove regular file `mssm01.dbf'? y
--session 2:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
156 37 13826 alter system kill session '156,37' immediate;
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50655
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
--也許插入的塊正好在記憶體，不會報錯。
--session 1:
SCOTT@test> alter system flush buffer_cache;
System altered.
--session 1:
SCOTT@test> insert into t select * from t where rownum<=1;
insert into t select * from t where rownum<=1
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--出現錯誤。這個時候要快速的建立一個連結：
# ps -ef | grep ora_db[w]
oracle 13670 1 0 15:26 ? 00:00:00 ora_dbw0_test
oracle 13672 1 0 15:26 ? 00:00:00 ora_dbw1_test
oracle 13674 1 0 15:26 ? 00:00:00 ora_dbw2_test
$ ls -l /proc/13670/fd/ | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 15:39:22 25 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
$ ln -s /proc/13670/fd/25 /mnt/ramdisk/test/mssm01.dbf
--假設這個時候還有事務產生：
--session 1:
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
--session 3:
SCOTT@test> alter tablespace mssm read only ;
Tablespace altered.
--這個時候才不會報錯。
--這樣不可能再發生事務。
--session 1:
SCOTT@test> insert into t select * from t where rownum<=1;
insert into t select * from t where rownum<=1
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--這個時候刪除連結，再建立真實的檔案。
$ rm mssm01.dbf
/bin/rm: remove symbolic link `mssm01.dbf'? y
$ cp /proc/13670/fd/25 /mnt/ramdisk/test/mssm01.dbf
--如果這個時候執行alter tablespace mssm read write ;會報錯，因為兩者的檔案i節點不一致。
--不過我的測試不報錯。
--session 3:
SCOTT@test> alter tablespace mssm read write ;
Tablespace altered.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50657
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
$ ls -l /proc/13670/fd/ | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 15:39:22 25 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
--不顧這樣實際上有點亂套的。dbw程式寫的可能是刪除的檔案。
--session 3:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
156 41 13980 alter system kill session '156,41' immediate;
$ ls -l /proc/13980/fd |grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 15:50:05 14 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
--可以發現使用的還是刪除的連結。
--session 4：
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
143 77 14112 alter system kill session '143,77' immediate;
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50659
$ ls -l /proc/14112/fd |grep mssm
SCOTT@test> insert into t select * from t where rownum<=100;
100 rows created.
SCOTT@test> insert into t select * from t where rownum<=100;
100 rows created.
SCOTT@test> insert into t select * from t where rownum<=100;
100 rows created.
SCOTT@test> insert into t select * from t where rownum<=100;
100 rows created.
SCOTT@test> commit ;
Commit complete.
--session 1:
SCOTT@test> alter system flush buffer_cache;
System altered.
SCOTT@test> insert into t select * from t where rownum<=100;
100 rows created.
SCOTT@test> commit ;
Commit complete.
$ ls -l /proc/14112/fd |grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 15:53:51 10 -> /mnt/ramdisk/test/mssm01.dbf
--這個時候就亂套了。
--session 4：
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50759
--看看我前面在會話4插入400條記錄沒有報錯，並且提交(注意這個會話沒有開啟指向mssm01.dbf的控制程式碼)。而當我在會話1執行alter
--system flush buffer_cache;後插入100條。在會話4檢查，與前面的對比400條的插入由於訪問的檔案不同，消失了。
6.好了，我已經展示許多情況，有點亂。現在恢復是否正常：
SCOTT@test> alter database datafile 6 offline ;
Database altered.
$ cp /home/oracle/mssm1/mssm01.dbf /mnt/ramdisk/test/
/bin/cp: overwrite `/mnt/ramdisk/test/mssm01.dbf'? y
--這個是我冷備份的資料檔案。
SCOTT@test> alter database datafile 6 online ;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SCOTT@test> recover datafile 6;
ORA-00279: change 12694467422 generated at 10/23/2015 10:46:50 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_10_23/o1_mf_1_89_%u_.arc
ORA-00280: change 12694467422 for thread 1 is in sequence #89
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 12694487796 generated at 10/23/2015 11:41:50 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_10_23/o1_mf_1_90_%u_.arc
ORA-00280: change 12694487796 for thread 1 is in sequence #90
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_10_23/o1_mf_1_89_c2mc3yd7_.arc' no longer needed for this recovery
ORA-00283: recovery session canceled due to errors
ORA-12801: error signaled in parallel query server P005
ORA-00600: internal error code, arguments: [3020], [6], [837], [1], [93], [2614], [16], []
ORA-10567: Redo is inconsistent with data block (file#)
ORA-01112: media recovery not started
--已經無法恢復。
--後記補充1點，不要drop也可以。測試有點亂。我估計我自己忘記
alter database datafie 6 offline;
alter database datafie 6 online;
--而直接alter tablespace xxxx read write;
7.如果這種情況出現，正常的恢復步驟(如果要採用這種方式)：
利用先透過dbw0程式指向的控制程式碼，建立連結使用ln命令。
登入會話，執行alter tablespace xxxx read only;
然後使用rm刪除原連結，cp /proc/xxx/fd/NN delete_file.dbf。
這個時候不能執行alter tablespace xxxx read write;(切記!!!!!)
要執行
alter database datafie 6 offline drop; --注:後面說明為什麼要使用drop引數。
recover datafile 6;
alter database datafie 6 online ;
--下面是完整的顯示過程：
--session 1:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
157 5 14736 alter system kill session '157,5' immediate;
SCOTT@test> create table t tablespace mssm as select * from dba_objects ;
Table created.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50872
SCOTT@test> alter system checkpoint;
System altered.
$ rm /mnt/ramdisk/test/mssm01.dbf
/bin/rm: remove regular file `/mnt/ramdisk/test/mssm01.dbf'? y
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50873
--session 1操作依舊正常。
--session 2:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
147 10 14789 alter system kill session '147,10' immediate;
SCOTT@test> create index i_t_owner on t(owner) tablespace mssm;
create index i_t_owner on t(owner) tablespace mssm
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--這個時候發現報錯！回到session 1,再執行一些事務：
--session 1：
--session 1:
SCOTT@test> insert into t select * from t where rownum<=1000;
1000 rows created.
SCOTT@test> commit ;
Commit complete.
$ ps -ef | grep ora_db[w]
oracle 14687 1 0 16:21 ? 00:00:00 ora_dbw0_test
oracle 14689 1 0 16:21 ? 00:00:00 ora_dbw1_test
oracle 14691 1 0 16:21 ? 00:00:00 ora_dbw2_test
$ ls -l /proc/14687/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 16:28:48 23 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
--建立連結：
$ ln -s /proc/14687/fd/23 /mnt/ramdisk/test/mssm01.dbf
SYS@test> alter tablespace mssm read only ;
Tablespace altered.
--這個時候開始刪除原連結，複製檔案。
$ rm /mnt/ramdisk/test/mssm01.dbf
/bin/rm: remove symbolic link `/mnt/ramdisk/test/mssm01.dbf'? y
$ cp /proc/14687/fd/23 /mnt/ramdisk/test/mssm01.dbf
--下面不要執行alter tablespace mssm read write ;而是執行
SYS@test> alter database datafile 6 offline ;
Database altered.
SYS@test> recover datafile 6;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SYS@test> alter database datafile 6 online ;
Database altered.
$ ls -l /proc/14687/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 16:33:51 26 -> /mnt/ramdisk/test/mssm01.dbf
--注意看這個時候的dbw0程式指向正常的檔案。
SCOTT@test> select count(*) from t;
COUNT(*)
------------
51873
--可以發現沒有任何丟失。50872+1+1000=51873.
SCOTT@test> insert into t select * from t where rownum<=1;
insert into t select * from t where rownum<=1
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--沒有開啟任何讀寫。這個時候實際上還不能開啟讀寫。why？
# lsof | grep mssm01.dbf
oracle 14687 oracle 26uR REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
oracle 14736 oracle 18u REG 0,29 16654336 219968 /mnt/ramdisk/test/mssm01.dbf (deleted)
oracle 14736 oracle 19u REG 0,29 16654336 219968 /mnt/ramdisk/test/mssm01.dbf (deleted)
oracle 14906 oracle 10u REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
# ps -ef | egrep "14906|14687" |grep -v grep
oracle 14687 1 0 16:21 ? 00:00:00 ora_dbw0_test
oracle 14906 14905 0 16:34 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
--理論講必須kill掉session 1的程式(程式號14736)，才能設定讀寫模式。
SCOTT@test> alter database datafile 6 offline drop;
Database altered.
# lsof | grep mssm01.dbf
oracle 14906 oracle 10u REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
# lsof /mnt/ramdisk/test/mssm01.dbf
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 14906 oracle 10u REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
--ok現在沒事了。說明必須要使用drop引數。
SCOTT@test> recover datafile 6;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SCOTT@test> alter database datafile 6 online ;
Database altered.
# lsof | grep mssm01.dbf
oracle 14687 oracle 26uR REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
oracle 14789 oracle 14u REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
# ps -ef | egrep "14687|14789" | grep -v grep
oracle 14687 1 0 16:21 ? 00:00:00 ora_dbw0_test
oracle 14789 14788 0 16:25 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
SCOTT@test> select count(*) from t;
COUNT(*)
------------
51873
SCOTT@test> alter tablespace mssm read write ;
Tablespace altered.
--總結：
1.測試有點亂，有一些概念還是不清楚。
2.我最後的原則應該避免這樣的恢復，而是使用rman來恢復。
--以前曾經寫過一篇關於
--連結：http://blog.itpub.net/267265/viewspace-763969/
--裡面提到實際上這種方式對於生產系統不是很合適，而且生產系統情況非常複雜，不可能出現刪除資料檔案時沒有事務產生。
--這種方式僅僅適合no archivelog的模式(沒有辦法的選擇)，我當時還提到這種方式一定要快，因為我的測試執行 alter system
--checkpoint;,資料庫直接crash。
--正好別人問我一些檢查點的問題，讓我重新思考以前的解決思路。我喜歡透過例子詳細說明：
1.建立環境：
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 510 SYSTEM *** /mnt/ramdisk/test/system01.dbf
2 350 UNDOTBS1 *** /mnt/ramdisk/test/undotbs01.dbf
3 370 SYSAUX *** /mnt/ramdisk/test/sysaux01.dbf
4 100 USERS *** /mnt/ramdisk/test/users01.dbf
5 100 EXAMPLE *** /mnt/ramdisk/test/example01.dbf
6 15 MSSM *** /mnt/ramdisk/test/mssm01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 18 TEMP 32767 /mnt/ramdisk/test/test01.dbf
SYS@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
--保險期間我在關閉資料庫的情況下做了一個冷備份，當然僅僅備份沒mssm01.dbf檔案。
--注：我前面的測試是11g，這次是10g。
2.開始測試：
--session 1:
SCOTT@test> create table t tablespace mssm as select * from dba_objects ;
Table created.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50650
SCOTT@test> alter system checkpoint;
System altered.
SCOTT@test> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1));
SPID
------
10113
$ ls -l /proc/10113/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 10:50:40 18 -> /mnt/ramdisk/test/mssm01.dbf
lrwx------ 1 oracle oinstall 64 2015-10-23 10:50:40 19 -> /mnt/ramdisk/test/mssm01.dbf
--可以發現18,19指向/mnt/ramdisk/test/mssm01.dbf.
2.刪除資料檔案.
$ cd /mnt/ramdisk/test
# rm mssm01.dbf
/bin/rm: remove regular file `mssm01.dbf'? y
# ls -l /proc/10113/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 10:50:40 18 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2015-10-23 10:50:40 19 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
--session 1:
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50650
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SQL> commit ;
Commit complete.
--刪除了檔案,file habdle以及資料塊的記憶體並沒有釋放,依舊可以操作.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50651
--session 2,這個是刪除檔案後進入的：
SCOTT@test> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1));
SPID
------
10401
# ls -l /proc/10401/fd | grep mssm
--無輸出
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50651
SCOTT@test> alter system flush buffer_cache;
alter system flush buffer_cache
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
--在10g下直接crash.看看alert*.log檔案：
Fri Oct 23 10:58:07 2015
ALTER SYSTEM: Flushing buffer cache
Fri Oct 23 10:58:07 2015
Errors in file /u01/app/oracle/admin/test/bdump/test_dbw1_10067.trc:
ORA-01116: error in opening database file 6
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Fri Oct 23 10:58:07 2015
DBW1: terminating instance due to error 1116
Instance terminated by DBW1, pid = 10067
--注意看錯誤提示：dbw1程式。不是版本的問題。重新啟動恢復資料檔案。
SYS@test> show parameter cpu_count
NAME TYPE VALUE
------------------------------------ -------- ------
cpu_count integer 24
--這次的測試環境是cpu_count=24,24/8=3個，也就是會開啟3個dbw程式。
$ ps -ef | grep ora_db[w]
oracle 11198 1 0 11:46 ? 00:00:00 ora_dbw0_test
oracle 11200 1 0 11:46 ? 00:00:00 ora_dbw1_test
oracle 11202 1 0 11:46 ? 00:00:00 ora_dbw2_test
$ ls -l /proc/11198/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 11:48:02 23 -> /mnt/ramdisk/test/mssm01.dbf
$ ls -l /proc/11200/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 11:48:07 15 -> /mnt/ramdisk/test/mssm01.dbf
$ ls -l /proc/11202/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 11:48:10 15 -> /mnt/ramdisk/test/mssm01.dbf
--我估計前面的測試其中一個dbw1沒有開啟/mnt/ramdisk/test/mssm01.dbf的控制程式碼。重新測試：
--session 1:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
154 5 11289 alter system kill session '154,5' immediate;
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
--刪除檔案：
$ rm mssm01.dbf
/bin/rm: remove regular file `mssm01.dbf'? y
--session 2:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
151 7 11341 alter system kill session '151,7' immediate;
SCOTT@test> select count(*) from t;
select count(*) from t
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--因為訪問的檔案一些塊沒有讀到記憶體，報錯。
SCOTT@test> select * from t where rownum<=1;
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S
------ -------------------- ---------- ------------ -------------- ------------------- ------------------- ------------------- ------------------- ------- - - -
SYS ICOL$ 20 2 TABLE 2008-03-12 00:39:48 2008-03-12 00:58:12 2008-03-12:00:39:48 VALID N N N
--因為訪問的這個塊已經在記憶體中，不會報錯。但是執行如下：
SCOTT@test> alter tablespace mssm read only ;
alter tablespace mssm read only
*
ERROR at line 1:
ORA-03135: connection lost contact
--報錯出現：10g下一旦出現寫檔案出錯，資料庫直接crash。alert*.log檔案：
Fri Oct 23 11:54:46 2015
Errors in file /u01/app/oracle/admin/test/bdump/test_dbw2_11202.trc:
ORA-01116: error in opening database file 6
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Fri Oct 23 11:54:46 2015
DBW2: terminating instance due to error 1116
Instance terminated by DBW2, pid = 11202
--很奇怪，對於dbw2程式,檔案 /mnt/ramdisk/test/mssm01.dbf是開啟的。這樣對於10g的資料庫，我個人認為難度更大。
--猜測在執行alter tablespace mssm read only ;，會話也許要先開啟檔案控制程式碼，在透過dbw寫髒塊。因為檔案沒有刪除，
--報錯以後直接crash。
3.在重新測試,恢復過程略。
--session 1:
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50653
$ rm /mnt/ramdisk/test/mssm01.dbf
/bin/rm: remove regular file `/mnt/ramdisk/test/mssm01.dbf'? y
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50653
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
--因為控制程式碼是開啟的，訪問沒有問題。
SCOTT@test> alter tablespace mssm read only ;
Tablespace altered.
--可以發現在開啟控制程式碼的情況下，執行以上語句ok的。再次驗證我前面的推測。
SCOTT@test> alter tablespace mssm read write ;
Tablespace altered.
SCOTT@test> alter system flush buffer_cache;
System altered.
SCOTT@test> alter system checkpoint;
System altered.
--可以發現在開啟控制程式碼的情況下，執行以上語句ok的。並沒有像我前面的測試直接crash，實際上如果你新開一個會話執行以上語句，至
--少在10g的資料庫，直接crash，因為dbw程式在寫盤錯誤的情況下，直接crash。
4.這就提出一個問題，如果想透過copy的方式，必須設定表空間只讀，而連上的會話並沒有開啟/mnt/ramdisk/test/mssm01.dbf控制程式碼。
這樣alter tablespace mssm read only ;這樣的操作要寫檔案檢查點(我認為把它稱為表空間檢查點更準確一點。)
參考連結：http://blog.itpub.net/267265/viewspace-1798792/
--只有想辦法先騙過oracle這個檔案是存在的才行，有了這個思路就簡單了，先使用ln命令做一個軟連結，騙過oracle先。
$ ls -l /proc/11613/fd/12
lrwx------ 1 oracle oinstall 64 2015-10-23 12:14:57 /proc/11613/fd/12 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
$ ln -s /proc/11613/fd/12 /mnt/ramdisk/test/mssm01.dbf
--session 2:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
137 10 11770 alter system kill session '137,10' immediate;
SCOTT@test> select count(*) from t;
select count(*) from t
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--???，檢視alert*.log檔案，發現：
Fri Oct 23 12:07:43 2015
Errors in file /u01/app/oracle/admin/test/bdump/test_ckpt_11543.trc:
ORA-01171: datafile 6 going offline due to error advancing checkpoint
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--當我發alter system checkpoint;時，實際上資料檔案6已經無法寫入直接offline.
SCOTT@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
6 0 0 0 OFFLINE 0
--再次說明這種恢復方式值得商榷。
5.恢復重新繼續測試：
-- session 1:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
157 5 13787 alter system kill session '157,5' immediate;
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50655
$ rm mssm01.dbf
/bin/rm: remove regular file `mssm01.dbf'? y
--session 2:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
156 37 13826 alter system kill session '156,37' immediate;
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50655
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
--也許插入的塊正好在記憶體，不會報錯。
--session 1:
SCOTT@test> alter system flush buffer_cache;
System altered.
--session 1:
SCOTT@test> insert into t select * from t where rownum<=1;
insert into t select * from t where rownum<=1
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--出現錯誤。這個時候要快速的建立一個連結：
# ps -ef | grep ora_db[w]
oracle 13670 1 0 15:26 ? 00:00:00 ora_dbw0_test
oracle 13672 1 0 15:26 ? 00:00:00 ora_dbw1_test
oracle 13674 1 0 15:26 ? 00:00:00 ora_dbw2_test
$ ls -l /proc/13670/fd/ | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 15:39:22 25 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
$ ln -s /proc/13670/fd/25 /mnt/ramdisk/test/mssm01.dbf
--假設這個時候還有事務產生：
--session 1:
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
--session 3:
SCOTT@test> alter tablespace mssm read only ;
Tablespace altered.
--這個時候才不會報錯。
--這樣不可能再發生事務。
--session 1:
SCOTT@test> insert into t select * from t where rownum<=1;
insert into t select * from t where rownum<=1
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--這個時候刪除連結，再建立真實的檔案。
$ rm mssm01.dbf
/bin/rm: remove symbolic link `mssm01.dbf'? y
$ cp /proc/13670/fd/25 /mnt/ramdisk/test/mssm01.dbf
--如果這個時候執行alter tablespace mssm read write ;會報錯，因為兩者的檔案i節點不一致。
--不過我的測試不報錯。
--session 3:
SCOTT@test> alter tablespace mssm read write ;
Tablespace altered.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50657
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
$ ls -l /proc/13670/fd/ | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 15:39:22 25 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
--不顧這樣實際上有點亂套的。dbw程式寫的可能是刪除的檔案。
--session 3:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
156 41 13980 alter system kill session '156,41' immediate;
$ ls -l /proc/13980/fd |grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 15:50:05 14 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
--可以發現使用的還是刪除的連結。
--session 4：
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
143 77 14112 alter system kill session '143,77' immediate;
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50659
$ ls -l /proc/14112/fd |grep mssm
SCOTT@test> insert into t select * from t where rownum<=100;
100 rows created.
SCOTT@test> insert into t select * from t where rownum<=100;
100 rows created.
SCOTT@test> insert into t select * from t where rownum<=100;
100 rows created.
SCOTT@test> insert into t select * from t where rownum<=100;
100 rows created.
SCOTT@test> commit ;
Commit complete.
--session 1:
SCOTT@test> alter system flush buffer_cache;
System altered.
SCOTT@test> insert into t select * from t where rownum<=100;
100 rows created.
SCOTT@test> commit ;
Commit complete.
$ ls -l /proc/14112/fd |grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 15:53:51 10 -> /mnt/ramdisk/test/mssm01.dbf
--這個時候就亂套了。
--session 4：
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50759
--看看我前面在會話4插入400條記錄沒有報錯，並且提交(注意這個會話沒有開啟指向mssm01.dbf的控制程式碼)。而當我在會話1執行alter
--system flush buffer_cache;後插入100條。在會話4檢查，與前面的對比400條的插入由於訪問的檔案不同，消失了。
6.好了，我已經展示許多情況，有點亂。現在恢復是否正常：
SCOTT@test> alter database datafile 6 offline ;
Database altered.
$ cp /home/oracle/mssm1/mssm01.dbf /mnt/ramdisk/test/
/bin/cp: overwrite `/mnt/ramdisk/test/mssm01.dbf'? y
--這個是我冷備份的資料檔案。
SCOTT@test> alter database datafile 6 online ;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SCOTT@test> recover datafile 6;
ORA-00279: change 12694467422 generated at 10/23/2015 10:46:50 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_10_23/o1_mf_1_89_%u_.arc
ORA-00280: change 12694467422 for thread 1 is in sequence #89
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 12694487796 generated at 10/23/2015 11:41:50 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_10_23/o1_mf_1_90_%u_.arc
ORA-00280: change 12694487796 for thread 1 is in sequence #90
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_10_23/o1_mf_1_89_c2mc3yd7_.arc' no longer needed for this recovery
ORA-00283: recovery session canceled due to errors
ORA-12801: error signaled in parallel query server P005
ORA-00600: internal error code, arguments: [3020], [6], [837], [1], [93], [2614], [16], []
ORA-10567: Redo is inconsistent with data block (file#)
ORA-01112: media recovery not started
--已經無法恢復。
--後記補充1點，不要drop也可以。測試有點亂。我估計我自己忘記
alter database datafie 6 offline;
alter database datafie 6 online;
--而直接alter tablespace xxxx read write;
7.如果這種情況出現，正常的恢復步驟(如果要採用這種方式)：
利用先透過dbw0程式指向的控制程式碼，建立連結使用ln命令。
登入會話，執行alter tablespace xxxx read only;
然後使用rm刪除原連結，cp /proc/xxx/fd/NN delete_file.dbf。
這個時候不能執行alter tablespace xxxx read write;(切記!!!!!)
要執行
alter database datafie 6 offline drop; --注:後面說明為什麼要使用drop引數。
recover datafile 6;
alter database datafie 6 online ;
--下面是完整的顯示過程：
--session 1:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
157 5 14736 alter system kill session '157,5' immediate;
SCOTT@test> create table t tablespace mssm as select * from dba_objects ;
Table created.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50872
SCOTT@test> alter system checkpoint;
System altered.
$ rm /mnt/ramdisk/test/mssm01.dbf
/bin/rm: remove regular file `/mnt/ramdisk/test/mssm01.dbf'? y
SCOTT@test> insert into t select * from t where rownum<=1;
1 row created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select count(*) from t;
COUNT(*)
------------
50873
--session 1操作依舊正常。
--session 2:
SCOTT@test> @spid
SID SERIAL# SPID C50
------------ ------------ ------ --------------------------------------------------
147 10 14789 alter system kill session '147,10' immediate;
SCOTT@test> create index i_t_owner on t(owner) tablespace mssm;
create index i_t_owner on t(owner) tablespace mssm
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--這個時候發現報錯！回到session 1,再執行一些事務：
--session 1：
--session 1:
SCOTT@test> insert into t select * from t where rownum<=1000;
1000 rows created.
SCOTT@test> commit ;
Commit complete.
$ ps -ef | grep ora_db[w]
oracle 14687 1 0 16:21 ? 00:00:00 ora_dbw0_test
oracle 14689 1 0 16:21 ? 00:00:00 ora_dbw1_test
oracle 14691 1 0 16:21 ? 00:00:00 ora_dbw2_test
$ ls -l /proc/14687/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 16:28:48 23 -> /mnt/ramdisk/test/mssm01.dbf (deleted)
--建立連結：
$ ln -s /proc/14687/fd/23 /mnt/ramdisk/test/mssm01.dbf
SYS@test> alter tablespace mssm read only ;
Tablespace altered.
--這個時候開始刪除原連結，複製檔案。
$ rm /mnt/ramdisk/test/mssm01.dbf
/bin/rm: remove symbolic link `/mnt/ramdisk/test/mssm01.dbf'? y
$ cp /proc/14687/fd/23 /mnt/ramdisk/test/mssm01.dbf
--下面不要執行alter tablespace mssm read write ;而是執行
SYS@test> alter database datafile 6 offline ;
Database altered.
SYS@test> recover datafile 6;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SYS@test> alter database datafile 6 online ;
Database altered.
$ ls -l /proc/14687/fd | grep mssm
lrwx------ 1 oracle oinstall 64 2015-10-23 16:33:51 26 -> /mnt/ramdisk/test/mssm01.dbf
--注意看這個時候的dbw0程式指向正常的檔案。
SCOTT@test> select count(*) from t;
COUNT(*)
------------
51873
--可以發現沒有任何丟失。50872+1+1000=51873.
SCOTT@test> insert into t select * from t where rownum<=1;
insert into t select * from t where rownum<=1
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--沒有開啟任何讀寫。這個時候實際上還不能開啟讀寫。why？
# lsof | grep mssm01.dbf
oracle 14687 oracle 26uR REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
oracle 14736 oracle 18u REG 0,29 16654336 219968 /mnt/ramdisk/test/mssm01.dbf (deleted)
oracle 14736 oracle 19u REG 0,29 16654336 219968 /mnt/ramdisk/test/mssm01.dbf (deleted)
oracle 14906 oracle 10u REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
# ps -ef | egrep "14906|14687" |grep -v grep
oracle 14687 1 0 16:21 ? 00:00:00 ora_dbw0_test
oracle 14906 14905 0 16:34 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
--理論講必須kill掉session 1的程式(程式號14736)，才能設定讀寫模式。
SCOTT@test> alter database datafile 6 offline drop;
Database altered.
# lsof | grep mssm01.dbf
oracle 14906 oracle 10u REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
# lsof /mnt/ramdisk/test/mssm01.dbf
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 14906 oracle 10u REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
--ok現在沒事了。說明必須要使用drop引數。
SCOTT@test> recover datafile 6;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SCOTT@test> alter database datafile 6 online ;
Database altered.
# lsof | grep mssm01.dbf
oracle 14687 oracle 26uR REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
oracle 14789 oracle 14u REG 0,29 16654336 223268 /mnt/ramdisk/test/mssm01.dbf
# ps -ef | egrep "14687|14789" | grep -v grep
oracle 14687 1 0 16:21 ? 00:00:00 ora_dbw0_test
oracle 14789 14788 0 16:25 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
SCOTT@test> select count(*) from t;
COUNT(*)
------------
51873
SCOTT@test> alter tablespace mssm read write ;
Tablespace altered.
--總結：
1.測試有點亂，有一些概念還是不清楚。
2.我最後的原則應該避免這樣的恢復，而是使用rman來恢復。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1816212/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- linux下恢復誤刪除的資料檔案Linux
- [20151025]linux下刪除資料檔案的恢復細節3Linux
- [20151028]linux下刪除資料檔案的恢復細節4Linux
- linux下恢復誤刪除oracle的資料檔案LinuxOracle
- linux下 恢復被rm意外刪除資料檔案Linux
- Oracle資料恢復 - Linux / Unix 誤刪除的檔案恢復(轉）Oracle資料恢復Linux
- Linux下使用lsof恢復刪除的檔案Linux
- 歸檔模式下，線上刪除資料檔案的完全恢復模式
- Oracle恢復誤刪除的資料檔案Oracle
- RM 刪除資料檔案恢復操作
- [20130614]linux下刪除資料檔案的恢復的一些細節問題.txtLinux
- 透過控制程式碼檔案恢復linux下誤刪除的資料檔案Linux
- linux中誤刪除oracle資料檔案的恢復操作LinuxOracle
- Linux下用rm刪除的檔案的恢復方法Linux
- 恢復刪除的檔案
- 刪除檔案的恢復
- 恢復rm -f物理刪除資料檔案
- 恢復被rm意外刪除資料檔案
- linux/uninx恢復刪除的檔案<轉>Linux
- linux中誤刪除oracle資料檔案的恢復操作（轉）LinuxOracle
- solaris下使用lsof恢復刪除的檔案
- Oracle 檔案意外刪除恢復(Linux)OracleLinux
- 通過控制程式碼恢復Linux下誤刪除的資料庫資料檔案Linux資料庫
- 【伺服器資料恢復】Zfs檔案系統下誤刪除怎麼恢復資料伺服器資料恢復
- Git恢復刪除的檔案Git
- OS 刪除oracle資料檔案恢復過程Oracle
- 誤刪除資料檔案、控制檔案的非RMAN恢復方法
- 使用檔案描述符恢復誤刪除的資料檔案
- linux系統下檔案誤刪除該如何恢復？Linux
- 行動硬碟刪除的檔案能恢復嗎，怎樣恢復刪除的檔案硬碟
- 【伺服器資料恢復】linux ext3檔案系統下誤刪除mysql資料庫的資料恢復案例伺服器資料恢復LinuxMySql資料庫
- Linux 恢復rm -rf命令所刪除的達夢資料檔案Linux
- Linux下誤刪資料檔案從檔案控制程式碼恢復資料檔案Linux
- 怎樣恢復回收站已刪除檔案，檔案刪除恢復教程
- 恢復EXT3下被刪除的檔案
- Linux環境利用恢復被rm意外刪除資料檔案Linux
- Oracle資料庫意外刪除資料檔案的恢復（轉載）Oracle資料庫
- 行動硬碟刪除的檔案能恢復嗎，怎麼恢復硬碟刪除的檔案硬碟