[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来恢复。