Oracle erroneous deletion of tablespaces-recovery method (1)
Test for single instance version of Oracle 11.2.0.4
Demo process:
Erroneous operation to delete tablespace. As long as there is no restart of the library.
[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;-> wrong report
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 *
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
Lmurwx-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
Lmurwx-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)-> displayed
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
Lmurwx-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]
Lmurwx-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 >
