CODEWITHSUNDEEP
databaseoracle-database
Jay J
Jay J

Reputation: 61

ORACLE Database | remove tablespace with missing datafile

I mistakenly remove the datafiles before I drop the tablespace. But the tablespace occupy a large size space. I need to remove it, any method?

It occur:

DROP TABLESPACE abc;

*

ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 8: '/data/oradata/oracle/abc.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Upvotes: 4

Views: 14834

Answers (3)

Hybris95
Hybris95

Reputation: 2400

If your datafile is held inside a PDB.
You will have to follow the next commands :

SHUTDOWN ABORT
STARTUP
ALTER PLUGGABLE DATABASE $MyPDB OPEN;

On this last command it should fail with an ORA-01110 error.
And if you try ALTER DATABASE DATAFILE $datafileNumber OFFLINE DROP;
You will encounter an ORA-01516.

This is because you are trying to DROP a datafile on the CDB instead of the PDB.

To do this properly, you have to modify the session to target PDB :

ALTER SESSION SET CONTAINER=$MyPDB;

Now you can drop the datafile and open the database :

ALTER DATABASE DATAFILE $datafileNumber OFFLINE DROP;
ALTER PLUGGABLE DATABASE $MyPDB OPEN;

References

https://blogs.oracle.com/robertgfreeman/pdb-recovery-your-pdb-wont-open-because-a-datafile-is-missing

Upvotes: 4

Moudiz
Moudiz

Reputation: 7377

try to recover the datafile , identefy the name of the tablespace

select tablespace_name from dba_data_files where file_id =  8;

change the status of the tablespace to offline , so you can run the RMAN(recovery manager)

alter tablespace test offline immediate;

after that you have to run the RMAN to recovery the file.. for more info how to do that check this read this more about RMAN Burleson

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172618

You can follow the steps given here in this Oracle forum:

Follow the below steps : -

1) Shutdown abort

2) sqlplus sys/xxx as sysdba

3) Alter database mount

4) alter database datafile '' offline drop;

5) Alter database open

Upvotes: 2

Related Questions