Home > oracle > Drop a datafile in oracle 10g RAC with ASM

Drop a datafile in oracle 10g RAC with ASM

By mistake, i added a datafile in an ASM+ at a wrong location.

ALTER TABLESPACE "TEST" ADD DATAFILE '+DG_MIKE_DATA1/mike_03.dbf' SIZE 1G

The correct ASM path would be ‘+DG_MIKE_DATA1/mike/datafile/mike_03.dbf’

Trying to solve the mistake, i took the datafile offline (not offline normal) using the Enterprise Manager console:

ALTER DATABASE DATAFILE '+DG_MIKE_DATA1/mike_03.dbf' OFFLINE;

When I tried to drop the datafile via sqlplus i get this:

SQL> ALTER TABLESPACE MIKE DROP DATAFILE '+DG_MIKE_DATA1/mike_03.dbf';
ERROR at line 1:
ORA-01113: file 76 needs media recovery
ORA-01110: data file 76:
'+DG_MIKE_DATA1/mike_03.dbf'

Thus, I’ve found this blog http://esemrick.blogspot.com/2006/03/recovery-of-offline-data-files.html and applied his knowledge to my mistake:

SQL> select status from v$datafile where file#=76;
STATUS
----------
RECOVER
SQL> recover datafile 76;

with all the suggested logs to take back offline the datafile:

SQL> select status from v$datafile where file#=76;
STATUS
----------
OFFLINE
SQL> alter database datafile 76 online;
SQL> alter tablespace TEST DROP DATAFILE 76;

And now I can re-create the datafile in it’s correct location:

ALTER TABLESPACE "TEST" ADD DATAFILE '+DG_MIKE_DATA1/mike/datafile/mike_03.dbf' SIZE 1G

[UPDATED 2010-08-17]

Be careful if you drop a datafile on your primary database (in my case a RAC database) and  you have a standby database (dataguard). Now I have the bug 5623467, “Corrupt redo from ALTER TABLESPACE DROP DATAFILE”, and I think the only workaround is to restore&recover the standby database from the primary database 😦

Advertisements
Categories: oracle Tags: , , , , , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: