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

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://azwzsj.com/article/gisjgg.html

Oracle误删除表空间-恢复方式（一）

其他资讯