[20130614]linux下删除数据文件的恢复的一些细节问题.txt

前天看了链接:
http://space.itpub.net/26015009/viewspace-763506

我仅仅做一些测试以及补充,以及注意的细节问题,实际上最好的方法依旧是使用rman备份恢复.

1.测试环境:

--session 1

SQL> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

CREATE TABLESPACE AAA DATAFILE 

  '/u01/app/oracle11g/oradata/test/aaa01.dbf' SIZE 65528K AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

--使用rman备份.

RMAN> backup datafile 11 format '/data/testtest/aaa%U.dbf' ;

Starting backup at 2013-06-14 09:32:32

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=198 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00011 name=/u01/app/oracle11g/oradata/test/aaa01.dbf

channel ORA_DISK_1: starting piece 1 at 2013-06-14 09:32:34

channel ORA_DISK_1: finished piece 1 at 2013-06-14 09:32:35

piece handle=/data/testtest/aaa0loc5g1i_1_1.dbf tag=TAG20130614T093234 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2013-06-14 09:32:35

Starting Control File and SPFILE Autobackup at 2013-06-14 09:32:36

piece handle=/u01/app/oracle11g/flash_recovery_area/TEST/autobackup/2013_06_14/o1_mf_s_818069557_8vnwopcj_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 2013-06-14 09:32:40

SQL> create table t tablespace aaa as select * from dba_objects ;

Table created.

SQL> select count(*) from t;

  COUNT(*)

----------

     77569

SQL> alter system checkpoint;

System altered.

SQL> 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

------

18627

$ ls -l /proc/18627/fd | grep aaa

lrwx------  1 oracle11g oinstall 64 2013-06-14 11:52:45 260 -> /u01/app/oracle11g/oradata/test/aaa01.dbf

--可以发现260指向/u01/app/oracle11g/oradata/test/aaa01.dbf.

2.删除数据文件.

$ cd /u01/app/oracle11g/oradata/test

$ rm aaa01.dbf

/bin/rm: remove regular file `aaa01.dbf'? y

$ ls -l /proc/18627/fd | grep aaa

lrwx------  1 oracle11g oinstall 64 2013-06-14 11:52:45 260 -> /u01/app/oracle11g/oradata/test/aaa01.dbf (deleted)

--session 1:

SQL> select count(*) from t;

  COUNT(*)

----------

     77569

SQL> insert into t select * from t where rownum<=1;

1 row created.

SQL> commit ;

Commit complete.

--删除了文件,file habdle以及数据块的内存并没有释放,依旧可以操作.

SQL> select count(*) from t;

  COUNT(*)

----------

     77570

--session 2:

SQL> 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

------

18633

$ ls -l /proc/18633/fd | grep aaa

--无输出

SQL> select count(*) from t;

  COUNT(*)

----------

     77570

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from t;

select count(*) from t

                     *

ERROR at line 1:

ORA-01116: error in opening database file 11

ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

--回到回话1,依旧能查询:

SQL> select count(*) from t;

  COUNT(*)

----------

     77570

--接着回到回话2,执行查询:

SQL> select count(*) from t;

  COUNT(*)

----------

     77570

--这次成功!     

SQL> 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 11

ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

$ ls -l /proc/18633/fd | grep aaa
--无输出,因为aaa01.dbf已经删除.
--回话2不能插入,因为没有指向aaa01.dbf的文件句柄.但是查询应该没有问题.只要信息在数据缓存里面.

--session 1:

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from t;

  COUNT(*)

----------

     77570

--可以发现回话1根本没有影响,因为在删除前已经打开了指向aaa01.dbf的文件句柄.甚至可以继续插入数据.

SQL> insert into t select * from t where rownum<=1;

1 row created.

SQL> commit ;

Commit complete.

SQL> select count(*) from t;

  COUNT(*)

----------

     77571

--SQL> alter system checkpoint;

--alter system checkpoint

--*

--ERROR at line 1:

--ORA-03113: end-of-file on communication channel

--Process ID: 18627

--Session ID: 191 Serial number: 21

--如果我执行alter system checkpoint;数据库直接crash.

--说明如果是正常的生产环境,你删除了文件,这个时候不可能没有事务发生对这个数据文件.这样问题会变得非常复杂.
--如果这时使用cp命令拷贝文件,得到的文件可能"有问题"!

3.比较正确的操作:
SQL> alter  tablespace aaa read only ;
Tablespace altered.
--仅仅能对表空间设置为读写.

$ cd /proc/18627/fd
$ cp 260 /u01/app/oracle11g/oradata/test/aaa01.dbf

--注意拷贝后文件的权限!!!
--如果这个时候使用alter  tablespace aaa read write ;应该不行的,因为copy回来的文件不是原来打开的文件句柄.
--正确的做法是:

alter database datafile 11 offline ;

--RMAN> recover datafile 11 ;

alter database datafile 11 on ;

--实际上这个步骤要块,如果执行了检查点(alter system checkpoint;),我的测试数据库会直接crash.

SQL> 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 11 cannot be modified at this time
ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'

SQL> alter  tablespace aaa read write ;
Tablespace altered.

4.使用rman操作:
--我个人感觉使用rman是比较可靠的,除非数据库没有数据文件的备份或者为非归档模式.

SQL> alter database datafile 11 offline ;

Database altered.

RMAN> restore datafile 11 ;

Starting restore at 2013-06-14 10:08:17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=13 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle11g/oradata/test/aaa01.dbf

channel ORA_DISK_1: reading from backup piece /data/testtest/aaa0loc5g1i_1_1.dbf

channel ORA_DISK_1: piece handle=/data/testtest/aaa0loc5g1i_1_1.dbf tag=TAG20130614T093234

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Finished restore at 2013-06-14 10:08:25

RMAN> recover datafile 11 ;

Starting recover at 2013-06-14 10:08:34

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 2013-06-14 10:08:38

--

SQL>  alter database datafile 11 online ;

Database altered.

总结:
我不知道我这样恢复还有别的问题,至少按照原链接操作是有问题的.也许实际的生产情况更加复杂,个人感觉选择rman来恢复还是比较正
规的,除非没备份或者是非归档模式.