Moving a datafile from File System to ASM (Alejandro Vargas' Blog)

Moving a datafile from File System to ASM

By alejandro.vargas on June 19, 2007 3:32 AM

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

Permalink

TrackBack

TrackBack URL for this entry:
http://blogs.oracle.com/mte1521/mt-tb.cgi/4319

Comments (9)

Steve Swartzlander:

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?

Posted by Steve Swartzlander | June 20, 2007 11:33 AM

Posted on June 20, 2007 11:33

Virag Sharma:

Nice article , Thanks

Posted by Virag Sharma | July 2, 2007 9:31 AM

Posted on July 2, 2007 09:31

ricky:

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();

Posted by ricky | July 4, 2007 3:01 AM

Posted on July 4, 2007 03:01

Steve Howard:

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';

Posted by Steve Howard | July 6, 2007 6:07 PM

Posted on July 6, 2007 18:07

Vinod:

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?

Posted by Vinod | July 9, 2007 6:19 AM

Posted on July 9, 2007 06:19

Vinod:

Another query - is it possible to recreate a ASM instance in case of failures - lets say my backups are corrupted ...

Posted by Vinod | July 9, 2007 6:21 AM

Posted on July 9, 2007 06:21

Srikanth:

Good Article, Need some more updates regarding ASM

Posted by Srikanth | July 10, 2007 3:41 AM

Posted on July 10, 2007 03:41

Anonymous:

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"

Posted by Anonymous | July 18, 2007 8:54 AM

Posted on July 18, 2007 08:54

Susan :

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

Posted by Susan | May 8, 2009 6:19 AM

Posted on May 8, 2009 06:19

Alejandro Vargas' Blog