Oracle erroneous deletion of tablespaces-recovery method (1) 03/29 Update SLTechnology News&Howtos

Oracle erroneous deletion of tablespaces-recovery method (1)

2025-03-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

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

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 >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.