RAC: How to move a datafile that was added by mistake on local storage to shared location (Doc ID 1678747.1)

Last updated on OCTOBER 13, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Goal

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms