oracle物理删除数据文件的恢复

一、归档模式下测试 1、open状态下物理删除数据文件，未关闭数据库情况下的恢复 SQL> create tablespace scott datafile '/data/oracle/oradata/orcl/scott.dbf' size 100M;SQL>  create table scott_test tablespace scott as select * from dba_objects;SQL>  select table_name,tablespace_name from user_tables;TABLE_NAME                     TABLESPACE_NAME------------------------------ ------------------------------DEPT                           USERSEMP                            USERSBONUS                          USERSSALGRADE                       USERSTB                             USERSTA                             USERSTEST                           USERSSCOTT_TEST                     SCOTTT_EX_USER                       SQL> conn / as sysdba;  Connected. SQL> alter system checkpoint; System altered.oracle@oracle[/data/oracle/oradata/orcl] lscontrol01.ctl  redo02.log  scott.dbf     system01.dbf  undotbs01.dbfredo01.log     redo03.log  sysaux01.dbf  temp01.dbf    users01.dbforacle@oracle[/data/oracle/oradata/orcl] rm -rf scott.dbf oracle@oracle[/data/oracle/oradata/orcl] sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 29 10:46:24 2021 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> conn scott/tiger; Connected. SQL> create table scott_t tablespace scott as select * from v$session; create table scott_t tablespace scott as select * from v$session                                                      * ERROR at line 1: ORA-01116: error in opening database file 5 ORA-01110: data file 5: '/data/oracle/oradata/orcl/scott.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL>exit oracle@oracle[/data/oracle/oradata/orcl] ps -ef|grep dbw0oracle   18486     1  0 10:07 ?        00:00:00 ora_dbw0_orcloracle   20994 18002  0 10:51 pts/0    00:00:00 grep --color=auto dbw0oracle@oracle[~] cd /proc/18486/fdoracle@oracle[/proc/18486/fd] ll总用量 0lr-x------ 1 oracle oinstall 64 7月  29 10:55 0 -> /dev/nulll-wx------ 1 oracle oinstall 64 7月  29 10:55 1 -> /dev/nulllrwx------ 1 oracle oinstall 64 7月  29 10:55 10 -> /data/oracle/product/11.2.0/db_1/dbs/lkORCLlr-x------ 1 oracle oinstall 64 7月  29 10:55 11 -> /data/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msblrwx------ 1 oracle oinstall 64 7月  29 10:55 12 -> socket:[5901296]l-wx------ 1 oracle oinstall 64 7月  29 10:55 2 -> /dev/nulllrwx------ 1 oracle oinstall 64 7月  29 10:55 256 -> /data/oracle/oradata/orcl/control01.ctllrwx------ 1 oracle oinstall 64 7月  29 10:55 257 -> /data/oracle/fast_recovery_area/orcl/control02.ctllrwx------ 1 oracle oinstall 64 7月  29 10:55 258 -> /data/oracle/oradata/orcl/system01.dbflrwx------ 1 oracle oinstall 64 7月  29 10:55 259 -> /data/oracle/oradata/orcl/sysaux01.dbflrwx------ 1 oracle oinstall 64 7月  29 10:55 260 -> /data/oracle/oradata/orcl/undotbs01.dbflrwx------ 1 oracle oinstall 64 7月  29 10:55 261 -> /data/oracle/oradata/orcl/users01.dbflrwx------ 1 oracle oinstall 64 7月  29 10:55 262 -> /data/oracle/oradata/orcl/scott.dbf (deleted)lrwx------ 1 oracle oinstall 64 7月  29 10:55 264 -> /data/oracle/oradata/orcl/temp01.dbflr-x------ 1 oracle oinstall 64 7月  29 10:55 3 -> /dev/nulllr-x------ 1 oracle oinstall 64 7月  29 10:55 4 -> /dev/nulllr-x------ 1 oracle oinstall 64 7月  29 10:55 5 -> /dev/nulllr-x------ 1 oracle oinstall 64 7月  29 10:55 6 -> /data/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msblr-x------ 1 oracle oinstall 64 7月  29 10:55 7 -> /proc/18486/fdlr-x------ 1 oracle oinstall 64 7月  29 10:55 8 -> /dev/zerolrwx------ 1 oracle oinstall 64 7月  29 10:55 9 -> /data/oracle/product/11.2.0/db_1/dbs/hc_orcl.dat oracle@oracle[/proc/18486/fd] cp 262 /data/oracle/oradata/orcl/scott.dbf oracle@oracle[/proc/18486/fd] sqlplus scott/tiger SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 29 10:57:27 2021 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> alter database datafile '/data/oracle/oradata/orcl/scott.dbf' offline; Database altered. SQL> select name,status from v$datafile;NAME                                                                             STATUS-------------------------------------------------------------------------------- -------/data/oracle/oradata/orcl/system01.dbf                                           SYSTEM/data/oracle/oradata/orcl/sysaux01.dbf                                           ONLINE/data/oracle/oradata/orcl/undotbs01.dbf                                          ONLINE/data/oracle/oradata/orcl/users01.dbf                                            ONLINE/data/oracle/oradata/orcl/scott.dbf                                              RECOVER SQL> recover datafile '/data/oracle/oradata/orcl/scott.dbf';Media recovery complete. SQL> alter database datafile '/data/oracle/oradata/orcl/scott.dbf' online; Database altered. SQL>  select table_name,tablespace_name from user_tables;TABLE_NAME                     TABLESPACE_NAME------------------------------ ------------------------------DEPT                           USERSEMP                            USERSBONUS                          USERSSALGRADE                       USERSTB                             USERSTA                             USERSTEST                           USERSSCOTT_TEST                     SCOTTT_EX_USER                      9 rows selectedSQL> select count(*) from scott_test;  COUNT(*)----------    1453512、open 状态下物理删除数据文件 ，关闭了数据库，无法启动情况下的恢复 SQL> create tablespace www datafile '/data/oracle/oradata/orcl/ size 40M; Tablespace created. SQL> show user; USER is "SCOTT" SQL> create table t1 tablespace www as select * from user_objects; Table created. SQL> alter system checkpoint; System altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options 11:26:17 oracle@oracle[/proc/18486/fd] cd /data/oracle/oradata/orcl/ 11:26:49 oracle@oracle[/data/oracle/oradata/orcl] ls control01.ctl  redo02.log  scott.dbf     system01.dbf  undotbs01.dbf  redo01.log     redo03.log  sysaux01.dbf  temp01.dbf    users01.dbf oracle@oracle[/data/oracle/oradata/orcl] rm -rf   oracle@oracle[/data/oracle/oradata/orcl] sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 29 11:27:24 2021 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> shutdown abort; ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area  835104768 bytes Fixed Size     2257840 bytes Variable Size   700451920 bytes Database Buffers   125829120 bytes Redo Buffers     6565888 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/data/oracle/oradata/orcl/ SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> alter database create datafile '/data/oracle/oradata/orcl/; Database altered. SQL> recover datafile '/data/oracle/oradata/orcl/; Media recovery complete. SQL> alter database open; Database altered. SQL> conn scott/tiger; Connected. SQL> select count(*) from t1;   COUNT(*) ---------- 14二、非归档模式下的恢复 SQL> archive log list; Database log mode         No Archive Mode Automatic archival         Disabled Archive destination        USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     608 Current log sequence        610 SQL> conn scott/tiger; Connected. SQL> select table_name,tablespace_name from user_tables; TABLE_NAME        TABLESPACE_NAME ------------------------------ ------------------------------ DEPT        USERS EMP        USERS BONUS        USERS SALGRADE        USERS TB        USERS TA        USERS TEST        USERS SCOTT_TEST        SCOTT T1        WWW T_EX_USER 10 rows selected. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options oracle@oracle[~] cd /data/oracle/oradata/orcl/ oracle@oracle[/data/oracle/oradata/orcl] ls control01.ctl  redo02.log  scott.dbf     system01.dbf  undotbs01.dbf  redo01.log     redo03.log  sysaux01.dbf  temp01.dbf    users01.dbf oracle@oracle[/data/oracle/oradata/orcl] rm -rf   oracle@oracle[/data/oracle/oradata/orcl] sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 30 09:19:49 2021 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing optionsoracle@oracle[/data/oracle/oradata/orcl] ps -ef|grep dbw0oracle   23704     1  0 7月29 ?       00:00:04 ora_dbw0_orcloracle   30381 29991  0 09:21 pts/0    00:00:00 grep --color=auto dbw0oracle@oracle[/data/oracle/oradata/orcl] cd /proc/23704/fdoracle@oracle[/proc/23704/fd] ll总用量 0lr-x------ 1 oracle oinstall 64 7月  30 09:21 0 -> /dev/nulll-wx------ 1 oracle oinstall 64 7月  30 09:21 1 -> /dev/nulllrwx------ 1 oracle oinstall 64 7月  30 09:21 10 -> /data/oracle/product/11.2.0/db_1/dbs/lkORCLlr-x------ 1 oracle oinstall 64 7月  30 09:21 11 -> /data/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msbl-wx------ 1 oracle oinstall 64 7月  30 09:21 2 -> /dev/nulllrwx------ 1 oracle oinstall 64 7月  30 09:21 256 -> /data/oracle/oradata/orcl/control01.ctllrwx------ 1 oracle oinstall 64 7月  30 09:21 257 -> /data/oracle/fast_recovery_area/orcl/control02.ctllrwx------ 1 oracle oinstall 64 7月  30 09:21 258 -> /data/oracle/oradata/orcl/system01.dbflrwx------ 1 oracle oinstall 64 7月  30 09:21 259 -> /data/oracle/oradata/orcl/sysaux01.dbflrwx------ 1 oracle oinstall 64 7月  30 09:21 260 -> /data/oracle/oradata/orcl/undotbs01.dbflrwx------ 1 oracle oinstall 64 7月  30 09:21 261 -> /data/oracle/oradata/orcl/users01.dbflrwx------ 1 oracle oinstall 64 7月  30 09:21 262 -> /data/oracle/oradata/orcl/scott.dbflrwx------ 1 oracle oinstall 64 7月  30 09:21 263 -> /data/oracle/oradata/orcl/ (deleted)lrwx------ 1 oracle oinstall 64 7月  30 09:21 264 -> /data/oracle/oradata/orcl/temp01.dbflr-x------ 1 oracle oinstall 64 7月  30 09:21 3 -> /dev/nulllr-x------ 1 oracle oinstall 64 7月  30 09:21 4 -> /dev/nulllr-x------ 1 oracle oinstall 64 7月  30 09:21 5 -> /dev/nulllr-x------ 1 oracle oinstall 64 7月  30 09:21 6 -> /data/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msblr-x------ 1 oracle oinstall 64 7月  30 09:21 7 -> /proc/23704/fdlr-x------ 1 oracle oinstall 64 7月  30 09:21 8 -> /dev/zerolrwx------ 1 oracle oinstall 64 7月  30 09:21 9 -> /data/oracle/product/11.2.0/db_1/dbs/hc_orcl.da oracle@oracle[/proc/23704/fd] cp 263 /data/oracle/oradata/orcl/ oracle@oracle[/proc/23704/fd] sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 30 09:24:02 2021 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area  835104768 bytes Fixed Size     2257840 bytes Variable Size   700451920 bytes Database Buffers   125829120 bytes Redo Buffers     6565888 bytes Database mounted. Database opened. 也可以先启动到 mount 状态再 recover datafile ' /data/oracle/oradata/orcl/';alter database open; SQL> conn scott/tiger; Connected. SQL> select table_name,tablespace_name from user_tables;  TABLE_NAME        TABLESPACE_NAME ------------------------------ ------------------------------ DEPT        USERS EMP        USERS BONUS        USERS SALGRADE        USERS TB        USERS TA        USERS TEST        USERS SCOTT_TEST        SCOTT T1        WWW T_EX_USER 10 rows selected. SQL> select count(*) from t1;   COUNT(*) ---------- 14 非归档模式下恢复测试须注意：当前所使用 redo 日志不能被覆盖(不能多次切换日志)，否则用本方法无法恢复。