Home » RDBMS Server » Server Administration » move file system datafile to asm? (Oracld RDBMS 11.1.7, AIX 6)
move file system datafile to asm? [message #569255] Tue, 23 October 2012 13:08 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I mistakenly added a datafile to a tablespace which is asm, however the datafile was created in a default location and not the asm location:

alter tablespace pdaiidata1 add datafile '<filename>' size 2048M;


What I should have done:

alter tablepsace <tablespace_name> add datafile '+DATA1' size 2048M;


Is there any way to move this filesystem datafile into the asm tablespace? In previous Oracle versions, I've taken a tablespace offline, moved a datafile, renamed it, then brought the tablespace back online. Can I do something similar here in this situation?

Thanks.
Re: move file system datafile to asm? [message #569257 is a reply to message #569255] Tue, 23 October 2012 13:27 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
What if I do this?

alter tablespace <tablespace_name> offline;


$asmcmd

asmcmd> cp <filename> '+DATA1';


alter database rename file '<filename>' to '+DATA1/<tablespace_name>';
alter tablespace <tablespace_name> online;


Re: move file system datafile to asm? [message #569259 is a reply to message #569257] Tue, 23 October 2012 13:45 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
That should work no problem. Except that you will need to put some thought into the destination filename. ASM will generate the underlying ASM filename, but you should provide a meaningful alias which will be externally visible. Perhaps,

asmcmd> cp <filename> '+DATA1/<databasename>/datafiles/<tablespacename>01.dbf'

or whatever: something that will match whatever naming convention is in place already.
Re: move file system datafile to asm? [message #569264 is a reply to message #569259] Tue, 23 October 2012 14:50 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Ok, my test worked:

create tablespace tony_test datafile '+ORADATA1' size 100k;

alter tablespace tony_test add datafile 'tony_test2' size 100k;

alter tablespace tony_test offline;

echo $ASM_SID
export ORACLE_SID=+ASM1
export ORACLE_HOME=/u01/oracle/product/11.2.0/grid

asmcmd
> cp /opt/oracle/product/11.2.0/db_1/dbs/tony_test2 +ORADATA1/DIR01/DATAFILE

export ORACLE_SID=dnovo011
export ORACLE_HOME=/opt/oracle/product/11.2.0/db_1
alter database rename file '/opt/oracle/product/11.2.0/db_1/dbs/tony_test2' to '+ORADATA1/dir01/datafile/tony_test2';

alter tablespace tony_test online;

drop tablespace tony_test including contents and datafiles;



Now, when I look in the original dbs directory, the tony_test2 file is still there:

ls -lhrt /opt/oracle/product/11.2.0/db_1/dbs
total 136K
-rw-r--r-- 1 oracle oinstall 2.8K May 15  2009 init.ora
-rw-r----- 1 oracle dba      112K Oct 23 15:15 tony_test2


Should this file be dropped when I issued the 'drop tablespace' command? Or, when I issued the 'cp' within asmcmd, was the file copied to the new location, rendering the original tony_test2 obsolete?

Thanks.
Re: move file system datafile to asm? [message #569266 is a reply to message #569264] Tue, 23 October 2012 14:53 Go to previous message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
Cool! Just delete the original file.
Previous Topic: not able to print if login as sqlplus username@RMSPRD
Next Topic: Oracle patch information
Goto Forum:
  


Current Time: Mon Jul 28 18:16:37 CDT 2014

Total time taken to generate the page: 0.24797 seconds