X

Alejandro Vargas' Blog

  • June 19, 2007

Moving a datafile from File System to ASM

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

Moving datafiles from file system to ASM can be done in different ways, one of them using the RMAN copy command.

These are the steps:

1. Check where to build a new file system based tablespace:

[oracle@rac1 ~]$ cd /u01/oradata/racdb
[oracle@rac1 ~]$ df -k .
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/vx/dsk/u01
                      31457280  17540576  13819976  56% /u01

2. Connect to sqlplus and create a new tablespace

[oracle@rac1 racdb]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 19 06:07:50 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> create tablespace trtst datafile '/u01/oradata/racdb/trtst01.dbf' size 150M;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name ='TRTST';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/racdb/trtst01.dbf

3. take the tablespace offline, you may take offline a single datafile from a multifile tablespace if required.

SQL> ALTER TABLESPACE TRTST OFFLINE;

Tablespace altered.

4. Check where are your datafiles located on ASM

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
+DATADG/racdb/datafile/users.259.623629857
+DATADG/racdb/datafile/sysaux.257.623629849
+DATADG/racdb/datafile/undotbs1.258.623629855
+DATADG/racdb/datafile/system.256.623629845
+DATADG/racdb/datafile/undotbs2.261.623630209
/u01/oradata/racdb/trtst01.dbf

6 rows selected.

5. Log out from sqlplus, start an RMAN session and execute the copy command

Note that when giving the destination inside ASM you just need to pass the disk group name when using omf (Oracle Managed Files) that is the best practice in ASM.

SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@rac1 racdb]$ rman target / nocatalog

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Jun 19 06:12:14 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: RACDB (DBID=621246832)
using target database control file instead of recovery catalog

RMAN> copy datafile '/u01/oradata/racdb/trtst01.dbf' to '+DATADG';

Starting backup at 19-JUN-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/racdb/trtst01.dbf
output filename=+DATADG/racdb/datafile/trtst.263.625644857 tag=TAG20070619T061416 recid=1 stamp=625644858
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 19-JUN-07

RMAN> exit


Recovery Manager complete.

6. Start an sqlplus session, rename the old file to the new ASM file

[oracle@rac1 racdb]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 19 06:15:11 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter database rename file '/u01/oradata/racdb/trtst01.dbf' to '+DATADG/racdb/datafile/trtst.263.625644857';

Database altered.

SQL> alter tablespace trtst online;

Tablespace altered.

7. remove the old file

rm /u01/oradata/racdb/trtst01.dbf

8. Create a test object

SQL> create table testtb tablespace trtst as select * from dba_source;

Table created.

End of the Example


Join the discussion

Comments ( 9 )
  • Steve Swartzlander Wednesday, June 20, 2007
    I see no reference to a COPY command in the RMAN documentation. Is it the same as BACKUP AS COPY? If so, are there any issues with RMAN recording the target file as an available backup for future recoveries?
  • Virag Sharma Monday, July 2, 2007
    Nice article , Thanks
  • ricky Wednesday, July 4, 2007
    Also you can copy a file from ASM to File System using the package DBMS_DISKGROUP.
    DBMS_DISKGROUP.open();
    DBMS_DISKGROUP.close();
    DBMS_DISKGROUP.patchfile();
  • Steve Howard Friday, July 6, 2007
    The only thing I would add is that you don't have to go into SQL*PLUS to move the datafiles, you can do it all from RMAN. Using the example from the blogger...
    copy datafile 6 to '+DATADG';
    sql 'alter database datafile 6 offline';
    switch datafile 6 to copy;
    recover datafile 6;
    sql 'alter database datafile 6 online';
  • Vinod Monday, July 9, 2007
    Hey there,
    I have couple of questions around ASM. What if my ASM fails and how do go bout protecting my ASM instance in terms of backup and recovery and yah in a RAC environment esp?
  • Vinod Monday, July 9, 2007
    Another query - is it possible to recreate a ASM instance in case of failures - lets say my backups are corrupted ...
  • Srikanth Tuesday, July 10, 2007
    Good Article, Need some more updates regarding ASM
  • guest Wednesday, July 18, 2007
    RMAN> backup as copy datafile 5 format '+DISK1';
    Starting backup at 16-MAR-07
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00005 name=/10.2.0/db10g/TBS_GRID/mgmt.dbf
    output filename=+DISK1/emrep/datafile/mgmt_tablespace.268.617342123
    tag=TAG20070316T035521 recid=1 stamp=617342150
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
    Finished backup at 16-MAR-07
    RMAN> switch datafile 5 to copy;
    datafile 5 switched to datafile copy
    "+DISK1/emrep/datafile/mgmt_tablespace.268.617342123"
  • Susan Friday, May 8, 2009
    This is a great article. We followed the example and move the datafiles that were originally on /u01 to the ASM area '+DATA'. The process went fine.
    However, when trying to do a RMAN backup after the move, we got the following errors. Could you advice as how to resolve it?
    RMAN-06207: WARNING: 18 objects could not be deleted for DISK channel(s) due
    RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
    RMAN-06210: List of Mismatched objects
    RMAN-06211: ==========================
    RMAN-06212: Object Type Filename/Handle
    RMAN-06213: --------------- ---------------------------------------------------
    RMAN-06214: Datafile Copy +DATA/bbdev/datafile/bbadmin_indx.341.686244945
    RMAN-06214: Datafile Copy +DATA/bbdev/datafile/bbadmin_data.340.686245279
    RMAN-06214: Datafile Copy +DATA/bbdev/datafile/bb_bb60_data.342.686245653
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services