Oracle Database can be migrated Non ASM to ASM storage using RMAN command, asmcmd and dbms_file_transfer. With 12c onwards, we can even migrate using the online move datafile command to minimize the downtime. Here in this post, I am concentrating on RMAN methods.
How to migrate Oracle database to ASM storage using RMAN
There are two cases to migrate database to ASM storage
(1) When you have enough disk space to hold the entire database both in non-ASM storage and in ASM storage
(2) When you do not have enough disk space to have a complete copy of your database in ASM and another in non-ASM storage
Case 1
(a) The process described in this section is intended to minimize downtime for your database
(b) Back up your database files as copies to the ASM disk group.
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+DATA' TAG 'ASM_MIGRATION';
You can perform this backup with multiple channels to improve performance, depending upon your hardware configuration.
For example:
run
{allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate channel d8 type disk;
allocate channel d9 type disk;
allocate channel d10 type disk;
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+DATA' TAG 'ASM_MIGRATION;
}
This backup may take a long time, depending upon the size of your database. If there has been a lot of activity on the database during the time the backup was created, you may wish to use the following procedure to create an incremental backup of the database afterwards, to refresh the copy with changes since the migration process started. If so, use the following script:
RMAN> backup incremental level 1 for recover of copywith tag 'ASM_MIGRATION' database ; RMAN> recover copy of database with tag 'ASM_MIGRATION';
This minimizes the time required for the media recovery performed just before the copy of the database in ASM is opened at the end of the migration process. You may also want to perform this step using multiple channels, if using them improves performance in your environment.
(c) Create spfile on ASM
create spfile='+DATA/spfile' from pfile='/ora/init.ora';
(d) shutdown immediate
(e) startup nomount with new spfile
( f) Change the control to new Path
alter system set control_files='+DATA/control1.f','+FLASH/controlt2.f' scope=spfile sid='*';
(g) Change the DB_RECOVERY_FILE_DEST_SIZE
alter system set DB_RECOVERY_FILE_DEST_SIZE=1000G SID=''; alter system set DB_RECOVERY_FILE_DEST='+FLASH' SID='';
( h) Now perform following steps
shutdown immediate; startup nomount PFILE='/tmp/pfile.ora'; using ASM SPFILE now restore controlfile from '/ora/controlt1.f'; alter database mount; switch database to copy; recover database; alter database open;
(i) Change the tempfile and redo log to ASM group using following command
Tempfile
SQL> alter tablespace TEMP add tempfile '+DATA' SIZE 100M; Drop any existing tempfile on the old diskgroup SQL> alter database tempfile '/ora/temp01.dbf' drop;
Redolog
Find out how many members we have have in oracle redo log groups, make sure that we have only one member in each log group.(drop other members).
Suppose we have 3 log groups, then add one member to each log group as following:
SQL> alter database add logfile member '+DATA' to group 1; SQL> alter database add logfile member '+DATA' to group 2; SQL> alter database add logfile member '+DATA' to group 3;
Then we can drop the old logfile member from earlier diskgroups as:
SQL> alter database drop logfile member 'complete_name';
(j) Use following query to see if all the database related files are on ASM
SQL> select name from v$controlfile union select name from v$datafile union select name from v$tempfile union select member from v$logfile union select filename from v$block_change_tracking;
(k) The above procedure can be used to move the database from one ASM diskgroup to another diskgroup
Case II
(a) Take the cold backup of database to tape
(b) Drop the database
(c) Create ASM diskgroup
(d) Restore the database on ASM Diskgroup using RMAN
I hope you are clear with the steps on How to migrate Oracle database to ASM storage using RMAN. if we have doubts or feedback, please do comment on it
All the Oracle ASM related articles
Automatic Storage Management
ASM disks
ASM Diskgroups
ASM Failure Groups and CSS
ASM Parameters
ASM Rebalance
ASM Metadata
Oracle ASM queries
Oracle ASM best practice to add disk
https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmasm.htm#BRADV12000