物理刪除oracle數據文件的恢復

歸檔模式下測試：

(普通文件系統)：open狀態下物理刪除數據文件，未關閉情況恢復：

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/oracle/oradata/CPP/example01.dbf

/u01/oracle/oradata/CPP/users01.dbf

/u01/oracle/oradata/CPP/undotbs01.dbf

/u01/oracle/oradata/CPP/sysaux01.dbf

/u01/oracle/oradata/CPP/system01.dbf

SQL> create tablespace test datafile '/u01/oracle/oradata/CPP/test01.dbf' size 10M;

Tablespace created.SQL> create user test identified by test default tablespace test;

User created.

SQL> grant connect ,resource to test;

Grant succeeded.

SQL> conn test/test;

Connected.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test;

Connected.

SQL> create table t1 as select * from dba_objects where rownum<=1000;

Table created.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME       TABLESPACE_NAME

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

T1       TEST

SQL> conn /as sysdba

Connected.

SQL> alter system checkpoint;

System altered.

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/oracle/oradata/CPP/example01.dbf

/u01/oracle/oradata/CPP/users01.dbf

/u01/oracle/oradata/CPP/undotbs01.dbf

/u01/oracle/oradata/CPP/sysaux01.dbf

/u01/oracle/oradata/CPP/system01.dbf

/u01/oracle/oradata/CPP/test01.dbf

6 rows selected.

[root@orcl1 CPP]# ls

control01.ctl  redo01.log  redo03.log    system01.dbf  test01.dbf     users01.dbf

example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    undotbs01.dbf

[root@orcl1 CPP]# rm -rf test01.dbf 

SQL> create table t2 as select * from t1;

create table t2 as select * from t1                               *

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/u01/oracle/oradata/CPP/test01.dbf'

ORA-27041: unable to open file

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

Additional information: 3

[oracle@orcl1 ~]$ ps -ef | grep dbw0

oracle    2898     1  0 09:24 ?        00:00:00 ora_dbw0_CPP

oracle    5423  5382  3 09:50 pts/2    00:00:00 grep dbw0

[oracle@orcl1 ~]$ su - root

Password: 

[root@orcl1 ~]# cd /proc/2898/

[root@orcl1 2898]# ls

attr       clear_refs       cwd      fdinfo    maps       mountstats  oom_score      root       smaps  status

autogroup  cmdline          environ  io        mem        net         oom_score_adj  sched      stack  syscall

auxv       coredump_filter  exe      limits    mountinfo  numa_maps   pagemap        schedstat  stat   task

cgroup     cpuset           fd       loginuid  mounts     oom_adj     personality    sessionid  statm  wchan

[root@orcl1 2898]# cd fd

[root@orcl1 fd]# ls

0  1  10  11  2  256  257  258  259  260  261  262  263  264  3  4  5  6  7  8  9

[root@orcl1 fd]# ll

total 0

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 0 -> /dev/null

l-wx------ 1 oracle oinstall 64 Jan  7 09:51 1 -> /dev/null

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 10 -> /u01/oracle/product/11.2.0/db_1/dbs/lkCPP

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 11 -> /u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

l-wx------ 1 oracle oinstall 64 Jan  7 09:51 2 -> /dev/null

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 256 -> /u01/oracle/oradata/CPP/control01.ctl

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 257 -> /u01/oracle/fast_recovery_area/CPP/control02.ctl

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 258 -> /u01/oracle/oradata/CPP/system01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 259 -> /u01/oracle/oradata/CPP/sysaux01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 260 -> /u01/oracle/oradata/CPP/undotbs01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 261 -> /u01/oracle/oradata/CPP/users01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 262 -> /u01/oracle/oradata/CPP/example01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 263 -> /u01/oracle/oradata/CPP/temp01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 264 -> /u01/oracle/oradata/CPP/test01.dbf (deleted)

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 3 -> /dev/null

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 4 -> /dev/null

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 5 -> /dev/null

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 6 -> /u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 7 -> /proc/2898/fd

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 8 -> /dev/zero

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 9 -> /u01/oracle/product/11.2.0/db_1/dbs/hc_CPP.dat

[oracle@orcl1 fd]# cp 264 /u01/oracle/oradata/CPP/test01.dbf

SQL> select name,status from v$datafile;

NAME     STATUS

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

/u01/oracle/oradata/CPP/system01.dbf     SYSTEM

/u01/oracle/oradata/CPP/sysaux01.dbf     ONLINE

/u01/oracle/oradata/CPP/undotbs01.dbf     ONLINE

/u01/oracle/oradata/CPP/users01.dbf     ONLINE

/u01/oracle/oradata/CPP/example01.dbf     ONLINE

/u01/oracle/oradata/CPP/test01.dbf     ONLINE

6 rows selected.

SQL> alter database datafile '/u01/oracle/oradata/CPP/test01.dbf' offline;

Database altered.

SQL> select name,status from v$datafile;

NAME     STATUS

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

/u01/oracle/oradata/CPP/system01.dbf     SYSTEM

/u01/oracle/oradata/CPP/sysaux01.dbf     ONLINE

/u01/oracle/oradata/CPP/undotbs01.dbf     ONLINE

/u01/oracle/oradata/CPP/users01.dbf     ONLINE

/u01/oracle/oradata/CPP/example01.dbf     ONLINE

/u01/oracle/oradata/CPP/test01.dbf     RECOVER

6 rows selected.

SQL> recover datafile '/u01/oracle/oradata/CPP/test01.dbf';

Media recovery complete.

SQL> alter database datafile '/u01/oracle/oradata/CPP/test01.dbf' online;

Database altered.

SQL> select name,status from v$datafile;

NAME     STATUS

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

