Zahid Karim
Recent Posts
1.Draw plus and cross sign into PDF via PLSQL using PL_FPDF
2.Draw Triangle directly into PDF via PLSQL using PL_FPDF
3.Write fast and efficient PLSQL - DBMS_PROFILER
4.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
5.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
Find me on
How to perform Block Media Recovery using RMAN How to Configure ASM disks using ASMLib or Raw Disks

How to migrate Oracle datafiles to ASM storage via RMAN

Posted by Zahid on August 28, 2009.

This tutorial is about migrating your database files to ASM storage. It assumes that you have already have a database up and running and have just installed ASM instance. Your database was created on file system and you want to migrate it to ASM you have just installed.

NOTE: This tutorial was performed on Oracle 10g hosted on RHEL5.

$ sqlplus / as sysdba SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; ALTER DATABASE DISABLE BLOCK CHANGE TRACKING * ERROR at line 1: ORA-19759: block change tracking is not enabled /* If you are using block change tracking, then disable it. In my case I am not using it. */ SQL> alter system set db_create_file_dest='+DATA' scope=spfile; System altered. /* This parameter define the default location for data files, control_files etc, if no location for these files is specified at the time of their creation. +DATA is the diskgroup in ASM. */ SQL> alter system set db_create_online_log_dest_1='+DATA' scope=spfile; System altered. /* This parameter defines the default location number 1 for online logs. */ SQL> alter system reset control_files scope=spfile sid='*'; System altered. /* Here we remove the control_files parameter from spfile. So next time we restore the control file it will automatically go to +DATA diskgroup since it is defined in db_create_file_dest, and the new path will be automatically updated in spfile. */ SQL> exit

Once all initialization parameters are set properly, start RMAN to migrate the files to ASM diskgroups.

$ rman target / RMAN> startup nomount Oracle instance started Total System Global Area 608174080 bytes Fixed Size 1220820 bytes Variable Size 171970348 bytes Database Buffers 427819008 bytes Redo Buffers 7163904 bytes RMAN> restore controlfile from '/u01/apps/oracle/oradata/ora10g/control01.ctl'; output trimmed...... channel ORA_DISK_1: copied control file copy output filename=+DATA/ora10g/controlfile/backup.256.716296513 Finished restore at 28-AUG-09 /* Control file restored to +DATA from its old location. It will be restored to +DATA because we set db_create_file_dest to +DATA. */ RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> backup as copy database format '+DATA'; ....... input datafile fno=00001 name=/u01/apps/oracle/oradata/ora10g /system01.dbf output filename=+DATA/ora10g/datafile/system.257.716296627 tag=TAG20090828T111707 recid=2 stamp=716296715 ....... input datafile fno=00003 name=/u01/apps/oracle/oradata/ora10g /sysaux01.dbf output filename=+DATA/ora10g/datafile/sysaux.258.716296723 tag=TAG20090828T111707 recid=3 stamp=716296785 ....... input datafile fno=00005 name=/u01/apps/oracle/oradata/ora10g /example01.dbf output filename=+DATA/ora10g/datafile/example.259.716296789 tag=TAG20090828T111707 recid=4 stamp=716296809 ....... input datafile fno=00002 name=/u01/apps/oracle/oradata/ora10g /undotbs01.dbf output filename=+DATA/ora10g/datafile/undotbs1.260.716296815 tag=TAG20090828T111707 recid=5 stamp=716296822 ....... input datafile fno=00004 name=/u01/apps/oracle/oradata/ora10g /users01.dbf output filename=+DATA/ora10g/datafile/users.261.716296823 tag=TAG20090828T111707 recid=6 stamp=716296823 ....... Finished backup at 28-AUG-09 /* make a copy of all datafiles on +DATA diskgroup. */ RMAN> switch database to copy; datafile 1 switched to datafile copy "+DATA/ora10g/datafile /system.257.716296627" datafile 2 switched to datafile copy "+DATA/ora10g/datafile /undotbs1.260.716296815" datafile 3 switched to datafile copy "+DATA/ora10g/datafile /sysaux.258.716296723" datafile 4 switched to datafile copy "+DATA/ora10g/datafile /users.261.716296823" datafile 5 switched to datafile copy "+DATA/ora10g/datafile /example.259.716296789" /* Switch database to the datafile copies. After this switch the copies on +DATA have become the database datafiles and the original files have become copies. */ RMAN> alter database open; database opened RMAN> exit Recovery Manager complete.

After you open the database, go ahead and enable the block change tracking file if you disabled it in the start of this process.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Lets now verify where our control file and data files are sitting:

$ sqlplus / as sysdba SQL> select name from v$datafile; NAME ------------------------------------------------- +DATA/ora10g/datafile/system.257.716296627 +DATA/ora10g/datafile/undotbs1.260.716296815 +DATA/ora10g/datafile/sysaux.258.716296723 +DATA/ora10g/datafile/users.261.716296823 +DATA/ora10g/datafile/example.259.716296789 SQL> show parameter control_files NAME TYPE VALUE ------------------- ----------- ------------------------------ control_files string +DATA/ora10g/controlfile/backu p.256.716296513 SQL>

They are on the +DATA diskgroup in ASM storage.

Migrate tempfile to ASM storage:
SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
/u01/app/oracle/oradata/ora10g/temp01.dbf

SQL> alter tablespace temp add tempfile size 20m;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
/u01/app/oracle/oradata/ora10g/temp01.dbf
+DATA/ora10g/tempfile/temp.264.718706509

SQL> alter tablespace temp drop tempfile 
  2  '/u01/app/oracle/oradata/ora10g/temp01.dbf';

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------
+DATA/ora10g/tempfile/temp.264.718706509

SQL> 

See also:
How to install ASM instance on a non-asm Oracle database
How to configure Raw Disks in RHEL 5
How to Configure ASM disks using ASMLib or Raw Disks
How to migrate online redo logs to Oracle ASM storage
Migrate Oracle Flash Recovery Area to ASM storage via RMAN
Add ASM Instance into Enterprise Manager