Oracle误删除表空间-恢复方式（一）
针对Oracle 11.2.0.4 单实例版本测试
站在用户的角度思考问题，与客户深入沟通，找到永丰网站设计与永丰网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：成都网站建设、成都网站制作、企业官网、英文网站、手机端网站、网站推广、域名注册、网站空间、企业邮箱。业务覆盖永丰地区。
演示过程：
误操作删除表空间。前提是 没有重启库。
[oracle@oracle fd]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 16:39:21 2017
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, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/sales/system01.dbf
/u01/app/oracle/oradata/sales/sysaux01.dbf
/u01/app/oracle/oradata/sales/undotbs01.dbf
/u01/app/oracle/oradata/sales/users01.dbf
/u01/app/oracle/oradata/sales/example01.dbf
SQL> host rm /u01/app/oracle/oradata/sales/users01.dbf
SQL> create table t tablespace users as select * from dual; ------>报错了
create table t tablespace users as select * from dual
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/sales/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[oracle@oracle ~]$ ps -ef |grep dbw0 |grep -v grp
oracle 2765 1 0 16:19 ? 00:00:00 ora_dbw0_sales
oracle 3041 3017 0 16:41 pts/1 00:00:00 grep dbw0
[oracle@oracle ~]$ cd /proc/2765/fd
[oracle@oracle fd]$ ls -l
total 0
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Sep 18 16:32 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 10 -> /u01/app/oracle/product/11.2.0.1/db_1/dbs/lkSALES
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 11 -> /u01/app/oracle/product/11.2.0.1/db_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Sep 18 16:32 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 256 -> /u01/app/oracle/oradata/sales/control01.ctl
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 257 -> /u01/app/oracle/fast_recovery_area/sales/control02.ctl
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 258 -> /u01/app/oracle/oradata/sales/system01.dbf
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 259 -> /u01/app/oracle/oradata/sales/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 260 -> /u01/app/oracle/oradata/sales/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 261 -> /u01/app/oracle/oradata/sales/users01.dbf (deleted) ----->有显示
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 262 -> /u01/app/oracle/oradata/sales/example01.dbf
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 263 -> /u01/app/oracle/oradata/sales/temp01.dbf
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 6 -> /u01/app/oracle/product/11.2.0.1/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 7 -> /proc/2765/fd
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 9 -> /u01/app/oracle/product/11.2.0.1/db_1/dbs/hc_sales.dat
[oracle@oracle fd]$ cp 261 /u01/app/oracle/oradata/sales/users01.dbf
[oracle@oracle fd]$ ls -l
total 0
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Sep 18 16:32 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 10 -> /u01/app/oracle/product/11.2.0.1/db_1/dbs/lkSALES
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 11 -> /u01/app/oracle/product/11.2.0.1/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Sep 18 16:45 12 -> socket:[18634]
l-wx------ 1 oracle oinstall 64 Sep 18 16:32 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 256 -> /u01/app/oracle/oradata/sales/control01.ctl
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 257 -> /u01/app/oracle/fast_recovery_area/sales/control02.ctl
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 258 -> /u01/app/oracle/oradata/sales/system01.dbf
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 259 -> /u01/app/oracle/oradata/sales/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 260 -> /u01/app/oracle/oradata/sales/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 261 -> /u01/app/oracle/oradata/sales/users01.dbf
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 262 -> /u01/app/oracle/oradata/sales/example01.dbf
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 263 -> /u01/app/oracle/oradata/sales/temp01.dbf
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 6 -> /u01/app/oracle/product/11.2.0.1/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 7 -> /proc/2765/fd
lr-x------ 1 oracle oinstall 64 Sep 18 16:32 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Sep 18 16:32 9 -> /u01/app/oracle/product/11.2.0.1/db_1/dbs/hc_sales.dat
[oracle@oracle fd]$
SQL> alter database datafile 4 offline;
Database altered.
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
SQL> desc v$datafile;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
FOREIGN_DBID NUMBER
FOREIGN_CREATION_CHANGE# NUMBER
FOREIGN_CREATION_TIME DATE
PLUGGED_READONLY VARCHAR2(3)
PLUGIN_CHANGE# NUMBER
PLUGIN_RESETLOGS_CHANGE# NUMBER
PLUGIN_RESETLOGS_TIME DATE
SQL> select FILE# , STATUS ,name from v$datafile;
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
1 SYSTEM
/u01/app/oracle/oradata/sales/system01.dbf
2 ONLINE
/u01/app/oracle/oradata/sales/sysaux01.dbf
3 ONLINE
/u01/app/oracle/oradata/sales/undotbs01.dbf
FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
4 ONLINE
/u01/app/oracle/oradata/sales/users01.dbf
5 ONLINE
/u01/app/oracle/oradata/sales/example01.dbf
SQL>
本文标题：Oracle误删除表空间-恢复方式（一）
分享网址：http://ybzwz.com/article/gisjgg.html
