Archive

Posts Tagged ‘standby’

Drop a datafile in oracle 10g RAC with ASM

August 11, 2010 Leave a comment

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: , , , , , , ,