| move file system datafile to asm? [message #569255] |
Tue, 23 October 2012 13:08  |
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  |
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 |
John Watson
Messages: 8960
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
