How to move a datafile that was added by mistake on local storage to shared location

How to move a datafile that was added by mistake on local storage to shared location

In Real Application Cluster (RAC) environments the datafiles need to be on the shared storage. It is possible that a datafile gets added to a tablespace on the local filesystem instead of the shared storage subsystem by mistake. 

When another instance tries to contact the local file it will error out with:

ORA-01157: cannot identify/lock data file 10
ORA-01110: data file 10: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA' 

Typically this happens when the datafile needed to be added to ASM but the '+'-sign is omited when specifying the diskgroup. In this case the datafile will be created in the default directory specified by the 'db_create_file_dest' parameter which defaults to $ORACLE_HOME/dbs.

This article explains how you can resolve this issue when the database is in archivelog mode and when the database is running in noarchivelog mode.

SOLUTION

A. When the database is running in archivelog mode

Note: You need to have all the archive files since the creation of the datafile (when it was added to the tablespace)

1.  Find out the exact file name, file location, size and file number: 

SQL> select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = '';

FILE_ID FILE_NAME                                          BYTES      ONLINE_STATUS
------- -------------------------------------------------- ---------- -------------
      8 +DATA/V11R2/datafile/data.258.832695063            10485760   ONLINE
     10 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA   10485760   ONLINE          <<--- nbsp="" span="">

   

2. Put the datafile offline

SQL> alter datafile 10 offline;

3. Recreate the datafile on the shared storage, please note that you need to do this on the node where the physical file resides and you need to specify the size retrieved in step 1

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA' as '+DATA' size 10485760;

4. Recover the datafile

SQL> recover datafile 10;

5. Place the datafile back online

SQL> alter database datafile 10 online;

Note: In some specific or particular case the end-user might copy the physical file by mistake again to another physical location on the same or another node. When perfoming the solution there is no risk in data lost between the different versions of the physical files because the 'alter database create datafile ',  always recovers starting from the online redolog that was current at the moment the datafile was created originally, so it doesn't matter what happend with the datafile in between as long as we have all the archive logs since the datafile creation.

B. When the database is running in NOarchivelog mode

1.  Find out the exact file name, file location, size and file number  and stop the instance on the node where you added the file.

SQL> select file_id, file_name, bytes, online_status from dba_data_files where tablespace_name = '';

FILE_ID  FILE_NAME                                         BYTES     ONLINE_STATUS
-------- ------------------------------------------------- --------- -------------
       8 +DATA/V11R2/datafile/data.258.832695063           10485760  ONLINE
      10 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA  10485760  ONLINE <--- span="">

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@V11R2i1> startup mount
ORACLE instance started.

2. Move the datafile to the shared storage

  2.1 using ASMCMD cp command (11g and above)

# asmcmd
ASMCMD> cd //DATAFILE
        example: DATA/V11R2/DATAFILE
ASMCMD> cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA +DATA/V11R2/DATAFILE/test02.dbf
ASMCMD> ls -l
Type Redund Striped Time Sys Name
                                       N test02.dbf => +DATA/ASM/DATAFILE/DATA.340.849266075                    <<--- br="">DATAFILE UNPROT COARSE JUN 03 10:00:00 Y SYSAUX.261.818315029
...

  --OR--

  2.2 Using RMAN:

RMAN> copy datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA' to '+DATA';

Starting backup at 06-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 instance=V11R2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA
output file name=+DATA/V11R2/DATAFILE/DATA.340.849266075 tag=TAG20140106T132548 RECID=1 STAMP=836141162
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 06-JAN-14

3.  Mount the database:

SQL> startup mount;

ORACLE instance started.
...
Database mounted. 

4. Rename the datafile:

SQL> alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/DATA' to '+DATA/V11R2/DATAFILE/';

5. Open the DB:

SQL> alter database open;
Database altered.

6. You still need to restart the other instances as well otherwise you will still get errors when the instance is trying to access the non-existing file

========================================================================

Note---> This informationmation taken from oracle metalink. all copy rights oracle only.