[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(*)

----------


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

------


$ 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(*)

----------


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(*)

----------


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

------


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

--無輸出


SQL> select count(*) from t;

  COUNT(*)

----------


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(*)

----------

--接著回到回話2,執行查詢:

SQL> select count(*) from t;

  COUNT(*)

----------

--這次成功!


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(*)

----------

--可以發現回話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(*)

----------


--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來恢復還是比較正
規的,除非沒備份或者是非歸檔模式.

[20130614]linux下刪除資料檔案的恢復的一些細節問題.txt

相關文章