Moving Accidentally Created Datafile on Local File System in RAC to the Shared File System

 ORA-01157 is a common incident that happens when a DBA accidentally creates a datafile for a cluster database on the local file system of one of the nodes instead of creating on a shared file system or ASM. In this case, all instances other than the instance/node where fiel was locally created would report ORA-01157 and ORA-01110. Alert log file would show entires similar to the following.

Thu May 03 03:52:58 2018 LNS: Standby redo logfile selected for thread 2 sequence 43145 for destination LOG_ARCHIVE_DEST_2 Thu May 03 03:53:04 2018 Archived Log entry 232402 added for thread 2 sequence 43144 ID 0x930f2233 dest 1: Thu May 03 03:58:53 2018 Errors in file /u01/app/oracle/base/diag/rdbms/MYDB/MYDB2/trace/MYDB2_m000_71472.trc: ORA-01157: cannot identify/lock data file 207 - see DBWR trace file ORA-01110: data file 15: '/u01/app/oracle/product/12.2/db/dbs/mydata01.dbf' Thu May 03 04:07:50 2018 Thread 2 advanced to log sequence 43146 (LGWR switch)   Current log# 16 seq# 43146 mem# 0: /u03/MYDB/onlinelog/o1_mf_16__1488204883019347_.log   Current log# 16 seq# 43146 mem# 1: /u03/MYDB/onlinelog/o1_mf_16__1488204888783016_.log Thu May 03 04:07:50 2018

There are two ways to move this file to the shared file system or ASM. First is by re-creating this datafile. I would recommend you this method only if you have become aware of this mistake soon after adding the datafile to the local file system of a RAC node. For example, you realized your mistake within a few minutes or a couple of hours. In this case, you may re-create your datafile on a shared file system or ASM (whatever you are using) and perform recovery. If this datafile was created a few days ago and then you realized the problem, I would suggest not use this method, but a method I would explain later along with the reason of doing so

Recreating datafile on the correct location(Shared file system or ASM)

SQL> select online_status,file_name,bytes from dba_data_files where file_id=15;   ONLINE_ FILE_NAME                                                                      BYTES ------- ----------------------- -----------------------------           ---------------------- ---------- SYSTEM  ‘/u01/app/oracle/product/12.2/db/dbs/mydata01.dbf'         2956984320

Take datafile offline and recreate it to the correct location

SQL> alter database datafile '/u01/app/oracle/product/12.2/db/dbs/mydata01.dbf'' offline;   Database altered.   SQL> alter database create datafile '/u01/app/oracle/product/12.2/db/dbs/mydata01.dbf’ as /u05/mydb/datafile/mydata01.dbf’ size 2956984320;   Database altered.

ORA-01136: specified size of file 207 (6400 blocks) is less than original size of 65536 blocks

You need to dspecify exact size of dataile as returned in the first query above, otherwise you would face ORA-01336 error. I tested this command in 12c without “size” clause, and it worked perfectly fine. You need to use “size” clause in older than 12c versions.

If you are using OMF (Oracle managed files), you do not need to specify path after “as” clause as Oracle already know where to create datafiles.

For ASM, only use ASM diskgroup goroung without any path, after “as” clause.

Recover datafile and bring it online

SQL> recover datafile 15; Media recovery complete.   SQL> alter database datafile 15 online.   Media recovery complete.

As I stated above, you could use this method of recreating datafile only if you added datafile on local file system very recently. If a couple of days or more have passed, this process would need to restore all the archived log files since the datafile was created and to recover the datafile. Therefore, if a lot of time has passed, create a copy of this file on the shared file system or ASM using RMAN COPY command, and then use RMAN SWITCH command to switch to copy. Following is the procedure to do this.

SQL> alter database datafile '/u01/app/oracle/product/12.2/db/dbs/mydata01.dbf'' offline;

Copy datafile to the shared file system or ASM. If you want to copy on ASM, you can use cp command available in ASM. Copy to ASM would generated ASM compatible name of the datafile.

Execute rename command, perform recovery and bring the file online.

SQL> alter database rename file from  '/u01/app/oracle/product/12.2/db/dbs/mydata01.dbf'' to ‘/u05/mydb/datafile/mydata01.dbf’;   SQL> recover datafile 15;   SQL> alter database datafile '//u05/mydb/datafile/mydata01.dbf' online.