Moving a datafile from File System to ASM


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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s