I received a panicked call the other day from a DBA who had a user create a new datafile on a cooked filesystem. All was not lost and it didn’t require a downtime to fix, however, it did require that tablespace to be taken offline for a time.

In all databases I create using ASM I set the parameter: DB_FILE_CREATE_DEST (and it’s cohort DB_CREATE_ONLINE_LOG_DEST_X, which I will discuss in the future) and set it to the value of the default diskgroup (in our case +DGROUP1). This makes life much easier for a dba as the file parameters are no longer needed:

 SQL> create tablespace asm_demo datafile size 20m;
 
Tablespace created.
 
SQL> select file_name from dba_data_files
  2  where tablespace_name = 'ASM_DEMO';
 
FILE_NAME
------------------------------------------------------------
+DGROUP1/qa/datafile/asm_demo.974.671209339

In the above example, if the db_create_file_dest parameter is set, the only information I need to create a tablespace is the tablespace name. Technically, even the size is not needed as a default size of 10m is used if the size is omitted.

This is all well and good, but what happens if someone comes along and creates a file the old fashioned way:

SQL> create tablespace test datafile '/home/oracle/test_01.dbf' 
  2> size 10m;

How do you get this file into asm? Elementary my dear Watson! First take the tablespace offline.

 SQL>  alter tablespace test offline;

Start RMAN and backup a copy of the datafile into ASM, since the tablespace is offline, no transactions will change the datafile. (I will leave out the extraneous rman info)

 RMAN> backup as copy tablespace 'TEST' format '+DGROUP1';

Tell the data dictionary to use the new copy.

 RMAN> switch tablespace 'TEST' to copy;
datafile 50 switched to datafile copy 
    "+DGROUP1/qa/datafile/test.973.671207181"

Finally, bring the tablespace back online, either from rman or sqlplus, and check where the datafile is no located.

SQL> alter tablespace test online;
 
Tablespace altered.
 
SQL> select file_name from dba_data_files 
  2> where tablespace_name = 'TEST';
 
FILE_NAME
-------------------------------------------------------
+DGROUP1/qa/datafile/test.973.671206913

That is it, now, admittedly taking an active tablespace offline can be a challenge and may require a downtime based on your application. But, the movement of the file into ASM is quite simple. In the end the DBA is happy and the user that created the cooked datafile was educated and given 50 lashes with a wet noodle.