/u01/oracle/oradata/CPP/system01.dbf     SYSTEM

/u01/oracle/oradata/CPP/sysaux01.dbf     ONLINE

/u01/oracle/oradata/CPP/undotbs01.dbf     ONLINE

/u01/oracle/oradata/CPP/users01.dbf     ONLINE

/u01/oracle/oradata/CPP/example01.dbf     ONLINE

/u01/oracle/oradata/CPP/test01.dbf     ONLINE

6 rows selected.

SQL> conn test/test;

Connected.

SQL> create table t2 as select * from t1;

Table created.

（ASM文件系統）open狀態下物理刪除數據文件，關閉情況恢復：

SQL> create tablespace test datafile '+DATA/mecbs/datafile/test01.dbf' size 10M;

Tablespace created.

SQL> conn /as sysdba

Connected.

SQL> create user test identified by test default tablespace test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test;

Connected.

SQL> conn /as sysdba

Connected.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table t1 as select * from dba_objects where rownum<=1000;

Table created.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME       TABLESPACE_NAME

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

T1       TEST

SQL> select file_name from dba_data_files;

FILE_NAME

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

+DATA/mecbs/datafile/users.259.862339391

+DATA/mecbs/datafile/undotbs1.258.862339391

+DATA/mecbs/datafile/sysaux.257.862339391

+DATA/mecbs/datafile/system.256.862339387

+DATA/mecbs/datafile/example.264.862339751

+DATA/mecbs/datafile/undotbs2.265.862341013

+DATA/mecbs/datafile/system01.dbf

+DATA/mecbs/datafile/crm01.dbf

+DATA/mecbs/datafile/test01.dbf

+DATA/mecbs/datafile/cross.dbf

+DATA/mecbs/datafile/aix_trans.dbf

11 rows selected.

ASMCMD [+data/mecbs/DATAFILE] > ls

AIX_TRANS.281.868377837

CRM.276.863565267

CROSSTBS.279.868372675

EXAMPLE.264.862339751

SYSAUX.257.862339391

SYSTEM.256.862339387

SYSTEM.275.863564943

TEST.278.868380831

UNDOTBS1.258.862339391

UNDOTBS2.265.862341013

USERS.259.862339391

aix_trans.dbf

crm01.dbf

cross.dbf

system01.dbf

test01.dbf

SQL> alter tablespace test offline;

Tablespace altered.

ASMCMD [+data/mecbs/DATAFILE] > rm -rf test01.dbf

ASMCMD [+data/mecbs/DATAFILE] > 

SQL> select name,status from v$datafile;

NAME     STATUS

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

+DATA/mecbs/datafile/system.256.862339387     SYSTEM

+DATA/mecbs/datafile/sysaux.257.862339391     ONLINE

+DATA/mecbs/datafile/undotbs1.258.862339391     ONLINE

+DATA/mecbs/datafile/users.259.862339391     ONLINE

+DATA/mecbs/datafile/example.264.862339751     ONLINE

+DATA/mecbs/datafile/undotbs2.265.862341013     ONLINE

+DATA/mecbs/datafile/system01.dbf     SYSTEM

+DATA/mecbs/datafile/crm01.dbf     ONLINE

+DATA/mecbs/datafile/test01.dbf     OFFLINE

+DATA/mecbs/datafile/cross.dbf     ONLINE

+DATA/mecbs/datafile/aix_trans.dbf     ONLINE

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  484356096 bytes

Fixed Size    2254464 bytes

Variable Size  264243584 bytes

Database Buffers  209715200 bytes

Redo Buffers    8142848 bytes

Database mounted.

SQL> alter database create datafile '+DATA/mecbs/datafile/test01.dbf';

Database altered.

SQL> recover datafile '+DATA/mecbs/datafile/test01.dbf';

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select name,status from v$datafile;

NAME     STATUS

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

+DATA/mecbs/datafile/system.256.862339387     SYSTEM

+DATA/mecbs/datafile/sysaux.257.862339391     ONLINE

+DATA/mecbs/datafile/undotbs1.258.862339391     ONLINE

+DATA/mecbs/datafile/users.259.862339391     ONLINE

+DATA/mecbs/datafile/example.264.862339751     ONLINE

+DATA/mecbs/datafile/undotbs2.265.862341013     ONLINE

+DATA/mecbs/datafile/system01.dbf     SYSTEM

+DATA/mecbs/datafile/crm01.dbf     ONLINE

+DATA/mecbs/datafile/test01.dbf     OFFLINE

+DATA/mecbs/datafile/cross.dbf     ONLINE

+DATA/mecbs/datafile/aix_trans.dbf     ONLINE

11 rows selected.

SQL> alter tablespace test online;

Tablespace altered.

SQL> select name,status from v$datafile;

NAME     STATUS

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

+DATA/mecbs/datafile/system.256.862339387     SYSTEM

+DATA/mecbs/datafile/sysaux.257.862339391     ONLINE

+DATA/mecbs/datafile/undotbs1.258.862339391     ONLINE

+DATA/mecbs/datafile/users.259.862339391     ONLINE

+DATA/mecbs/datafile/example.264.862339751     ONLINE

+DATA/mecbs/datafile/undotbs2.265.862341013     ONLINE

+DATA/mecbs/datafile/system01.dbf     SYSTEM

+DATA/mecbs/datafile/crm01.dbf     ONLINE

+DATA/mecbs/datafile/test01.dbf     ONLINE

+DATA/mecbs/datafile/cross.dbf     ONLINE

+DATA/mecbs/datafile/aix_trans.dbf     ONLINE

11 rows selected.

SQL> conn test/test;

Connected.

SQL> select count(*) from t1;

  COUNT(*)

----------

      1000