Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Files that are created accidentally or deliberately in the file system in a database that uses ASM often cause problems.
In particular, a file created in the file system on a node in RAC databases will not be seen by the other node, so there will be problems with the operations associated with this datafile.
In this case, a file in the file system can be moved into the ASM disk group by following the steps below.
Step1:
The relevant tablespace must be offline.
1 2 3 | SQL> ALTER TABLESPACE TEST OFFLINE; Tablespace altered. |
Step2:
Find the exact path of the datafile in the file system.
1 2 3 4 5 6 7 8 9 10 | SQL> SELECT FILE_NAME FROM DBA_DATA_FILES; FILE_NAME -------------------------------------------------------------------------------- +DATA/racdb/datafile/users.259.623629857 +DATA/racdb/datafile/sysaux.257.623629849 +DATA/racdb/datafile/undotbs1.258.623629855 +DATA/racdb/datafile/system.256.623629845 +DATA/racdb/datafile/undotbs2.261.623630209 /u01/oradata/racdb/test01.dbf |
Steps3:
We copy the corresponding file into the ASM disk group (+ DATA) with rman.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [oracle@rac1 racdb]$ rman target / nocatalog Recovery Manager: Release 11.2.0.4.0 - Production on Wed Dec 14 17:58:18 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: RACDB (DBID=2708277737) using target database control file instead of recovery catalog RMAN> copy datafile '/u01/oradata/racdb/test01.dbf' to '+DATA'; Starting backup at 14-DEC-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile fno=00006 name=/u01/oradata/racdb/test01.dbf output filename=+DATA/racdb/datafile/test.263.625644857 tag=TAG20161214T061416 recid=1 stamp=625644858 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04 Finished backup at 14-DEC-16 RMAN> exit Recovery Manager complete. |
Steps4:
We’re changing the name of the relavent file with sqlplus.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [oracle@rac1 racdb]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 14 18:01:43 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> alter database rename file '/u01/oradata/racdb/test01.dbf' to '+DATA/racdb/datafile/test.263.625644857'; Database altered. |
Steps5:
We set the relevant tablespace online.
1 2 3 | SQL> alter tablespace test online; Tablespace altered. |
Steps6:
Finally, we delete the file in the file system.
1 | [oracle@rac1 racdb]$ rm /u01/oradata/racdb/test01.dbf |
When we check all datafiles again, there should be no datafile in the file system.
1 2 3 4 5 6 7 8 9 10 | SQL> SELECT FILE_NAME FROM DBA_DATA_FILES; FILE_NAME -------------------------------------------------------------------------------- +DATA/racdb/datafile/users.259.623629857 +DATA/racdb/datafile/sysaux.257.623629849 +DATA/racdb/datafile/undotbs1.258.623629855 +DATA/racdb/datafile/system.256.623629845 +DATA/racdb/datafile/undotbs2.261.623630209 +DATA/racdb/datafile/test.263.625644857 |
